Posted inデータベース

MySQL Performance Schema：内部統計テーブルでスロークエリを診断する

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Performance SchemaはMySQLに内蔵された可観測性レイヤーです。クエリの実行時間からミューテックス待ちまで、インスタンス内で起きているすべてを記録する80以上のテーブルで構成されています。本番環境で毎日使い続けて6ヶ月が経つ頃には、ほとんどのデバッグ作業で外部プロファイリングツールを使わなくなっていました。

本当に重要なことをお伝えします。

クイックスタート：5分で始める

まず、Performance Schemaが有効になっているか確認します。

SHOW VARIABLES LIKE 'performance_schema';

OFFと表示された場合は、my.cnfで有効にします。

[mysqld]
performance_schema = ON

MySQLを再起動して確認します。

USE performance_schema;
SHOW TABLES;

80以上のテーブルが表示されます。作業の90%で使うのはこれらです。

  • events_statements_summary_by_digest — クエリの集計統計
  • events_waits_summary_global_by_event_name — MySQLが待機しているもの
  • table_io_waits_summary_by_table — テーブル別I/O統計
  • file_summary_by_event_name — ファイルI/Oの内訳

すぐに成果を得るには、このクエリを実行して今最も遅いクエリパターントップ5を確認しましょう。

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
  ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;

このクエリだけで、複数のプロジェクトでログを掘り起こす作業を何時間も節約できました。

深掘り：数値が意味するものを理解する

ステートメントダイジェストテーブル

events_statements_summary_by_digestテーブルはクエリを正規化し、リテラル値を?に置き換えます。そのためWHERE id = 1とWHERE id = 42は同じダイジェストになります。数千回の実行にわたるパターンを発見する上で非常に重要な機能です。

まとめて確認したい主要カラム：

SELECT
  SCHEMA_NAME,
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(MIN_TIMER_WAIT / 1e9, 2) AS min_ms,
  ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
  ROUND(MAX_TIMER_WAIT / 1e9, 2) AS max_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  SUM_NO_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'your_db'
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

SUM_NO_INDEX_USEDは明らかな手がかりです。1時間に何千回も実行されるクエリでこの値が高ければ、インデックスが欠けている証拠です。SUM_ROWS_EXAMINED / SUM_ROWS_SENTの比率はクエリの効率を示します。1000:1という比率は、1行を返すためにMySQLが1000行をスキャンしたことを意味し、ほぼ確実にインデックスの問題です。

待機イベント：隠れたボトルネックを発見する

さまざまなプロジェクトでMySQL、PostgreSQL、MongoDBを使ってきましたが、それぞれ独自の可観測性モデルを持っています。MySQLの待機イベントシステムは、もっと早く学んでおけばよかったと感じるもので、スロークエリログには決して現れないボトルネックを明らかにしてくれます。

SELECT
  EVENT_NAME,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec,
  ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
  AND EVENT_NAME NOT LIKE '%idle%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 15;

注目すべきポイント：

  • wait/io/file/innodb/innodb_data_file — ディスクI/Oがボトルネック。RAMの増設かより高速なストレージの検討を
  • wait/synch/mutex/innodb/buf_pool_mutex — バッファプールの競合。innodb_buffer_pool_sizeの拡大が必要な可能性あり
  • wait/lock/table/sql/handler — テーブルロックの競合。トランザクションの欠如やロックの競合を確認

テーブルI/O分析

SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COUNT_READ,
  COUNT_WRITE,
  ROUND(SUM_TIMER_READ / 1e9, 2) AS read_ms,
  ROUND(SUM_TIMER_WRITE / 1e9, 2) AS write_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'your_db'
ORDER BY SUM_TIMER_READ + SUM_TIMER_WRITE DESC
LIMIT 10;

これにより、どのテーブルが最もホットかを特定できます。あるクライアントのシステムで実行したとき、アーカイブ戦略がないままに数百万行を蓄積し続けていたログテーブルを発見しました。そのテーブルとJOINするすべてのリクエストに400msのレイテンシが追加されていたのです。

応用編：特定クエリの計測

ステートメント履歴の有効化

デフォルトでは、MySQLは集計データのみを保持します。特定のセッションをデバッグするには、実行ごとの履歴を有効にします。

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_statements%';

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

その後、問題のあるクエリを実行し、履歴を確認します。

SELECT
  SQL_TEXT,
  TIMER_WAIT / 1e9 AS duration_ms,
  ROWS_EXAMINED,
  ROWS_SENT,
  CREATED_TMP_DISK_TABLES,
  CREATED_TMP_TABLES,
  SELECT_FULL_JOIN,
  NO_INDEX_USED
FROM performance_schema.events_statements_history_long
ORDER BY TIMER_WAIT DESC
LIMIT 20;

CREATED_TMP_DISK_TABLESがゼロでない場合、MySQLが一時テーブルをディスクにスピルしたことを意味します。tmp_table_sizeとmax_heap_table_sizeを調整してください。

sysスキーマの活用（MySQL 5.7.7以降）

sysスキーマはPerformance Schemaを人間が読みやすいビューにラップしています。

-- 総実行時間が長いクエリのトップ
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- インデックスを使用していないクエリ
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC LIMIT 10;

-- テーブル統計の概要
SELECT * FROM sys.schema_table_statistics
WHERE table_schema = 'your_db'
ORDER BY total_latency DESC;

週次のcronジョブを設定して、sys.statements_with_runtimes_in_95th_percentileをファイルにダンプしています。数週間にわたるトレンド分析により、スポット監視では見逃してしまうパフォーマンス退行を検出できます。

ユーザーレベルの内訳

複数のアプリケーションが1つのMySQLインスタンスを共有している場合、これで負荷の原因を特定できます。

SELECT * FROM sys.user_summary ORDER BY total_latency DESC;

実践的なヒント

負荷テストの間に統計をリセットする。統計はサーバー起動からの累積値であるため、ベンチマークが歪みます。変更をテストする前に：

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

ステートメント履歴を常時有効にしない。events_statements_history_longテーブルには固定の行数制限（デフォルト10,000行）があり、オーバーヘッドが発生します。デバッグセッション中のみ有効にして、終わったら無効にしてください。

UPDATE performance_schema.setup_consumers
SET ENABLED = 'NO'
WHERE NAME = 'events_statements_history_long';

EXPLAINと組み合わせる。Performance Schemaは何が遅いかを教えてくれ、EXPLAINはなぜ遅いかを教えてくれます。ダイジェストで特定した後、スロークエリログから実際のクエリを取り出してEXPLAIN ANALYZEを実行しましょう。

ベースラインキャプチャスクリプトを構築する。Performance Schemaに数分ごとにクエリを実行し、時系列データストアに書き込むシンプルなPythonジョブで、トレンドデータを取得できます。

import mysql.connector

def capture_top_queries(conn, limit=20):
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT
            DIGEST,
            DIGEST_TEXT,
            COUNT_STAR,
            ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
            ROUND(SUM_TIMER_WAIT / 1e9, 2) AS total_ms,
            SUM_NO_INDEX_USED AS no_index_count
        FROM performance_schema.events_statements_summary_by_digest
        WHERE SCHEMA_NAME IS NOT NULL
        ORDER BY SUM_TIMER_WAIT DESC
        LIMIT %s
    """, (limit,))
    return cursor.fetchall()

デプロイ後にavg_msがどう変化するかを監視することで、ユーザーが気づく前に複数回のパフォーマンス退行を検出できました。

クエリリストの上位での順位変動に注目する。監視間隔の間に1位のクエリが変わった場合、何かが変化しています。負荷パターンの変化か、新しいクエリのデプロイかのどちらかです。ユーザーからの苦情を待つのではなく、すぐに調査すべきシグナルとして扱いましょう。

Performance Schemaは地味な存在ですが、MySQLの内部状態を把握するために私が見つけた中で最も信頼できるシグナルです。データはすでに収集されています。ほとんどのチームがその読み方を知らないだけなのです。

Share:
Tags: