DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

MySQLパフォーマンス監視：SHOW STATUSとSHOW PROCESSLISTの活用

SHOW STATUSとSHOW PROCESSLISTという2つの必須コマンドを使用して、リアルタイムのMySQLパフォーマンス監視を習得します。グローバルパフォーマンスカウンターの解釈、アクティブな接続の特定、長時間実行クエリやブロッキングクエリの発見、リソースボトルネックの即時診断方法を学びます。このガイドでは、スレッドアクティビティ、InnoDBメトリクスの分析、KILLなどのターゲットアクションの実行に関する実践的な例を提供します。

MySQLパフォーマンス監視：SHOW STATUSとSHOW PROCESSLISTの活用

MySQLをバックエンドに使用するアプリケーションが遅くなった場合、ダッシュボードを開く前に実行できる最も迅速な組み込みチェックはSHOW STATUSとSHOW PROCESSLISTです。これらだけで全ての問題を説明できるわけではありませんが、サーバーがこれまで何をしてきたか、そして現在何が実行されているかという2つの実用的な質問に答えてくれます。

SHOW STATUSによるリアルタイムシステムヘルスの理解

SHOW STATUSコマンドは、SHOW GLOBAL STATUSやSHOW SESSION STATUSと同義で使用されることが多く、サーバーが最後に再起動されてから、または現在のセッションが開始されてからのサーバーアクティビティに関する豊富な情報を提供します。これらのステータス変数はカウンターとして機能し、接続試行からキャッシュ効率、ロック待機まであらゆるものを追跡します。

グローバルステータスとセッションステータス

このコマンドを実行する際、そのスコープを理解することが重要です：

  • SHOW GLOBAL STATUS：MySQLサーバーインスタンスが起動してから蓄積されたカウンターを表示します。これにより、サーバー全体のヘルスと長期的な傾向を俯瞰できます。
  • SHOW SESSION STATUS：現在使用している接続（セッション）に固有のカウンターのみを表示します。これは特定のトランザクションのパフォーマンスへの影響を分離するのに役立ちます。

SHOW GLOBAL STATUSからの主要業績評価指標（KPI）

SHOW GLOBAL STATUSは数百の変数を返しますが、初期のパフォーマンストリアージにはいくつかが重要です。通常、出力をgrepにパイプするか、WHERE句を使用して関連性のあるものにフィルタリングします。

1. 接続とスレッドの監視

これらの変数は接続負荷を理解するのに役立ちます：

変数名 説明
Threads_connected 現在開いている接続（クライアント）の数。
Threads_running 現在クエリを実行しているアクティブなスレッドの数（通常は低くあるべき）。
Max_used_connections サーバー起動以降の同時接続の最大数。max_connectionsのサイジングに役立ちます。

例： アクティブな接続の確認：

SHOW GLOBAL STATUS LIKE 'Threads_%';

2. クエリキャッシュと効率性

レガシークエリキャッシュ（古いMySQLバージョンで利用可能、新しいバージョンでは非推奨/削除済み）を使用している場合、これらのメトリクスは重要です：

  • Qcache_hits：クエリがキャッシュから提供された回数。
  • Qcache_lowmem_prunes：メモリ不足によりキャッシュが古いエントリを削除したクエリの数。

3. InnoDBエンジンメトリクス（現代のMySQLで最も重要）

InnoDBストレージエンジンを使用する現代のデプロイメントでは、バッファプールアクティビティを監視します：

  • Innodb_buffer_pool_read_requests：読み取りリクエストの総数。
  • Innodb_buffer_pool_reads：ディスクからの物理読み取りの数（物理読み取りのリクエストに対する比率が高い場合、より大きなバッファプールが必要であることを示します）。

実用的なヒント： バッファプールの効率性を迅速に評価するには、ヒット率を計算します：(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests。

4. 一時テーブルとソート

これらはMySQLが内部処理をどれだけ行っているかを示します：

  • Created_tmp_tables：作成されたメモリ内一時テーブルの数。
  • Created_tmp_disk_tables：ディスクに書き込まなければならなかった一時テーブルの数（低速）。

Created_tmp_disk_tablesが高い場合、tmp_table_sizeまたはmax_heap_table_sizeを増やす必要があるかもしれません。

SHOW PROCESSLISTによるアクティブワークロードの診断

SHOW STATUSがこれまでに何が起こったかを教えるのに対し、SHOW PROCESSLISTは今何が起こっているかを教えます。サーバー内で現在実行中のスレッドに関する情報を表示し、長時間実行クエリやブロックされたクエリを特定できます。

プロセスリストの構造

コマンドはいくつかのカラムを出力し、それぞれがアクティブな接続に関するコンテキストを提供します：

カラム 説明
Id 一意の接続ID（プロセスを強制終了するために使用）。
User 接続しているユーザーアカウント。
Host 接続元のホスト。
db スレッドが現在使用しているデータベース。
Command 実行中のコマンドの種類（例：Query、Sleep、Connect）。
Time スレッドが現在の状態にある秒数。
State スレッドが実行している特定のアクション（例：Sending data、Copying to tmp table）。
Info 実行中の実際のSQL文（長い場合は切り捨てられる可能性あり）。

出力のフィルタリングと解釈

大規模な本番システムでは、完全なプロセスリストは圧倒される可能性があります。FULLキーワードを使用してクエリテキスト全体を確認し、TimeまたはStateカラムでフィルタリングするのが標準的な方法です。

1. 完全なコマンドテキストの表示

遅いクエリが疑われる場合は常にFULLを使用します。標準出力ではInfoフィールドが切り捨てられることが多いためです：

SHOW FULL PROCESSLIST;

2. ブロッキングクエリや遅いクエリの特定

TimeとCommandカラムを監視します：

  • 高いTime値：長時間実行されているクエリ（例：SLAに応じて10秒以上）は直ちに調査が必要です。対応するInfoカラムでSQLを確認します。
  • Command = 'Sleep'：これらの接続はアイドル状態ですが、リソースを消費しています。過剰に蓄積される場合は、wait_timeout変数の調整を検討します。
  • Command = 'Query'：これらはアクティブに実行されているステートメントです。そのStateに特に注意を払います。

3. ロック問題の特定

クエリがリソース待ちでスタックしている場合、Stateカラムがそれを示すことがよくあります：

  • Waiting for table metadata lock
  • Waiting for table lock
  • Waiting for lock

待機状態のスレッドが多数見られる場合、競合を示しており、通常は他のスレッドが必要とするロックを保持している長時間実行トランザクションが原因です。

アクション：プロセスの終了

パフォーマンスを著しく低下させている暴走クエリを特定した場合、KILLコマンドにプロセスIdを指定して終了できます：

KILL 12345; -- 12345をプロセスリストの実際のIdに置き換えてください

警告： KILLは慎重に使用してください。アクティブなトランザクションを終了すると、トランザクションが複雑な書き込み操作の途中だった場合、データベースが不整合な状態になる可能性があります。可能であれば、まずクエリを特定して最適化することを試みてください。

トラブルシューティングのためのステータスとプロセス情報の組み合わせ

効果的なMySQL監視には、これら2つのコマンド間の相関関係がしばしば必要です：

  1. 初期チェック： SHOW FULL PROCESSLISTを実行します。高い時間のクエリや過剰な接続がないか確認します。
  2. コンテキストチェック： SHOW GLOBAL STATUS LIKE 'Threads_connected'を使用して接続数を確認します。フラッド（大量の接続）なのか、単一の悪いクエリなのかを判断します。
  3. 詳細調査： 特定のクエリが遅い場合、クエリ実行中にInnodb_buffer_pool_readsや一時テーブル作成率などのリソースカウンターへの影響を分析します（ベースライン比較が必要）。

これらの動的出力を定期的にチェックすることで、推測を超えてターゲットを絞ったソリューションを適用し、MySQLの安定性と速度を向上させることができます。

現実的なトリアージルーチン

適切な最初のパスは1分もかかりません。プロセスリストから始めます：

SHOW FULL PROCESSLIST;

アクティブなクエリの山、長いTime値、ロック待機、多数のアイドル状態のSleep接続をスキャンします。単一の遅いレポートクエリは、同じテーブルロックを待機している何百ものWeb接続とは異なる方法で処理されます。

次に、スレッドカウンターを確認します：

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections'
);

Threads_connectedは接続しているクライアントの数を示します。Threads_runningは、スローダウン中は通常より重要です。これはアクティブに作業を行っているスレッドの数を示すからです。接続された多くのスリープ状態のクライアントは無駄かもしれませんが、多くの実行中のスレッドはサーバーが実際のプレッシャー下にあることを意味します。

次に、ワークロードがディスクベースの一時テーブルを作成しているかどうかを確認します：

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Sort_merge_passes'
);

これらのカウンターは起動時からの累積であるため、1回のスナップショットでは誤解を招く可能性があります。インシデント中に数分間隔で2回のスナップショットを取得します。ディスク一時テーブルが急速に増加している場合は、GROUP BY、ORDER BY、大きな結合、テキストカラム、または欠落したインデックスを持つクエリを調査します。tmp_table_sizeを上げることが役立つ場合もありますが、より良いクエリやインデックスがよりクリーンな修正であることがよくあります。

InnoDBのプレッシャーを見る

ほとんどの現代のMySQLデプロイメントはInnoDBを使用しているため、InnoDBカウンターに注意を払う価値があります：

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requestsは論理読み取りリクエストをカウントします。Innodb_buffer_pool_readsはディスクにアクセスしなければならなかった読み取りをカウントします。通常のトラフィック中に物理読み取りが急速に増加している場合、バッファプールがワーキングセットに対して小さすぎるか、クエリが過剰なデータをスキャンしているか、バッチジョブが有用なページをキャッシュから追い出している可能性があります。

ロック待機も一般的な問題の原因です：

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_row_lock%';

行ロック待機の増加は、自動的にInnoDBが壊れていることを意味するわけではありません。通常、トランザクションがアプリケーションの想定よりも長くロックを保持していることを意味します。オープントランザクション、遅い更新、またはトランザクションを開始し、外部サービスを呼び出し、後でコミットするコードパスを探します。

より深いロックとトランザクションの詳細については、SHOW ENGINE INNODB STATUS\Gが役立ちますが、その出力は密度が高いです。プロセスリストがロック待機を示し、その背後にあるトランザクションパターンを特定する必要がある場合に使用します。

KILLのより安全な使用法

KILLは便利ですが、クリーンアップボタンではありません。大規模なトランザクションを実行している接続を強制終了すると、MySQLは作業をロールバックする必要がある場合があり、ロールバックには時間がかかることがあります。一部のインシデントではそれが正しい選択である場合もありますが、意図的に決定を下してください。

MySQLのバージョンと権限がサポートしている場合は、最初にクエリを強制終了することを好みます：

KILL QUERY 12345;

これにより、接続を維持したまま現在のステートメントを停止しようとします。クライアントが不正動作しているか、接続を切断する必要がある場合は、次を使用します：

KILL CONNECTION 12345;

何かを強制終了する前に、プロセスリストの行、ユーザー、ホスト、データベース、SQLテキストをキャプチャします。インシデント後、その詳細は、同じクエリが戻ってくるのを待つのではなく、原因を修正するのに役立ちます。

一般的なプロセスリストの状態とその示唆するもの

Sending dataは、必ずしもMySQLがネットワーク経由で行を送信していることを意味するわけではありません。多くの場合、サーバーが行の読み取り、フィルタリング、ソート、または準備を行っていることを意味します。クエリがそこで長時間を費やしている場合、ステートメントに対してEXPLAINを実行し、テーブルスキャン、不適切な結合順序、または欠落したインデックスを探します。

Copying to tmp tableまたはCreating sort indexは、多くの場合、高コストなソートやグループ化を示しています。WHEREとORDER BYのパターンをインデックスがサポートできるかどうかを確認します。時には、クエリは製品が要求したことを正確に実行しているが、リクエストパスではなく非同期レポートに属する場合があります。

Waiting for table metadata lockは、DDLと通常のクエリが衝突したときに頻繁に表示されます。一見単純なALTER TABLEがオープントランザクションの背後で待機し、後続のクエリが保留中のDDLの後ろに積み重なる可能性があります。その場合、すべての待機クエリを強制終了するよりも、最も古いブロッカーを強制終了する方が安全かもしれません。

カウンターを有用な証拠に変える

SHOW STATUSの値はほとんどがカウンターであるため、生の数値よりもレートの方が有用です。同じ変数を2回キャプチャします：

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

60秒待ってから、再度実行します。その差がその1分間のレートを示します。これはダッシュボードが使用するのと同じ考え方ですが、ターミナルアクセスしかない場合に手動で行うと役立ちます。

インシデント中はメモを取っておきます。「Threads_runningが8から90に跳ね上がり、プロセスリストはordersのメタデータロックを待っている70のクエリを示し、Max_used_connectionsは変化しなかった」は有用な診断です。「MySQLが遅かった」はそうではありません。

これらのコマンドだけでは不十分な場合

SHOW STATUSとSHOW PROCESSLISTは初動対応ツールです。これらはスロークエリログ、Performance Schema、クエリプラン、またはホストレベルのメトリクスを置き換えるものではありません。同じ問題が再発する場合は、スロークエリログを有効にするか確認し、EXPLAINを使用して最悪のステートメントを調査します。

繰り返し発生する接続スパイクについては、アプリケーションのプール設定とデプロイメント動作を確認します。max_connectionsを上げることで時間を稼げるかもしれませんが、サーバーが実際に実行できる以上の作業を受け入れる可能性もあります。繰り返し発生するロック待機については、アプリケーション内のトランザクション境界を調査します。コードが外部APIを呼び出している間もトランザクションが開いたままになると、無関係なリクエストをブロックし、MySQLを実際よりも遅く見せる可能性があります。

また、ホストも確認します。ディスクレイテンシが高い、CPUが飽和している、メモリがスワップしている、またはノイジーネイバーがリソースを盗んでいる場合、MySQLカウンターは症状を示しますが、原因全体は示しません。適切な診断は、データベースコマンドとシステムメトリクスを組み合わせます。

SHOW STATUSはカウンターとコンテキストを提供します。SHOW FULL PROCESSLISTはライブワークロードを提供します。これらを一緒に使用することで、接続プレッシャー、1つの悪いクエリ、ロック競合、ディスク負荷の高い一時作業、InnoDBキャッシュプレッシャーの違いを見分けることができます。