TiDB 7.5.1: The health of some wide table partitions remains at 0, and neither drop stats nor manual analyze works

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

Original topic: tidb7.5.1:部分宽表分区健康度一直是0,drop stats 和手动analyze也不行

| username: foxchan

[TiDB Usage Environment] Production Environment
[TiDB Version] 7.5.1

Most wide tables have a partition health score of 0.
Number of fields:

Dropped statistics:
image

Cannot see the table’s health score:

Manual analyze:

Multiple analyze attempts were successful:

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

What is the stats healthy of the P20240501 partition? It looks like the statistics information for this partition has been successfully collected.

| username: foxchan | Original post link

The post is there, but the health status is not visible.

| username: 友利奈绪 | Original post link

Details needed.

| username: shigp_TIDBER | Original post link

What is the health status after performing analyze on P20240501?

| username: 连连看db | Original post link

Check the analyze status to see if there are any failed tasks related to the table you are concerned about.

| username: TIDB-Learner | Original post link

Health calculation method:
When modify_count >= row_count, the health is 0; when modify_count < row_count, the health is (1 - modify_count / row_count) * 100.
Are there a lot of data modification operations?
What is the current GC time? Try increasing it.

| username: zhh_912 | Original post link

First, clean up the junk data, then perform an OP and see if it has any effect.

| username: hawkingrei | Original post link

Additionally, how many partitions does your partitioned table have? If the number of partitions is relatively large, please increase tidb_auto_analyze_partition_batch_size. The default value has been changed to 128 in version 7.6 and above, which can speed up the auto analyze process for partitioned tables.

Also, if the total number of rows within your partition is less than 1000, auto analyze will not be performed.

| username: YuchongXU | Original post link

It might be a bug.

| username: yytest | Original post link

It is indeed quite strange. Check if there are any error logs.

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

Where is 20240501… Am I blind?

| username: xfworld | Original post link

Hehe, you didn’t rebuild it after deleting it, right? :laughing:

| username: 何明_亿玛 | Original post link

After dropping the partition statistics on 20240501, they were not automatically rebuilt.

| username: 何明_亿玛 | Original post link

After analyze, it is still 0


image

| username: 不想干活 | Original post link

I feel that the health results after manually analyzing should actually be the same as the situation after dropping, and other information needs to be analyzed.

| username: Jasper | Original post link

I tried to reproduce it locally with v7.5.1 but was unsuccessful. Could you please share your table structure?

| username: foxchan | Original post link

The table structure is as follows:
sql.txt (141.9 KB)

| username: Jasper | Original post link

Still couldn’t reproduce it. Have you changed the tidb_partition_prune_mode parameter? What is it set to now?

| username: foxchan | Original post link

The data is not large either.