Analyze will not execute automatically

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

Original topic: analyze 不会自动执行

| username: 艾琳逼哥索

Health below the threshold did not execute statistics.

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

The automatic collection of statistics at the table level is serial. Check the mysql.analyze_jobs table for records of currently executing and historically executed statistics collection tasks.

| username: zhanggame1 | Original post link

The minimum value for tidb_auto_analyze_ratio is 0.3, changing it to 0.2 is useless.

| username: 艾琳逼哥索 | Original post link

I set it up, but it didn’t execute.

| username: zhaokede | Original post link

Can’t find the record in the historical executions either?

| username: Kongdom | Original post link

  1. Pay attention to the time zone.
  2. Check the parameter tidb_enable_auto_analyze.
  3. How much data is in the table? If it’s less than 1000 rows, it won’t be triggered.
| username: residentevil | Original post link

I have also encountered this problem. For scenarios with many tables and a large number of rows in each table, this issue arises. In versions before 6.5, the system performed analyze table serially, which was very inefficient. Versions >=6.5 support concurrent execution, but TiDB’s health calibration in this area is not very perfect. We are also reviewing the source code in this area to see if we can add more coroutines.

| username: TiDBer_aaO4sU46 | Original post link

Generally, it does not meet the conditions for auto analyze.

| username: residentevil | Original post link

In many scenarios, before auto analyze is triggered, the SQL response time has already increased several times to dozens of times. :sweat_smile:

| username: TIDB-Learner | Original post link

Two values: 0.3 and 1000

| username: FutureDB | Original post link

As mentioned by the experts above,

  1. Whether tidb_enable_auto_analyze is enabled;
  2. Pay attention to whether the auto_analyze meets the tidb_auto_analyze_ratio threshold (minimum is 0.3);
  3. Whether the data in the table is less than 1000 rows, if it is less than 1000 rows, automatic statistics collection will not be triggered;
| username: 艾琳逼哥索 | Original post link

It seems that there is no such parameter tidb_enable_auto_analyze in version 5.2. I can’t find this parameter. Could it be a time zone issue? Should it be set to +0800?

| username: Trouble | Original post link

Check if the parameter is enabled and the trigger conditions.

| username: 艾琳逼哥索 | Original post link

Version 5.2 does not have this tidb_enable_auto_analyze parameter.

| username: Kongdom | Original post link

Sorry, I didn’t notice the version number before. This is the FAQ corresponding to version 5.2.

| username: redgame | Original post link

You still need to check the trigger conditions.

| username: 小于同学 | Original post link

The data in the table is not enough to reach 1000, right?

| username: 艾琳逼哥索 | Original post link

There are 20 million records.

| username: 艾琳逼哥索 | Original post link

I’ve checked everything but it still doesn’t work.


| username: Kongdom | Original post link

Try changing it to 1