【MySQL】performance_schemaで重いクエリを特定する

MySQLで実行されたSQL文ごとの実行回数・実行時間を集計するには、performance_schema.events_statements_summary_by_digestを使う。
sys.statement_analysisを使うと、単位が読みやすく整形された形で同様の情報を確認できる。

参考: 【PostgreSQL】pg_stat_statementsで重いクエリを特定する

events_statements_summary_by_digestとは

events_statements_summary_by_digestは、実行されたSQL文をリテラル値によらず正規化（ダイジェスト化）して集計するperformance_schemaのテーブルである。
PostgreSQLのpg_stat_statementsと異なり拡張機能ではなく、performance_schemaが有効であれば追加のインストールやCREATE EXTENSIONなしに利用できる。

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

デフォルトで有効になっているため、事前準備なしにすぐ使い始められる。

累積の実行時間でクエリを絞り込む

以降の例では、主キーidと、インデックスのないbalanceカラムを持つtestdb.accountsテーブルへ5,000行を投入した状態を使う。

CREATE TABLE accounts (id INT PRIMARY KEY, balance INT);

SUM_TIMER_WAIT（累積の実行時間）で降順に並べると、システム全体への負荷が大きいクエリから確認できる。
値の単位はピコ秒であるため、ROUND関数と組み合わせてミリ秒に変換する。

mysql> SELECT
    ->   DIGEST_TEXT,
    ->   COUNT_STAR,
    ->   ROUND(SUM_TIMER_WAIT / 1000000000, 3) AS total_exec_time_ms,
    ->   ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS avg_exec_time_ms
    -> FROM performance_schema.events_statements_summary_by_digest
    -> WHERE SCHEMA_NAME = 'testdb' AND DIGEST_TEXT LIKE 'SELECT%'
    -> ORDER BY total_exec_time_ms DESC
    -> LIMIT 10;
+----------------------------------------------+------------+--------------------+------------------+
| DIGEST_TEXT                                  | COUNT_STAR | total_exec_time_ms | avg_exec_time_ms |
+----------------------------------------------+------------+--------------------+------------------+
| SELECT * FROM `accounts` WHERE `balance` = ? | 3          | 1.522              | 0.507            |
| SELECT * FROM `accounts` WHERE `id` = ?      | 2          | 0.122              | 0.061            |
+----------------------------------------------+------------+--------------------+------------------+

DIGEST_TEXT: 正規化されたSQL文（リテラル値は?に置き換えられる）
COUNT_STAR: 実行回数
SUM_TIMER_WAIT: 実行時間の累積値（ピコ秒）
AVG_TIMER_WAIT: 1回あたりの実行時間の平均値（ピコ秒）

主キーのid列を条件にしたクエリより、インデックスのないbalance列を条件にしたクエリのほうが、実行回数は3回と少ないにもかかわらずtotal_exec_time_msが大きい。
avg_exec_time_msを見ても1回あたり約8倍かかっており、インデックスが効いていないと推測できる。

SUM_TIMER_WAITとAVG_TIMER_WAITの使い分け

SUM_TIMER_WAITは実行回数の影響を受けるため、COUNT_STARが多いクエリは1回あたりが軽くても上位に来やすい。
1回あたりの重さを見るにはAVG_TIMER_WAITで並べ替える。

SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

システム全体のボトルネックを探すならSUM_TIMER_WAIT、個々のクエリの効率を見るならAVG_TIMER_WAITというように、目的に応じて並べ替えの基準を使い分ける。
PostgreSQLのpg_stat_statementsにおけるtotal_exec_time・mean_exec_timeと同じ考え方である。

sys.statement_analysisで読みやすく確認する

events_statements_summary_by_digestの値はピコ秒の生の数値であり、そのままでは読みにくい。
sysスキーマのstatement_analysisビューを使うと、単位が自動でマイクロ秒・ミリ秒・秒などに整形された形で確認できる。

mysql> SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg
    -> FROM sys.statement_analysis
    -> WHERE db = 'testdb' AND query LIKE 'SELECT%'
    -> ORDER BY total_latency DESC
    -> LIMIT 10\G
*************************** 1. row ***************************
            query: SELECT * FROM `accounts` WHERE `id` = ?
       exec_count: 2
    total_latency: 121.94 us
      avg_latency: 60.97 us
rows_examined_avg: 1
*************************** 2. row ***************************
            query: SELECT * FROM `accounts` WHERE `balance` = ?
       exec_count: 3
    total_latency: 1.52 ms
      avg_latency: 507.47 us
rows_examined_avg: 5000

rows_examined_avg（平均の走査行数）も一緒に確認できるため、id列を条件にしたクエリは1行しか走査していないのに対し、balance列を条件にしたクエリは平均5,000行を走査しており、フルスキャンに近い状態であることが一目で分かる。
sys.statement_analysisには他にもfull_scan（フルスキャンの有無）やexec_countあたりのrows_sent_avgなど、チューニングの手がかりになる列が用意されている。

統計情報のリセット

集計はperformance_schemaが起動してからの累積値である。リリース後の変化など特定期間の傾向を調べたい場合は、対象期間の開始前にTRUNCATE TABLEでリセットする。

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

PostgreSQLのpg_stat_statements_reset()のような専用の関数はなく、テーブルを直接TRUNCATEする点がMySQLの特徴である。

注意点

events_statements_summary_by_digestはリテラル値ごとに区別せず?へ正規化して集計するため、同じ形のクエリであれば条件の値が異なっても1行に集計される。
特定のパラメータの組み合わせで遅いクエリを調べたい場合は、実際の値を使ってEXPLAINで個別に実行計画を確認する。

集計対象のダイジェスト数にはperformance_schema_digests_sizeで上限があり、デフォルトは10,000である。

mysql> SHOW VARIABLES LIKE 'performance_schema_digests_size';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| performance_schema_digests_size  | 10000 |
+----------------------------------+-------+

上限を超えると、新しく現れたクエリパターンはSCHEMA_NAME・DIGESTがNULLの集計行にまとめられ、個別には追跡できなくなる。
この特殊な行が全体に占める割合が大きい場合、performance_schema_digests_sizeの引き上げを検討する。