Qiitaを含め色々なところで何度も取り上げられているPercona Toolkitのpt-query-digestですが、久々に引っ張り出してきて解析する機会があったので、忘れないうちにメモしておきます。
0. 準備と基本的な使い方
…私が書かなくても、Oracle ACEのyoku0825さんが、Think ITで詳しく解説されています。
私の場合は、スロークエリログを純粋に「時間がかかるクエリの抽出」に使っているので、普段はtcpdumpでデータを取得してpt-query-digestしています。
tcpdump -s 65535 -x -nn -q -tttt -i eth0 -c 100000 port 3306 > 【出力ファイル名】
※この例では、10万パケット取得しています。
pt-query-digest --type tcpdump --limit 99%:1000 【tcpdump出力ファイル名】
※この例では、総実行時間が長い順から99%、または1,000件（どちらか小さい／少ないほう）までを解析しています。
プロダクト環境に影響を与えてはいけないので、pt-query-digestはWebサーバ・DBサーバ以外のところにインストールして実行します。
なお、AWSのようなクラウド環境でDBへの接続が同一ゾーン／別ゾーンで混在しているとき、ゾーンをまたぐクエリの実行時間にはゾーン間を往復する通信時間が上乗せされます（私がAWSで実験したときには、同一ゾーンと別ゾーンで2ms強の差が生じました）。結果を見るときにはその点を考慮に入れてください。
1. データが増えてきたときの確認ポイント
基本的なポイントは先のThink ITの記事に書かれていますが、それだけではこのエントリを書く意味がないので、1つだけ付け加えておきます。
※データ量が増えてきたときだけではなく、バージョンアップやサーバー移行などを行ったときに確認するのも有効です（テーブルの統計情報やオプティマイザの判定内容が変わる可能性があるため）。
ランキングのR/Call（クエリ1回あたり実行時間平均値）が他と比較して大きく、V/M（標準偏差）の値が0.00ではないものについて、Query IDをキーに集計結果詳細を見ます。
そのとき、
- Query_time sparklineにピーク（^）が2つ以上あるか、値の分布に切れ目がある（_ __など）
- Query_time distributionにピークが複数ある、または分布に切れ目がある
のであれば、
- 抽出条件によってINDEXが使われたり使われなかったりしている（No index useの行が表示されます）
- 結合（JOIN）している場合、クエリ実行時の抽出条件によって駆動表（外部表）と内部表が入れ替わっている
可能性があります。
とりあえず「USE INDEX」「FORCE INDEX」などのヒント句を付けて（使って欲しいINDEXが使われる状態で）クエリを実行してみて状況が改善されるようなら、その可能性が高いです。
対症療法としては、そのままヒント句の付いたクエリに変更するだけでも効果はありますが、あまり良い解決策とは言えないので、
- テーブル構造を変更する
- クエリが複雑な場合はできるだけ単純化する（アプリケーションの処理方法を再検討する）
- クエリを分解し、一部の要素をメモリにキャッシュする
- テーブル／オプティマイザ統計情報の計算に関わる設定を調整する（参考：MySQL 5.6 リファレンスマニュアル：14.13.16 オプティマイザ統計）
などの方法で改善します。
一方、単純なクエリであり実行時間の分布に切れ目ができる理由がない・ヒント句を付けても変わらない場合は、他のクエリの影響を受けたことによって実行時間が延びている可能性がありますので、その場合は原因となっているクエリを見つけてそちらを先に改善します。
但し、単純なクエリであっても、
- SELECTである
- 実行回数が非常に多い
- SELECT対象レコードの更新頻度が低い
のであれば、メモリにキャッシュするなどしてクエリ実行回数を減らすことも検討します。
2. その他
そもそも、Query_time sparklineの「_」の範囲が広い（長い）とか、Query_time distributionの各時間区分にまんべんなく「#」が表示されている場合、データ量の増加に合わせて線形に実行時間が延びており、さらにデータが増えたときにどんどん遅くなっていく恐れがあります。
INDEXが全く効いていないか、効いていても絞り込みが不十分かもしれません。
データ量に対する実行時間が設計時の想定通りであれば良いのですが、想定を超えて延びているのであれば、テーブル構造やアプリケーションの処理方法・アルゴリズムを再検討したほうが良いでしょう。