実際のワークロードに近いSQLの負荷テストをmysqlslapのような感覚で行う方法を考える

こんにちは，S.T.です。今回は，お手軽かつ実際のワークロードに近い形でMySQLの負荷検証をする方法を考えます。お手軽にMySQLに負荷をかけるツールとしてmysqlslapがありますが，mysqlが実行するSQLはアプリケーションから実行するSQLとは性質が異なります。JOINなども絡めてアプリケーションが実行するSQLに近いSQLで負荷をかけられ，しかも簡単に実行できる，というものがあれば便利そうです。

1.mysqlslapとは

mysqlslapは，MySQLの負荷をエミュレーションできるクライアントアプリケーションです。MySQLに接続して複数のクエリを実行し，その実行時間を計測することができるツールです。インストールも簡単で，たいていはLinuxディストリビューションのパッケージマネージャ経由でMySQLをインストールすると，一緒に入ってきます。実行も簡単で，いくつかのオプションを与えてコマンドを実行するのみです。

このように簡単に使えるのは大きなメリットなのですが，もう少し複雑なことをしたいという欲が出てきます。しかし，この使い勝手を捨てることはできません。そこで，今回はできるだけこれらを両立するできる仕組みを考えてみます。

2.mysqlslapの動きを確認する

まずは，mysqlslapがどのようなテーブルにどのようなSQLを投げているのかを確認してみます。

2.1.初期データ投入とテストのSQL

mysqlslapは，実行時に数値型のカラムと文字列型のカラムをいくつ生成するかを指定することができます。例えば，それぞれ10カラムずつ指定して，AUTO INCREMENTのPKを有効にして実行すると，以下のようなテーブルが生成されます。mysqlslapは，この単一のテーブルに対して初期データを投入し，参照や更新，挿入の負荷をかけます。

CREATE TABLE `t1` (
  id serial,
  intcol1 INT(32) ,intcol2 INT(32) ,intcol3 INT(32) ,intcol4 INT(32) ,intcol5 INT(32) ,
  intcol6 INT(32) ,intcol7 INT(32) ,intcol8 INT(32) ,intcol9 INT(32) ,intcol10 INT(32) ,
  charcol1 VARCHAR(128),charcol2 VARCHAR(128),charcol3 VARCHAR(128),charcol4 VARCHAR(128),charcol5 VARCHAR(128),
  charcol6 VARCHAR(128),charcol7 VARCHAR(128),charcol8 VARCHAR(128),charcol9 VARCHAR(128),charcol10 VARCHAR(128)
)

投入される初期データは以下のようになっています。INT型，VARCHAR型ともに，ランダムな値が挿入されます。INSERTの負荷をかける際も，同様のDMLが実行されます。

INSERT INTO t1 VALUES (
  NULL,
  1804289383,846930886,1681692777,1714636915,1957747793,
  424238335,719885386,1649760492,596516649,1189641421,
  '27qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBLb97RGHZ65',
  'mNzkSrYT3zWoSbg9cNePQr1bzSk81qDgE4Oanw3rnPfGsBHSbnu1evTdFDe83ro9w4jjteQg4yoo9xHck3WNqzs54W5zEm92ikdRF48B2oz3m8gMBAl11Wy50w46i5',
  '8Giekxik0cYzfA8BZBLADEg3JhzGfZDoqvQQk0Akcic7lcJInYSsf9wqin6LDC1vzJLkJXKn5onqOy04MTw1WksCYqPl2Jg2eteqOqTLfGCvE4zTZwWvgMz4DPh7kD',
  '1E6f4MMQk1ioopsoIIcoD83DD8Wu7689K6oHTAjD3Hts6lYGv8x9G0EL0k87q8G2ExJjz2o3KhnIJBbEJYFROTpO5pNvxgyBT9nSCbNO9AiKL9QYhi0x3hL9WlwRHu',
  'Wm4HE8leYmg66uGYIp6AnAr0BDd7YmuvYqCfqp9EbhKZRSymA4wx6gpHlJHI53DetH9j7Ixar90Jey5outd1ZIAJdJTjMaD7rMiqYXHFhHaB7Xr1HKuqe51GGMrfw4',
  'egILWisfxPwOc3nJx4frnAwgI539kr5EXFbupSZelM2MHqZEmD6ZNuEZzHib8fqYuHQbdrDND8lXqIdcNbAeWOBLZlpZOX5AoNlQFzpK7QjxcLP0wbWIriYGJLqksn',
  'ug3YyANnWWDEJiRkiFC4a3e6KyJ2i3hSjksiuFLHlRXw9yhjDtnfoQd0OouyrcIbCB9zQWG4pf0yTZhaIT67nj7BY21FWJqaWrZxEh13Kt2hRbGl4MsrxsuLmvd8FJ',
  'id3GaHpRC2L6jgirPm5AW3uGGgCloJ5Ww0eNHSiLWvS5bAxto23AxxR6TXr9qofeoAtxWcJsXnxzxmsdhvoekFc5mSES8tyxvsuPK5Hjs7ihtaJaLz5xEh2s1GCA22',
  'zxutF6rOqjXYHHzSrKRwAhWCPXTdhNXYKQIRO9sEkFf1YeTGqw40Ta5u6QNfpvC1DWTTXDkFSFHtte9bbDSwgZjmryHglLhqjAKEF4MkJfT49eXcjzZNOG1F6BnsYT',
  'i8X2EnycNH7sDHMltxcILtQE0ZPoPq9zyg24J0hiAgQNpg8jedtrWK5WtXIALR9B03FJ4ou6TCTAtWtN7fETzBzkiAmvTv6LrEZn2RtNfMaOkJfjytCp54ZfEJbb7Z'
)

次に，参照系のテストのSQLを確認します。これは想像どおり，単純なSELECT文を実行しているだけです。Keyのテストの場合，WHERE句でidを指定します。テストの前に，まずidを取得するためのSELECTが走ります。そのIDをアプリケーション側で覚えておいて，順次SELECTを発行しているようです。

-- まずidを取得するクエリが走る
SELECT id from t1
-- 取得したidでSELECT
SELECT
  intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,
  charcol1,charcol2,charcol3,charcol4,charcol5,charcol6,charcol7,charcol8,charcol9,charcol10 
FROM t1 WHERE id =  '9'

テーブルスキャンの場合，WHERE句は付与されません。

SELECT 
  intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,
  charcol1,charcol2,charcol3,charcol4,charcol5,charcol6,charcol7,charcol8,charcol9,charcol10
FROM t1

最後に，更新系テストのDMLを確認します。更新はKey指定で行うため，SELECTと同様にまずidを取得してから，そのidを用いて全カラムをランダムな値で更新しています。

UPDATE t1 
SET
  intcol1 = 1948852474,intcol2 = 423918855,intcol3 = 1603287162,intcol4 = 821269768,intcol5 = 948559171,
  intcol6 = 342725411,intcol7 = 1878779232,intcol8 = 1759762165,intcol9 = 934270557,intcol10 = 1300032369,
  charcol1 = 'NOBn7JQui9gKbXCLtziXL8mvSH6ZaviyBmdJxv5f5lrg84t1vRZ8RB3wT1Tvo3l0pEJeAOI711fAxY3tzc1IgxE1qbwffifKoqzq69x72cHs0LlrpeATRGWAsJpXtK',
  charcol2 = 'jh2iY8jT7myPWyeFiRlsp58BF0WwNN87xAhTAHqHvgaPXCYDg95LE5YLy3HLIPKFRucdBJn6zLNWQvj64gJBmrYkmixxzACJ4ho70b5zpSMFNxDJeYMGFvs2TpzSr3',
  charcol3 = 'nvkc2c3zSsRq7inKuHkGZ03HuYzttqoYeP8Y14YTwzBwiz8S8tkalorfCqO5hCWBdWjXaFsNF4JpvJH3CtdpZTSmBsjkxDvQkfpIuih1msihCrkGlMyIjQwuBgf0lQ',
  charcol4 = 'QT5GgsqDlcyS92BtAw787ibvTeR8eDQwaY46GuC1MATXChHMTGXSzyOSSjS6ouuNIzLalZc8j5XveoA05ZKwNcOrKkp8ejNxalXCI7C25bpbOrRLOo9fOPWc9Blfn0',
  charcol5 = 'cDl0P4zTyw5fOLXranfjRw8xYH99owAP9nHRj8wh5tLTEsKofsXygXw4gxETlGmmv35fB2C8nZd2Hqipi6nEdbIIOOf9gPoSKt7XnJYbl9T2rBjPHNfnyyxFMcOv14',
  charcol6 = 'NLyMklZGCIIhD9twb2J6pA4mPJMhEFmTccrDLOLQbf7oh0c83oEkqBXFWtok1Q5T2wiGCwinRpR8i48lsNTiasp4eTgfwl8yZjEdfwP6ms7SoF5YeQ64Jv0pPgSDHc',
  charcol7 = 'BiBQKqEdpsTwCAB9iicpmncnctJ46TYIBccXQi87bW3nYFpEpPvb4YEgHacOv0a6SAWnseuJ0yzY5ofu5aL9ychG2Hgx9EveGPP8wJKw9JL6700CQELPquhk3DZCRS',
  charcol8 = 'ibuzjiBveKErJDrJPIPeb7OGrRTGgolENXEyDPujd0bwEI8XSZ23ZQBGugZQKAgjhmamBSLhKoTa7dyb3sXcIis4FHWbR3B0plmt50aPo3svZiNcam4kSopj6BBPEM',
  charcol9 = 'Qwz4p44FLtIDgjnvBLhFyAwnluqwJxAlt9ia5mpRXzYCbKzmZh2XjqAKk193yJhjLN9Q9qtZhQlGec3BHxj0ntDXmMQch7J2WKLYAE5sYi8AIBMc8xSvihIT3l5dsH',
  charcol10 = 'XFdsnozsgXQoakswC0lZoR6yw2j2fSJ2jonMcm6G9Yxj8pXDhGmLksKYuvQPFdSyQFC2tIB2i0erh94MQic2aoQLjtmzM6poD2j6Cm1vmfeRoih6GH0q5R3hCqYaoL'
WHERE id =  '7'

2.2.イテレーション

mysqlslapは同じテストを複数回実行して平均の結果を返してくれるのでした。MySQLの一般クエリログから，mysqlslapがイテレーションごとに実行している処理を確認します。

DROP SCHEMA IF EXISTS `mysqlslap`
CREATE SCHEMA `mysqlslap`
CREATE TABLE `t1` ...
INSERT INTO t1 ...

このように，イテレーションの度にテスト用のスキーマをDROPして作成しなおし，テーブルを作成し，初期データを投入する，という動作を行っています。更新や挿入のテストもあるため，イテレーションごとにスキーマやテーブルを使い回すことはしてないようです。

2.3.結果のレポート

mysqlslapが出力する結果のレポートを見てみましょう。mysqlslapは様々なパラメータがありますが，今回は以下に示すパラメータを用いています。

mysqlslap \
 --no-defaults --auto-generate-sql --engine=innodb --auto-generate-sql-add-autoincrement \
 --host=localhost --port=3306 -u XXXX -pXXXX \
 --number-int-cols=10 \
 --number-char-cols=10 \
 --iterations=10 \
 --concurrency=30 \
 --auto-generate-sql-write-number=1000 \
 --number-of-queries=300 \
 --auto-generate-sql-load-type=key 

INT型カラムとVARCHAR型カラムを10個持つテーブルに1000行のレコードを投入し，Key指定での読み取りのテストを行う設定です。30スレッドの並列で合計で300回のクエリを発行する，という処理を10回繰り返します。

Running for engine innodb
Average number of seconds to run all queries: 0.043 seconds
Minimum number of seconds to run all queries: 0.038 seconds
Maximum number of seconds to run all queries: 0.051 seconds
Number of clients running queries: 30
Average number of queries per client: 10

このように，実行時間の最大・最小・平均を出力してくれます。実行したスレッド数と，各スレッドが実行したSQLの数の平均も出力されています。注意が必要なのは，各スレッドが300回のクエリを実行するわけではなく，全スレッド合計で300回のクエリを実行することと，表示されている実行時間は全300回のクエリの実行に要した時間である，ということです。

3.mysqlslapができないこと

mysqlslapはコマンド一発でお手軽にクエリの実行時間を計測することができて，便利です。しかし，シンプル故にできないこともあります。とくに私が気になっているのは以下の点です。

1. 複数テーブルをJOINしたクエリを発行できない
2. 非UNIQUEのINDEXスキャンができない（複数件ヒットのテストができない）
3. ソートができない（上とほぼ同様）
4. 初期データの投入とテストで異なる接続先を指定できない
5. 書き込みと読み込みで異なる接続先を指定できない

最初の3つは，実際のワークロードに近いクエリを発行できない，という点が気になります。原理的にはKey読み取りとテーブルスキャンのテストがわかれば十分というケースも多く，実際のSQLのパフォーマンスは個別にチェックしていけば良い，というのもわかりますが，もう少し実戦で流れるクエリでテストをしたい気持ちがあります。ただし，これは–queryオプションや–createオプションで自分でテーブル定義やSELECT文を与えることで実現可能です。しかし，それらを都度準備するのは手間ですし，JOINするテーブルの数や，参照と更新を同時にテストする際の割合などを変更したい場合，ファイルで指定するのでは都合が悪いこともあります。
また，初期データ投入や読み書きの接続先を個別に設定できないのは，Galera Clusterのテストや参照を複数台に分散するプロキシを経由する場合のテストで困ってしまいます。

4.測定ツールを作ってみる

mysqlslapのようにシンプルな使い勝手で，これらの要件を満たせるツールは現状なさそうなので，自分で作ってみます。
基本的な機能はmysqlslapを踏襲しつつ，上記で紹介した課題に対応するものを作ることになりますが，いきなりすべてを実装するのは結構大変そうです。そこで，今回はJOINを含めたKey読み取りに絞って実装してみます。
実装にはScalaを用います。明確にクエリを並列実行する数を指定したいため，FixedThreadPool上でTaskを起動する方式を採用しました。

まずは，JOINのない1テーブルのKey読み取りのテストをmysqlslapと同じ条件で実行して，結果がどのくらい近いものになっているか確認してみます。今回の条件は以下の通りです。

1. INT型カラム数：10
2. VARCHAR型カラム数：10
3. レコード数：1000
4. クエリ実行数：300
5. スレッド数：10
6. MySQL：ローカルの仮想マシン上に用意したデフォルト設定の8.0.30

mysqlslapの結果は以下の通りです（先で紹介した例と同様）。mysqlslapは実行時間が秒単位で出力されます。

Running for engine innodb
Average number of seconds to run all queries: 0.043 seconds
Minimum number of seconds to run all queries: 0.038 seconds
Maximum number of seconds to run all queries: 0.051 seconds
Number of clients running queries: 30
Average number of queries per client: 10

そして，今回作成したツールによる結果は以下の通りです。こちらは，ミリ秒で表示されます。

avg: 49.3
min: 25
max: 73

自作ツールのほうが若干ばらつきが多いようですが，平均値をみると，だいたい一緒です。mysqlslapに近い条件で測定できているようです。

ツールに問題がないことがわかったところで，JOINを含めたクエリを実行してみます。mysqlslapはt1という1テーブルにクエリを流していましたが，自作ツールではt1，t2という2つのテーブルを準備して，t1にt2のidに紐づけたt2_idというカラムを作成し，INDEXを張っておきます。外部キーは設定しません。この状態で，t1をKeyで参照し，そこにt2をJOINする，というクエリを実行します。動作としては，t1をKeyで引いたあとに，さらにt2をKeyで引く，という動作になります。t2をKeyで参照する分実行時間が増えるので，倍まではいかなくても，それに近いくらい時間がかかることが予想できます。実行結果は以下の通りです。

avg: 66.8
min: 34
max: 110

平均をみると，だいたい17ms実行時間が長くなりました。実行時間は長くなりましたが，取ってくるデータの量が倍になりましたが，実行時間の増加は約35%でした。つまり，もともとの平均の実行時間であった49.3msのうち，実際にデータを取得している部分の時間は35%程度であり，残りの6割強は通信やクエリのパースなどの時間であることがわかります。

今回作成したツールは，mysqlslapと同様に，コマンド一発で実行して待っていれば結果が返ってきます。使い勝手はmysqlslapと同等で，様々な条件のクエリを実行して性能を評価するためのベースとなる機能を実装することができました。

5.まとめ

mysqlslapは気軽に使うことができる性能計測ツールですが，JOINやソートに対応していなかったり，読み書きのエンドポイントを分けたりすることができません。今回は，その課題に対応するツールづくりの最初の一歩を踏み出しました。mysqlslapと同等の使い勝手を持っており，同等の条件で近い測定結果を得ることができました。
今後，このツールを拡張し，最終的にはpublicに公開することを目標に開発を進めることが目標です。

次世代システム研究室では，グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発者の方，次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら，ぜひ募集職種一覧からご応募をお願いします。

参考

MySQL 8.0 Reference Manual / mysqlslap