https://dshimizu.hatenablog.com/entry/2024/11/23/121448

はじめに

Aurora MySQL の Performance Insight で表示されるようなどこでどのように情報が保存されたりしているのかちゃんとわかっていませんでした。

sys スキーマにも似たような情報があるなと思ったのですが、これは performance_schema にあるテーブルへのアクセスの代替手段であるとのことでした。

MySQL :: MySQL 8.0 Reference Manual :: 30.1 Prerequisites for Using the sys Schema

performance_schema もあまりよくわかっていなかったので、調べたことの雑なメモ(ほぼドキュメント抜粋)です。

環境

環境は以下です。

> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.000 sec)

performance_schema

MySQLのパフォーマンス情報を参照する場合に見るのが performance_schema データベースに保存されいている情報かと思います。ドキュメントによると performance_schema は MySQL Server の実行を低レベルで監視するための機能、と記載されています。

MySQL :: MySQL 8.0 Reference Manual :: 29 MySQL Performance Schema

記載されている特徴について、気になったものをいくつか抜粋してみます

performance_schema は、実行時にサーバーの内部実行を検査する方法を提供し、PERFORMANCE_SCHEMA ストレージエンジンと performance_schema データベースを使用して実装されており、主にパフォーマンスデータに焦点を当てている
performance_schema のテーブルは、永続的なディスク上のストレージを使用しないメモリ内テーブル。コンテンツは、サーバーの起動時に再作成され、サーバーのシャットダウン時に破棄される
performance_schema は、サーバーのパフォーマンスへの影響を最小限に抑えながら、サーバーの実行に関する有用な情報へのアクセスを提供することを目的としている
- サーバーの監視は、オーバーヘッドがほとんどなく、継続的に目立たずに行われる。パフォーマンススキーマをアクティブ化しても、サーバーが使用できなくなることはない
- パーサーは変更されない、新しいキーワードやステートメントもない
- パフォーマンススキーマが内部的に失敗した場合でも、サーバーコードの実行は正常に続行される
- ほとんどのパフォーマンススキーマテーブルにはインデックスがあり、これにより、オプティマイザは完全なテーブルスキャン以外の実行計画にアクセスできる

設定値は以下で確認できます。デフォルトで有効になっているはずです。

> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.008 sec)

設定ファイルでも制御できます。

/etc/my.cnf ファイルの [mysqld] グループに以下のような記述をして、MySQL Serverを再起動することで反映できます。無効化したい場合は OFF を設定してMySQL Serverを再起動します。

[mysqld]
performance_schema=ON

Performance Schemaでどのような情報を取得するかは, performance_schemaデータベースの以下のテーブルで管理されているようです。

> SHOW TABLES FROM performance_schema LIKE 'setup\_%';
+-----------------------------------------+
| Tables_in_performance_schema (setup\_%) |
+-----------------------------------------+
| setup_actors                            |
| setup_consumers                         |
| setup_instruments                       |
| setup_objects                           |
| setup_threads                           |
+-----------------------------------------+
5 rows in set (0.002 sec)

instruments

MySQLのソースコードの各所に埋め込まれた計測用の処理(ドキュメントでは instruments と表記されているものと)によって、処理時間や待機時間といった情報を収集することで、その処理が呼ばれた回数やそかかった時間などの情報が performance_schema の各テーブルへ保存されるようです。

どの instruments が有効化されているかなどの状態は、 performance_schema データベース内の setup_instruments テーブルで管理されています。 1200個ほどの instruments があります、

> select * from performance_schema.setup_instruments;
(略)

> select count(*) from setup_instruments;
+----------+
| count(*) |
+----------+
|     1239 |
+----------+
1 row in set (0.001 sec)

一般的な調査等に必要そうなものはデフォルトで有効化されているので、特に設定をしなくても情報を見ることができると思います。それ以外で必要なものは必要に応じて設定を有効化する必要があります。

'wait/io/file/sql/binlog' を無効にする場合は以下のようにUPDATE文を実行します。

> UPDATE performance_schema.setup_instruments SET ENABLED = 'NO', TIMED = 'NO' WHERE NAME = 'wait/io/file/sql/binlog';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

> select * from performance_schema.setup_instruments WHERE NAME = 'wait/io/file/sql/binlog';
+-------------------------+---------+-------+------------+-------+------------+---------------+
| NAME                    | ENABLED | TIMED | PROPERTIES | FLAGS | VOLATILITY | DOCUMENTATION |
+-------------------------+---------+-------+------------+-------+------------+---------------+
| wait/io/file/sql/binlog | NO      | NO    |            | NULL  |          0 | NULL          |
+-------------------------+---------+-------+------------+-------+------------+---------------+
1 row in set (0.006 sec)

この変更もMySQL再起動したら元に戻ってしまうので注意が必要です。

setup_consumers

各 instrument によって有効化されたものから、どのイベント情報を保存するかどうかの設定テーブルが setup_consumers になるようです。以下のように、 setup_consumers テーブルの中身は consumers の何を有効にしているかを示しています。

> select * from performance_schema.setup_consumers;

setup_actors

フォアグラウンドサーバースレッドに関する監視を有効にするかどうか等を管理するもののようです。

> select * from performance_schema.setup_actors;

setup_threads

instrumentsのスレッドに関する情報が管理されているようです。 MySQL はシングルプロセス・マルチスレッドなアプリケーションで、instrumentsを実行するためのスレッドの情報が管理されているようです。

> select * from performance_schema.setup_threads;

実行中のスレッドは以下で確認できます。

> SELECT * FROM performance_schema.threads;

setup_objects

特定のオブジェクトをモニターするかどうかを管理するテーブルが setup_objects というものになるようです。

> select * from performance_schema.setup_objects;

おわりに

MySQL 8.0 の performance_schema について、改めて調べてみました。

MySQLのパフォーマンス情報は performance_schema データベース内の各テーブルに保存されている
基本的な設定はデフォルトで有効になっているが performance_schema データベース内の setup_xxx テーブルで設定を変更できる
performance_schemaの情報はメモリに保持されているので、MySQLサーバーを再起動するとリセットされる
sys は performance_schema にあるテーブルへのアクセスの代替手段であるとのこと

MySQL 8.0 の performance_schema を浅く理解していくための自分用メモ