gihyo.jpサイトのロゴ

MySQL道普請便り

第151回知っておくとちょっと得するperformance_schemaのテーブル

2021-07-20

本連載ではこれまで、いくつかのperformance_schemaテーブルについて紹介しました。

過去にこのような記事がありますので、ご参照ください。

今回は、知っておくとちょっと得するperformance_schemaのテーブルをいくつか紹介したいと思います。

accountsテーブル

accountsテーブルは、MySQLが起動してから、そのMySQLに接続したユーザアカウントの情報を格納します。MySQLを再起動する、またはaccountsテーブルをTRUNCATE TABLEすることで初期化されます。このテーブルはMySQL 5.7と、それ以上から閲覧可能です。

各カラムの内容は以下のとおりです。

  • USER …接続元のユーザー名
  • HOST …接続元のホスト
  • CURRENT_CONNECTIONS …ユーザアカウントの現在の接続数
  • TOTAL_CONNECTIONS …ユーザアカウントの合計の接続数

accountsテーブルの使いどころ

accountsテーブルの使いどころを紹介します。

たとえばMySQLのマイグレーションを行った場合、そのMySQLに対してユーザから接続が来ていないことを確認するのに使用できます。TOTAL_CONNECTIONSを確認して値がインクリメントされていなければ、接続がきていないことがわかります。

variables_by_threadテーブル

variables_by_threadテーブルは、それぞれのスレッドごとのセッションシステム変数（variables）の値を確認することができます。このテーブルはMySQL5.7とそれ以上から閲覧可能です。

各カラムの内容は以下のとおりです。

  • THREAD_ID …セッションのスレッドID
  • VARIABLE_NAME …セッションシステム変数
  • VARIABLE_VALUE …THREAD_IDカラムで指定されたセッションシステム変数の値

THREAD_IDは、SHOW PROCESSLISTで表示されるIDとは異なるのでご注意ください。THREAD_IDとPROCESSLIST IDとのマッピングについては第38回　performance_schemaのthreadsテーブルをご確認ください。

また、グローバルシステム変数を確認する場合はglobal_variablesテーブルを確認します。

variables_by_threadテーブルの使いどころ

variables_by_threadテーブルの使いどころとしては、2つあると思います。

1つめは、アプリケーションがスレッド内でSET SESSION句で変更した箇所を確認することです。たとえば、my.cnfに設定したシステム変数値とアプリケーションでの動作が期待する挙動と異なった場合、そのスレッドではセッション変数を変更している可能性があります。variables_by_threadテーブルから、グローバルシステム変数値と異なるセッションシステム変数値をもつスレッドを特定することができます。

以下のようなSQLを実行します。

mysql> SELECT vbt.THREAD_ID, vbt.VARIABLE_NAME,vbt.VARIABLE_VALUE THREAD_VARIABLE_VALUE,gv.VARIABLE_VALUE GLOBAL_VARIABLE_VALUE 
FROM variables_by_thread vbt JOIN global_variables gv USING(VARIABLE_NAME)
WHERE vbt.VARIABLE_VALUE <> gv.VARIABLE_VALUE;

+-----------+-----------------------+-----------------------+-----------------------+
| THREAD_ID | VARIABLE_NAME         | THREAD_VARIABLE_VALUE | GLOBAL_VARIABLE_VALUE |
+-----------+-----------------------+-----------------------+-----------------------+
|        47 | transaction_isolation | READ-COMMITTED        | REPEATABLE-READ       |
+-----------+-----------------------+-----------------------+-----------------------+

2つめは、SET GLOBAL句で変更したシステム変数がすべてのセッションで有効になったか確認することです。SET GLOBAL句で変更したシステム変数は、接続中のセッションには反映されません。変更後にセッションが接続されたときに設定されるためです。

variables_infoテーブル

variables_infoテーブルは、どのソースからシステム変数が設定されたかを確認することができます。このテーブルはMySQL 8.0以上から閲覧可能です。

各カラムと値の内容は以下のとおりです。

  • VARIABLE_NAME …変数名
  • VARIABLE_SOURCE …変数が最後に設定されたソース
    • COMMAND_LINE …MySQLの起動オプションで設定。
    • COMPILED …コンパイルされたデフォルト値（暗黙のデフォルト値）
    • DYNAMIC …実行時に設定。例:SET GLOBAL句から設定
    • EXPLICIT …--defaults-fileオプションで指定されたオプションファイルから設定
    • EXTRA …--defaults-extra-fileオプションで指定されたオプションファイルから設定
    • GLOBAL …グローバルオプションファイルから設定
    • LOGIN …ユーザー固有のログインパスファイル（~/.mylogin.cnf）から設定
    • PERSISTED …mysqld-auto.cnfオプションファイルから設定
    • SERVER …サーバー固有の $MYSQL_HOME/my.cnfオプションファイルから設定。
    • USER …ユーザー固有の ~/.my.cnfオプションファイルから設定。
  • VARIABLE_PATH …変数がオプションファイルから設定された場合、VARIABLE_PATHはそのファイルのパス名。例: /etc/mysql/my.cnf
  • MIN_VALUE, MAX_VALUE …許可される最小値と最大値
  • SET_TIME …最後に設定された時刻

variables_infoテーブルの使いどころ

variables_infoテーブルの使いどころを紹介します。

たとえば、Master-Replica構成のMySQLがあり一部MySQLのみシステム変数の設定が異なっていたときに、そのシステム変数を変更したソースの特定や、変更した時刻などを確認することができます。

また、MySQL 5.7とそれ以前では起動中のMySQLに対して、どのディレクトリに配置しているmy.cnfを読んだかはわかりませんでした。variables_infoテーブルから、設定に反映されたmy.cnfのファイル位置も確認可能になりました。

まとめ

今回は、知っておくとちょっと得するperformance_schemaのテーブル（accountsテーブル、variables_by_threadテーブルとvariables_infoテーブル）について紹介しました。performance_schemaのテーブルにはパフォーマンスの分析や情報を保持するさまざまテーブルが用意されています。ぜひ、いちどマニュアル 第27章 MySQL パフォーマンススキーマを読んでみると良いと思います。

おすすめ記事

記事・ニュース一覧