How to Trigger Timely and Automatic Updates of INFORMATION_SCHEMA.tables Table Information

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

Original topic: INFORMATION_SCHEMA.tables表信息如何触发其及时、自动更新

| username: Jolyne

[TiDB Usage Environment] Production Environment / Testing / PoC
Production Environment
[TiDB Version]
5.2.1
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
The data in the tables_rows field of the INFORMATION_SCHEMA.tables table is inaccurate. How to trigger its timely and automatic update
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: Kongdom | Original post link

According to the TiDB official documentation, the TABLE_ROWS field in the INFORMATION_SCHEMA.TABLES table is an estimate and is not updated in real-time; it is updated when the ANALYZE TABLE command is executed. Therefore, if you need to update the TABLE_ROWS field value timely and automatically, you can achieve this by periodically executing the ANALYZE TABLE command. You can use the following command to update the statistics of all tables:

ANALYZE TABLE table_name;

Here, table_name is the name of the table whose statistics need to be updated. If you need to update the statistics of multiple tables, you can separate the table names with commas. Additionally, TiDB provides a batch update method for the ANALYZE TABLE command, and you can use the following command to update the statistics of all tables:

ANALYZE TABLE table_name1, table_name2, ...;

It is important to note that the ANALYZE TABLE command will lock the table, so you need to be aware of its impact on business operations when executing this command.

| username: Jolyne | Original post link

Sure, I’ll give it a try, thanks.

| username: Jolyne | Original post link

We have a task that involves counting and displaying data. It’s not feasible to execute analyze regularly as it would impact business operations. I would like to ask if there is any configuration file where we can modify the update frequency of information_schema.tables.

| username: Kongdom | Original post link

TiDB can set parameters for automatic updates.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.