Automatic Table Analyze Not Triggered

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

Original topic: 表的自动analyze不触发

| username: zhanggame1

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] 7.5
[Reproduction Path] What operations were performed when the issue occurred
Yesterday, I imported some data and then checked the SQL execution plan, which seemed incorrect. I also checked the table’s health and saw many zeros. Why wasn’t there an automatic analysis?

| username: Kongdom | Original post link

Trigger policy: If a new table reaches 1000 records and there is no write within 1 minute, it will be automatically triggered.

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

If it was imported, it might not have been analyzed yet. Analysis doesn’t happen that quickly.

| username: 哈喽沃德 | Original post link

Small data changes

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

Could you take a look at SPL first? The health score is already 0, but the changes are still minimal.

| username: zhanggame1 | Original post link

Some of the tables here have data in the tens of thousands. The empty database was imported, and it was imported yesterday.

| username: zhanggame1 | Original post link

The data was imported yesterday and reviewed today; it’s been a long time.

| username: changpeng75 | Original post link

Is the tidb_enable_auto_analyze parameter enabled? Could it be that the tidb_auto_analyze_ratio hasn’t been reached?

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

Check the automatic collection of relevant parameters, refer to: 常规统计信息 | PingCAP 文档中心
Then check the execution status of the automatic collection tasks by querying mysql.analyze_jobs for currently executing statistics collection tasks and historical task records from the past 7 days.

| username: zhanggame1 | Original post link

The parameters are enabled, 7.5 default installation.

| username: changpeng75 | Original post link

Can manual analysis succeed?

| username: zhanggame1 | Original post link

The image is not available for translation. Please provide the text content directly.

| username: zhanggame1 | Original post link

Sure, the analysis was successful just now.

| username: zhanggame1 | Original post link

Opened

| username: changpeng75 | Original post link

Can you find a small table where data can be written, then write twice the amount of data into it to see if it can be triggered?

| username: zhanggame1 | Original post link

I checked, the automatic analysis should be running, but it is very slow due to the partition table running on individual partitions. Manual analysis is much faster. Here is the automatic analysis:

Here is the manual analysis from this morning:

| username: changpeng75 | Original post link

Is this large partitioned table also analyzed manually?

| username: zhanggame1 | Original post link

It was manually analyzed. After checking the logs, the automatic analysis is very slow, analyzing one partition at a time. Manual analysis is much faster.

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

Well, it’s better to manually analyze when migrating a large amount of data. The automatic default concurrency is low and relatively slow, with the aim of reducing the impact on the online system.

| username: Kongdom | Original post link

How was it imported? For example, restoring with BR requires a manual analyze.