『High Performance MySQL 第4版』を読んだ内容を章ごとにまとめています。
第3章 Performance Schema
概要
本章では、MySQLの内部動作を可視化するための仕組みである Performance Schema について解説する。
これはMySQLサーバ内部で発生しているさまざまなイベント（クエリ実行・ロック・I/O待機など）を低レベルで記録・分析できるメカニズムであり、パフォーマンスチューニングやボトルネック特定に不可欠な機能である。
一方で、誤ってすべての項目を有効化するとCPUやメモリに負荷がかかるため、仕組みを理解したうえで「必要な範囲のみを有効化」することが重要である。
Performance Schemaの基本概念
はじめに
データベースの性能を改善する際は、「どこで時間がかかっているのか」を把握することが第一歩となる。
Performance Schemaは、MySQL内部で発生するイベントを“計測用データベース”として蓄積し、それをSQLで参照できるようにしたもの。
質問に答えるためのデータベース（meta DB）であり、「なぜ遅いのか」を数値で説明するための仕組みといえる。
Instrument（インストゥルメント）
Instrument は「観測対象となるコード部分」を指す。
たとえばメタデータロックを観測したい場合は、wait/lock/metadata/sql/mdl というInstrumentを有効にする。
Instrumentの名称は階層構造を持ち、statement/sql/select や wait/synch/mutex/innodb/autoinc_mutex のように、「種別／サブシステム／詳細」の形式で構成される。
setup_instruments テーブルで有効・無効を確認でき、ENABLED列がYESなら監視対象となる。
なお、DOCUMENTATION列に説明が含まれる場合もあるが、NULLの場合も多い。
[MEMO]
Instrumentを増やすほどCPUコストが上がる。
特に「行ロック」など頻繁に発生する処理を計測するとCPU使用率が急上昇するため注意。
Consumer（コンシューマ）
Consumer は、Instrumentから送られてきた計測結果を受け取って保存するテーブル群を指す。
MySQL 8.0.25時点では110以上のテーブルが存在し、用途別に整理されている。
主なカテゴリ
| 分類 | 説明 |
|---|---|
| *_current / *_history / *_history_long | 現在・直近・過去のイベントを保存 |
| events_waits | ロックやmutexなどの待機イベント |
| events_statements | SQLステートメント単位の情報 |
| events_stages | ステージ（例: 一時テーブル作成）単位の情報 |
| events_transactions | トランザクション単位の情報 |
| summary tables | 集約情報（スレッド別・イベント別など） |
| instances | ファイルやメモリなどのインスタンス情報 |
| setup tables | 設定関連（有効/無効などの定義） |
Digest（ダイジェスト）機能により、同じ構造のクエリをプレースホルダ化して集計できる。
例：
SELECT user, birthdate FROM users WHERE user_id=?;この形式でまとめることで、SQLのバリエーションを1つのパターンとして集約できる。
Resource Consumption（リソース消費）
Performance Schemaのデータはすべてメモリ上に保持される。
容量を制御するには、コンシューマごとの最大サイズを設定する。
一部のテーブルは自動スケーリング（autoscaling）対応で、必要に応じてメモリを拡張するが、一度確保したメモリは解放されない。
また、計測対象（Instrument）が増えるほどCPU負荷も上がるため、目的に応じて最小限にすることが原則。
Limitations（制限事項）
Performance Schemaにはいくつかの制約がある：
- 対応していないコンポーネントは計測できない（例：一部のストレージエンジン）
- 有効化した後からしかデータを収集できない
→ サーバ起動時に無効だった場合、過去の情報は得られない - 無効化しても確保済みのメモリは解放されない（再起動が必要）
sys Schema
MySQL 5.7以降では、Performance Schemaの補助として sysスキーマ が標準搭載された。
sysスキーマはPerformance Schemaの上に作られたビュー群であり、複雑な集計をわかりやすく表示する。
データ自体は保持せず、Performance Schemaの情報を読み出すだけ。
[MEMO]
sysスキーマのビューは、よく使う診断を簡単に実行できる。
例：sys.statements_with_full_table_scansでフルテーブルスキャンを検出。
Understanding Threads（スレッドの理解）
MySQLはマルチスレッド構造で動作する。
各スレッドには2種類のIDがあり、それぞれ次のように管理される。
| 項目 | 意味 |
|---|---|
| THREAD_ID | Performance Schema内で使われる内部スレッドID |
| PROCESSLIST_ID | SHOW PROCESSLIST で表示される接続ID |
⚠ THREAD_ID と PROCESSLIST_ID は別物。
ロックを保持している接続を強制終了する場合などは、threadsテーブルを参照してPROCESSLIST_IDを特定する必要がある。
設定と構成
Performance Schemaの有効化
Performance Schema自体の有効・無効は起動時オプションで指定する：
performance_schema=ON実行中に切り替えることはできない。
Instrumentの有効化・無効化
有効化には3通りの方法がある：
setup_instrumentsテーブルを直接UPDATEsys.ps_setup_enable_instrument()ストアドプロシージャ- 起動パラメータ
performance-schema-instrument='instrument_name=ON'
UPDATE performance_schema.setup_instruments
SET ENABLED='YES' WHERE NAME LIKE 'statement/sql/%';設定変更は再起動でリセットされる。永続化したい場合は設定ファイルで指定する。
Consumerの有効化
Consumerも同様に3通りの手段で制御できる：
setup_consumersテーブルのUPDATEsys.ps_setup_enable_consumer()の呼び出し- 起動パラメータ
performance-schema-consumer='events_statements_history=ON'
代表的なConsumerの目的：
| Consumer名 | 内容 |
|---|---|
| events_stages_* | クエリの処理ステージ（例：一時テーブル作成） |
| events_statements_* | SQL文単位の情報 |
| events_transactions_* | トランザクション単位 |
| events_waits_* | 待機イベント（ロック・I/Oなど） |
| statements_digest | クエリのダイジェスト統計 |
| global_instrumentation / thread_instrumentation | 計測の全体有効化・スレッド単位制御 |
特定オブジェクトやスレッドの監視設定
setup_objects テーブルを使うと、スキーマ・テーブル・トリガー単位で監視のON/OFFを設定できる。
INSERT INTO performance_schema.setup_objects
(OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED)
VALUES ('TRIGGER', 'test', '%', 'NO');スレッドごとの制御は setup_threads テーブルで行い、
ユーザーごとのルールは setup_actors テーブルで管理する。
メモリサイズ調整
performance_schema の各テーブルはメモリ上に存在し、テーブルごとに最大件数やサイズを設定できる。
例)
performance_schema_events_stages_history_size=10
performance_schema_events_stages_history_long_size=10000バッファサイズを調整することで、収集データ量とメモリ消費のバランスを取る。
デフォルト設定
- MySQL 5.7以降はPerformance Schemaがデフォルト有効
- MySQL 8.0ではさらに「メモリ」「メタデータロック」計測も有効化
mysql,information_schema,performance_schemaの3スキーマは対象外
Performance Schemaの活用例
SQLステートメントの解析
events_statements_* テーブルを使うと、クエリ単位の詳細な実行情報を取得できる。
SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, NO_INDEX_USED
FROM performance_schema.events_statements_history_long
WHERE NO_INDEX_USED > 0;この結果から、インデックスを使わないクエリや一時テーブル生成の多いSQLを特定できる。sys スキーマを使えばさらに簡単に確認可能：
SELECT query, total_latency, no_index_used_count
FROM sys.statements_with_full_table_scans;主な指標（event_statement_history）
| カラム | 意味 | 重要度 |
|---|---|---|
| CREATED_TMP_DISK_TABLES | ディスク上に一時テーブル作成（最適化要） | 高 |
| SELECT_FULL_JOIN | フルテーブルJOIN（インデックス不足） | 高 |
| SELECT_RANGE_CHECK | インデックスなしJOIN | 高 |
| NO_INDEX_USED / NO_GOOD_INDEX_USED | インデックス未使用または非効率 | 高 |
| SORT_SCAN | テーブルスキャンでソート | 高 |
| CREATED_TMP_TABLES | メモリ内一時テーブル作成 | 中 |
[MEMO]
Performance Schemaを使うと、EXPLAINでは見えない実際の実行統計を収集できる。
アプリケーション側で遅延が発生しているとき、ここを照合すると原因追跡がしやすい。
まとめ
- Performance SchemaはMySQL内部の動作を数値化するための仕組みで、問題の「原因」ではなく「結果」を明確にする。
- Instrument（観測対象）とConsumer（結果保存先）の関係を理解するのが第一歩。
- 必要な計測だけを有効化し、過剰な設定は避ける。
- sysスキーマのビューを併用すると、一般的な分析を簡単に実行できる。
- 計測データはリアルタイム解析やトラブル対応だけでなく、SLO（サービスレベル目標）改善の根拠にもなる。
業務での活かし方
- Performance InsightsやDatadogと併用する
→ クラウド監視では見えないロックやI/Oの詳細を把握できる - スロークエリ調査の裏付けに使う
→ スロークエリログは「どのSQLが遅いか」まで、Performance Schemaは「なぜ遅いか」まで追える - 監視コストを最小化する
→ 問題発生時だけInstrumentを限定的にONにする運用が現実的 - SRE的運用と連携
→ 「可用性」「レイテンシ」「エラー率」をSLOとして定義し、Performance Schemaのデータを数値根拠に使う
補足：Performance Schema 活用例（by ChatGPT）
具体例が知りたかったため、ChatGPTに出してもらいました。あくまでイメージになります。
events_statements_summary_by_digest
頻出クエリ・遅いクエリの把握
実務ユースケース
管理画面「顧客一覧」だけ遅い → rows_examined が異常に多い。
取得SQL
SELECT *
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 5;サンプル結果
| digest_text | count_star | avg_timer_wait | rows_examined_avg |
|---|---|---|---|
| SELECT * FROM customers ORDER BY created_at DESC | 1523 | 0.023 s | 120000 |
| SELECT id FROM orders WHERE customer_id=? | 8420 | 0.008 s | 15 |
ポイント
120,000 行スキャン → インデックス不足
→ created_at にインデックス追加 or LIMIT 活用
events_waits_summary_by_instance
リソース遅延の原因を可視化
実務ユースケース
レポート生成が遅い → CPU ではなくロック待ちだった。
取得SQL
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS wait_ms
FROM performance_schema.events_waits_summary_by_instance
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;サンプル結果
| EVENT_NAME | COUNT_STAR | wait_ms |
|---|---|---|
| wait/lock/row/exclusive | 20100 | 5234 |
| wait/io/table/sql/handler | 5000 | 842 |
ポイント
行ロック競合が多発
→ バッチ UPDATE と管理 SELECT が衝突
events_statements_history_long
過去の遅いクエリを”遡って”調査
実務ユースケース
深夜のレプリケーション遅延の原因解析。
取得SQL
SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS time_ms
FROM performance_schema.events_statements_history_long
WHERE TIMER_WAIT > 1000000000
ORDER BY TIMER_WAIT DESC
LIMIT 10;サンプル結果
| EVENT_ID | SQL_TEXT | time_ms |
|---|---|---|
| 292919 | UPDATE orders SET status=’archived’ WHERE created_at < ‘2024-01-01’ | 3421 |
| 292920 | SELECT * FROM orders WHERE created_at < ‘2024-01-01’ LIMIT 50000 | 1889 |
ポイント
大量 UPDATE が原因
→ チャンク分割で改善
memory_summary_by_thread_by_event_name
スレッドごとのメモリ使用量
実務ユースケース
CSV エクスポートがメモリ爆食 → OOM。
取得SQL
SELECT THREAD_ID, EVENT_NAME, SUM_NUMBER_OF_BYTES_USAGE AS bytes
FROM performance_schema.memory_summary_by_thread_by_event_name
ORDER BY bytes DESC
LIMIT 5;サンプル結果
| THREAD_ID | EVENT_NAME | bytes |
|---|---|---|
| 125 | memory/sql/SELECT | 820000000 |
| 126 | memory/sql/parse | 8200000 |
ポイント
SELECT で 800MB 使用
→ ストリーミング処理にリファクタリング
table_io_waits_summary_by_table
テーブルごとの I/O ボトルネック
実務ユースケース
顧客検索が遅い → 実は access_logs の INSERT が詰まっていた。
取得SQL
SELECT OBJECT_SCHEMA, OBJECT_NAME,
COUNT_READ, COUNT_WRITE,
SUM_TIMER_READ/1000000000 AS read_ms,
SUM_TIMER_WRITE/1000000000 AS write_ms
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WRITE DESC
LIMIT 5;サンプル結果
| OBJECT_NAME | COUNT_READ | COUNT_WRITE | write_ms |
|---|---|---|---|
| access_logs | 100 | 99000 | 5320 |
| customers | 12000 | 200 | 120 |
ポイント
ログ書き込みが本番検索を阻害
→ バルク化 / 非同期キュー化
file_summary_by_event_name
temp file や binlog の I/O を可視化
実務ユースケース
temp file が激増 → ORDER BY に必要なメモリ不足。
取得SQL
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS time_ms
FROM performance_schema.file_summary_by_event_name
ORDER BY COUNT_STAR DESC
LIMIT 10;サンプル結果
| EVENT_NAME | COUNT_STAR | time_ms |
|---|---|---|
| file/tmpfile | 12000 | 1800 |
| file/innodb/innodb_log_file | 9500 | 320 |
ポイント
一時ファイルが多い → ソート/集計の重さ
→ インデックス追加 or sort_buffer_size 調整
events_stages_history_long
クエリのどの段階が重いか
実務ユースケース
集計処理が遅い → GROUP BY がボトルネック。
取得SQL
SELECT NESTING_EVENT_ID, EVENT_NAME,
TIMER_WAIT/1000000000 AS time_ms
FROM performance_schema.events_stages_history_long
ORDER BY TIMER_WAIT DESC
LIMIT 10;サンプル結果
| EVENT_NAME | time_ms |
|---|---|
| stage/sorting | 1120 |
| stage/creating tmp table | 880 |
| stage/executing | 55 |
ポイント
sorting + tmp table が主犯
→ GROUP BY インデックス最適化へ
threads テーブル
現在の接続状態確認
実務ユースケース
バッチ暴走で接続枯渇 → 即特定。
取得SQL
SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_STATE
FROM performance_schema.threads
WHERE TYPE='FOREGROUND';サンプル結果
| THREAD_ID | USER | STATE |
|---|---|---|
| 77 | batch_user | Sending data |
| 78 | webapp | Sleep |
| 79 | webapp | Locked |
ポイント
batch_user がロック保持
→ KILL 77 で解放
コメント