Extremely Slow Partition Table Analyze Statement

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.0

[Encountered Problem: Problem Phenomenon and Impact]
Very slow analyze statements related to partitioned tables seen in slow queries. This table is a partitioned table.

What is this analyze statement analyzing? Why is it so slow?
The table has only a few thousand rows of data, but the number of partitions created by the automated testing script is 1000.
ANALYZE TABLE test_vegas2.win_ticket PARTITION p366752 INDEX PRIMARY;

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

Check the data in the mysql.analyze_jobs table.

| username: xfworld | Original post link

Was it manually initiated or triggered by automatic scheduling?

| username: zhanggame1 | Original post link

Automatic

| username: zhanggame1 | Original post link

Not sure if it’s this one

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

It seems that the merge global is relatively slow. Check to see if it merges global after collecting each partition.

| username: zhanggame1 | Original post link

It is possible that this step is slow.

| username: dba远航 | Original post link

For partitioned tables, the more partitions there are, the slower it becomes. For the database, each partition is equivalent to a table, which means that having over 1000 partitions is equivalent to analyzing over 1000 tables.

| username: zhanggame1 | Original post link

It doesn’t seem to have much to do with the amount of data; it just gets slower with more partitions.

| username: 江湖故人 | Original post link

The larger the amount of data, the slower the analyze process will be. With more data, more samples are taken, which consumes more resources during analysis. Of course, the number of partitions also has an impact, especially for partitions with a small amount of data, where the correlation will be greater.

| username: 江湖故人 | Original post link

It is recommended to change this partitioned table to a regular table.
To convert the partitioned table win_ticket to a non-partitioned table, you can execute the following statement:

ALTER TABLE win_ticket REMOVE PARTITIONING
| username: system | Original post link

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