Collecting Partition Statistics for Empty Partition Tables is Very Slow

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

Original topic: 空分区表收集分区统计信息很慢

| username: yeminhua

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.1
[Encountered Problem: Problem Phenomenon and Impact]
There is a partitioned table, which is an empty table, but manually collecting statistics for a single partition takes more than 4 minutes, and most of the time is spent on the “merge global stats” step. What could be the reason for this? Is there a way to speed up or skip the global merge step?

| username: zhanggame1 | Original post link

show table XXX regions to see how many regions this table has.

| username: yeminhua | Original post link

There are 226 regions.

| username: h5n1 | Original post link

In dynamic pruning mode, after analyzing the statistics of a partition, it will read the statistics of other partitions and then calculate the statistics of the partition set as global-level statistics. You can try disabling the dynamic pruning feature by setting @@tidb_partition_prune_mode = 'static'. Test it in your environment, as this will affect the SQL execution plan.

statistics: optimize the global histogram merging algorithm

  1. Remove empty buckets
  2. Introduce a damping factor when calculating bucket NDV
  3. Limit the minimum size of global buckets
  4. Adjust bucket boundaries according to NDV
  5. Disable bucket NDV in global-stats

| username: zhanggame1 | Original post link

Just drop and recreate it. Partition tables can also delete partitions, but space cannot be freed by delete.

| username: redgame | Original post link

Do not skip, as it is almost the same as not collecting. Skipping or speeding up the merging of global statistics may lead to inaccuracies in the global statistics.

| username: TiDBer_vfJBUcxl | Original post link

Is this the cause?

Partition table statistics collection bug, for example, after adding a new partition, the statistics collection did not obtain the metadata of this new partition, resulting in no collection details 6.4.4 TiDB 分区表问题处理 | tidb-in-action

| username: cy6301567 | Original post link

Be cautious when using analyze table on partitioned tables, set a threshold. At that time, one partition took more than 30 seconds.

| username: yeminhua | Original post link

Disabling dynamic partition pruning is definitely not appropriate. Originally, partitions were cleaned using the truncate method, but I changed it to the drop method. After reducing the number of partitions to a lower count, the speed of merging global stats has significantly increased.

| username: yeminhua | Original post link

Well, I didn’t skip it, I changed it to delete empty partitions.

| username: system | Original post link

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