インデックスをチューニングをした時に、
そこで今回は、
検証環境
今回は2台データを用意して差分を確認していきたいので、
第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
検証用のデータとして、
今回はpercona-toolkitを利用するのですが、
pt-upgradeとは？
今回紹介するpt-upgradeは、
注意としては、
pt-upgradeの利用方法は以下の通りです。
pt-upgrade [OPTIONS] LOGS|RESULTS DSN [DSN]
OPTIONSで指定できる値を詳しく知りたい場合はpercona-toolkitのpt-upgradeのドキュメントを見るとわかります。
LOGS|RESULTSに関しては、--typeオプションで変更することができます。今回はスロークエリーログをそのまま利用するので特に指定しません。
RESULTSに関しては、
DSNはデータベースを指定するための記述方法で、option=valueという形で指定できます。複数のパラメータを指定する場合は,で空白を入れないようにします。
今回使用オプションは以下のものになります。
| オプション | 説明 |
|---|---|
| D | データベース名 |
| h | ホスト名 |
| p | password |
| P | Port指定 |
| u | user名 |
今回使用していないオプションなどもあるので、
実際に動かしてみる
今回は、
まずはスロークエリーログに値を出力したいので、
$ 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 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での実行結果を基準にすると、
ここで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
実行結果は省略しますが、
このように、
まとめ
今回は、
pt-upgradeは、