一、前情提要
近日，测试服务器配置时，发现当复杂聚合场景的并发度压到20时，会出现clickhouse内存溢出，内存不足报错，如包含Exception: Memory limit (for query)、Exception: Memory limit (total) exceeded等，直接扩内存max_server_memory_usage似乎是最好的方法，但是过于财大气粗，那如果想要使用其他方法来降低内存该怎么去优化呢？
二、优化与提升
1.1 如何查询你的内存消耗？
SELECT
event_time,
query_duration_ms / 1000 AS query_duration_s,
formatReadableSize(memory_usage) AS memory,
Settings['max_bytes_before_external_group_by'] AS group_mem,
substring(query, 1, 20) AS query,interface,user,port,initial_address,initial_port
FROM system.query_log
WHERE (user = '业务用户替换') AND (type = 'QueryFinish')
ORDER BY event_time DESC
LIMIT 40;system.query_log表非常通用，但我们将重点介绍几个关键列。首先，每个查询记录多个事件。事件名称位于类型列中。我们将重点介绍 QueryFinish 事件类型，因为它显示了查询的完整统计信息。
其次，query_duration_s列和memory_usage列分别显示查询的持续时间和内存使用情况。它们很容易解释，前者是这个语句所花费的时间，后者是内存使用，从图中可以看出 insert into table select from other-table 的语句，每条需要7s左右时间，且内存9G左右，这样62G的内存完全hold不住啊。
1.2 内存优化参数
在寻找参数之前，我们先要了解聚合场景是什么？进行聚合查询时，ClickHouse 通常会分两个阶段处理数据：
1. 查询并建立中间数据：ClickHouse 会扫描原始数据并按 GROUP BY 字段构建中间聚合状态（通常是哈希表），所有分组的中间结果都会缓存在内存中。
2. 合并中间数据：当聚合涉及多个分片或数据量过大时，还需要对多个中间状态进行合并，以生成最终结果。
这一过程中，大量的中间数据对内存消耗非常高，尤其在分组数量大或维度较多的情况下，哈希表可能迅速膨胀，从而造成 OOM 风险。为了控制内存使用、提升查询稳定性，ClickHouse 提供了以下两个关键参数：
- max_bytes_before_external_group_by：限制在执行 GROUP BY 时可使用的最大内存。当内存使用超过此限制时，ClickHouse 会将中间结果写入磁盘（触发外部聚合），以避免内存耗尽。
- max_bytes_before_external_sort：用于限制排序操作的内存占用。超过限制后，将启用外部排序，即将数据写入临时文件并使用归并算法完成排序。
下面节选了Clickhouse官网的关于这两个参数的介绍：
1.2.1 max_bytes_before_external_group_by
Type |
Default value |
UInt64 |
0 |
Cloud default value: half the memory amount per replica.
Enables or disables execution of GROUP BY clauses in external memory. (See GROUP BY in external memory)
Possible values:
- Maximum volume of RAM (in bytes) that can be used by the single GROUP BY operation.
0—GROUP BYin external memory disabled.
note
If memory usage during GROUP BY operations is exceeding this threshold in bytes, activate the 'external aggregation' mode (spill data to disk).
The recommended value is half of the available system memory.
1.2.2 max_bytes_before_external_sort
Type |
Default value |
UInt64 |
0 |
Cloud default value: half the memory amount per replica.
Enables or disables execution of ORDER BY clauses in external memory. See ORDER BY Implementation Details If memory usage during ORDER BY operation exceeds this threshold in bytes, the 'external sorting' mode (spill data to disk) is activated.
Possible values:
- Maximum volume of RAM (in bytes) that can be used by the single ORDER BY operation. The recommended value is half of available system memory
0—ORDER BYin external memory disabled.
SELECT *
FROM settings
WHERE name = 'max_bytes_before_external_group_by'
Query id: 683ca1c0-4d38-443a-bea6-4e1dfd253331
┌─name───────────────────────────────┬─value─┬─changed─┬─description──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type───┬─default─┬─alias_for─┬─is_obsolete─┬─tier───────┐
1. │ max_bytes_before_external_group_by │ 0 │ 0 │ If memory usage during GROUP BY operation is exceeding this threshold in bytes, activate the 'external aggregation' mode (spill data to disk). Recommended value is half of the available system memory. │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 0 │ UInt64 │ 0 │ │ 0 │ Production │
└────────────────────────────────────┴───────┴─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────┴──────┴──────────┴────────┴─────────┴───────────┴─────────────┴────────────┘
1 row in set. Elapsed: 0.010 sec. Processed 1.17 thousand rows, 420.07 KB (122.30 thousand rows/s., 43.76 MB/s.)
Peak memory usage: 0.00 B.
crms-10-10-177-35 :) select * from settings where name ='max_bytes_before_external_sort'
SELECT *
FROM settings
WHERE name = 'max_bytes_before_external_sort'
Query id: 5dcee1c3-ada7-437c-8e04-a434b0f0dfba
┌─name───────────────────────────┬─value─┬─changed─┬─description──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type───┬─default─┬─alias_for─┬─is_obsolete─┬─tier───────┐
1. │ max_bytes_before_external_sort │ 0 │ 0 │ If memory usage during ORDER BY operation is exceeding this threshold in bytes, activate the 'external sorting' mode (spill data to disk). Recommended value is half of the available system memory. │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 0 │ UInt64 │ 0 │ │ 0 │ Production │
└────────────────────────────────┴───────┴─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────┴──────┴──────────┴────────┴─────────┴───────────┴─────────────┴────────────┘
1 row in set. Elapsed: 0.010 sec. Processed 1.17 thousand rows, 420.07 KB (118.11 thousand rows/s., 42.26 MB/s.)
Peak memory usage: 0.00 B.发现一个有趣的一点，自己搭建的clickhouse数据库这两个参数默认是0，但是在 Cloud 上是默认为 内存的一半；（PS：果然花钱的才值得被认真对待，哈哈，其实自建起码自主性高）
由此，建议这两个参数的值可以设置为 max_memory_usage 的50%～70%；
先来看看设置参数之后的效果？
1.3 优化结果
这边需要注意一点，参数需要设置在user.xml下，不需要要重启，保存后就会生效。该会话也可以设置在9090代理端口配置文件。
<max_bytes_before_external_group_by>bytes值</max_bytes_before_external_group_by>
<max_bytes_before_external_sort>bytes值</max_bytes_before_external_sort>
写错位置会有报错，也可以很快解决，生产环境还是要提前确定好的。
A setting 'max_bytes_before_external_group_by' appeared at top level in config /etc/clickhouse-server/config.xml. But it is user-level setting that should be located in users.xml inside <profiles> section for specific profile. You can add it to <profiles><default> if you want to change default value of this setting. You can also disable the check - specify <skip_check_for_incorrect_settings>1</skip_check_for_incorrect_settings> in the main configuration file. (UNKNOWN_ELEMENT_IN_CONFIG)
修改完参数之后再次执行测试之后，查看语句结果如下，内存消耗明显降低，达到优化目的。
SELECT
event_time,
query_duration_ms / 1000 AS query_duration_s,
formatReadableSize(memory_usage) AS memory,
Settings['max_threads'] AS threads,
substring(query, 1, 20) AS query
FROM system.query_log
WHERE (user = '你的业务用户') AND (type = 'QueryFinish')
ORDER BY event_time DESC
LIMIT 50
Query id: e74139d1-ac45-4e2e-8c05-c0e040846d63
┌──────────event_time─┬─query_duration_s─┬─memory───┬─threads─┬─query────────────────┐
1. │ 2025-04-30 09:48:01 │ 13.647 │ 3.57 GiB │ │ INSERT INTO ddat. │
2. │ 2025-04-30 09:48:00 │ 15.553 │ 3.57 GiB │ │ INSERT INTO ddat. │
3. │ 2025-04-30 09:47:59 │ 16.412 │ 3.58 GiB │ │ INSERT INTO ddat. │
4. │ 2025-04-30 09:47:58 │ 17.1 │ 3.57 GiB │ │ INSERT INTO ddat. │
└─────────────────────┴──────────────────┴──────────┴─────────┴──────────────────────┘
┌──────────event_time─┬─query_duration_s─┬─memory───┬─threads─┬─query────────────────┐
5. │ 2025-04-30 09:47:57 │ 18.856 │ 3.58 GiB │ │ INSERT INTO ddat. │
6. │ 2025-04-30 09:47:56 │ 20.147 │ 3.57 GiB │ │ INSERT INTO ddat. │
7. │ 2025-04-30 09:47:54 │ 21.751 │ 3.57 GiB │ │ INSERT INTO ddat. │
8. │ 2025-04-30 09:47:52 │ 21.765 │ 3.58 GiB │ │ INSERT INTO ddat. │
9. │ 2025-04-30 09:47:50 │ 22.359 │ 3.57 GiB │ │ INSERT INTO ddat. │
10. │ 2025-04-30 09:47:48 │ 22.032 │ 3.58 GiB │ │ INSERT INTO ddat. │
11. │ 2025-04-30 09:47:47 │ 2.812 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
12. │ 2025-04-30 09:47:44 │ 0.001 │ 4.00 MiB │ │ select 1 │
13. │ 2025-04-30 09:47:44 │ 0.032 │ 6.17 MiB │ │ select currentUser() │
14. │ 2025-04-30 09:47:44 │ 2.176 │ 4.95 MiB │ │ /** CREATEDATE: 202 │
15. │ 2025-04-30 09:47:42 │ 2.379 │ 4.95 MiB │ │ /** CREATEDATE: 202 │
16. │ 2025-04-30 09:47:42 │ 18.447 │ 3.57 GiB │ │ INSERT INTO ddat. │
17. │ 2025-04-30 09:47:41 │ 2.746 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
18. │ 2025-04-30 09:47:39 │ 17.679 │ 3.57 GiB │ │ INSERT INTO ddat. │
19. │ 2025-04-30 09:47:38 │ 2.312 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
20. │ 2025-04-30 09:47:36 │ 2.401 │ 4.95 MiB │ │ /** CREATEDATE: 202 │
21. │ 2025-04-30 09:47:34 │ 15.304 │ 3.58 GiB │ │ INSERT INTO ddat. │
22. │ 2025-04-30 09:47:32 │ 0.573 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
23. │ 2025-04-30 09:47:32 │ 17.101 │ 2.07 GiB │ │ INSERT INTO ddat. │
24. │ 2025-04-30 09:47:32 │ 14.863 │ 2.07 GiB │ │ INSERT INTO ddat. │
25. │ 2025-04-30 09:47:30 │ 0.426 │ 4.95 MiB │ │ /** CREATEDATE: 202 │
26. │ 2025-04-30 09:47:29 │ 0.024 │ 6.17 MiB │ │ select currentUser() │
27. │ 2025-04-30 09:47:29 │ 16.91 │ 2.07 GiB │ │ INSERT INTO ddat. │
28. │ 2025-04-30 09:47:29 │ 0.001 │ 4.00 MiB │ │ select 1 │
29. │ 2025-04-30 09:47:28 │ 0.495 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
30. │ 2025-04-30 09:47:26 │ 0.35 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
31. │ 2025-04-30 09:47:25 │ 0.021 │ 4.00 MiB │ │ select 1 │
32. │ 2025-04-30 09:47:25 │ 0.093 │ 6.17 MiB │ │ select currentUser() │
33. │ 2025-04-30 09:47:25 │ 14.595 │ 2.07 GiB │ │ INSERT INTO ddat. │
34. │ 2025-04-30 09:47:23 │ 0.379 │ 4.95 MiB │ │ /** CREATEDATE: 202 │
35. │ 2025-04-30 09:47:23 │ 14.5 │ 2.08 GiB │ │ INSERT INTO ddat. │
36. │ 2025-04-30 09:47:21 │ 0.255 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
37. │ 2025-04-30 09:47:19 │ 0.293 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
38. │ 2025-04-30 09:47:18 │ 12.243 │ 2.08 GiB │ │ INSERT INTO ddat. │
39. │ 2025-04-30 09:47:18 │ 13.416 │ 2.07 GiB │ │ INSERT INTO ddat. │
40. │ 2025-04-30 09:47:17 │ 0.222 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
41. │ 2025-04-30 09:47:17 │ 0.1 │ 6.17 MiB │ │ select currentUser() │
42. │ 2025-04-30 09:47:17 │ 0.003 │ 4.00 MiB │ │ select 1 │
43. │ 2025-04-30 09:47:15 │ 0.39 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
44. │ 2025-04-30 09:47:14 │ 0.026 │ 6.17 MiB │ │ select currentUser() │
45. │ 2025-04-30 09:47:14 │ 0.001 │ 4.00 MiB │ │ select 1 │
46. │ 2025-04-30 09:47:13 │ 0.279 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
47. │ 2025-04-30 09:47:12 │ 0.002 │ 4.00 MiB │ │ select 1 │
48. │ 2025-04-30 09:47:12 │ 0.049 │ 6.17 MiB │ │ select currentUser() │
49. │ 2025-04-30 09:47:10 │ 0.229 │ 3.98 MiB │ │ /** CREATEDATE: 202 │
50. │ 2025-04-30 09:47:10 │ 0.001 │ 4.00 MiB │ │ select 1 │
└─────────────────────┴──────────────────┴──────────┴─────────┴──────────────────────┘
50 rows in set. Elapsed: 0.045 sec. Processed 146.12 thousand rows, 47.35 MB (3.21 million rows/s., 1.04 GB/s.)
Peak memory usage: 35.01 MiB.当然，搭配负载均衡食用效果更佳哦。
1.4 能力提升
到这里，可能脑袋里还是有些模糊的。 为什么两个参数可以让本应该内存溢出的任务正常执行了？看上面的介绍说是超过的在外部（磁盘）上聚合和排序，那么对数据库来说有什么影响呢，以及这种外部排序和内存有什么关系。
在 ClickHouse 里，大多数聚合和排序操作默认是在内存中完成的。这种方式效率高，处理速度快，但内存是有限资源，如果中间数据特别大（比如分组过多、维度太高），就会出现内存不足，甚至导致查询失败或节点 OOM 崩溃。这时候，max_bytes_before_external_group_by 和 max_bytes_before_external_sort 就起到了“兜底保护机制”的作用：
为什么这些参数能让任务“正常执行”：
当中间数据超过设定的内存阈值时，ClickHouse 会自动将部分中间结果写入临时磁盘文件；
聚合或排序继续进行，但过程被拆分成了“先写磁盘、后合并”的方式；
这样就不用一次性把所有数据都加载进内存，而是用磁盘“做缓冲”，防止内存溢出。
对数据库的影响
1. 性能下降（不可避免）：
访问内存的速度远远快于磁盘，外部聚合/排序的效率要比纯内存操作慢很多，尤其在磁盘 IO 性能不佳的情况下；
2. 更稳定、更可控：
虽然慢了一些，但不会因为“内存炸了”而中断查询，提升了稳定性，适合大数据量的复杂查询；
3. 资源利用更合理：
可以把服务器配置得比较保守，例如给 ClickHouse 限一个 一半内存的查询阈值，让大任务自动用磁盘缓冲。
本质上，这两个参数是内存使用的上限控制器，它们把查询执行逻辑从“内存优先”变成了“内存+磁盘混合”，一旦超过设定值，就切换到“外部处理模式”。这两个参数不是提升性能的工具，而是提升稳定性和容错能力的工具。合理设置它们能让 ClickHouse 在大数据量场景下“宁愿慢一点，也不挂掉”。
三、常见问题
1. Too many parts(304). Merges are processing significantly slower than inserts
出现异常的原因是因为MergeTree的merge的速度跟不上目录生成的速度, 数据目录越来越多就会抛出这个异常, 所以一般情况下遇到这个异常，降低一下插入频次就ok了，单纯调整background_pool_size的大小是治标不治本的
官方文档的上每秒不超过1个插入请求，更准确的说是每秒不超过1个数据目录。
background_pool_size
后台线程池的大小，merge 线程就是在该线程池中执行，当然该线程池不仅仅是给 merge 线程用的，默认值 16，推荐 32 提升 merge 的速度 (CPU 允许的前提下)。
2. DB::NetException: Connection reset by peer, while reading from socket xxx
clickhouse-server进程挂掉，导致clickhouse-server内存使用量太高被OS KILL掉
上面推荐的内存参数强烈推荐全部加上，max_memory_usage_for_all_queries该参数没有正确设置是导致该case触发的主要原因。
3. Memory limit (for query) exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888 bytes), maximum: 9.31 GiB
该异常很直接，就是我们限制了SQL的查询内存(max_memory_usage)使用的上线，当内存使用量大于该值的时候，查询被强制KILL。单个SQL在单台机器最大内存使用量，该值可以设置的比较大，这样可以提升集群查询的上限。
附录
文章推荐：
Clickhouse专业避坑指南&最佳实践_clickhouse频繁写入-CSDN博客
clickhouse聚合之内存不足怎么办？那就提升聚合性能_memory limit (total) exceeded: would use 3.64 gib -CSDN博客
Clickhouse查询优化问题记录_clickhouse memory limit (total) exceeded-CSDN博客