TiDB 5.1.0 Automatic Analyze Time Setting Not Effective

Translated
1

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

Original topic: tidb 5.1.0设置自动analyze的时间不起效果

| username: liuwenhe

To improve efficiency, please provide the following information. Clear problem descriptions can be resolved more quickly:
[TiDB Usage Environment] Production

[Overview] The automatic analyze time setting for TiDB 5.1.0 is not effective, as shown below:

mysql> show variables like ‘%analyze%’;
±-----------------------------±------------+
| Variable_name | Value |
±-----------------------------±------------+
| tidb_analyze_version | 2 |
| tidb_auto_analyze_end_time | 23:59 +0000 |
| tidb_auto_analyze_ratio | 0.5 |
| tidb_auto_analyze_start_time | 10:30 +0000 |
| tidb_enable_fast_analyze | OFF |
±-----------------------------±------------+
5 rows in set (0.01 sec)
However, checking mysql> SHOW ANALYZE STATUS\G
Table_schema: bidata
Table_name: dws_111111
Partition_name:
Job_info: auto analyze table
Processed_rows: 1281117
Start_time: 2022-07-24 07:50:17
End_time: 2022-07-24 07:51:11
State: finished
I found that it was executed before 10:30 AM. Shouldn’t it only be executed between 10:30 and 23:59 after setting it this way???
Question 2: The mechanism of auto analyze table is if you set a time range for automatic execution, when the data ratio of a table you update exceeds tidb_auto_analyze_ratio, if it is not within this time range, will it not automatically analyze the table first, put it in the queue, and then read the information in the queue for auto analyze table during the time range of 10:30-23:59? Or will it abandon the execution of auto analyze table directly if it is not within this time range?

[Background] What operations have been done

[Phenomenon] Business and database phenomena

[Problem] Current problems encountered

[Business Impact]

[TiDB Version]

[Application Software and Version]

[Attachments] Relevant logs and configuration information

  • TiUP Cluster Display information
  • TiUP Cluster Edit config information

Monitoring (https://metricstool.pingcap.com/)

  • TiDB-Overview Grafana monitoring
  • TiDB Grafana monitoring
  • TiKV Grafana monitoring
  • PD Grafana monitoring
  • Corresponding module logs (including logs 1 hour before and after the issue)

If the question is about performance optimization or troubleshooting, please download the script and run it. Please select all and copy and paste the terminal output results for upload.

2
| username: h5n1 | Original post link

Change to 23:59 +0800, the start/end parameters are the time range for starting the auto analyze task. TiDB will periodically check which tables meet the analysis conditions and determine whether the current time is within the time range. If it is, the analysis task will be started.

3
| username: tidb狂热爱好者 | Original post link

If the repair time is too long, it will be abandoned. You can specify it manually.

4
| username: Mark | Original post link

Automatically update statistics based on the modify ratio of the table.

5
| username: 长安是只喵 | Original post link

Question 1: Time zone issue, refer to the first post and set it to
SET GLOBAL tidb_auto_analyze_end_time=‘23:59 +0800’;

Question 2: Firstly, the modification row count and other information of the table are stored in the metadata table, which can be viewed through SHOW STATS_META. During the defined automatic update time range, the table metadata will be queried. Based on the modification row count and other information stored in the table metadata, it will be determined whether to automatically collect statistics. Therefore, if the modification is not within the range, it will not trigger an automatic update. When it is within the time range, it will trigger an automatic update and will not be discarded.

(Quoted text) When the ratio of the modified row count to the total row count of a table tbl is greater than tidb_auto_analyze_ratio, and the current time is between tidb_auto_analyze_start_time and tidb_auto_analyze_end_time, TiDB will execute the ANALYZE TABLE tbl statement in the background to automatically update the statistics of this table.

6
| username: liuwenhe | Original post link

Thank you very much.

7
| username: system | Original post link

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