MySQLの状況を知る

MySQLの状況を確認するといえば、過去2回 第12回　MySQLのヘルスチェックをする［死活監視の基礎編］, 第14回　MySQLのヘルスチェックをする［応用的な死活監視編］でMySQLのプロセスが稼働しているかどうかを確認する方法を紹介しました。

今回は「MySQLの状況」の中でも、ステータス変数を通してMySQLの状況を確認する方法を紹介します。

SHOW STATUSステートメント

MySQLではmysqldの稼働中にステータス変数と呼ばれる変数を持っており、特定の操作が行われるたびに変数が更新（多くのステータス変数はカウンターであり、インクリメントされることが多い）されていきます。

SHOW STATUSステートメントはMySQL内部のステータス変数にアクセスし、値を表示させるためのステートメントです。

mysql> SHOW STATUS;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name                                 | Value                                            |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients                               | 0                                                |
..
| Uptime_since_flush_status                     | 603979                                           |
+-----------------------------------------------+--------------------------------------------------+
356 rows in set (0.00 sec)

ドキュメントのSHOW STATUS構文にも記載がありますが、SHOW STATUSステートメントの利用方法をざっくりとまとめます。

1. 一般的な用途で利用する場合はSHOW GLOBAL STATUSとGLOBAL修飾子を使う。GLOBAL修飾子を指定しない場合、セッションスコープのステータス変数が表示される（後述）
2. 表示項目を絞って表示させるにはLIKE '変数名ワイルドカード'またはWHERE 式が利用できる。WHERE句ではWHERE variable_name LIKE .. AND value > 10000のような式が記述できるが、変数名以外でマッチさせることはまれ
3. セッションスコープ、グローバルスコープともSHOW STATUSの実行に特別な権限は必要ない。MySQLに接続さえできればどのユーザーでも表示可能

ステータス変数のスコープ

ステータス変数にはセッションスコープ（現在のセッションのみのカウントアップ値）とグローバルスコープ（mysqldが起動してからの累計値）あるいはその両方があり、修飾子なしのSHOW STATUSはSHOW SESSION STATUS（またはSHOW LOCAL STATUS）と等価です。

SHOW SESSION STATUS, SHOW GLOBAL STATUSと両スコープの表示関係は以下の表のようになっています。

一般的にステータス変数にアクセスする際はMySQL全体の状況を確認する目的が多く、グローバルスコープの値を表示させるのが適しています。SELECTステートメントが何回実行されたかをカウントアップするCom_selectステータス変数であれば、修飾子なしのSHOW STATUS LIKE 'Com_select'（＝そのセッションの中で何回SELECTステートメントが実行されたか）ではなくSHOW GLOBAL STATUS LIKE 'Com_select'（＝mysqldが起動してから合計何回SELECTステートメントが実行されたか）を知りたいことが多いでしょう。

mysql> SHOW STATUS LIKE 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 3     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 539   |
+---------------+-------+
1 row in set (0.00 sec)

セッションスコープでステータス変数を確認するのは、1つずつクエリーを叩きながら、そのクエリーが（内部的に）どんな操作を何回繰り返したかを確認することによく用いられます。Handlerで始まるステータス変数はMySQLの低レイヤーなAPIの呼び出し回数を記録するステータス変数ですが、接続してすぐ確認したいクエリーを実行し、その直後にセッションスコープの値を参照することで、それらのAPIが実行したクエリーで何回呼び出されたかを確認することができます。

mysql> SELECT * FROM t1 ORDER BY val LIMIT 3;
..
3 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 2     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

出力結果の絞り込み

SHOW GLOBAL STATUS LIKE '..'の構文を用いて、ステータス変数の名前でフィルタリングすることができます。通常のLIKE演算子と同様に"%"および"_"を用いたワイルドカード検索が利用できます（そのため、先ほどの段落で例に出した SHOW STATUS LIKE 'Com_select' は "_" が任意の一文字にマッチしてしまうため厳密にはLIKE 'Com\_select'とするべきかもしれませんが、大概の場合これが問題になることはありません⁠）⁠。

WHEREを用いた式による記述も可能ですが、ステータス変数の名前以外で絞り込みを行うことはあまりないため、LIKEのみおぼえておけば概ね問題ないと思われます。

ステータス変数にどのようなものがあるかは「サーバーステータス変数」のドキュメントに記載があります。MySQL 5.6日本語版またはMySQL 5.7英語版などを参照してください。

概ね"Com_%", "Handler_%", "Innodb_%", "Qcache_%"などのように系統立てた名前がついているため、前方一致でワイルドカード検索をするのが便利です。

FLUSH STATUSによくある誤解

まれに、グローバルスコープのステータス変数をリセットすることを期待してFLUSH STATUSステートメントを利用しようとしている人を見かけることがありますが、これは正しくありません。

ドキュメントにも記載がありますが、FLUSH STATUSステートメントは「現在のスレッドのセッションステータス変数値をグローバル値に追加し、セッション値を 0 にリセット」するステートメントであり、リセットされるのはセッションスコープのステータス変数のみです。グローバルスコープのステータス変数をリセットする方法は、mysqldの再起動以外にはありません。

まとめ

MySQL内部のステータス変数を確認することで、MySQLが起動してから「どのような操作が何回呼び出されたか」を確認することができます。

ステータス変数にはセッションスコープとグローバルスコープがあり、それぞれ使いどころが違います。

ステータス変数にアクセスするためのSHOW STATUSステートメントのオプションの構文を知り、必要なステータス変数にスマートにアクセスしましょう。