ClickHouse外部排序触发MEMORY_LIMIT_EXCEEDED问题求助

问题背景 #

使用MergeTree引擎存储数亿行、近百列的大表，无ORDER BY的复杂条件查询可正常返回数千万行数据，但添加ORDER BY后触发MEMORY_LIMIT_EXCEEDED错误：

Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: (total) memory limit exceeded:
would use 56.36 GiB (attempt to allocate chunk of 6628255 bytes),
current RSS 48.65 GiB, maximum: 56.36 GiB.
OvercommitTracker decision: Query was selected to stop by OvercommitTracker:
While executing BufferingToFileTransform. (MEMORY_LIMIT_EXCEEDED)

已尝试设置max_bytes_before_external_sort = 10000000000（10GB），并为排序字段fname_ts添加minmax索引：

┌─database─┬─table──┬─name─────────┬─type───┬─type_full─┬─expr─────┬─granularity─┬─data_compressed_bytes─┬─data_uncompressed_bytes─┬─marks─┐
1. │ default  │ mylogs │ idx_fname_ts │ minmax │ minmax    │ fname_ts │           1 │                236431 │                 2573020 │ 49574 │
└──────────┴────────┴──────────────┴────────┴───────────┴──────────┴─────────────┴───────────────────────┴─────────────────────────┴───────┘

解决方案 #

1. 优化外部排序触发与内存参数 #

• 降低max_bytes_before_external_sort值：当前设置的10GB接近内存上限，ClickHouse缓冲到外部文件时仍需额外内存，建议将该值调至内存上限的1/5~1/3（比如5GB），强制更早触发外部排序：

  SET max_bytes_before_external_sort = 5000000000;
  

• 开启分区排序：设置sort_with_partitioning = 1，让ClickHouse按数据分区拆分排序任务，减少单批次内存占用：

  SET sort_with_partitioning = 1;
  

• 调大查询级内存限制：如果服务器硬件允许，临时调高当前查询的内存上限：

  SET max_memory_usage_for_query = 100000000000; -- 100GB
  

2. 利用MergeTree有序性减少排序开销 #

如果查询的ORDER BY字段与MergeTree表的ORDER BY主键一致，ClickHouse会直接利用数据有序性避免全量排序。若当前表主键不是fname_ts，可：

• 创建预排序物化视图：基于原表创建物化视图，将fname_ts设为排序主键，查询时直接从物化视图获取数据：

  CREATE MATERIALIZED VIEW mylogs_sorted TO MergeTree ORDER BY fname_ts AS SELECT * FROM mylogs;
  

3. 分阶段处理查询 #

通过拆分查询步骤，减少单次排序的数据量：

• 先将过滤后的数据写入临时表：

  CREATE TEMPORARY TABLE temp_result ENGINE = MergeTree ORDER BY tuple() AS SELECT [需要的几十列] FROM mylogs WHERE [复杂条件];
  

• 再对临时表执行排序查询：

  SELECT * FROM temp_result ORDER BY fname_ts;
  

临时表会将数据落盘，排序时直接基于磁盘数据进行外部排序，缓解内存压力。

4. 优化查询过滤逻辑 #

进一步缩小排序的数据范围：

• 将WHERE条件中的过滤字段移至PREWHERE，让ClickHouse先过滤数据再读取全量列，减少参与排序的行数：

  SELECT [几十列] FROM mylogs PREWHERE [复杂过滤条件] ORDER BY fname_ts;
  

内容的提问来源于stack exchange，提问作者CuriousD