Issues with Setting tidb_auto_analyze_end_time and tidb_auto_analyze_start_time

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

Original topic: tidb_auto_analyze_end_time和 tidb_auto_analyze_start_time 时间设置问题

| username: yulei7633

Currently, my configuration is as follows:


System time zone:

Time zone used by MySQL:

In the above image, I have already configured automatic updates only from 0:00 to 7:00. Why can I still see analyze statements in the slow query log outside the 0:00 to 7:00 window? Should I change the configuration from +0800 to +0000?

| username: 有猫万事足 | Original post link

Select the column and bring up the execution username to see. If it is empty, it is automatic analysis.
Also, check the is it an internal SQL query option.

| username: yulei7633 | Original post link

It is automatically analyzed by the system. The time should be set as follows:


Consult the official response and confirm it later.

| username: RenlySir | Original post link

Yes, UTC time is 8 hours behind Beijing time. So you need to calculate and switch accordingly.

| username: yulei7633 | Original post link

Okay. Thanks.

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

Did you solve your problem? I didn’t understand. Didn’t you set the parameters to collect statistical information from 0 to 7 in the +08 time zone before? Isn’t it still the +08 time zone after the change?

| username: yulei7633 | Original post link

The problem is solved. In the +08 time zone, the start time is 16:00, not the previous 00:00. Calculating from 16:00, adding 8 hours results in 0:00. That’s how it’s calculated.

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

:rofl: :rofl: :rofl: Is this how it’s calculated? :rofl: :rofl: :rofl: It’s best to double-check.

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

I want to ask if this is a partitioned table.

| username: forever | Original post link

UTC time is calculated like this.

| username: tidb菜鸟一只 | Original post link

Your time isn’t UTC either, isn’t it CST? Setting the collection time from 00:00 +0800 to 07:00 +0800, isn’t that from midnight to 7 AM? You have an analyze statement at 10 o’clock, it should be executed manually, right…

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

According to the official documentation and the initial configuration by the original poster, I understand that TiDB should execute the statistics collection task within the window from 0 to 7 AM Beijing time. However, in reality, we see from the slow query log that an analyze statement finished execution at 10:07 AM Beijing time, with an execution time of 43 minutes. The SQL statement started around 9 AM, and the original poster confirmed that it was automatically initiated by the auto statistics feature, which does not comply with the window setting.

The maximum execution time for automatic statistics collection is limited by the parameter tidb_max_auto_analyze_time. I remember there was a bug that might not correctly limit the execution time for partitioned tables, which could lead to analyze statements being initiated outside the specified window (PS: this is just my speculation).

| username: dba远航 | Original post link

This confusion is caused by the system time zone settings.

| username: system | Original post link

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