Posted inMySQL

Performance SchemaとSys SchemaでMySQLのパフォーマンスを監視する：ボトルネックを素早く特定する

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Performance Schemaが必要になる場面

プロジェクトのusersテーブルが数十万行程度だった頃は、すべて順調に動いていた。しかしデータが1000万行を超えた途端、スロークエリが現れ始めた——レスポンスタイムが急増し、MySQLのCPU使用率が80〜90%まで跳ね上がった。Slow Query Logを確認すると（この設定については別記事で解説している）、遅いSQL文のリストは見えた。でもなぜ遅いのかがわからなかった：インデックス不足？ロック競合？I/O？それとも同時接続数の問題？

そこで登場するのがPerformance Schemaだ。MySQLに組み込まれたこの診断ツールは、各クエリの実行時間、どのスレッドがロック待ちをしているか、ディスクの読み取り回数など、Slow Query Logでは把握できない細部まで可視化してくれる。

Sys SchemaはPerformance Schemaの上に被さるラッパー層だ。4〜5テーブルを手動でJOINする代わりに、sys.statement_analysisやsys.innodb_lock_waitsといった使いやすいビューが最初から用意されており、そのままクエリして結果を読むだけでいい。

事前に押さえておくべき基本概念

Performance Schemaとは？

MySQLに組み込まれた特別なデータベース（名前はperformance_schema）で、MySQL 5.6以降はデフォルトで有効になっている。MySQLはあらゆる動作をここに記録し続けている：どのクエリがどれだけかかったか、どのスレッドが何を待っているか、どのテーブルが最もよく読まれているか。

データはすべてRAM上に保存される——クエリは極めて高速だが、MySQL再起動後はすべて消える。

Sys Schemaとは？

Sys Schema（データベース名はsys）はMySQL 5.7.7以降に付属している。新しいデータを収集するわけではなく、performance_schemaとinformation_schemaからデータを読み取り、より読みやすいビューとして整形し直すだけだ。たとえばperformance_schemaで4〜5テーブルをJOINする代わりに、SELECT * FROM sys.statements_with_runtimes_in_95th_percentileと書くだけで済む。

Performance Schemaが有効かどうか確認する

-- 状態を確認する
SHOW VARIABLES LIKE 'performance_schema';

-- 期待される結果：
-- +--------------------+-------+
-- | Variable_name      | Value |
-- +--------------------+-------+
-- | performance_schema | ON    |
-- +--------------------+-------+

結果がOFFの場合は、/etc/mysql/my.cnfに以下の行を追加してMySQLを再起動する：

[mysqld]
performance_schema = ON

実践：ボトルネックを段階的に特定する

ステップ1 — 最もリソースを消費しているクエリを特定する

デバッグ時にまず実行するクエリはこれだ：

-- 最も遅いクエリTOP10（Sys Schemaを使用）
SELECT 
    query,
    exec_count,
    total_latency,
    avg_latency,
    rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

rows_examined_avg列に注目しよう。この値がrows_sent_avgよりはるかに大きい場合、MySQLは必要以上に多くの行をスキャンしている——インデックスがない、あるいは適切なインデックスが使われていないケースが多い。

実際の例：あるクエリでrows_examined_avg = 9,800,000なのにrows_sent_avg = 12という状況を見たことがある。1000万行のテーブルをフルスキャンして、たった12件しか返していなかった。複合インデックスを追加して解決した。

ステップ2 — ロック待機（ブロックされたクエリ）を検出する

ロック待機は静かな犯人だ——クエリ自体は速くても、別のトランザクションが持つロックの解放を待ち続けることがある。Slow Query Logではこれを捕捉できない。

-- 現在ブロックされているスレッドを確認する
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

もしくはSys Schemaを使ってよりシンプルに：

-- テーブルごとのロック待機サマリー
SELECT * FROM sys.innodb_lock_waits\G

あるスレッドが複数のスレッドをブロックしている場合は、COMMITされていないトランザクションが残っていないか確認しよう。よくある原因：アプリケーションがトランザクションを開いたまま外部APIを呼び出し、3〜5秒かかってからCOMMITする——その間ずっとロックが保持され続ける。デッドロックへの発展を防ぐ根本的な解決策についても合わせて確認しておくと役立つ。

ステップ3 — I/O分析：どのテーブルが最も読み書きされているか？

-- I/O回数の多いテーブルTOP10
SELECT 
    table_name,
    total_read_latency,
    total_write_latency,
    io_read_requests,
    io_write_requests
FROM sys.schema_table_statistics
ORDER BY total_read_latency DESC
LIMIT 10;

total_read_latencyが他のテーブルと比べて異常に高いテーブルが見つかったら、そこのインデックスとキャッシュ戦略を見直す候補だ。

ステップ4 — 使われていないインデックスを確認する

最適化作業の後によく実行するビューだ——RAM を無駄に消費している使われていないインデックスを探し出す：

-- 直近のMySQL再起動から一度も使われていないインデックス
SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('performance_schema', 'sys', 'information_schema', 'mysql');

すぐに削除するのは禁物だ。月次レポートのような定期的なクエリ（月1回実行されるものなど）で使われている可能性があるため、少なくとも数日は様子を見ること。

ステップ5 — 最も負荷をかけているユーザー／ホストを確認する

-- ユーザー別の統計
SELECT 
    user,
    total_latency,
    statements,
    table_scans,
    rows_sent
FROM sys.user_summary
ORDER BY total_latency DESC;

複数のサービスが同一のMySQLサーバーに接続している場合に役立つ——異常な負荷を引き起こしているサービスを特定できる。

ステップ6 — 統計をリセットして計測をやり直す

最適化が完了したら、Performance Schemaのデータをリセットしてクリーンな状態から再計測しよう：

-- すべての統計をリセットする（MySQL再起動は不要）
CALL sys.ps_truncate_all_tables(FALSE);

-- または種類ごとにリセットする：
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;

実際の診断フロー

「MySQLが遅い」という報告を受けたとき、通常は以下の順番で進める：

  1. sys.statement_analysisを実行 — total_latencyとrows_examined_avgでトップクエリを特定する
  2. sys.innodb_lock_waitsを実行 — ロック競合が発生していないか確認する
  3. sys.schema_table_statisticsを実行 — I/Oが集中しているテーブルを確認する
  4. 疑わしいクエリに対してEXPLAINを実行 — インデックスが使われているか確認する
  5. 修正する（インデックス追加・クエリ最適化・トランザクションスコープの縮小）
  6. 統計をリセットして24〜48時間監視する

ステップ4は省略できない：Performance Schemaは何が遅いかを教えてくれるが、実行計画レベルでなぜ遅いかを教えてくれるのはEXPLAINだ。

まとめ

Performance SchemaとSys SchemaはSlow Query Logを置き換えるものではなく、互いを補完するものだ。Slow Query Logはすぐに有効にして確認できる手軽さがある。一方Performance Schemaはより深い洞察を提供する：ロック待機、I/Oの内訳、インデックスの使用状況、ユーザーごとの負荷などだ。

Performance Schemaは本番環境で常に有効にしておくべきだ。Oracleのベンチマークによると実際のオーバーヘッドは5%未満——気にするほどではない。より重要なのは、問題が発生したとき、既に履歴データが蓄積されているため、問題を最初から再現し直す必要がないという点だ。

まずはシンプルに始めよう：sys.statement_analysisを開いてrows_examined_avg列を見る。その数値がMySQLの健全性をそのまま反映している——どのクエリが余計なスキャンをしているか、どのテーブルが酷使されているかが一目でわかる。

Share:
Tags: