The source of SQL statement analysis data in TiDB's dashboard

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb 的dashboard 中的SQL语句分析数据来源

| username: Raymond

  1. Yes, some of the data sources for SQL statement analysis in TiDB’s dashboard come from the STATEMENTS_SUMMARY and STATEMENTS_SUMMARY_HISTORY tables under the information_schema database.

  2. The STATEMENTS_SUMMARY and STATEMENTS_SUMMARY_HISTORY tables can store data for a certain period or a specific number of entries. The official documentation might not be very detailed. According to the configuration, statements_summary is cleared every 30 minutes, so statements_summary_history retains the last 12 hours of historical data. —> Why does statements_summary_history retain the last 12 hours of historical data?

2
| username: Kongdom | Original post link
  1. Yes
  2. The size is 24, the interval is 30 minutes, 24x30 minutes = 12 hours
3
| username: 张雨齐0720 | Original post link

Capacity is not an issue. You can adjust these parameters according to your needs.

4
| username: buddyyuan | Original post link

Question 1: Yes.
Question 2: There are 24 time periods, each interval is 30 minutes, so 12 hours of historical data is retained. The amount stored is controlled by the parameter tidb_stmt_summary_max_stmt_count, which is currently designed to save 3000 types of SQL statements. Ideally, one type of SQL statement is retained as one entry, ignoring differences in constants, whitespace, and case. If the SQL has multiple execution plans, multiple entries may be retained.

5
| username: Raymond | Original post link

tidb_stmt_summary_history_size: The number of historical records saved for each type of SQL in statements_summary_history, which is also the table capacity of statements_summary_evicted. The default value is 24. ----> Does this mean that if a particular SQL has different execution plans, a maximum of 24 records will be retained for this SQL?

6
| username: buddyyuan | Original post link

If a statement is executed frequently and quickly exceeds 3000 statements, it will still retain 24 time windows (12 hours). At this point, it will use the LRU list to eliminate old SQL statements and add them to the eviction table. (Note that eviction will only occur if current + history > 3000)