インデックスをチューニングをした時に、遅いクエリに狙ってインデックスを貼ったのに使われなかったり、実は効果が微妙で影響がほとんどなかったり、悔しい思いをされることがあると思います。特に本番環境に適用後に気がついたりすると、大分悔しい気持ちになります。月次リリースの形を採用している場合には、次のリリースまであまり有効でなかったインデックスはそのままなんて事になりかねません。
そこで今回は、percona-toolkitの1つであるpt-upgradeを利用して、インデックスチューニングの成果を確認できる方法を紹介したいと思います。
検証環境
今回は2台データを用意して差分を確認していきたいので、以下のようにデータを用意していきます。ローカルで2台MySQLを動かしたいのでDockerを利用します。DockerHub上のMySQLイメージを利用して行います。バージョンは8.0.18を使用しています。
第113回 anemoeaterを使ってスローログを可視化してみるの環境設定で設定した方法と同様に、スロークエリーログが出力できる状態にしておきます。
以下のように2個コンテナを建てます。michibushin2のコンテナでは、今回スロークエリーログを出力しなくても良いのでマウントをしていません。
検証用のデータとして、第2回 MySQLにはじめてのデータを入れてみるからKEN_ALL.CSVを利用して作成していきます。
今回はpercona-toolkitを利用するのですが、macOSにHomebrewを利用してインストールしたものを利用していきます。
pt-upgradeとは?
今回紹介するpt-upgradeは、本来の使い方としては異なる2バージョンのMySQL間での互換性をチェックするためのツールで、かかった時間やバージョン間で同じクエリを実行した時にエラーになるならないなどの違いを検出してくれるツールになります。仕組みとしては、保存しているSQLを2つのデータベースで実行して結果の差分を取るという仕組みになっています。
注意としては、ベンチマークツールの一種なので、本番環境などで実行するのはなるべく止めたほうが良いでしょう。
pt-upgradeの利用方法は以下の通りです。
OPTIONSで指定できる値を詳しく知りたい場合はpercona-toolkitのpt-upgradeのドキュメントを見るとわかります。
LOGS|RESULTSに関しては、こちらはMySQLの各種ログファイル(スロークエリーログ、ジェネラルログ、バイナリログ、tcpdump、生のSQL)等が選択できます。デフォルトではスロークエリーログ形式が選択されていて、違う形式のものを利用したい場合は--type
オプションで変更することができます。今回はスロークエリーログをそのまま利用するので特に指定しません。
RESULTSに関しては、こちらはMySQLを1台で実行する際に使う仕組みで、変更前のデータを保存して指定することができます。
DSNはデータベースを指定するための記述方法で、option=value
という形で指定できます。複数のパラメータを指定する場合は,
で空白を入れないようにします。
今回使用オプションは以下のものになります。
オプション | 説明 |
D | データベース名 |
h | ホスト名 |
p | password |
P | Port指定 |
u | user名 |
今回使用していないオプションなどもあるので、ぜひ公式のドキュメントを参照してください。
実際に動かしてみる
今回は、先ほど解説したスロークエリーログを利用する方法を紹介します。
まずはスロークエリーログに値を出力したいので、以下のようなコマンドを実行しましょう。一度中身を綺麗にしてから進めていきたいので、中身を一度消してから実行します。
さて、スロークエリーログが出力していることを確認してみましょう。
出力されている事が確認できました。
次にとりあえず比較をしてみましょう。以下のコマンドを実行します。
出力結果を解説していきます。
このセクションでは使用したLogファイルとファイルサイズが記載されています。
このセクションでは接続先のDBの情報が表示されています。
最後にこのセクションでは実行されたSQLの情報が確認できます。今回は変更が無いことがわかります。
indexを貼って比較をしてみる
ポート番号が3307のMySQLにindexを貼って比較をしてみましょう。
この状態で、またpt-upgradeを実行してみましょう。
上記のブロックが増えたことがわかると思います。インデックスを貼ったポート番号3307のMySQLでの実行結果を基準にすると、ポート番号3306のMySQLが46.5倍遅いことがわかります。ということで、このインデックスは効果的であることがわかります。
ここでDSNを逆にして確認をしましょう。
実行結果は省略しますが、こちらではQuery classの項目が表示されないはずです。遅くなっていると表示されている場合は、インデックスを貼り直した結果遅くなっているクエリがあることになります。
このように、同じバージョンでもインデックスチューニングを行った際の計測にpt-upgradeが使用できました。
まとめ
今回は、pt-upgradeを使ってインデックスチューニングをした際に効果を測定するのに利用してみました。チューニングが空振った時に悲しい思いをしないためにも、実践する前に一度確認してみると良いと思います。
pt-upgradeは、本文にも記載したとおり本来MySQLのバージョン違いを確認するためのツールなのですが、インデックスの効果を確認するのにも便利なので、皆さんも使ってみてはいかがでしょうか。