なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLのGLOBAL_STATUSとGLOBAL_VARIABLES

MySQL

今更MySQL5.7を扱うにあたって

MySQL5.6とMySQL5.7のパラメータ差分をあらためて見直してたら、「show_compatibility_56」という、プロダクトのアーキ移行期間にありがちな「いかにも」な名前のパラメータがありまして。


これは何？

「SHOW [GLOBAL] STATUS」や「SHOW [GLOBAL] VARIABLES」という、とてもお世話になるコマンドがあり、その実、テーブルに格納されている値を表示していたわけで、テーブル*1があることから、SELECT文を使うことでSHOWコマンドよりもより柔軟な条件式などを使って参照ができたわけです。

そんなテーブルたち、MySQL5.6まではInformation_Schemaにあったのが、MySQL 5.7からはPerformance_schemaに移動するぞ、ってドキュメントに書いてあります。
その説明のためにまるまる1節とってあります。
https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html
（MySQLの公式は5.7以降の日本語ドキュメントが存在しないので、ちょっとツライ。Google翻訳で頑張った。）


これがことの始まり。


なんで格納先スキーマが変更になったのか、なんでオプション扱い（とはいえデフォルト有効）な「Performance_schema」に移したのか、理由はよくわかりません。
が、事実として、移動しているわけです。


で、MySQL5.6以前に慣れている人は、身体に染み付いたInformation_schemaへの問い合わせを投げて、MySQL5.7以降だとエラーになるわけです。


そんなとき、このオプション「show_compatibility_56」をONにしてあげると、Information_schemaにあるテーブルをSELECTしてもエラーにならずに値を返してくれます。


あれ、performance_schemaってOffったらどうなるの？performance_schemaのOn/Off関係なく、Information_schemaには従来通りテーブルある？

いろいろ疑問が湧いてくるので、調べてまとめてみました。

パラメータと挙動の対応関係表

以下の表のようになっています。

performance _schema show _compatibility_56 Information _schemaへのSELECT Performance _schemaへのSELECT SHOWコマンド
on on OK OK OK
on off NG OK OK
off on OK OK OK
off off NG OK OK


システム変数「performance_schema」がONであれOFFであれ、「Performance_schemaへのSELECT」と「SHOWコマンド」は利用可能です。

システム変数「show_compatibility_56」がONであれば、Information_schemaへのSELECTは利用可能ですし、逆にOFFなら、一切利用できません。


なお、NGの際のエラーメッセージは、「Unknown table...」ではなく、「...future is disabled; see the documentaion for 'show_compatibility_56'」となります。


show_compatibility_56=OFFの場合、SHOWコマンドでは取得できない値がある

公式ドキュメントの同じページに書いてあります。

The Performance Schema does not collect statistics for Com_xxx status variables in the status variable tables. To obtain global and per-session statement execution counts, use the events_statements_summary_global_by_event_name and events_statements_summary_by_thread_by_event_name tables, respectively.
https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html

超訳「Com_xxx statusとれねーよ。events_statements_summary_global_by_event_name とか events_statements_summary_by_thread_by_event_name 使ってね！」


というわけで、どうなっているのか見てみて見たら、まあわけわかんねえ。

mysql> select @@version,@@performance_schema,@@show_compatibility_56;
+-----------+----------------------+-------------------------+
| @@version | @@performance_schema | @@show_compatibility_56 |
+-----------+----------------------+-------------------------+
| 5.7.20    |                    1 |                       1 |
+-----------+----------------------+-------------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.global_status;
（略）
361 rows in set, 1 warning (0.01 sec)


mysql> select * from performance_schema.global_status;
（略）
206 rows in set (0.00 sec)


mysql> show global status;
（略）

361 rows in set (0.00 sec)



mysql> select @@version,@@performance_schema,@@show_compatibility_56;
+-----------+----------------------+-------------------------+
| @@version | @@performance_schema | @@show_compatibility_56 |
+-----------+----------------------+-------------------------+
| 5.7.20    |                    1 |                       0 |
+-----------+----------------------+-------------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.global_status;
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'

mysql> select * from performance_schema.global_status;
（略）
206 rows in set (0.00 sec)

mysql> show global status;
（略）
353 rows in set (0.00 sec)

件数は、performance_schemaがOnでもOffでも同じでした。


で、Performance_schemaにCom_xxx系の行がないのは、まあ理解した。
しかし、SHOW コマンドの出力結果が8件少ない。。。


比較してみたトコロ、この件数の差分は
Compression
Last_query_cost
Last_query_partial_plans
Slave_heartbeat_period
Slave_last_heartbeat
Slave_received_heartbeats
Slave_retried_transactions
Slave_running
でした。


で、本題の、Com系なんですが。。。大量にでてくるので結果出力は省略しました。カウンタが動いていないからと言って、表示されないわけではなさそうです。
Performance_schemaをSELECTした時は、Com_XXXは1行しか出てきません。これが、Information_schemaやSHOWコマンドに比べて大幅に件数が少ない原因。

で、項目としてはCom_XXXも全部出力されるSHOWコマンドについては、正直良くわからず。少なくともCom_Selectや、Com_show_statusなど、この確認の際に使ったコマンド関連のカウンタは、show_compatibility_56の状態に関係なくカウントアップしてます。
ドキュメント上、明確にどれが機能しなくなるって書いてないので、しばらく回してみないとわからないのかな。


いずれにせよ、show_compatibility_56はそのうち意味がなくなるよ！って宣言されてるパラメータなので、新仕様前提で扱えるようにしたいものです。

まとめ

  • MySQL 5.6とMySQL 5.7で、SHOW STATUS/VARIVLESの取得元となるテーブルの場所がInformation_SchemaからPerformance_Schemaに変わってる。
  • SHOW STATUS/VARIVLESの代わりに、テーブル直接参照のSELECT使えるけど、MySQL 5.7からはInformation_Schemaを見に行くとエラーになるから、Performance_schemaを見よう。
  • MySQL 5.7に関しては、「show_compatibility_56=ON」すると、Information_Schema側をSELECTしてもエラーにならなくなる。
  • SHOW [GLOBAL] {STATUS | VARIABLES}使えば、システム変数performance_schemaã‚„show_compatibility_56の状態に関係なく通る。
  • Information_Schema.GLOBAL_STATUSでカウント取れた「Com_XXX」の値は、Performance_schemaでは取れなくなる、ってドキュメントに書いてあるけど、詳細わからんｗ

そして

  • 今更感があろうが、調べたことはアウトプットする！


とりあえず、MySQL 5.7について新機能・変更点をキャッチアップするのに、この本が大活躍してます！マジおすすめ！

詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)

詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)

*1:公式ドキュメントでは「INFORMATION_SCHEMA データベースには複数の読み取り専用テーブルが含まれます。これらには実際にはビューがあるので、関連付けられたファイルはなく、トリガーは設定できません。また、その名前を持つデータベースディレクトリもありません。」と書いてありますが、SHOW CREATE TABLEを見ると「CREATETABLE TEMPORARY TABLE ... ENGINE=MEMORY」と表示されます。