Statistics for some tables are not automatically collected

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

Original topic: 部分表的统计信息未自动收集

| username: chenhanneu

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Problem Phenomenon and Impact]
Parameter Settings:
1702866519083

1702866572342

Record of the last successful automatic collection on one of the tables:

Checking with show analyze status shows no tasks are currently running.
These tables also have data volumes in the millions.

| username: dba远航 | Original post link

Check if the table data is the latest change; the latest changes may not have been analyzed and counted yet.

| username: chenhanneu | Original post link

The latest data is from 2 hours ago.

| username: 像风一样的男子 | Original post link

You can create a scheduled task to manually perform analysis during off-peak business hours.

| username: heiwandou | Original post link

Is there a significant change in the data volume of the table without collected information?

| username: Kongdom | Original post link

:yum: I suggest adding a task definition for manually collecting statistical information to fill in the gaps. The official automatic collection has conditional limitations; it only collects automatically when the change records reach a certain threshold.

| username: 小龙虾爱大龙虾 | Original post link

If the table has fewer than 1000 rows, TiDB will not trigger automatic updates for this table. Refer to 常规统计信息 | PingCAP 文档中心 to confirm whether the tables that haven’t been updated have millions of rows.

After v6.1, you can check the analyze records of the past 7 days through mysql.analyze_jobs to further confirm that these tables have not triggered updates, rather than failing during updates.

Is there any locking of statistical information?

| username: chenhanneu | Original post link

In the picture, the smallest tables with health between 70-90 have hundreds of thousands of rows. Some tables have fewer than 1000 rows, and their health is 0. The status in mysql.analyze_jobs is all finished, with no errors, and there were automatic collection records for tables that were not automatically collected a few days ago. There is no locking. If the tidb_auto_analyze_ratio parameter is not fully effective, then manual collection is the only option.

| username: 小龙虾爱大龙虾 | Original post link

The stats-lease parameter should be the default value, right?

| username: residentevil | Original post link

The collection of statistics in TiDB is indeed not very intelligent, and it seems that quite a few people have encountered such issues.

| username: 春风十里 | Original post link

tidb_auto_analyze_ratio* This variable is used to set the threshold for TiDB to automatically execute ANALYZE TABLE in the background to update statistics. 0.5 means that when more than 50% of the rows in the table are modified, the automatic ANALYZE update is triggered. You can specify tidb_auto_analyze_start_time and tidb_auto_analyze_end_time to restrict the time for automatic ANALYZE.

I see you set it to 0.1, which is a 10% modification rate.
But calculating the modification rate of the table through healthy seems inaccurate.
I saw the official documentation shows that deleting 30% of the data makes healthy become 50%.


It feels like the calculation method is different.

| username: 小龙虾爱大龙虾 | Original post link

Health formula = (1 - modify_count / row_count) * 100
The delete operation will increase modify_count and decrease row_count, so deleting 30% of the data will cause the health to become 50. Please see the following test:

MySQL [test]> show stats_meta where table_name='tt';
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | tt         |                | 2023-12-18 18:24:26 |            0 |       100 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.01 sec)

MySQL [test]> 
MySQL [test]> 
MySQL [test]> 
MySQL [test]> show stats_healthy where table_name='tt';
+---------+------------+----------------+---------+
| Db_name | Table_name | Partition_name | Healthy |
+---------+------------+----------------+---------+
| test    | tt         |                |     100 |
+---------+------------+----------------+---------+
1 row in set (0.00 sec)

MySQL [test]> delete from tt where id<=33;
Query OK, 33 rows affected (0.01 sec)

MySQL [test]> select sleep(60);
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.00 sec)

MySQL [test]> show stats_meta where table_name='tt';
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | tt         |                | 2023-12-18 18:25:12 |           33 |        67 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

MySQL [test]> show stats_healthy where table_name='tt';
+---------+------------+----------------+---------+
| Db_name | Table_name | Partition_name | Healthy |
+---------+------------+----------------+---------+
| test    | tt         |                |      50 |
+---------+------------+----------------+---------+
1 row in set (0.00 sec)


MySQL [test]> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.5.5
Edition: Community
Git Commit Hash: 71bcc44f77a37cfb0a6dc3660e092c78c1e46acb
Git Branch: heads/refs/tags/v6.5.5
UTC Build Time: 2023-09-18 10:20:26
GoVersion: go1.19.12
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The health formula is the same in higher versions, but the value of row_count has changed, see statistics: rename Table.Count to Table.RealtimeCount by xuyifangreeneyes · Pull Request #42764 · pingcap/tidb · GitHub
It seems to be calculated using the old row_count, please see the test:

MySQL [test]> show stats_meta where table_name='tt';
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | tt         |                | 2023-12-18 09:25:58 |            0 |       100 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

MySQL [test]> show stats_healthy where table_name='tt';
+---------+------------+----------------+---------+
| Db_name | Table_name | Partition_name | Healthy |
+---------+------------+----------------+---------+
| test    | tt         |                |     100 |
+---------+------------+----------------+---------+
1 row in set (0.00 sec)

MySQL [test]> delete from tt where id<=33;
Query OK, 33 rows affected (0.02 sec)

MySQL [test]> select sleep(60);
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.00 sec)

MySQL [test]> show stats_meta where table_name='tt';
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | tt         |                | 2023-12-18 09:28:02 |           33 |        67 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)

MySQL [test]> show stats_healthy where table_name='tt';
+---------+------------+----------------+---------+
| Db_name | Table_name | Partition_name | Healthy |
+---------+------------+----------------+---------+
| test    | tt         |                |      67 |
+---------+------------+----------------+---------+
1 row in set (0.00 sec)

MySQL [test]> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.1.1
Edition: Community
Git Commit Hash: cf441574864be63938524e7dfcf7cc659edc3dd8
Git Branch: heads/refs/tags/v7.1.1
UTC Build Time: 2023-07-19 10:20:53
GoVersion: go1.20.6
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
| username: 春风十里 | Original post link

Thank you for the practical test. I was looking at the 7.5 documentation, and it seems there is an issue with the documentation that needs updating. The Healthy data in the new version is still accurate. I see that the original poster mentioned version 7.1.1, so the problem should be that the automatic collection of statistics did not execute.

| username: Jellybean | Original post link

The trigger conditions for automatic background updates of statistics are not particularly precise. It is recommended to start a scheduled task during off-peak business hours to manually analyze important tables that need to have the latest updated statistics.

| username: 江湖故人 | Original post link

You can use SHOW STATS_LOCKED; to check if the relevant table has locked statistics.
During routine inspections, pay attention to the health of key large tables and monitor the table mysql.analyze_jobs.
Personally, I suggest not adding script tasks and just using the built-in threshold mechanism of analyze. If it fails automatically, the script won’t work either.

| username: forever | Original post link

It’s still safer to update manually. In the past, Oracle also manually collected statistical information for each period.

| username: Kongdom | Original post link

Considering cluster performance, automatic analyze will definitely not be performed when there are few data changes or no data changes. Also, a table health score not being 100 does not necessarily mean poor performance; it just means that the probability of executing an incorrect execution plan is higher as the table health score decreases. If you are particularly concerned about the table health score being below 100, it is still recommended to perform analyze manually through scheduled jobs. :yum:

| username: chenhanneu | Original post link

Default value, 3 seconds

| username: andone | Original post link

Set up a scheduled task to manually execute statistics updates during off-peak business hours.

| username: 路在何chu | Original post link

Automatic collection is indeed not very effective; I collect everything manually.