DevOpsナレッジハブ
DevOpsナレッジハブ

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

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

50 ビュー

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

パフォーマンスのボトルネックを診断し、MySQLデータベースの健全性を理解することは、すべての管理者や開発者にとって不可欠なスキルです。遅いクエリ、接続の氾濫、または予期せぬリソース利用は、アプリケーションのパフォーマンスに深刻な影響を与える可能性があります。幸いなことに、MySQLには、即座にリアルタイムのインサイトを提供する、組み込みで簡単にアクセスできるコマンドが用意されています。この記事では、パフォーマンス診断に最も重要な2つのコマンド、SHOW STATUSとSHOW PROCESSLISTについて詳しく掘り下げます。

これらのツールを習得することで、アクティブな接続を分析し、サーバー全体のカウンターを確認し、システムリソースがどこで消費されているかを正確に特定する能力を得ることができます。

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. 完全なコマンドテキストの表示

標準出力ではInfoフィールドが切り詰められることが多いため、遅いクエリを疑う場合は常にFULLを使用してください。

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

多数のスレッドが待機状態にある場合、それは競合を示しています。これは通常、他のスレッドが必要とするロックを長時間のトランザクションが保持していることによって引き起こされます。

アクション: プロセスの強制終了

パフォーマンスを著しく低下させている暴走クエリを特定した場合、プロセスIdに続けてKILLコマンドを使用してそれを終了させることができます。

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

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

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

効果的なMySQL監視には、これら2つのコマンド間の相関関係を把握することがよく含まれます。

  1. 初期チェック: SHOW FULL PROCESSLISTを実行します。実行時間の長いクエリや過剰な接続がないか確認します。
  2. コンテキストチェック: SHOW GLOBAL STATUS LIKE 'Threads_connected'を使用して接続数を確認します。接続の氾濫に直面しているのか、それとも単に1つの問題のあるクエリに直面しているのか？
  3. 詳細分析: 特定のクエリが遅い場合、クエリの実行中にInnodb_buffer_pool_readsや一時テーブル作成率を確認し、リソースカウンターへの影響を分析します（ベースラインとの比較が必要です）。

これらの動的な出力を定期的にチェックすることで、当て推量を超越し、MySQLの安定性と速度を向上させるための的を絞った解決策を適用できます。

まとめ

SHOW STATUSとSHOW PROCESSLISTコマンドは、リアルタイムのMySQL診断のための最前線のツールです。SHOW STATUSはサーバー設定を調整するために必要な履歴コンテキストとカウンターメトリクスを提供し、SHOW PROCESSLISTは現在の緊急事態や長時間実行されている実行に対処するために必要な即時スナップショットを提供します。これらのコマンドを定期的に利用することは、高性能なデータベース環境を維持するために不可欠です。