Background

TiDB provides the analyze table table_name command to generate statistics for tables. When analyzing partitioned tables, TiDB processes each partition independently and in parallel. Once analysis completes, TiDB aggregates the individual partition statistics into a single global statistics object for the entire table.

The analyze table command has two concurrency-related parameters:

• tidb_build_stats_concurrency: Determines how many partitions can be processed simultaneously during statistics collection
• tidb_analyze_partition_concurrency: Controls parallel workers for saving partition statistics

However, these parameters have several drawbacks:

• The name tidb_build_stats_concurrency is imprecise - it actually governs parallel processing of partitions/tables rather than statistics building itself
• Having two separate concurrency controls is unnecessarily complex and makes it difficult for users to understand and predict the performance impact of their settings

In this blog post, we will discuss the issues with the current parameters and propose a solution to merge them into one.

Test Result

Small Partition Table

To evaluate the performance impact of these parameters, I benchmarked an analyze table command on a partitioned table containing 100 partitions and 30 million rows. Here are the results:

Note: Test environment: 3 TiKV nodes and 3 TiDB nodes, each with 16 cores and 32GB RAM.

The benchmark results clearly show that tidb_build_stats_concurrency has a much larger impact on overall execution time than tidb_analyze_partition_concurrency.

This makes sense given the small partition sizes (300k rows per partition) - analyzing each partition completes quickly and generates minimal statistics data to save. As a result, the parallel statistics saving controlled by tidb_analyze_partition_concurrency isn’t a performance bottleneck, explaining its minimal impact on total execution time.

The following sections will prove this point by showing the analyze_jobs table.

Analysis

Before we dive into the details, let’s first take a look at the analysis model of TiDB.

Collection Phase

Here’s how TiDB’s analyze workflow works:

1. The Analyze Plan Builder splits the work into tasks by table/partition
2. Multiple analyze workers run these tasks in parallel
3. Each worker:
   • Processes its assigned data independently
   • Streams results back to a central handler
   • Updates system tables with statistics as it goes
4. After all workers finish:
   • Global statistics are merged if needed
   • The statistics cache gets refreshed

This parallel architecture enables efficient and reliable statistics collection across the TiDB cluster. The tidb_build_stats_concurrency parameter plays a crucial role here by determining how many analyze workers can run simultaneously, directly impacting the throughput of partition processing.

Persistence Phase

Once data collection completes, TiDB enters the persistence phase. During this phase, the system writes the collected statistics to some system tables: mysql.stats_meta, mysql.stats_buckets, etc. The tidb_analyze_partition_concurrency parameter determines how many concurrent workers handle these write operations.

These parameters have different but related roles. The system follows a producer-consumer pattern, with tidb_build_stats_concurrency as the producer controlling statistics collection, and tidb_analyze_partition_concurrency as the consumer managing persistence. This design means their relative impact on performance can vary significantly depending on the specific workload characteristics.

Now that we understand both phases, let’s dive into the execution details of the analyze table command to see how it works in practice.

TiDB records the execution details of the analyze table command in the mysql.analyze_jobs table. The following is an example of the analyze_jobs table:

SELECT table_schema,
       table_name,
       partition_name,
       job_info,
       processed_rows,
       start_time,
       end_time,
       state
FROM mysql.analyze_jobs
LIMIT 3;

+------------+----------+--------------+-------------------------------------------------------------------------------------------------------------+--------------+-------------------+-------------------+--------+
|table_schema|table_name|partition_name|job_info                                                                                                     |processed_rows|start_time         |end_time           |state   |
+------------+----------+--------------+-------------------------------------------------------------------------------------------------------------+--------------+-------------------+-------------------+--------+
|test        |test_table|p18           |auto analyze table all indexes, columns id, part_id with 256 buckets, 100 topn, 1 samplerate                 |413000        |2024-11-18 15:53:04|2024-11-18 15:53:05|finished|
|test        |test_table|p6            |auto analyze table all indexes, columns id, part_id with 256 buckets, 100 topn, 0.2722772277227723 samplerate|425000        |2024-11-18 15:53:05|2024-11-18 15:53:06|finished|
|test        |test_table|p0            |auto analyze table all indexes, columns id, part_id with 256 buckets, 100 topn, 0.2736318407960199 samplerate|434000        |2024-11-18 15:53:05|2024-11-18 15:53:06|finished|
+------------+----------+--------------+-------------------------------------------------------------------------------------------------------------+--------------+-------------------+-------------------+--------+

Let’s analyze the overall timeline from when statistics collection begins to when the data is persisted to storage. This will give us insight into both the collection and persistence phases of the operation.

Examining the start_time and end_time columns reveals that individual partition processing times remain consistent across different parameter configurations, with all partitions completing within a 10-second window. This consistent timing indicates that the overall performance differences we observed cannot be explained by variations in how long it takes to process each partition.

However, examining the number of concurrent partition operations reveals that tidb_build_stats_concurrency directly controls the degree of parallelism in partition processing.

SELECT COUNT(*) AS record_count
FROM mysql.analyze_jobs
WHERE start_time BETWEEN '2024-11-22 11:31:00' AND '2024-11-22 11:31:59';

The analysis reveals that increasing tidb_build_stats_concurrency enables more partitions to be processed concurrently. Since statistics persistence is not a performance bottleneck, this higher degree of partition-level parallelism directly translates to faster overall statistics collection.

This finding explains the significant performance impact of tidb_build_stats_concurrency compared to the relatively minor effect of tidb_analyze_partition_concurrency.

Large Partition Table

I also tested the analyze table command on a partitioned table containing 100 partitions and 2 billion rows. The results are as follows:

Note: Test environment: 3 TiKV nodes and 3 TiDB nodes, each with 16 cores and 32GB RAM. Same as the previous test.

The results from this large-scale test validate our earlier findings - increasing tidb_build_stats_concurrency provides significant performance improvements, while tidb_analyze_partition_concurrency has a relatively minor impact on overall execution time.

While our initial findings suggest that tidb_build_stats_concurrency is the dominant factor in performance optimization, let’s examine a different scenario to validate this hypothesis.

Wide Table

I tested the analyze table command on a wide table containing 500 partitions and 200 columns and 3 million rows. Here are the results:

The execution time is influenced by both parameters, with tidb_analyze_partition_concurrency having a more significant impact compared to the small partition table test. This is because the increased number of columns creates a bottleneck during the statistics persistence phase.

These settings are hard to tune correctly. Optimal configuration requires deep understanding of both the collection and persistence phases for your specific workload. Additionally, since these parameters are cluster-wide settings rather than table-specific, finding ideal values that work well across different table schemas becomes challenging.

Conclusion

From our tests, we can see that adjusting these two parameters is actually quite challenging. While tidb_build_stats_concurrency dominates performance for partitioned tables, our wide table test shows that tidb_analyze_partition_concurrency can have a more significant impact in certain scenarios. To simplify configuration, I recommend merging these parameters. Since tidb_analyze_partition_concurrency better describes the overall functionality of controlling parallelism during statistics collection, we should keep this parameter and deprecate tidb_build_stats_concurrency. This will make tuning more straightforward while still providing the necessary control over concurrency for different table types.