実務でMySQLのパフォーマンス改善に取り組むことになり、pt-query-digestについて勉強したため内容をまとめておきます。
pt-query-digestとは？
MySQLのスロークエリログをいい感じに集計して、負荷が大きかった順に上から並べてくれるツールです。MySQLの付属コマンドであるmysqldumpslowでもスロークエリログの分析は可能ですが、それよりも詳細な分析ができます。
インストール方法は他の記事をご参照ください。
出力方法
pt-query-digestの後にファイル名を続けるだけです。
$ pt-query-digest 'スロークエリログのファイル名'
なお以下のように集計期間を指定することも可能です。
pt-query-digest --since="2023-01-01" --until="2023-01-31" 'スロークエリログのファイル名'
出力結果の見方
出力結果はざっくりランキングパートと詳細な解析結果パートの2つに分かれます。
ランキングパート
負荷の大きかったクエリのランキングが表示されます。
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
# 1 0xABCDEFGHIJK12345 0.2825 73.1% 113 0.0025 0.00 SELECT * FROM tests
# 2 0xABCDEFGHIJK12346 0.1679 13.2% 73 0.0023 0.00 SELECT * FROM answers
# 3 0xABCDEFGHIJK12347 0.2041 8.3% 55 0.0021 0.00 SELECT * FROM comments
# (以降省略)
各項目の意味は以下の通りです。
| 項目 | 内容 |
|---|---|
| Rank | 順位 |
| Query ID | クエリのハッシュ値 |
| Response time | 実行時間の合計及び全体に占める割合 |
| Calls | 実行回数 |
| R/Call | 1回あたりの実行時間 |
| V/M | 標準偏差 |
| Item | 対象のSQL |
詳細な解析結果パート
各クエリの詳細な分析結果が表示されます。
# Query 1: 0 QPS, 0x concurrency, ID 0xABCDEFGHIJK12345 at byte 213153 _____
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 28 113
# Exec time 73 282ms 1ms 3ms 2ms 2ms 300us 2ms
# Lock time 32 7ms 37us 589us 64us 76us 57us 54us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 45 18.46k 81 87 84.12 83.16 0 85.43
# (以降省略)
主な項目だけ紹介します。
| 項目 | 内容 |
|---|---|
| Count | 集計期間中のクエリ実行回数 |
| Exec time | クエリの実行時間 |
| Lock time | クエリの実行開始までにかかった時間(他スレッドのロックによる待ち時間) |
| Rows sent | クライアントヘ返却した行数 |
| Rows examine | クエリ実行時に走査した行数 |
| Query size | クエリ(文字列)の長さ |
確認ポイントの例
基本的には上位のクエリから原因を究明していきます。
-
Rows examineに対してRows sentが小さすぎる。
→インデックスが適切に活用されていない可能性あり。例えばRows sentが5件なのにRows examineが10万件のような場合はインデックスが適切に張られておらず全件検索になってしまっている可能性があります。逆にRows examineとRows sentが大差ないにも関わらず上位に位置しているクエリはインデックス以外の箇所でチューニングが必要な可能性が高いです。 - クエリ実行回数が異常に多い。
→N+1問題の可能性あり。
MySQLのEXPLAINコマンドでSQLの実行計画を調べてみるのも原因の究明に繋がるはずです。