# Managing Gathering Job Performance

| System Variable                      | Default | Function                                                                                                                                                                                                                                      |
| :----------------------------------- | :------ | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `tidb_build_stats_concurrency `      | 4       | Currently, when executing `ANALYZE`, it is split into individual tasks, with each task responsible for a specific column or index. The system variable `tidb_build_stats_concurrency` can control the number of tasks executed simultaneously |
| `tidb_distsql_scan_concurrency`      | 15      | When executing tasks to analyze regular columns, the system variable `tidb_distsql_scan_concurrency` can be used to control the number of regions read in one scan.                                                                           |
| `tidb_index_serial_scan_concurrency` | 1       | When executing tasks to analyze index columns, the system variable `tidb_index_serial_scan_concurrency` can be used to control the number of regions read in one scan.                                                                        |

------------------------------------------------------------------------------
We now recognize that collecting statistics involves performing a full table scan, which demands substantial CPU and I/O resources. Consequently, it is crucial to avoid collecting statistics during peak business hours.

How can we expedite the statistics collection process to complete it within the shortest possible time frame?

When executing the `ANALYZE` statement, you can adjust the concurrency or the number of regions collected by modifying certain system variables, thereby achieving a balance between TiDB cluster load and execution speed.

- `tidb_build_stats_concurrency` (Default: 4):
  When the `ANALYZE TABLE` command is executed, the collection task is divided into smaller sub-tasks, each handling a specific column or index. Adjusting this variable controls the number of tasks executed concurrently. It is advised not to set this value higher than the total number of CPU cores across all TiKV nodes.
  
  For example, if a table has 4 indexes, statistics need to be collected for the table, its columns, and the 4 indexes. With a concurrency setting of 4 (`tidb_build_stats_concurrency = 4`), one index or column statistics collection will be in a pending state, as the workload comprises the table and 4 index/column collections. Increasing this variable to 5 would allow for the simultaneous collection of all statistics for this table, thereby reducing the overall collection time.
  
  Note: Increasing this variable may impact the performance of business SQL executions within the cluster and should be evaluated accordingly.

- **`tidb_distsql_scan_concurrency`:**
  This variable sets the concurrency for table scan operations. Higher values are recommended for OLAP (analytical) applications, whereas lower values suit OLTP (transactional) applications. For OLAP applications, it is recommended to not exceed the total number of CPU cores across all TiKV nodes.
  
  Note: This variable is not specific to statistics collection. Adjusting it will affect all table scan operations involved in SQL queries.

- **`tidb_index_serial_scan_concurrency`:**
  This variable sets the concurrency for sequential scan operations (index scan). Like the previous variable, higher values are suitable for OLAP applications, and lower values are preferable for OLTP applications. For OLAP applications, ensure the setting does not exceed the total CPU core count across all TiKV nodes.
  
  Note: This variable is not exclusive to statistics collection. Changes to this variable will impact all table scan operations related to SQL.

By carefully tuning these variables, you can optimize the statistics collection process to coexist efficiently with other operations in the database environment.