Through analysis, it was found that there is a bottleneck in the concurrent operators of the SQL. To improve speed by adjusting concurrency, how should tidb_executor_concurrency be set appropriately?

Translated
1

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

Original topic: 通过分析发现sql有现瓶颈在有并发的算子上，想调整并发提升速度，tidb_executor_concurrency该怎么调合适

| username: Jolyne

【TiDB Usage Environment】Production Environment / Testing / Poc
Testing
【TiDB Version】
5.4.1
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Phenomenon and Impact】
How to adjust the value of tidb_executor_concurrency to an appropriate value.
【Resource Configuration】
【Attachments: Screenshots / Logs / Monitoring】

2
| username: 我是咖啡哥 | Original post link

Is it okay to increase this if the memory is large enough?

tidb_executor_concurrency introduced from version v5.0

  • Scope: SESSION | GLOBAL
  • Persisted to the cluster: Yes
  • Default value: 5
  • Range: [1, 256]

This variable is used to uniformly set the concurrency of various SQL operators, including:

  • index lookup
  • index lookup join
  • hash join
  • hash aggregation (partial and final stages)
  • window
  • projection

In OOM solutions, it is generally recommended to set it to 1
tidb_executor_concurrency = 1 to trigger the HashAgg spill-to-disk feature.

3
| username: Jolyne | Original post link

I understand that if the value here is greater than 1, it means that multiple operators can be executed. If it is equal to 1, only one operator is executed. If the data volume is large, it will spill to disk to reduce pressure, but it will consume a lot of memory. One thing I don’t understand is, in most cases, is this faster than multi-threading?

4
| username: tidb菜鸟一只 | Original post link

I think it should be similar to Oracle’s parallel execution, consuming CPU and memory. Originally, each operator executed individually and sequentially pulled data into memory. Now, with parallel execution, it can easily put too much pressure on the server.

5
| username: 我是咖啡哥 | Original post link

Setting tidb_executor_concurrency = 1 triggers the HashAgg spill-to-disk feature.
Spilling to disk reduces OOM and doesn’t consume a lot of memory. However, it becomes slower because disk is definitely slower than memory.

6
| username: system | Original post link

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