Statistics for several tables remain at 0

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

Original topic: 统计信息有几个表一直为0

| username: 路在何chu

[TiDB Usage Environment] Production Environment
4013
[TiDB Version]
4013
[Reproduction Path] What operations were performed when the issue occurred
Just collected statistical information
[Encountered Issue: Problem Phenomenon and Impact]
There are still values of 0, and the health values of two tables remain unchanged

| username: 江湖故人 | Original post link

Perform an analyze, and at the same time, show analyze status to check the progress.

| username: 江湖故人 | Original post link

Check if mysql.analyze_jobs has any historical failure records.

| username: Soysauce520 | Original post link

How much data is there?

| username: wangccsy | Original post link

Why must it be non-zero?

| username: forever | Original post link

Is there no data in the table?

| username: FutureDB | Original post link

Automatic statistics collection generally has a threshold parameter (tidb_auto_analyze_ratio). When the threshold is reached, automatic statistics collection will be triggered. If the amount of changed data is too small or the amount of data in the table is too small, it may not trigger automatic statistics collection. You can try using the ANALYZE TABLE command to manually collect statistics and see the results.

| username: TiDBer_lBAxWjWQ | Original post link

How large is the amount of data in the table?

| username: dba远航 | Original post link

If the table has not been analyzed and counted, this might happen. It could be due to no data or too little data.

| username: Kongdom | Original post link

There are requirements for the number of table records and table changes.

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

When the ratio of (number of modifications/current total rows) of the table is greater than tidb_auto_analyze_ratio, the analyze statement will be automatically triggered. The default value of tidb_auto_analyze_ratio is 0.5, which means auto analyze is enabled by default. To be on the safe side, when enabling auto analyze, the minimum value of tidb_auto_analyze_ratio is 0.3. However, this variable value cannot be greater than or equal to pseudo-estimate-ratio (default value is 0.8), otherwise pseudo statistics will be used for a period of time. It is recommended to set the value to 0.5.

| username: andone | Original post link

Check the amount of data in the table; if it’s too small, it might not trigger the automatic update of statistics.

| username: 路在何chu | Original post link

I just collected and then deleted the statistics, collected again, and it became 100, but after a few days, it happened again. The key issue is with these few tables, others are normal and have data.

| username: FutureDB | Original post link

Did these tables have any data changes before they became 0?