MySQL道普請便り

第116回 pt-upgradeを使ってインデックスチューニングの成果を確認する

この記事を読むのに必要な時間:およそ 3 分

インデックスをチューニングをした時に,遅いクエリに狙ってインデックスを貼ったのに使われなかったり,実は効果が微妙で影響がほとんどなかったり,悔しい思いをされることがあると思います。特に本番環境に適用後に気がついたりすると,大分悔しい気持ちになります。月次リリースの形を採用している場合には,次のリリースまであまり有効でなかったインデックスはそのままなんて事になりかねません。

そこで今回は,percona-toolkitの1つであるpt-upgradeを利用して,インデックスチューニングの成果を確認できる方法を紹介したいと思います。

検証環境

今回は2台データを用意して差分を確認していきたいので,以下のようにデータを用意していきます。ローカルで2台MySQLを動かしたいのでDockerを利用します。DockerHub上のMySQLイメージを利用して行います。バージョンは8.0.18を使用しています。

第113回 anemoeaterを使ってスローログを可視化してみるの環境設定で設定した方法と同様に,スロークエリーログが出力できる状態にしておきます。

以下のように2個コンテナを建てます。michibushin2のコンテナでは,今回スロークエリーログを出力しなくても良いのでマウントをしていません。

$ docker run --name michibushin -v "/tmp:/tmp" -v "$PWD/config:/etc/mysql/conf.d" -v "$PWD/logs/mysql:/var/log/mysql"  -e MYSQL_ROOT_PASSWORD=my-secret-pw -d -p 3306:3306  -d mysql:8.0.18 --secure-file-priv=/tmp
$ docker run --name michibushin2 -v "/tmp:/tmp" -e MYSQL_ROOT_PASSWORD=my-secret-pw -d -p 3307:3306  -d mysql:8.0.18 --secure-file-priv=/tmp

検証用のデータとして,第2回 MySQLにはじめてのデータを入れてみるからKEN_ALL.CSVを利用して作成していきます。

今回はpercona-toolkitを利用するのですが,macOSにHomebrewを利用してインストールしたものを利用していきます。

pt-upgradeとは?

今回紹介するpt-upgradeは,本来の使い方としては異なる2バージョンのMySQL間での互換性をチェックするためのツールで,かかった時間やバージョン間で同じクエリを実行した時にエラーになるならないなどの違いを検出してくれるツールになります。仕組みとしては,保存しているSQLを2つのデータベースで実行して結果の差分を取るという仕組みになっています。

注意としては,ベンチマークツールの一種なので,本番環境などで実行するのはなるべく止めたほうが良いでしょう。

pt-upgradeの利用方法は以下の通りです。

pt-upgrade [OPTIONS] LOGS|RESULTS DSN [DSN]

OPTIONSで指定できる値を詳しく知りたい場合はpercona-toolkitのpt-upgradeのドキュメントを見るとわかります。

LOGS|RESULTSに関しては,こちらはMySQLの各種ログファイル(スロークエリーログ,ジェネラルログ,バイナリログ,tcpdump,生のSQL)等が選択できます。デフォルトではスロークエリーログ形式が選択されていて,違う形式のものを利用したい場合は--typeオプションで変更することができます。今回はスロークエリーログをそのまま利用するので特に指定しません。

RESULTSに関しては,こちらはMySQLを1台で実行する際に使う仕組みで,変更前のデータを保存して指定することができます。

DSNはデータベースを指定するための記述方法で,option=valueという形で指定できます。複数のパラメータを指定する場合は,で空白を入れないようにします。

今回使用オプションは以下のものになります。

オプション説明
Dデータベース名
hホスト名
ppassword
PPort指定
uuser名

今回使用していないオプションなどもあるので,ぜひ公式のドキュメントを参照してください。

実際に動かしてみる

今回は,先ほど解説したスロークエリーログを利用する方法を紹介します。

まずはスロークエリーログに値を出力したいので,以下のようなコマンドを実行しましょう。一度中身を綺麗にしてから進めていきたいので,中身を一度消してから実行します。

$ echo '' > logs/mysql/mysql-slow.sql
$ mysql -uroot -h0.0.0.0 -P3306 -pmy-secret-pw
mysql > select * from zipcode where prefecture = '神奈川県' and city = '相模原市緑区';

さて,スロークエリーログが出力していることを確認してみましょう。

$ tail -n 5 logs/mysql/mysql-slow.sql
# Time: 2020-02-19T21:38:42.751650Z
# User@Host: root[root] @  [172.17.0.1]  Id:    21
# Query_time: 0.062968  Lock_time: 0.000161 Rows_sent: 125  Rows_examined: 124395
SET timestamp=1582148322;
select * from zipcode where prefecture = '神奈川県' and city = '相模原緑区';

出力されている事が確認できました。

次にとりあえず比較をしてみましょう。以下のコマンドを実行します。

$ pt-upgrade logs/mysql/mysql-slow.sql h=0.0.0.0,p=my-secret-pw,u=root,P=3307,D=zipcode h=0.0.0.0,p=my-secret-pw,u=root,P=3306,D=zipcode

出力結果を解説していきます。

#-----------------------------------------------------------------------
# Logs
#-----------------------------------------------------------------------

File: logs/mysql/mysql-slow.sql
Size: 289

このセクションでは使用したLogファイルとファイルサイズが記載されています。

#-----------------------------------------------------------------------
# Hosts
#-----------------------------------------------------------------------

host1:

  DSN:       h=0.0.0.0,P=3306
  hostname:  759f4fafd559
  MySQL:     MySQL Community Server - GPL 8.0.18

host2:

  DSN:       h=0.0.0.0,P=3307
  hostname:  624cb0a45731
  MySQL:     MySQL Community Server - GPL 8.0.18

このセクションでは接続先のDBの情報が表示されています。

#-----------------------------------------------------------------------
# Stats
#-----------------------------------------------------------------------

failed_queries        0
not_select            0
queries_filtered      0
queries_no_diffs      1
queries_read          1
queries_with_diffs    0
queries_with_errors   0

最後にこのセクションでは実行されたSQLの情報が確認できます。今回は変更が無いことがわかります。

indexを貼って比較をしてみる

ポート番号が3307のMySQLにindexを貼って比較をしてみましょう。

$ mysql -uroot -h0.0.0.0 -P3307 -pmy-secret-pw
mysql> use zipcode;
mysql> ALTER TABLE zipcode add index prefecture_and_city(prefecture, city);

この状態で,またpt-upgradeを実行してみましょう。

$  pt-upgrade logs/mysql/mysql-slow.sql h=0.0.0.0,p=my-secret-pw,u=root,P=3307,D=zipcode h=0.0.0.0,p=my-secret-pw,u=root,P=3306,D=zipcode

〈~省略~〉

########################################################################
# Query class 8F7EEA5E93052F78
########################################################################

Reporting class because it has diffs, but hasn't been reported yet.

Total queries      1
Unique queries     1
Discarded queries  0

select * from zipcode where prefecture = ? and city = ?

##
## Query time diffs: 1
##

-- 1.

0.001292 vs. 0.060060 seconds (46.5x increase)

select * from zipcode where prefecture = '神奈川県' and city = '相模原緑区'

〈~省略~〉

上記のブロックが増えたことがわかると思います。インデックスを貼ったポート番号3307のMySQLでの実行結果を基準にすると,ポート番号3306のMySQLが46.5倍遅いことがわかります。ということで,このインデックスは効果的であることがわかります。

ここでDSNを逆にして確認をしましょう。

$ pt-upgrade logs/mysql/mysql-slow.sql h=0.0.0.0,p=my-secret-pw,u=root,P=3306,D=zipcode h=0.0.0.0,p=my-secret-pw,u=root,P=3307,D=zipcode

実行結果は省略しますが,こちらではQuery classの項目が表示されないはずです。遅くなっていると表示されている場合は,インデックスを貼り直した結果遅くなっているクエリがあることになります。

このように,同じバージョンでもインデックスチューニングを行った際の計測にpt-upgradeが使用できました。

まとめ

今回は,pt-upgradeを使ってインデックスチューニングをした際に効果を測定するのに利用してみました。チューニングが空振った時に悲しい思いをしないためにも,実践する前に一度確認してみると良いと思います。

pt-upgradeは,本文にも記載したとおり本来MySQLのバージョン違いを確認するためのツールなのですが,インデックスの効果を確認するのにも便利なので,皆さんも使ってみてはいかがでしょうか。

著者プロフィール

木村浩一郎(きむらこういちろう)

株式会社オプティム 技術統括本部のエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter:@kk2170