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
ステートメントから確認できます。
また、最大値を超えてDIGESTを計測できない場合は以下のようになります。
Performance_schema_digest_lost
ステータス変数がインクリメントされる
events_statements_summary_by_digest
テーブルのSCHEMA_NAMEカラムとDIGESTカラムをNULLに設定した特別な行にグループ化
分析が必要な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を実行します。
mdl |
p_s_mdl |
DIGEST |
DIGEST_TEXT |
100 |
100 |
08fac3fdf9bddce9f9089244c7d76988e0f67a2967f795 98f61f92b0c8c8d312 |
SELECT id FROM t WHERE id2 = ? |
10 |
100 |
50020ff1320eba8e7345bfa76401ac511ba3434fb47d8a 12e49745c688ed431f |
SELECT id FROM |
100 |
10 |
08fac3fdf9bddce9f9089244c7d76988e0f67a2967f795 98f61f92b0c8c8d312 |
SELECT id FROM |
- 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からテーブル情報を見てみましょう。
このように、実際に実行したステートメントを表示します。
- 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 DBAは、events_statements_summary_by_digest
テーブルを使用して分析することが多いと思います。これらのパラメータを考慮していただければと思います。
今回紹介した内容は26.15 Performance Schema System Variablesと26.10 Performance Schema Statement Digests and Samplingを基にしています。こちらも併せてご参照ください。