mysqldumpslow
mysqldumpslowはMySQLのスロークエリログを集計するツール。MySQL本体に同梱されており、追加のソフトウェアなしで利用できる。
同様の目的で使えるツールにpt-query-digest がある。mysqldumpslowはMySQL本体に同梱されているぶん手軽に使える一方、集計項目や表示の詳しさではpt-query-digestに劣る。
mysqldumpslowのインストール
mysqldumpslowはMySQLのパッケージに含まれる。
Ubuntuの場合
MySQLクライアントのパッケージに含まれる。
$ sudo apt install mysql-client-core-8.0
CentOSの場合
MySQLサーバーのパッケージに含まれる。mysqlパッケージ（クライアントのみ）にはmysqldumpslowが含まれないため、mysql-serverパッケージをインストールする。
$ sudo yum install -y mysql-server
MySQLのスロークエリを出力する設定
MySQLのスロークエリを出力する設定は以下のとおり。
[mysqld]
...
slow_query_log = 1
slow_query_log_file = /tmp/mysql-slow.log
long_query_time = 0
- slow_query_log: スロークエリを出力するかどうか。1なら出力する。
- slow_query_log_file: スロークエリを出力するファイルパス。
- long_query_time: スロークエリとみなすクエリの実行時間。0ならすべてのクエリをスロークエリとみなす。
上記設定では/tmp/mysql-slow.logにすべてのクエリを出力する。
以下、スロークエリを出力したファイルを/tmp/mysql-slow.logとしてコマンドを実行する。
mysqldumpslowでスロークエリの集計
mysqldumpslowでスロークエリを集計するには以下のコマンドを実行する。
$ mysqldumpslow /tmp/mysql-slow.log
上記コマンドを実行すると以下のような結果が出力される。
Reading mysql slow query log from /tmp/mysql-slow.log
Count: 4 Time=1.36s (5s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
SELECT * FROM users WHERE age > N AND SLEEP(N.N)
Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
UPDATE users SET age = age + N WHERE id = N AND SLEEP(N.N) IS NOT NULL
Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (5), root[root]@localhost
SELECT COUNT(*) FROM users WHERE SLEEP(N.N) IS NOT NULL
mysqldumpslowの集計結果のみかた
集計結果は1クエリパターンにつき2行で表示される。
1行目の各項目は以下のとおり。
- Count: クエリが実行された回数
- Time: 実行時間の平均（合計）
- Lock: ロック時間の平均（合計）
- Rows: 送信された行数の平均（合計）
- 実行したユーザーとホスト
2行目にはクエリの内容が表示される。数値はN、文字列は'S'に置き換えられ、同じ構造のクエリはまとめて集計される。
Timeが長いクエリの場合、Countも併せて確認する。Countが多い場合はそのクエリの「実行回数」を減らすことでパフォーマンス改善が期待できる。
一方、Countが少ない場合はそのクエリの「実行時間」を短縮することでパフォーマンス改善が期待できる。
特にCountが想像よりも多い場合、N+1問題が発生している可能性がある。
デフォルトではTimeの平均が長い順にソートされる。
よく使うオプション
-s: ソート順を指定する
-sオプションでソート順を指定できる。
$ mysqldumpslow -s c /tmp/mysql-slow.log
at: 実行時間の平均（デフォルト）t: 実行時間の合計al: ロック時間の平均l: ロック時間の合計ar: 送信行数の平均r: 送信行数の合計c: 実行回数
Countが多いクエリから確認したい場合は-s cを指定する。
-t: 表示件数を絞る
-tオプションで上位N件のみを表示できる。
$ mysqldumpslow -s c -t 3 /tmp/mysql-slow.log
集計結果が多い場合は-tで件数を絞ると見やすい。
-g: クエリを絞り込む
-gオプションで指定したパターンにマッチするクエリのみを絞り込める。
$ mysqldumpslow -g "select" /tmp/mysql-slow.log
Reading mysql slow query log from /tmp/mysql-slow.log
Count: 4 Time=1.36s (5s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
SELECT * FROM users WHERE age > N AND SLEEP(N.N)
Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (5), root[root]@localhost
SELECT COUNT(*) FROM users WHERE SLEEP(N.N) IS NOT NULL
テーブル名やSELECT・UPDATEといったクエリの種類で絞り込みたい場合に使う。
繰り返し集計する場合の注意
スロークエリは/tmp/mysql-slow.logに追記されていく。そのため実行し直して集計し直す場合は/tmp/mysql-slow.logを削除する必要がある。
削除しないと前回のスロークエリも含まれてしまうため、正しい集計結果が得られない。
pt-query-digestとの違い
pt-query-digest もmysqldumpslowと同様にスロークエリログを集計するツールである。
pt-query-digestはPercona Toolkitの追加インストールが必要だが、クエリごとの実行時間の分布や具体的なSQL例など、より詳細な情報を確認できる。
一方mysqldumpslowはMySQLに同梱されており手軽に使えるが、確認できる情報はpt-query-digestよりも少ない。
まずmysqldumpslowで大まかに時間のかかっているクエリを把握し、さらに詳しく調査したい場合にpt-query-digest を使う使い分けもできる。
