TIDB_HOT_REGIONS_HISTORY
The TIDB_HOT_REGIONS_HISTORY
table provides information about history hot Regions that are periodically recorded locally by PD.
You can specify the record interval by configuring hot-regions-write-interval
. The default value is 10 minutes. You can specify the period for reserving history information about hot Regions by configuring hot-regions-reserved-days
. The default value is 7 days. See PD configuration file description for details.
USE information_schema;
DESC tidb_hot_regions_history;
+-------------+--------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+------+---------+-------+
| UPDATE_TIME | timestamp(6) | YES | | NULL | |
| DB_NAME | varchar(64) | YES | | NULL | |
| TABLE_NAME | varchar(64) | YES | | NULL | |
| TABLE_ID | bigint(21) | YES | | NULL | |
| INDEX_NAME | varchar(64) | YES | | NULL | |
| INDEX_ID | bigint(21) | YES | | NULL | |
| REGION_ID | bigint(21) | YES | | NULL | |
| STORE_ID | bigint(21) | YES | | NULL | |
| PEER_ID | bigint(21) | YES | | NULL | |
| IS_LEARNER | tinyint(1) | NO | | 0 | |
| IS_LEADER | tinyint(1) | NO | | 0 | |
| TYPE | varchar(64) | YES | | NULL | |
| HOT_DEGREE | bigint(21) | YES | | NULL | |
| FLOW_BYTES | double | YES | | NULL | |
| KEY_RATE | double | YES | | NULL | |
| QUERY_RATE | double | YES | | NULL | |
+-------------+--------------+------+------+---------+-------+
16 rows in set (0.00 sec)
The fields in the TIDB_HOT_REGIONS_HISTORY
table are described as follows:
- UPDATE_TIME: The update time of the hot Region.
- DB_NAME: The database name of the object in which the hot Region is located.
- TABLE_ID: The ID of the table in which the hot Region is located.
- TABLE_NAME: The name of the table in which the hot Region is located.
- INDEX_NAME: The name of the index in which the hot Region is located.
- INDEX_ID: The ID of the index in which the hot Region is located.
- REGION_ID: The ID of the hot Region.
- STORE_ID: The ID of the store in which the hot Region is located.
- PEER_ID: The ID of the Peer corresponding to the hot Region.
- IS_LEARNER: Whether the PEER is the LEARNER.
- IS_LEADER: Whether the PEER is the LEADER.
- TYPE: The type of the hot Region.
- HOT_DEGREE: The hot degree of the hot Region.
- FLOW_BYTES: The number of bytes written and read in the Region.
- KEY_RATE: The number of keys written and read in the Region.
- QUERY_RATE: The number of queries written and read in the Region.
Common user scenarios
Query hot Regions within a specific period of time. Replace
update_time
with your actual time.SELECT * FROM INFORMATION_SCHEMA.TIDB_HOT_REGIONS_HISTORY WHERE update_time >'2021-08-18 21:40:00' and update_time <'2021-09-19 00:00:00';Query hot Regions in a table within a specific period of time. Replace
update_time
andtable_name
with your actual values.SELECT * FROM INFORMATION_SCHEMA.TIDB_HOT_REGIONS_HISTORY WHERE update_time >'2021-08-18 21:40:00' and update_time <'2021-09-19 00:00:00' and TABLE_NAME = 'table_name';Query the distribution of hot Regions within a specific period of time. Replace
update_time
andtable_name
with your actual values.SELECT count(region_id) cnt, store_id FROM INFORMATION_SCHEMA.TIDB_HOT_REGIONS_HISTORY WHERE update_time >'2021-08-18 21:40:00' and update_time <'2021-09-19 00:00:00' and table_name = 'table_name' GROUP BY STORE_ID ORDER BY cnt DESC;Query the distribution of hot Leader Regions within a specific period of time. Replace
update_time
andtable_name
with your actual values.SELECT count(region_id) cnt, store_id FROM INFORMATION_SCHEMA.TIDB_HOT_REGIONS_HISTORY WHERE update_time >'2021-08-18 21:40:00' and update_time <'2021-09-19 00:00:00' and table_name = 'table_name' and is_leader=1 GROUP BY STORE_ID ORDER BY cnt DESC;Query the distribution of hot Index Regions within a specific period of time. Replace
update_time
andtable_name
with your actual values.SELECT count(region_id) cnt, index_name, store_id FROM INFORMATION_SCHEMA.TIDB_HOT_REGIONS_HISTORY WHERE update_time >'2021-08-18 21:40:00' and update_time <'2021-09-19 00:00:00' and table_name = 'table_name' group by index_name, store_id order by index_name,cnt desc;Query the distribution of hot Index Leader Regions within a specific period of time. Replace
update_time
andtable_name
with your actual values.SELECT count(region_id) cnt, index_name, store_id FROM INFORMATION_SCHEMA.TIDB_HOT_REGIONS_HISTORY WHERE update_time >'2021-08-18 21:40:00' and update_time <'2022-09-19 00:00:00' and table_name = 'table_name' and is_leader=1 group by index_name, store_id order by index_name,cnt desc;