Posted inMySQL

Performance Schemaとsys Schemaを活用してMySQLのI/Oボトルネックを解消する実践ガイド

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

1. 深夜に突如訪れるI/Oボトルネックの悪夢

時計の針は深夜1時30分を指していた。オンコール担当のSlackチャンネルにはP1アラートがけたたましく鳴り響く。サーバーのCPU使用率は98%まで跳ね上がり、ディスクI/Oは100%に張り付き、NVMe SSDのディスクレイテンシ（await）は普段の0.5msから一気に45msへと急騰。ショッピングカートAPIのレスポンスタイムは通常の50msから30秒のタイムアウトへと悪化し、ユーザーからの問い合わせやクレームが殺到し始める。

多くのエンジニアが反射的に真っ先に実行するのが SHOW PROCESSLIST; だろう。しかし、画面には数百ものコネクションが一気に流れ落ちてくる。大半の SELECT クエリは Sending data や Creating tmp table といったステータスで止まっている。その混沌とした情報の中から、一体どのクエリが最初の引き金となったのかを見分けることは不可能に近い。どのテーブルがディスクに過大な負荷をかけているのか？どのスレッドがメモリを食い尽くしているのか？この状況において、SHOW PROCESSLIST; は完全に無力だ。

同じようなステータスが並ぶ何百行ものリストを手作業で探っても、混乱が深まるだけだ。パニック状態で当て推量に頼るのではなく、具体的で実用的なメトリクスが必要不可欠となる。

2. I/Oボトルネックの真の原因はどこにあるのか？

ハードウェア障害を除けば、InnoDBにおけるI/Oボトルネックは主に次の3つの原因から発生する：

  • フルテーブルスキャン（Full Table Scan）: インデックスが適切に利用されないクエリにより、InnoDBはディスクからBuffer Poolへ何百万ものデータページを読み込まざるを得なくなる。Buffer Poolが枯渇すると、空き領域を確保するためにダーティページのフラッシュ処理が激しく走り、ディスク帯域を圧迫する。
  • 一時テーブルのディスク溢れ（Disk Temp Tables）: GROUP BY、DISTINCT、UNION などの処理が temptable_max_ram（MySQL 8.0の場合）や tmp_table_size の閾値を超えると、データは即座にディスクへ退避され、InnoDBまたはMyISAMの一時テーブルとして書き出される。
  • REDOログおよびダブルライトバッファの競合: 大量のINSERT/UPDATEを含むトランザクションによりデータが一気に書き込まれ、I/Oスレッドの処理が追いつかなくなる。

一般的なツールは「ディスクが逼迫している」ことしか教えてくれない。ディスクを待たせている原因がどのスレッドにあるのかまでは特定できないのだ。

3. 従来の診断ツールにおける限界

アプローチ1: OSコマンド（top, htop, iostat）

# 毎秒の詳細なI/O統計を監視
iostat -xz 1 5

限界: iostat コマンドでわかるのは、mysqld プロセスが %util 100%に達し、await が極めて高くなっているという事実だけだ。OSレイヤーからは内部の詳細まで掘り下げることはできない。どのテーブルやどの .ibd データファイルが数千ものIOPSを引き起こしているのかまでは特定できないのだ。

アプローチ2: スロークエリログ（Slow Query Log）とEXPLAINの併用

EXPLAIN ANALYZE 
SELECT * FROM orders WHERE status = 'PENDING' ORDER BY created_at DESC;

限界: スロークエリログはクエリが実行完了した後にしか記録されない。重いトランザクションが15分間ハングしている最中には、スローログには何も出力されない。さらに「無数の小さな傷による死（Death by a thousand cuts）」のパターン――例えば実行時間20msの軽量クエリが1秒間に1,000回一斉に実行されるようなケースでは、間接的にI/Oを圧迫しているにもかかわらず、long_query_time = 1 に設定されているとログをすり抜けてしまう。

4. 解決策: Performance Schemaとsys Schemaの活用

MySQL 5.7以降（MySQL 8.0ではデフォルト有効）、Performance Schema は非常に低いオーバーヘッド（CPU負荷わずか1%〜3%程度）で動作するように設計されている。そしてこれに付属する sys Schema は、複雑な生メトリクスを ms、sec、MB、GB といった人間が直感的に理解しやすいフォーマットへ変換してくれる強力な組み込みビュー群を提供する。

ステップ1: Performance Schemaが有効になっているか確認する

サーバー上のシステム変数を確認する：

SHOW VARIABLES LIKE 'performance_schema';

値が ON であれば準備完了だ。もし OFF と表示された場合は、/etc/my.cnf ファイルに以下の設定を追加してMySQLを再起動する：

[mysqld]
performance_schema=ON

ステップ2: 最も実行時間を消費しているクエリ上位5件を特定する

statement_analysis ビューにクエリを投げて、サーバー起動以降で合計実行時間（total latency）が最も大きい上位のステートメントを特定する：

SELECT 
    query,
    exec_count,
    total_latency,
    avg_latency,
    rows_sent_avg,
    rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 5;

rows_examined_avg（平均スキャン行数）と rows_sent_avg（平均返却行数）の2つのカラムを比較してみよう。もし、わずか10行の結果を取得するために平均50万行ものスキャンが行われているなら、カバリングインデックス等のインデックス不足によって無駄なディスク読み取りを発生させている真犯人を突き止めたことになる。

ステップ3: フルテーブルスキャンが最も頻発しているテーブルを特定する

schema_tables_with_full_table_scans ビューを使えば、フルテーブルスキャンが最も多発しているテーブルを即座に抽出できる：

SELECT 
    object_schema,
    object_name,
    rows_full_scanned,
    latency
FROM sys.schema_tables_with_full_table_scans
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema')
ORDER BY latency DESC
LIMIT 5;

1,000万件ものレコードを抱えるテーブルで rows_full_scanned が継続して跳ね上がっている場合、アプリケーション側でインデックス付きカラムの絞り込み条件が漏れている可能性が高い。

ステップ4: 最もI/O負荷がかかっている.ibdファイルを特定する

ディスクを圧迫している物理ファイルを正確に特定するには、io_global_by_file_by_bytes ビューを参照する：

SELECT 
    file,
    count_read,
    total_read,
    count_write,
    total_written
FROM sys.io_global_by_file_by_bytes
WHERE file LIKE '%.ibd'
ORDER BY (total_read + total_written) DESC
LIMIT 5;

この結果から、どのテーブルスペースファイルが最も大きな読み取り／書き込みボリュームを抱えているかが明確になる。該当テーブルを独立したストレージボリュームへ退避させる、データをパーティショニングする、OPTIMIZE TABLEによるテーブル断片化の最適化を行う、InnoDB Page Compressionを適用する、あるいはRedis等でのキャッシュ導入を検討するといった対策を講じることができる。

ステップ5: ディスク一時テーブルを強制発生させているクエリを検出する

statements_with_temp_tables ビューを使用すると、ディスク上に一時テーブルを作成しているステートメントを漏れなく捕捉できる：

SELECT 
    query,
    exec_count,
    memory_tmp_tables,
    disk_tmp_tables,
    avg_latency
FROM sys.statements_with_temp_tables
WHERE disk_tmp_tables > 0
ORDER BY disk_tmp_tables DESC
LIMIT 5;

disk_tmp_tables の値が大きい場合は、対象のSQL文が GROUP BY 実行時に TEXT や BLOB カラムを含む SELECT * を行っていないか確認しよう。あわせて、temptable_max_ram（MySQL 8.0、デフォルト1GB）やMySQL 5.7の tmp_table_size の設定値をチェックし、必要に応じて適切に調整する。

ステップ6: メトリクスをリセットして最適化の効果を検証する

インデックスの追加（あわせて書き込み負荷を抑えるための未使用インデックスの削除）やアプリケーションコードの修正が完了したら、統計カウンターをクリアして新たな測定セッションを開始する：

CALL sys.ps_truncate_all_tables(FALSE);

この操作は、Performance Schemaがメモリ上に保持している統計データのみを消去するものであり、実業務のテーブルデータには一切影響を与えない。これにより、最適化前後のI/O負荷を正確に比較・検証できるようになる。本番投入前の負荷検証には、mysqlslapによるストレステストを活用するのも有効だ。

Share:
Tags: