ClickHouse内存过高致241错误，如何降低或手动释放内存？

问题描述 #

我是ClickHouse新手，集群存有30亿数据，服务器内存为64GB。执行SQL：INSERT INTO a_table SELECT * FROM src_table WHERE create_time >= ? AND create_time <= ?时，随机返回错误码241。已执行以下查询语句查看内存相关指标：

SELECT
*,
formatReadableSize(value) AS b
FROM system.asynchronous_metrics
WHERE metric LIKE '%em%'
ORDER BY b DESC

查询结果：

┌─metric───────────────────────────────────┬─────────────────value─┬─b──────────┐
│ FilesystemLogsPathTotalBytes             │          105150078976 │ 97.93 GiB  │
│ FilesystemMainPathUsedBytes              │          889244708864 │ 828.17 GiB │
│ MemoryVirtual                            │           86271520768 │ 80.35 GiB  │
│ MemoryDataAndStack                       │           81446019072 │ 75.85 GiB  │
│ jemalloc.epoch                           │                  7976 │ 7.79 KiB   │
│ OSMemoryFreePlusCached                   │            7745302528 │ 7.21 GiB   │
│ OSMemoryTotal                            │           66174210048 │ 61.63 GiB  │
│ OSMemoryAvailable                        │            6853541888 │ 6.38 GiB   │
│ FilesystemLogsPathTotalINodes            │               6553600 │ 6.25 MiB   │
│ FilesystemLogsPathAvailableINodes        │               6397780 │ 6.10 MiB   │
│ jemalloc.arenas.all.dirty_purged         │             625232220 │ 596.27 MiB │
│ FilesystemLogsPathAvailableBytes         │           61849862144 │ 57.60 GiB  │
│ jemalloc.mapped                          │           58842886144 │ 54.80 GiB  │
│ jemalloc.resident                        │           58749423616 │ 54.71 GiB  │
│ MemoryResident                           │           58665074688 │ 54.64 GiB  │
│ jemalloc.active                          │           58473361408 │ 54.46 GiB  │
│ jemalloc.allocated                       │           57016602472 │ 53.10 GiB  │
│ jemalloc.arenas.all.muzzy_purged         │             483548433 │ 461.15 MiB │
│ FilesystemLogsPathUsedBytes              │           43300216832 │ 40.33 GiB  │
│ OSMemoryCached                           │            4472168448 │ 4.17 GiB   │
│ MemoryCode                               │             366669824 │ 349.68 MiB │
│ jemalloc.arenas.all.pdirty               │                  3830 │ 3.74 KiB   │
│ OSMemoryFreeWithoutCached                │            3273134080 │ 3.05 GiB   │
│ jemalloc.metadata                        │             262799792 │ 250.63 MiB │
│ MemoryShared                             │             253267968 │ 241.54 MiB │
│ FilesystemMainPathUsedINodes             │                215867 │ 210.81 KiB │
│ FilesystemMainPathTotalBytes             │         3170529116160 │ 2.88 TiB   │
│ FilesystemMainPathAvailableBytes         │         2281284407296 │ 2.07 TiB   │
│ FilesystemMainPathTotalINodes            │             196608000 │ 187.50 MiB │
│ FilesystemMainPathAvailableINodes        │             196392133 │ 187.29 MiB │
│ jemalloc.retained                        │           19604230144 │ 18.26 GiB  │
│ FilesystemLogsPathUsedINodes             │                155820 │ 152.17 KiB │
│ jemalloc.arenas.all.pactive              │              14275723 │ 13.61 MiB  │
│ jemalloc.arenas.all.pmuzzy               │                  1394 │ 1.36 KiB   │
└──────────────────────────────────────────┴───────────────────────┴────────────┘

疑问 #

能否手动释放内存？以及如何降低ClickHouse内存占用？

一、手动释放内存的方法 #

ClickHouse默认使用jemalloc内存分配器，支持以下手动内存回收方式：

• 执行SQL触发jemalloc清理可回收内存：SELECT jemalloc_purge()
• 系统层面清理页缓存（需root权限）：执行echo 3 > /proc/sys/vm/drop_caches，该命令会清理系统页缓存、目录项和inodes缓存，但不会影响ClickHouse进程已分配的活跃内存。

注意：jemalloc_purge()仅能回收jemalloc标记为可清理的闲置内存，无法强制释放正在使用的内存。

二、降低ClickHouse内存占用的措施 #

1. 优化INSERT SELECT语句 #

• 分批插入：用LIMIT拆分数据，比如每次插入100万条，避免一次性加载全量数据到内存。
• 限制查询内存上限：执行语句前添加SET max_memory_usage = 40G;（建议设为总内存的60%-70%），超出内存时自动将数据溢出到磁盘（需确保磁盘有足够空间）。
• 利用分区索引：确认create_time是表的分区键，确保查询仅扫描目标时间分区，避免全表扫描。

2. 调整ClickHouse配置参数 #

修改config.xml或用户级配置文件，调整以下参数：

• max_memory_usage_per_query：单个查询的最大内存使用量，例如设为32G。
• max_memory_usage_for_user：单个用户的总内存使用上限，防止多查询并发占满内存。
• jemalloc.background_thread：设为true，开启jemalloc后台线程自动清理闲置内存。
• memory_profiler_step/memory_profiler_sample_probability：开启内存 profiling，定位内存占用高的查询或表。

3. 表结构优化 #

• 替换高内存占用类型：用UInt32代替String存储ID类数据，用Date/DateTime代替String存储时间。
• 启用数据压缩：确保表使用LZ4或ZSTD等压缩算法，减少内存中存储的数据量（ClickHouse默认开启压缩）。
• 使用物化视图：对频繁执行的时间范围查询，预先将结果存储在物化视图中，避免每次查询都扫描大量原始数据。

4. 集群与资源管理 #

• 均衡节点负载：避免单个节点承担过多查询或写入任务。
• 清理闲置进程：关闭服务器上不必要的服务，释放内存给ClickHouse使用。

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