はじめに
前回の記事に引き続き、Percona Monitoring and Management(以下PMM)のQuery Analytics(以下QAN)について紹介していきます。
QANのソースはスロークエリログかパフォーマンススキーマか選択することができます。
スロークエリログの出力は製品によって異なるため、QANが取得出来るデータにも差異があります。
今回はMySQL,MariaDB,Percona Server for MySQL(以下Percona Server)でそれぞれスロークエリログとパフォーマンススキーマをソースにした場合にどのような情報が表示されるかを調査しました。
検証環境
今回利用したプログラムとバージョンは以下の通りです。
2021/07/29時点の最新バージョンを選択しています。
- PMM 2.19.0
- MySQL 8.0.25
- Percona Server 8.0.25
- MariaDB 10.6.3
設定
今回は検証のため、より多くの情報がスロークエリログに出力されるように設定しています。
ファイルサイズが肥大化する場合があるため注意してください。
なお、PMMのドキュメントでは設定のサンプルが紹介されています。
MySQL
|
1 2 3 4 5 6 7 |
[mysqld] slow_query_log=ON log_output=FILE long_query_time=0 log_slow_admin_statements=ON log_slow_extra=ON |
log_slow_extraをONにすることで、追加のフィールドが出力されます。
MariaDB
|
1 2 3 4 5 6 7 |
[mysqld] slow_query_log=ON log_output=FILE long_query_time=0 log_slow_admin_statements=ON log_slow_verbosity=query_plan,explain |
log_slow_verbosityはスロークエリログに記録される内容の詳細度を設定します。query_planはクエリプランに関する情報、explainはEXPLAIN出力を記録します。
Percona Server
|
1 2 3 4 5 6 7 |
[mysqld] slow_query_log=ON log_output=FILE long_query_time=0 log_slow_admin_statements=ON log_slow_verbosity='full' |
log_slow_verbosityはスロークエリログに記録される情報量を設定します。fullに設定した場合、全てのオプションが有効化されます。
検証
各DBにSakila Sample Databaseをインポートし、以下のクエリを実行します。
|
1 2 |
mysql> select * from payment where last_update < '2006-02-15 22:13:31' order by last_update; |
スロークエリログの出力
各DBのスロークエリログの出力は以下の通りです。
MySQL
|
1 2 3 4 5 6 7 8 |
# Time: 2021-07-27T02:21:45.050143Z # User@Host: root[root] @ localhost [127.0.0.1] Id: 344 # Query_time: 0.041054 Lock_time: 0.000151 Rows_sent: 4133 Rows_examined: 20182 Thread_id: 344 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 266380 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 16050 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 4133 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2021-07-27T02:21:45.009089Z End: 2021-07-27T02:21:45.050143Z SET timestamp=1627352505; select * from payment where last_update < '2006-02-15 22:13:31' order by last_update; |
※一部改行をしています。
MariaDB
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# User@Host: root[root] @ localhost [127.0.0.1] # Thread_id: 225 Schema: sakila QC_hit: No # Query_time: 0.021434 Lock_time: 0.000136 Rows_sent: 4133 Rows_examined: 20182 # Rows_affected: 0 Bytes_sent: 266380 # Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No # # explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra # explain: 1 SIMPLE payment ALL NULL NULL NULL NULL 16086 4133.00 100.00 100.00 Using where; Using filesort # SET timestamp=1627352536; select * from payment where last_update < '2006-02-15 22:13:31' order by last_update; |
Percona Server
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# Time: 2021-07-27T02:21:57.820942Z # User@Host: root[root] @ localhost [127.0.0.1] Id: 230 # Schema: sakila Last_errno: 0 Killed: 0 # Query_time: 0.013923 Lock_time: 0.000159 Rows_sent: 4133 Rows_examined: 20182 Rows_affected: 0 Bytes_sent: 266380 # Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 0 # Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort: Yes Filesort_on_disk: No Merge_passes: 0 # InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 45 SET timestamp=1627352517; select * from payment where last_update < '2006-02-15 22:13:31' order by last_update; |
QANの表示
QANでどのように表示されるかを見ていきます。
Overview Panelに表示されたクエリを選択すると、画面下部にDetails Panelが表示されます。ここでは選択したクエリの詳細な情報が確認できます。
Details PanelにはDetailsタブ、Examplesタブ、Explainタブ、Tablesタブと4つのタブが存在しています。
Detailsタブの表示
Detailsタブにはクエリの実行時間や実行回数など様々なメトリクスが表示されます。DBによって表示される項目が異なります。
MySQL(スロークエリログ)
MariaDB(スロークエリログ)
Percona Server(スロークエリログ)
パフォーマンススキーマ
MySQL,MariaDB,Percona Serverいずれも表示される項目は同じでした。
なお、実行されるクエリの性質によって、Detailsタブに表示される項目は異なります。
そのため、常に図にある項目が表示されるわけではありません。
例えば、以下のクエリをPercona Serverで実行します。
|
1 2 |
mysql> select * from rental join payment where rental.return_date = payment.payment_date; |
スロークエリログの出力は以下のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# Time: 2021-07-29T01:49:16.513009Z # User@Host: root[root] @ localhost [127.0.0.1] Id: 23791 # Schema: sakila Last_errno: 0 Killed: 0 # Query_time: 0.030981 Lock_time: 0.000081 Rows_sent: 30 Rows_examined: 32093 Rows_affected: 0 Bytes_sent: 5193 # Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 0 # Full_scan: Yes Full_join: Yes Tmp_table: No Tmp_table_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes: 0 # InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 93 SET timestamp=1627523356; select * from rental join payment where rental.return_date = payment.payment_date; |
Full_scan: Yes、Filesort: Noとなっています。
これをQANで確認すると、Full Joinの項目が追加され、Filesortの項目がなくなっていることがわかります。
Examplesタブ、Explainタブ、Tablesタブの表示
Examplesタブ、Explainタブ、Tablesタブはそれぞれ以下の内容が表示されます。
- Examplesタブ：選択したクエリの例を表示
- Explainタブ：選択したクエリのEXPLAIN出力を表示
- Tablesタブ：選択したクエリのテーブル、インデックスの情報を表示
これらのタブの情報はDB、ソースによっては表示されない場合があります。
MySQL(スロークエリログ)
- Examplesタブは情報が表示されます。
- ExplainタブとTablesタブは情報が表示されません。
MySQLの場合、スロークエリログにスキーマの情報が出力されません。
(MariaDB、Percona ServerのスロークエリログにはSchema: sakilaと出力されています。)
そのため、ExplainタブとTablesタブの情報は表示されません。
この問題はPMM-4832で報告されています。
MySQL(パフォーマンススキーマ)
- ExamplesタブとExplainタブは情報が表示されます。
- Tablesタブは一部クエリのみ表示されます。
MariaDB(スロークエリログ)
- ExamplesタブとExplainタブは情報が表示されます。
- Tablesタブは一部クエリのみ表示されます。
MariaDB(パフォーマンススキーマ)
- Examplesタブ、Explainタブ、Tablesタブすべての情報が表示されません。
performance_schema.events_statements_summary_by_digest のテーブル定義が他と異なることが原因のようです。
この問題はPMM-8203で報告されています。
Percona Server(スロークエリログ)
- Examplesタブは情報が表示されます。
- Explainタブ、Tablesタブは一部クエリのみ表示されます。
この問題はPMM-8467で報告されています。
Percona Server(パフォーマンススキーマ)
- Examplesタブは情報が表示されます。
- Explainタブ、Tablesタブは一部クエリのみ表示されます。
この問題はPMM-8467で報告されています。
Filters Panelの表示
QANの左側にはFilters Panelがあります。
MySQLでスロークエリログをソースとする場合、前述した通り、スロークエリログの出力にSchemaに関する情報が含まれないため、Schemaでフィルタリングすることができません。
全てn/aとなります。
また、User NameとClient Hostはそれぞれクエリを実行したユーザーとホストとなりますが、パフォーマンススキーマからはこれらの情報は収集しません。
そのため、パフォーマンススキーマをソースとしている場合は、User NameとClient Hostでフィルタリングすることはできません。
一覧
各ソースごとにPMMで参照できる情報
※スロークエリログ=log、パフォーマンススキーマ=P_S
| MySQL | MariaDB | Percona Server | ||||
|---|---|---|---|---|---|---|
| log | P_S | log | P_S | log | P_S | |
| Examplesタブ | 〇 | 〇 | 〇 | × | 〇 | 〇 |
| Explainタブ | × | 〇 | 〇 | × | △ | △ |
| Tablesタブ | × | △ | △ | × | △ | △ |
| Filters(schema) | × | 〇 | 〇 | 〇 | 〇 | 〇 |
| Filters(user,host) | 〇 | × | 〇 | × | 〇 | × |
まとめ
MySQL、MariaDBの場合、今回確認したクエリでは、スロークエリログをソースとしたときより、パフォーマンススキーマをソースとしたときの方がDetailsタブには多くの項目が表示されました。
一方、Filters Panelで使用できるユーザー情報やMariaDBのExamplesタブなどスロークエリログをソースとしたときのみ表示される情報もありました。
QANのソースに迷った場合、どういった情報を使用してクエリの分析を行いたいかをもとに決めてみてはいかがでしょうか。
