ステートメント要約表
SQLのパフォーマンスの問題をより適切に処理するために、MySQLはSQLを統計で監視するためにperformance_schemaのステートメント要約表を提供しています。これらのテーブルの中で、 events_statements_summary_by_digestは、レイテンシ、実行時間、スキャンされた行、全表スキャンなどの豊富なフィールドでSQLの問題を見つけるのに非常に役立ちます。
したがって、v4.0.0-rc.1以降、TiDBは、機能の点でevents_statements_summary_by_digestに類似したシステムテーブルをinformation_schema （ performance_schemaではなく）で提供します。
statements_summarystatements_summary_historycluster_statements_summarycluster_statements_summary_historystatements_summary_evicted
このドキュメントでは、これらのテーブルについて詳しく説明し、SQLパフォーマンスの問題をトラブルシューティングするためにそれらを使用する方法を紹介します。
statements_summary
statements_summaryはinformation_schemaのシステムテーブルです。 statements_summary SQLステートメントをSQLダイジェストとプランダイジェストでグループ化し、各SQLカテゴリの統計を提供します。
ここでの「SQLダイジェスト」とは、低速ログで使用されるものと同じ意味であり、正規化されたSQLステートメントによって計算される一意の識別子です。正規化プロセスでは、定数の空白文字は無視され、大文字と小文字は区別されません。したがって、構文が一貫しているステートメントのダイジェストは同じです。例えば：
SELECT * FROM employee WHERE id IN (1, 2, 3) AND salary BETWEEN 1000 AND 2000;
select * from EMPLOYEE where ID in (4, 5) and SALARY between 3000 and 4000;
正規化後、これらは両方とも次のカテゴリになります。
select * from employee where id in (...) and salary between ? and ?;
ここでの「プランダイジェスト」とは、正規化された実行プランによって計算された一意の識別子を指します。正規化プロセスは定数を無視します。同じSQLステートメントの実行プランが異なる可能性があるため、同じSQLステートメントが異なるカテゴリにグループ化される場合があります。同じカテゴリのSQLステートメントの実行プランは同じです。
statements_summaryは、SQL監視メトリックの集計結果を格納します。一般に、各監視メトリックには、最大値と平均値が含まれます。たとえば、実行待ち時間のメトリックは、 AVG_LATENCY （平均待ち時間）とMAX_LATENCY （最大待ち時間）の2つのフィールドに対応します。
監視メトリックが最新であることを確認するために、 statements_summaryテーブルのデータは定期的にクリアされ、最近の集計結果のみが保持および表示されます。定期的なデータクリアは、 tidb_stmt_summary_refresh_intervalのシステム変数によって制御されます。クリア直後にクエリを実行した場合、表示されるデータが非常に少ない可能性があります。
以下は、クエリstatements_summaryの出力例です。
SUMMARY_BEGIN_TIME: 2020-01-02 11:00:00
SUMMARY_END_TIME: 2020-01-02 11:30:00
STMT_TYPE: Select
SCHEMA_NAME: test
DIGEST: 0611cc2fe792f8c146cc97d39b31d9562014cf15f8d41f23a4938ca341f54182
DIGEST_TEXT: select * from employee where id = ?
TABLE_NAMES: test.employee
INDEX_NAMES: NULL
SAMPLE_USER: root
EXEC_COUNT: 3
SUM_LATENCY: 1035161
MAX_LATENCY: 399594
MIN_LATENCY: 301353
AVG_LATENCY: 345053
AVG_PARSE_LATENCY: 57000
MAX_PARSE_LATENCY: 57000
AVG_COMPILE_LATENCY: 175458
MAX_COMPILE_LATENCY: 175458
...........
AVG_MEM: 103
MAX_MEM: 103
AVG_DISK: 65535
MAX_DISK: 65535
AVG_AFFECTED_ROWS: 0
FIRST_SEEN: 2020-01-02 11:12:54
LAST_SEEN: 2020-01-02 11:25:24
QUERY_SAMPLE_TEXT: select * from employee where id=3100
PREV_SAMPLE_TEXT:
PLAN_DIGEST: f415b8d52640b535b9b12a9c148a8630d2c6d59e419aad29397842e32e8e5de3
PLAN: Point_Get_1 root 1 table:employee, handle:3100
ノート：
TiDBでは、ステートメントサマリーテーブルのフィールドの時間単位はナノ秒（ns）ですが、MySQLでは時間単位はピコ秒（ps）です。
statements_summary_history
statements_summary_historyのテーブルスキーマはstatements_summaryのテーブルスキーマと同じです。 statements_summary_historyは、時間範囲の履歴データを保存します。履歴データを確認することで、異常のトラブルシューティングを行い、さまざまな時間範囲の監視メトリックを比較できます。
フィールドSUMMARY_BEGIN_TIMEとSUMMARY_END_TIMEは、履歴時間範囲の開始時刻と終了時刻を表します。
statements_summary_evicted
tidb_stmt_summary_max_stmt_count変数は、 statement_summaryのテーブルがメモリに格納するステートメントの最大数を制御します。 statement_summaryテーブルはLRUアルゴリズムを使用します。 SQLステートメントの数がtidb_stmt_summary_max_stmt_countの値を超えると、最も長い未使用のレコードがテーブルから削除されます。各期間中に削除されたSQLステートメントの数は、 statements_summary_evictedの表に記録されます。
statements_summary_evictedテーブルは、SQLレコードがstatement_summaryテーブルから削除された場合にのみ更新されます。 statements_summary_evictedは、削除が発生する期間と削除されたSQLステートメントの数のみを記録します。
ステートメント要約のclusterテーブル
statements_summary 、およびstatements_summary_historyの表は、単一のstatements_summary_evictedサーバーのステートメントの要約のみを示しています。クラスタ全体のデータをクエリするには、 cluster_statements_summary 、またはcluster_statements_summary_historyのテーブルをクエリする必要がありcluster_statements_summary_evicted 。
cluster_statements_summaryは、各TiDBサーバーのstatements_summaryのデータを表示します。 cluster_statements_summary_historyは、各TiDBサーバーのstatements_summary_historyのデータを表示します。 cluster_statements_summary_evictedは、各TiDBサーバーのstatements_summary_evictedのデータを表示します。これらのテーブルは、 INSTANCEフィールドを使用してTiDBサーバーのアドレスを表します。その他のフィールドはstatements_summaryのフィールドと同じです。
パラメータ設定
次のシステム変数は、ステートメントの要約を制御するために使用されます。
tidb_enable_stmt_summary：ステートメント要約機能を有効にするかどうかを決定します。1はenableを表し、0はdisableを意味します。この機能はデフォルトで有効になっています。この機能を無効にすると、システムテーブルの統計がクリアされます。統計は、次にこの機能が有効になったときに再計算されます。テストでは、この機能を有効にしてもパフォーマンスにほとんど影響がないことが示されています。tidb_stmt_summary_refresh_interval：statements_summaryテーブルが更新される間隔。時間の単位は秒です。デフォルト値は1800です。tidb_stmt_summary_history_size：statements_summary_historyテーブルに格納されている各SQLステートメントカテゴリのサイズ。これは、statement_summary_evictedテーブルのレコードの最大数でもあります。デフォルト値は24です。tidb_stmt_summary_max_stmt_count：ステートメントサマリーテーブルに格納できるSQLステートメントの数を制限します。デフォルト値は3000です。制限を超えると、最近未使用のままになっているSQLステートメントがクリアされます。これらのクリアされたSQLステートメントは、statement_summary_evictedの表に記録されます。tidb_stmt_summary_max_sql_length：DIGEST_TEXTとQUERY_SAMPLE_TEXTの最長表示長を指定します。デフォルト値は4096です。tidb_stmt_summary_internal_query：TiDBSQLステートメントをカウントするかどうかを決定します。1はカウントすることを意味し、0はカウントしないことを意味します。デフォルト値は0です。
ノート：
tidb_stmt_summary_max_stmt_countの制限を超えたためにSQLステートメントのカテゴリを削除する必要がある場合、TiDBはすべての時間範囲のそのSQLステートメントカテゴリのデータをstatement_summary_historyのテーブルから削除します。したがって、特定の時間範囲内のSQLステートメントカテゴリの数が制限に達していない場合でも、statement_summary_historyのテーブルに格納されているSQLステートメントの数は実際のSQLステートメントの数よりも少なくなります。この状況が発生してパフォーマンスに影響を与える場合は、tidb_stmt_summary_max_stmt_countの値を増やすことをお勧めします。
ステートメントの要約構成の例を以下に示します。
set global tidb_enable_stmt_summary = true;
set global tidb_stmt_summary_refresh_interval = 1800;
set global tidb_stmt_summary_history_size = 24;
上記の設定が有効になった後、30分ごとにstatements_summaryのテーブルがクリアされます。 statements_summary_historyのテーブルには、過去12時間に生成されたデータが格納されます。
statements_summary_evictedの表は、SQLステートメントがステートメントの要約から削除された最近の24期間を記録します。 statements_summary_evictedのテーブルは30分ごとに更新されます。
上記のシステム変数には、グローバルとセッションの2つのスコープがあります。これらのスコープは、他のシステム変数とは動作が異なります。
- グローバル変数を設定すると、設定はクラスタ全体にすぐに適用されます。
- セッション変数を設定すると、設定は現在のTiDBサーバーにすぐに適用されます。これは、単一のTiDBサーバーインスタンスでデバッグする場合に役立ちます。
- セッション変数の読み取り優先度は高くなります。グローバル変数は、セッション変数が設定されていない場合にのみ読み取られます。
- セッション変数を空白の文字列に設定すると、グローバル変数が再読み取りされます。
ノート：
tidb_stmt_summary_history_size、およびtidb_stmt_summary_max_stmt_countの構成項目は、メモリー使用量に影響しtidb_stmt_summary_max_sql_length。ニーズ、SQLサイズ、SQLカウント、およびマシン構成に基づいて、これらの構成を調整することをお勧めします。大きすぎる値を設定することはお勧めしません。tidb_stmt_summary_history_sizetidb_stmt_summary_max_stmt_counttidb_stmt_summary_max_sql_length*3を使用してメモリ使用量を計算できます。
ステートメントの要約に適切なサイズを設定する
システムが一定期間実行された後（システムの負荷に応じて）、 statement_summaryのテーブルをチェックして、SQLエビクションが発生したかどうかを確認できます。例えば：
select @@global.tidb_stmt_summary_max_stmt_count;
select count(*) from information_schema.statements_summary;
+-------------------------------------------+
| @@global.tidb_stmt_summary_max_stmt_count |
+-------------------------------------------+
| 3000 |
+-------------------------------------------+
1 row in set (0.001 sec)
+----------+
| count(*) |
+----------+
| 3001 |
+----------+
1 row in set (0.001 sec)
statements_summaryのテーブルがレコードでいっぱいであることがわかります。次に、 statements_summary_evictedのテーブルから削除されたデータを確認します。
select * from information_schema.statements_summary_evicted;
+---------------------+---------------------+---------------+
| BEGIN_TIME | END_TIME | EVICTED_COUNT |
+---------------------+---------------------+---------------+
| 2020-01-02 16:30:00 | 2020-01-02 17:00:00 | 59 |
+---------------------+---------------------+---------------+
| 2020-01-02 16:00:00 | 2020-01-02 16:30:00 | 45 |
+---------------------+---------------------+---------------+
2 row in set (0.001 sec)
上記の結果から、最大59のSQLカテゴリが削除されていることがわかります。これは、ステートメントの要約の適切なサイズが59レコードであることを示しています。
制限
ステートメントサマリーテーブルには、次の制限があります。
上記のステートメント要約テーブルのすべてのデータは、TiDBサーバーを再起動すると失われます。これは、ステートメントサマリーテーブルがすべてメモリテーブルであり、データがストレージに永続化されるのではなく、メモリにキャッシュされるためです。
トラブルシューティングの例
このセクションでは、ステートメントの要約機能を使用してSQLパフォーマンスの問題をトラブルシューティングする方法を示す2つの例を示します。
サーバー側が原因でSQLの待ち時間が長くなる可能性はありますか？
この例では、クライアントはemployeeテーブルでのポイントクエリでパフォーマンスが低下しています。 SQLテキストに対してあいまい検索を実行できます。
SELECT avg_latency, exec_count, query_sample_text
FROM information_schema.statements_summary
WHERE digest_text LIKE 'select * from employee%';
1msと0.3msは、通常のavg_latencyの範囲内と見なされます。したがって、サーバー側が原因ではないと結論付けることができます。クライアントまたはネットワークでトラブルシューティングできます。
+-------------+------------+------------------------------------------+
| avg_latency | exec_count | query_sample_text |
+-------------+------------+------------------------------------------+
| 1042040 | 2 | select * from employee where name='eric' |
| 345053 | 3 | select * from employee where id=3100 |
+-------------+------------+------------------------------------------+
2 rows in set (0.00 sec)
どのカテゴリのSQLステートメントが最も長い合計時間を消費しますか？
QPSが10:00から10:30に大幅に減少した場合は、履歴テーブルから、消費時間が最も長いSQLステートメントの3つのカテゴリを見つけることができます。
SELECT sum_latency, avg_latency, exec_count, query_sample_text
FROM information_schema.statements_summary_history
WHERE summary_begin_time='2020-01-02 10:00:00'
ORDER BY sum_latency DESC LIMIT 3;
この結果は、次の3つのカテゴリのSQLステートメントが合計で最も長い時間を消費することを示しています。これらは、高い優先度で最適化する必要があります。
+-------------+-------------+------------+-----------------------------------------------------------------------+
| sum_latency | avg_latency | exec_count | query_sample_text |
+-------------+-------------+------------+-----------------------------------------------------------------------+
| 7855660 | 1122237 | 7 | select avg(salary) from employee where company_id=2013 |
| 7241960 | 1448392 | 5 | select * from employee join company on employee.company_id=company.id |
| 2084081 | 1042040 | 2 | select * from employee where name='eric' |
+-------------+-------------+------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)
フィールドの説明
statements_summaryフィールドの説明
以下は、 statements_summaryテーブルのフィールドの説明です。
基本フィールド：
STMT_TYPE：SQLステートメントタイプ。SCHEMA_NAME：このカテゴリのSQLステートメントが実行される現在のスキーマ。DIGEST：このカテゴリのSQLステートメントのダイジェスト。DIGEST_TEXT：正規化されたSQLステートメント。QUERY_SAMPLE_TEXT：SQLカテゴリの元のSQLステートメント。元のステートメントは1つだけです。TABLE_NAMES：SQLステートメントに関係するすべてのテーブル。複数のテーブルがある場合は、それぞれをコンマで区切ります。INDEX_NAMES：SQLステートメントで使用されるすべてのSQLインデックス。複数のインデックスがある場合は、それぞれをコンマで区切ります。SAMPLE_USER：このカテゴリのSQLステートメントを実行するユーザー。 1人のユーザーのみが取得されます。PLAN_DIGEST：実行計画のダイジェスト。PLAN：元の実行プラン。複数のステートメントがある場合は、1つのステートメントのみの計画が採用されます。PLAN_CACHE_HITS：このカテゴリのSQLステートメントがプランキャッシュにヒットした合計回数。PLAN_IN_CACHE：このカテゴリのSQLステートメントの前回の実行がプランキャッシュにヒットしたかどうかを示します。
実行時間に関連するフィールド：
SUMMARY_BEGIN_TIME：現在の要約期間の開始時刻。SUMMARY_END_TIME：現在の要約期間の終了時刻。FIRST_SEEN：このカテゴリのSQLステートメントが初めて表示される時刻。LAST_SEEN：このカテゴリのSQLステートメントが最後に表示された時刻。
TiDBサーバーに関連するフィールド：
EXEC_COUNT：このカテゴリのSQLステートメントの合計実行時間。SUM_ERRORS：実行中に発生したエラーの合計。SUM_WARNINGS：実行中に発生した警告の合計。SUM_LATENCY：このカテゴリのSQLステートメントの合計実行レイテンシ。MAX_LATENCY：このカテゴリのSQLステートメントの最大実行レイテンシ。MIN_LATENCY：このカテゴリのSQLステートメントの最小実行レイテンシ。AVG_LATENCY：このカテゴリのSQLステートメントの平均実行レイテンシ。AVG_PARSE_LATENCY：パーサーの平均レイテンシー。MAX_PARSE_LATENCY：パーサーの最大レイテンシー。AVG_COMPILE_LATENCY：コンパイラーの平均レイテンシー。MAX_COMPILE_LATENCY：コンパイラーの最大レイテンシー。AVG_MEM：使用された平均メモリ（バイト）。MAX_MEM：使用される最大メモリ（バイト）。AVG_DISK：使用された平均ディスク容量（バイト）。MAX_DISK：使用される最大ディスク容量（バイト）。
TiKVコプロセッサータスクに関連するフィールド：
SUM_COP_TASK_NUM：送信されたコプロセッサー要求の総数。MAX_COP_PROCESS_TIME：コプロセッサータスクの最大実行時間。MAX_COP_PROCESS_ADDRESS：最大実行時間のコプロセッサータスクのアドレス。MAX_COP_WAIT_TIME：コプロセッサータスクの最大待機時間。MAX_COP_WAIT_ADDRESS：最大待機時間のコプロセッサータスクのアドレス。AVG_PROCESS_TIME：TiKVでのSQLステートメントの平均処理時間。MAX_PROCESS_TIME：TiKVでのSQLステートメントの最大処理時間。AVG_WAIT_TIME：TiKVでのSQLステートメントの平均待機時間。MAX_WAIT_TIME：TiKVでのSQLステートメントの最大待機時間。AVG_BACKOFF_TIME：SQLステートメントで再試行が必要なエラーが発生した場合の再試行までの平均待機時間。MAX_BACKOFF_TIME：SQLステートメントで再試行が必要なエラーが発生した場合の再試行までの最大待機時間。AVG_TOTAL_KEYS：コプロセッサーがスキャンしたキーの平均数。MAX_TOTAL_KEYS：コプロセッサーがスキャンしたキーの最大数。AVG_PROCESSED_KEYS：コプロセッサーが処理したキーの平均数。avg_total_keysと比較すると、avg_processed_keysには古いバージョンのMVCCが含まれていません。avg_total_keysとavg_processed_keysの大きな違いは、多くの古いバージョンが存在することを示しています。MAX_PROCESSED_KEYS：コプロセッサーが処理したキーの最大数。
トランザクション関連のフィールド：
AVG_PREWRITE_TIME：プリライトフェーズの平均時間。MAX_PREWRITE_TIME：プリライトフェーズの最長時間。AVG_COMMIT_TIME：コミットフェーズの平均時間。MAX_COMMIT_TIME：コミットフェーズの最長時間。AVG_GET_COMMIT_TS_TIME：commit_tsを取得する平均時間。MAX_GET_COMMIT_TS_TIME：commit_tsを取得する最長時間。AVG_COMMIT_BACKOFF_TIME：コミットフェーズ中に再試行が必要なエラーがSQLステートメントで発生した場合の再試行までの平均待機時間。MAX_COMMIT_BACKOFF_TIME：コミットフェーズ中に再試行が必要なエラーがSQLステートメントで発生した場合の再試行までの最大待機時間。AVG_RESOLVE_LOCK_TIME：トランザクション間で発生したロックの競合を解決するための平均時間。MAX_RESOLVE_LOCK_TIME：トランザクション間で発生したロックの競合を解決するための最長時間。AVG_LOCAL_LATCH_WAIT_TIME：ローカルトランザクションの平均待機時間。MAX_LOCAL_LATCH_WAIT_TIME：ローカルトランザクションの最大待機時間。AVG_WRITE_KEYS：書き込まれたキーの平均数。MAX_WRITE_KEYS：書き込まれたキーの最大数。AVG_WRITE_SIZE：書き込まれたデータの平均量（バイト単位）。MAX_WRITE_SIZE：書き込まれるデータの最大量（バイト単位）。AVG_PREWRITE_REGIONS：プリライトフェーズに関係するリージョンの平均数。MAX_PREWRITE_REGIONS：プリライトフェーズ中のリージョンの最大数。AVG_TXN_RETRY：トランザクションの平均再試行回数。MAX_TXN_RETRY：トランザクションの再試行の最大数。SUM_BACKOFF_TIMES：このカテゴリのSQLステートメントで再試行が必要なエラーが発生した場合の再試行の合計。BACKOFF_TYPES：再試行が必要なすべてのタイプのエラーと各タイプの再試行回数。フィールドの形式はtype:numberです。複数のエラータイプがある場合は、それぞれがtxnLock:2,pdRPC:1のようにコンマで区切られます。AVG_AFFECTED_ROWS：影響を受ける行の平均数。PREV_SAMPLE_TEXT：現在のSQLステートメントがCOMMITの場合、PREV_SAMPLE_TEXTはCOMMITの前のステートメントです。この場合、SQLステートメントはダイジェストとprev_sample_textによってグループ化されます。これは、prev_sample_textが異なるCOMMITのステートメントが異なる行にグループ化されることを意味します。現在のSQLステートメントがCOMMITでない場合、PREV_SAMPLE_TEXTフィールドは空の文字列です。
statements_summary_evictedフィールドの説明
BEGIN_TIME：開始時刻を記録します。END_TIME：終了時刻を記録します。EVICTED_COUNT：レコード期間中に削除されたSQLカテゴリの数。