0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@numazaki345

MySQL内部の情報をSQLで読む

0
Posted at

はじめに

OSSのモニタリングツールや冗長構成管理ツールを導入することで、アラートやダッシュボードから簡単に異常がわかるようになりました。しかし、異常が発覚してから即座に復旧するためには、MySQLクライアントから接続してSQLで柔軟にその時の状況を把握・修正できるMTTRを短縮できます。

本記事は『MySQL運用・管理 実践入門』第7章4節の内容をもとに、MySQLの内部情報を確認するための3つの情報源（SHOWコマンド・performance_schema・information_schema）についてまとめています。

3つの情報源の使い分け

情報源 用途 特徴
SHOW コマンド 即座の状態確認 シンプルで手軽。高負荷時の注意点あり
performance_schema パフォーマンス傾向分析 起動後の統計情報のみ。バックアップには含まれない
information_schema InnoDBの詳細情報 トランザクション・テーブルスペースなど

1. SHOW コマンド

SHOW [FULL] PROCESSLIST

実行中のプロセス（接続中のセッション）を一覧するコマンドです。

SHOW FULL PROCESSLIST;

FULL を付けると Info カラムのクエリが先頭100文字で切られなくなります。

確認すべき主なカラム：

カラム 説明
Id MySQL内部のスレッドID
User 接続ユーザー
Host 接続元ホスト
Time プロセスの稼働秒数
State 実行状況
Info 実行中のクエリ

高負荷時の注意点

SHOW PROCESSLIST は内部で information_schema.PROCESSLIST テーブルを参照しており、これはMutexロックを取得します。高負荷時に実行するとかえって状況を悪化させる可能性があります。

MySQL 8.0.22以降では performance_schema.processlist の使用が推奨されています。

-- MySQL 8.0.22以降で推奨
SELECT * FROM performance_schema.processlist;

-- または設定変更でSHOW PROCESSLISTの参照先を変更
SET GLOBAL performance_schema_show_processlist = ON;

SHOW ENGINE INNODB STATUS

InnoDBの詳細なステータス情報を確認できます。

SHOW ENGINE INNODB STATUS\G

出力のセクションとその代替手段：

セクション 代替手段
TRANSACTIONS performance_schema.data_locks, performance_schema.data_lock_waits
BUFFER POOL AND MEMORY information_schema.INNODB_BUFFER_POOL_STATS
LOG SHOW GLOBAL STATUS LIKE '%lsn%'
ROW OPERATIONS SHOW GLOBAL STATUS LIKE '%Innodb_rows%'

MySQL 8.0以降では performance_schema に代替となるテーブルが充実しているため、このコマンドの出力を頑張って読み解く必要は減ってきています。

SHOW [GLOBAL] STATUS

サーバー内部のステータス変数を確認します。約500個あるため、プレフィックスで絞り込みます。

SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_rows%';
SHOW GLOBAL STATUS LIKE 'Threads_%';

実務でよく使うプレフィックス：

プレフィックス 用途
Com_ SELECT/UPDATE/DELETEの実行回数比較。Com_update と Handler_Update を比較してUPDATEの実行状況を把握
Innodb_rows_ 行単位の操作数。結果セットが急増していないか確認
Slow_queries スロークエリの累積数。差分を取って急増を監視
Threads_running 同時実行スレッド数。多い場合はCPUリソース不足を疑う
Connections 接続数の傾向。Connection_errors_max_connections が増えていたら上限に近い
Open_ 開いているテーブルやファイル数。table_open_cache が足りているか確認

SHOW REPLICA STATUS

レプリケーション構成を取っている場合に使います。

SHOW REPLICA STATUS\G

確認すべきカラム：

カラム 正常 異常のサイン
Replica_IO_Running Yes No
Replica_SQL_Running Yes No
Seconds_Behind_Source 0に近い 増加し続けている

2. performance_schema

パフォーマンススキーマの機能は performance_schema 変数で有効化されています（デフォルト：ON）。起動後の統計情報のみ保持しており、mysqldumpなどのバックアップには含まれません。

events_statements_summary_by_digest

実務で最も役立つテーブルです。クエリをダイジェスト（パラメータを除いた形）ごとに集計します。

スロークエリログとの違い：スロークエリログは1回の実行時間が long_query_time を超えたものしか記録しませんが、このテーブルではトータルの実行時間が長いクエリを発見できます。

たとえば1回あたり10msで1万回実行されているクエリは、スロークエリログには出ませんが合計100秒の負荷をかけています。

-- トータル実行時間が長いクエリを上位10件取得
SELECT
    DIGEST_TEXT,
    COUNT_STAR                                    AS 実行回数,
    ROUND(AVG_TIMER_WAIT / 1000000000, 3)         AS 平均実行時間_ms,
    ROUND(SUM_TIMER_WAIT / 1000000000, 3)         AS 合計実行時間_ms,
    SUM_ROWS_EXAMINED                             AS 合計スキャン行数,
    ROUND(QUANTILE_95 / 1000000000, 3)            AS p95_ms,
    ROUND(QUANTILE_99 / 1000000000, 3)            AS p99_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

SUM_SELECT_FULL_JOIN や SUM_NO_INDEX_USED が大きいクエリはインデックスを使えていないサインです。

file_summary_by_instance

システムファイル（Redoログ、ダブルライトファイル、テンポラリテーブルのファイルなど）へのI/O情報を確認できます。

SELECT
    FILE_NAME,
    COUNT_READ,
    COUNT_WRITE,
    ROUND(SUM_NUMBER_OF_BYTES_READ / 1024 / 1024, 1)  AS 読み込みMB,
    ROUND(SUM_NUMBER_OF_BYTES_WRITE / 1024 / 1024, 1) AS 書き込みMB
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

table_io_waits_summary_by_table

テーブルごとに取得・更新した行数とかかった時間を集計します。COUNT_READ や COUNT_WRITE が多いテーブルはbuffer_poolから頻繁に追い出されてI/Oを行っているサインで、シャーディングやパーティショニングの検討対象になります。

SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    ROUND(SUM_TIMER_WAIT / 1000000000, 3) AS 合計待ち時間_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

3. information_schema

MySQLのメタデータに関する情報を提供します。InnoDBのパフォーマンス情報を提供するテーブルもあります。

INNODB_TRX

実行中のすべてのトランザクション情報を提供します。長時間トランザクションの特定に使います。

-- 実行中トランザクションと実行クエリを取得
SELECT
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS 経過秒数,
    trx_isolation_level,
    processlist_host,
    processlist_user,
    sql_text
FROM information_schema.INNODB_TRX trx
INNER JOIN performance_schema.threads th
    ON trx.trx_mysql_thread_id = th.processlist_id
INNER JOIN performance_schema.events_statements_history e
    ON th.thread_id = e.thread_id
ORDER BY trx_started ASC;

問題のトランザクションを特定できたら trx_mysql_thread_id を使ってKILLします。

-- trx_mysql_thread_id でKILL（trx_id ではない点に注意）
KILL <trx_mysql_thread_id>;

注意：サービスにとって重要なトランザクションをKILLするとロールバックが発生します。原因を把握してから対処しましょう。

INNODB_METRICS

InnoDBのパフォーマンス情報やリソース状況を確認できます。デフォルトではすべての計器が有効になっているわけではなく、個別に有効化が必要なものがあります。

-- 有効化
SET GLOBAL innodb_monitor_enable = 'buffer_pool_reads';

-- 確認
SELECT NAME, COUNT, STATUS, COMMENT
FROM information_schema.INNODB_METRICS
WHERE NAME LIKE 'buffer_pool%'
  AND STATUS = 'enabled';

INNODB_TABLESPACES

各テーブルの実体となるibdファイルの情報を提供します。FILE_SIZE カラムに正確なファイルサイズが格納されており、正確な値を返すのはこのテーブルだけです（他のテーブルは見積もり値）。

SELECT
    NAME,
    ROUND(FILE_SIZE / 1024 / 1024, 1) AS ファイルサイズ_MB
FROM information_schema.INNODB_TABLESPACES
WHERE NAME NOT LIKE 'mysql/%'
  AND NAME NOT LIKE 'sys/%'
ORDER BY FILE_SIZE DESC
LIMIT 10;

障害時の調査フロー

これらの情報源を組み合わせた実際の調査フローはこのようになります。

1. SHOW FULL PROCESSLIST（または performance_schema.processlist）
   → 何が実行中か・詰まっているクエリはないか確認

2. SHOW GLOBAL STATUS LIKE 'Threads_running'
   → 同時実行スレッド数が異常に多くないか確認

3. information_schema.INNODB_TRX
   → 長時間トランザクションが原因でないか確認

4. performance_schema.events_statements_summary_by_digest
   → トータル実行時間が長いクエリを特定

5. SHOW REPLICA STATUS（レプリカ構成の場合）
   → レプリケーションのI/O・SQLスレッドが正常か確認

RDS環境での注意点

RDSでもこれらのSQLはほぼそのまま使えます。ただし以下の点に注意が必要です。

SHOW ENGINE INNODB STATUS はRDSでも使えますが、出力フォーマットがバージョンによって異なるため読み解きが難しい場面があります。performance_schema と information_schema のテーブルをメインで使うのが現実的です。

また Performance Insights が events_statements_summary_by_digest に相当する情報をGUIで提供しているため、日常的なクエリ調査はそちらを出発点にするのが効率的です。Performance InsightsからCloudWatchへのエクスポートも可能です。

まとめ

確認したいこと 使うべき手段
今どんなクエリが動いているか SHOW FULL PROCESSLIST / performance_schema.processlist
スロークエリログに出ないが重いクエリ events_statements_summary_by_digest
長時間トランザクションの特定 INNODB_TRX + performance_schema.threads
テーブルごとのI/O負荷 table_io_waits_summary_by_table
ibdファイルの正確なサイズ INNODB_TABLESPACES
レプリケーションの状態 SHOW REPLICA STATUS
スロークエリの急増監視 SHOW GLOBAL STATUS LIKE 'Slow_queries'
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?