2/system-variables#tidb_enable ➔ _parallel_hashagg_spill-new-in-v800">The parallel HashAgg algorithm ➔ of TiDB supports disk spill (GA) HashAgg is a widely used aggregation operator in TiDB v8.0.0 introduces ➔ parallel HashAgg as an experimental feature to further enhance ➔ processing speed. When memory resources are insufficient, parallel ➔ HashAgg spills temporary sorted data to (GA) and is enabled by default, enabling ➔ you to safely configure the concurrency of parallel HashAgg using < ➔ code>tidb_executor_concurrency. ![OCR图片](/uploads/documents/e/0/7/e071d0e9676

ble_parallel_hashagg_ → spill-从-v800-版本开始引入">并行 HashAgg 算法支持数据落盘成为正式功能 (GA) HashAgg 是 TiDB 中常用的聚合算子，用于快速聚合具有相同字段值的行。TiDB v8.0.0 引入并行 → HashAgg 作为实验特性，以进一步提升处理速度。当内存资源不足时，并行 HashAgg → 可以将临时排序数据落盘，避免因内存使用过度而导致的 风险，从而提升查询性能和节点稳定性 → 。该功能在 v8.2.0 成为正式功能，并默认开启，用户可以通过 tidb_executor_concurrency → 安全地设置并行 HashAgg 的并发度。 ``` ```html 0 码力 | 4987 页 | 102.91 MB | 1 年前

3

关的变量和行为会发生变化。 • TiDB 在 v8.0.0 引入了系统变量tidb_enable_parallel_hashagg_spill，用于控制 TiDB 是否支持并行 HashAgg 进行落盘。在未来版本中，系统变量tidb_enable_parallel_hashagg_spill 将被废弃。 • TiDB Lightning 参数conflict.max-record-rows 计划 �→ | hour(cast(bookshop.ratings.rated_at, time))->Column#6, Column#5 �→ | 17.7 KB �→ | N/A | | └─ HashAgg_5 | 299821.99 | 24 | root | | time:60.7ms �→ , loops:6, partial_worker:{wall_time:60.660079ms, concurrency:5 row *************************** min(plan): Sort_6 root 100.00131380758702 sbtest.sbtest25.c:asc └─HashAgg_10 root 100.00131380758702 group by:sbtest.sbtest25.c, �→ funcs:firstrow(sbtest.sbtest25.c)->sbtest

to accommodate various workloads. For more information, see documentation. - TiFlash introduces HashAgg aggregation calculation modes to improve the performance for high NDV data #9196 @guo-shaoge Before efficiency during the first stage of HashAgg aggregation when handling data with high NDV (number of distinct values). Starting from v8.3.0, TiFlash introduces multiple HashAgg aggregation calculation ![OCR图 different data characteristics. To choose a desired HashAgg aggregation calculation mode, you can configure the tiflash $ \hookrightarrow $ _hashagg_preaggregation_mode system variable. For more information

tidb_enable_parallel_hashagg_spill system variable to control whether TiDB supports disk spill for the concurrent HashAgg algorithm. In future versions, the tidb_enable_parallel_hashagg_spill system variable ```  ```sql | -HashAgg_5 | 299821.99 | 24 | root | | --- | --- | --- | --- | | time:60.7ms, loops:6, part *************************** min(plan): Sort_6 root 100.00131380758702 sbtest. → sbtest25.c:asc - HashAgg_10 root 100.00131380758702 group by:sbtest. → sbtest25.c, funcs:firstrow(sbtest.sbtest25

NULL as Oracle does. User document ## • Support spilling HashAgg Support spilling HashAgg into disk. When a SQL statement that includes an HashAgg operator causes out of memory (OOM), you can try to set enable-request-batch: false TiDB global variable configuration set global tidb_hashagg_final_concurrency=1; set global tidb_hashagg_partial_concurrency=1; set global tidb_enable_async_commit = 1; set global enable-request-batch: false TiDB global variable configuration set global tidb_hashagg_final_concurrency=1; set global tidb_hashagg_partial_concurrency=1; set global tidb_enable_async_commit = 1; set global

tidb_enable_parallel_hashagg_spill system variable to control whether TiDB supports disk spill for the concurrent HashAgg algorithm. In future versions, the tidb_enable_parallel_hashagg_spill system variable N/A |  ```sql | -HashAgg_5 | 299821.99 | 24 | root | | --- | --- | --- | --- | | time:60.7ms, loops:6, part **** min(plan): Sort_6 root 100.00131380758702 sbtest. → sbtest25.c:asc - HashAgg_10 root 100.00131380758702 group by:sbtest. → sbtest25.c, funcs:firstrow(sbtest

pessimistic-txn.pipelined: true TiDB global variable configuration set global tidb_hashagg_final_concurrency=1; set global tidb_hashagg_partial_concurrency=1; set global tidb_enable_async_commit = 1; set global scheduler-worker-pool-size: 20 TiDB global variable configuration set global tidb_hashagg_final_concurrency=1; set global tidb_hashagg_partial_concurrency=1; set global tidb_enable_async_commit = 1; set global ***** 1. row ***** min(plan): Sort_6 root 100.00131380758702 sbtest. sbtest25.c:asc -HashAgg_10 root 100.00131380758702 group by:sbtest. sbtest25.c, funcs:firstrow(sbtest.sbtest25.c)->sbtest

函数可以将字符串中出现的所有指定字符替换为其它字符，TiDB 中的 translate 函数不会像 Oracle 一样将空字符串视为 NULL。 ## 用户文档 ## • 支持 Spilling HashAgg 支持 HashAgg 的落盘。当包含 HashAgg 算子的 SQL 语句引起 OOM 时，可以尝试设置算子的并发度为 1 来触发落盘，缓解 TiDB 内存压力。 用户文档，#25882 ## · 提升优化器的估算准确度 pipelined: true server.enable-request-batch: false ## TiDB 全局变量配置 set global tidb_hashagg_final_concurrency=1; set global tidb_hashagg_partial_concurrency=1; set global tidb_enable_async_commit = 1; set global e: 20 server.enable-request-batch: false ## TiDB 全局变量配置 set global tidb_hashagg_final_concurrency=1; set global tidb_hashagg_partial_concurrency=1; set global tidb_enable_async_commit = 1; set global

enable-request-batch: false TiDB global variable configuration set global tidb_hashagg_final_concurrency=1; set global tidb_hashagg_partial_concurrency=1; set global tidb_enable_async_commit = 1; set global enable-request-batch: false TiDB global variable configuration set global tidb_hashagg_final_concurrency=1; set global tidb_hashagg_partial_concurrency=1; set global tidb_enable_async_commit = 1; set global example: ***** 1. row ***** min(plan): Sort_6 root 100.00131380758702 sbtest. → sbtest25.c:asc -HashAgg_10 root 100.00131380758702 group by:sbtest. → sbtest25.c, funcs:firstrow(sbtest.sbtest25.c)->sbtest