Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 空分区表收集分区统计信息很慢
[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?
show table XXX regions to see how many regions this table has.
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
- Remove empty buckets
- Introduce a damping factor when calculating bucket NDV
- Limit the minimum size of global buckets
- Adjust bucket boundaries according to NDV
- Disable bucket NDV in global-stats
Just drop and recreate it. Partition tables can also delete partitions, but space cannot be freed by delete.
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.
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
Be cautious when using analyze table on partitioned tables, set a threshold. At that time, one partition took more than 30 seconds.
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.
Well, I didn’t skip it, I changed it to delete empty partitions.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: yeminhua
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: