パフォーマンススキーマの有効化
MySQL 5.6 以降ではパフォーマンススキーマがデフォルトで有効になっているため、有効にするための起動オプション等を指定する必要はない。
performance_schema データベース自体もインストール時に作成されるため、ユーザー側で準備する必要はない。ただし、performance_schema データベースがユーザーが DROP できるため、もし削除してしまった場合は mysql_upgrade コマンドで修復する必要がある。
採取するデータの設定
パフォーマンスデータを採取するには、いくつかの単位や角度からデータ採集を有効化する必要がある。デフォルトの状態でも採集されるデータもあるが、それらはごく一部であり、多くの場合は目的に合わせてユーザーが有効化する必要がある。
全てを有効化するとオーバーヘッドが大きくなってしまうため、最初から全てのデータ収集が有効化されているわけではない。
パフォーマンスデータ収集の on/off は動的に performance_schema データベースにある設定用のテーブルを UPDATE して行う。現在の設定値は SELECT で確認可能。以下のテーブルが設定用テーブルとして用意されている。これらのテーブルは揮発性であり、変更は再起動によって消失する。
| テーブル名 | 説明 |
|---|---|
| setup_instruments | 計器ごとにデータ収集の on/off を切り替える。計器は多数あるので、このテーブルは行数が多い |
| setup_objects | テーブルなどのオブジェクトごとにデータ収集の on/off を切り替える |
| setup_consumers | コンシューマごとにデータ収集の on/off を切り替える |
| threads | 現在実行中のスレッドの一覧を表示する。スレッドごとにデータ収集をオフにできる |
| setup_actors | ユーザーごと、ホストごとにスレッドのデータ収集のデフォルト値を設定する |
| setup_timers | コンシューマごとに使用するタイマーを選択する |
mysql> SHOW TABLES LIKE 'setup%';
+---------------------------------------+
| Tables_in_performance_schema (setup%) |
+---------------------------------------+
| setup_actors |
| setup_consumers |
| setup_instruments |
| setup_objects |
| setup_timers |
+---------------------------------------+
5 rows in set (0.00 sec) 個々の計器の On/Off
パフォーマンススキーマのデータ収集は計器ひとつひとつについて on/off を切り替えられ、設定は setup_instruments テーブルで行う。
計器の名前はスラッシュで区切られたパスのような形式となっている。
mysql> SELECT * FROM setup_instruments LIMIT 1;
+-------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO |
+-------------------------------------------+---------+-------+
1 row in set (0.00 sec) 計器の名前 (NAME) カラムはスラッシュ区切りだが、一番右側の項目以外はカテゴリを表しており、より左にある項目の方が大きなカテゴリとなる。この例では wait が最も大きなカテゴリで、そのサブカテゴリが synch 、さらにそのサブカテゴリが mutex となる。
上記は TCLOGMMAP というクラスの LOCK_tc という名前のミューテックスロックを待つときにデータ収集される計器となる。これらは MySQL サーバのソースコードに記述されているクラス名と変数名であり、これらを理解するにはソースコードを読み解く必要がある。
なお、それぞれのカテゴリにどれだけの計器が存在するかは以下のクエリで確認できる。
mysql> SELECT SUBSTRING_INDEX(NAME, '/', 1) AS category, COUNT(1) FROM setup_instruments GROUP BY SUBSTRING_INDEX(NAME, '/', 1);
+-------------+----------+
| category | COUNT(1) |
+-------------+----------+
| idle | 1 |
| memory | 378 |
| stage | 132 |
| statement | 193 |
| transaction | 1 |
| wait | 325 |
+-------------+----------+
6 rows in set (0.00 sec) 上記のクエリからもわかるように、もう一つしたのカテゴリまで見たい場合は、SUBSTRING_INDEX() の引数の値を増やせば良い。
mysql> SELECT SUBSTRING_INDEX(NAME, '/', 2) AS category, COUNT(1) FROM setup_instruments GROUP BY SUBSTRING_INDEX(NAME, '/', 2);
+---------------------------+----------+
| category | COUNT(1) |
+---------------------------+----------+
| idle | 1 |
| memory/archive | 2 |
| memory/blackhole | 1 |
| memory/client | 7 |
| memory/csv | 5 |
| memory/innodb | 85 |
| memory/memory | 4 |
| memory/myisam | 21 |
| memory/myisammrg | 2 |
| memory/mysys | 21 |
| memory/partition | 3 |
| memory/performance_schema | 70 |
| memory/semisync | 1 |
| memory/sql | 153 |
| memory/vio | 3 |
| stage/innodb | 8 |
| stage/mysys | 1 |
| stage/semisync | 3 |
| stage/sql | 120 |
| statement/abstract | 3 |
| statement/com | 32 |
| statement/scheduler | 1 |
| statement/sp | 16 |
| statement/sql | 141 |
| transaction | 1 |
| wait/io | 56 |
| wait/lock | 2 |
| wait/synch | 267 |
+---------------------------+----------+
28 rows in set (0.00 sec) なお、以下に最上位カテゴリのおおまかな意味を記載する。
| カテゴリ | 導入されたバージョン | 説明 |
|---|---|---|
| wait | 5.5 | ラッチや IO 操作などの街が発生する操作に対する計器 |
| idle | 5.6 | アイドル状態を測定するための計器 |
| stage | 5.6 | SQL 実行の各段階に対する計器 |
| statement | 5.6 | SQL 文ごとの計器 |
| transaction | 5.7 | トランザクション全体に対する計器 |
| memory | 5.7 | メモリの割り当てと解放に対する計器 |
なお、具体的には以下のように設定する。
mysql> UPDATE setup_instruments SET ENABLED='Yes', TIMED='Yes' WHERE NAME LIKE 'memory/partition/%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0 上記はパーティショニング関連のメモリ割り当てに関する計器を有効化している。TIMED を Yes にすると、タイマーから取得した値を用いて処理にかかった時間を計測できるが、オーバーヘッドもあるので注意が必要となる。