MySQLには、I/O情報や実行されたクエリの情報などのパフォーマンスデータのモニタリングを可能とするperformance_schemaデータベースがあります。その中に、SQLステートメントのさまざま統計情報を格納するevents_statements_summary_by_digestテーブルがあります。

以前の記事 第85回　sysスキーマの便利なストアドプロシージャやストアドファンクション［その2］で、このテーブルの概要とテーブルデータから分析するためのプロシージャについて説明しましたので、詳しくはこちらをご確認ください。

今回はMySQL 8.0.19を使用して、events_statements_summary_by_digestテーブルに影響するパラメータについて紹介したいと思います。

パラメータ一覧

events_statements_summary_by_digestテーブルに関するパラメータは、以下の4つになります。これらがどのように影響するのかについて説明します。

• performance_schema_digests_size
• max_digest_length
• performance_schema_max_digest_length
• performance_schema_max_digest_sample_age

performance_schema_digests_size

events_statements_summary_by_digestはテーブル内の最大行数を設定します。デフォルト値は-1となっていて、自動調整されます。MySQL 8.0.19現在ではmax_connections、table_definition_cacheやtable_open_cacheパラメータ値を元に1000、5000、または10000のどれかに設定されます。my.cnfに記述して明示的に値を指定することも可能です。変更するにはMySQLの再起動が必要です。

現在の値を確認するにはSHOW ENGINE PERFORMANCE_SCHEMA STATUSステートメントのevents_statements_summary_by_digest.count（下記表示では一部割愛）または、show variablesステートメントから確認できます。

mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
+--------------------+-------------------------------------------------------------+-----------+
| Type               | Name                                                        | Status    |
+--------------------+-------------------------------------------------------------+-----------+
| performance_schema | events_statements_summary_by_digest.count                   | 10000     |

mysql> show global variables like 'performance_schema_digests_size';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| performance_schema_digests_size | 10000 |
+---------------------------------+-------+

また、最大値を超えてDIGESTを計測できない場合は以下のようになります。

• Performance_schema_digest_lost ステータス変数がインクリメントされる
• events_statements_summary_by_digestテーブルのSCHEMA_NAMEカラムとDIGESTカラムをNULLに設定した特別な行にグループ化

mysql> SELECT COUNT(*) FROM events_statements_summary_by_digest;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+

mysql> SHOW GLOBAL STATUS LIKE 'Performance_schema_digest_lost';
+--------------------------------+--------+
| Variable_name                  | Value  |
+--------------------------------+--------+
| Performance_schema_digest_lost | 313717 |
+--------------------------------+--------+

mysql> SELECT SCHEMA_NAME,DIGEST,DIGEST_TEXT,COUNT_STAR FROM events_statements_summary_by_digest;
+-------------+--------+------------+------------+
| SCHEMA_NAME | DIGEST | DIGEST_TEXT| COUNT_STAR |
+-------------+--------+------------+------------+
| NULL        | NULL   | NULL       |     313741 |
+-------------+--------+------------+------------+

分析が必要なMySQLでPerformance_schema_digest_lostステータス変数が増えている場合は、このパラメータの値を大きくすることを検討しましょう。

max_digest_lengthとperformance_schema_max_digest_length

max_digest_lengthは、DIGESTを作成するためのステートメント文字列の最大バイト数を表します。デフォルトは1024バイトで、オンラインでの変更は不可であるためMySQLの再起動が必要です。また1024バイトを超えるステートメントの場合、1024バイト目までを切り取りDIGEST化します。

よって、ステートメントが長いと途中で切られるために、ステートメントの後半のWHERE句の内容が異なる2つのステートメントが同じDIGESTとしてカウントされてしまうということが起こります。その際は、この値を大きくすることを検討してください。

続いて、performance_schema_max_digest_lengthはDIGEST_TEXTに表示するサイズを表します。こちらもデフォルトは1024バイトでオンラインでの変更は不可となりMySQLの再起動が必要です。max_digest_lengthで切り取られたステートメントからDIGEST_TEXTを生成するため、max_digest_lengthとperformance_schema_max_digest_lengthは同じ値にしておくことが望ましいです。

それぞれを異なる値にして、DIGESTとDIGEST_TEXTの違いを見てみましょう。以下のSQLを実行します。

mysql> SELECT id FROM t WHERE id2=10;
Empty set (0.02 sec)

• mdl=max_digest_length
• p_s_mdl=performance_schema_max_digest_length

上記のとおり、値が異なると、DIGESTを生成するために使用したステートメントと異なるDIGEST_TEXTが表示される場合があります。よって、値をデフォルトから変更する場合は、両方のパラメータを変更するほうが好ましいです。

performance_schema_max_digest_sample_age

MySQL 8.0から、DIGESTを生成したステートメントのサンプルを表示するようになりました。このパラメータは、そのサンプルを収集する周期です。デフォルトは60で、60秒に1回新しいサンプルに上書きされます。こちらはオンラインで変更可能です。

先ほど使用したSQLからテーブル情報を見てみましょう。

mysql> SELECT * FROM performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like '%id%'\G
                SCHEMA_NAME: t
                     DIGEST: 08fac3fdf9bddce9f9089244c7d76988e0f67a2967f79598f61f92b0c8c8d312
                DIGEST_TEXT: SELECT `id` FROM `t` WHERE `id2` = ?
〈表示割愛〉
          QUERY_SAMPLE_TEXT: SELECT id FROM t WHERE id2=10
          QUERY_SAMPLE_SEEN: 2020-04-10 15:25:54.919738
    QUERY_SAMPLE_TIMER_WAIT: 24271894000

このように、実際に実行したステートメントを表示します。

• QUERY_SAMPLE_TEXT … サンプルのステートメント情報
• QUERY_SAMPLE_SEEN … ステートメントが表示された時間
• QUERY_SAMPLE_TIMER_WAIT … ステートメントの待機時間または実行時間(ピコ秒)

また、performance_schema_max_digest_sample_ageの周期だけでなく、新しいステートメントの待機時間が現在のサンプルステートメントよりも待機時間が長い場合も上書きされます。

メモリ使用量

performance_schema_max_digest_sample_ageを除くパラメータをデフォルトから大きくした場合は、メモリを多く使うことになるで注意が必要です。performance_schemaのメモリ使用量はSHOW ENGINE PERFORMANCE_SCHEMA STATUSステートメントのperformance_schema.memoryから確認できます。パラメータを大きくする前に、かならずご確認ください。

mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
+--------------------+-------------------------------------------------------------+-----------+
| Type               | Name                                                        | Status    |
+--------------------+-------------------------------------------------------------+-----------+
| performance_schema | performance_schema.memory                                   | 196979216 |
+--------------------+-------------------------------------------------------------+-----------+

まとめ

最近のMySQL DBAは、events_statements_summary_by_digestテーブルを使用して分析することが多いと思います。これらのパラメータを考慮していただければと思います。

今回紹介した内容は26.15 Performance Schema System Variablesと26.10 Performance Schema Statement Digests and Samplingを基にしています。こちらも併せてご参照ください。