ClickHouse 内存超限错误排查与调优实战

它解决什么问题 / 适用场景

ClickHouse 的 Memory limit exceeded 错误是生产环境中最高频的故障之一。无论你是运维人员、数据工程师还是分析师，只要执行复杂聚合查询（如高基数 GROUP BY、大 JOIN），或者服务器并发查询过多，都可能触发这个错误。本文提供一套系统化的诊断和修复方法，涵盖从临时快速修复到深度配置优化的完整链路。

典型场景：

• 生产环境 ClickHouse 频繁报错 Memory limit (for query) exceeded，导致报表任务中断。
• 使用 LLM（如 GPT-4）生成的自然语言查询在 ClickHouse 上执行时，因查询复杂度高而频繁 OOM。
• 需要为不同用户或查询设置差异化的内存配额，防止单个查询耗尽服务器资源。

核心配置 / 参数说明

ClickHouse 的内存限制是分层模型：Server → User → Query。实际生效的是三者中的最小值。以下三个参数是调优的核心：

关键理解：假设你设置 max_memory_usage = 20GB，但用户限制是 15GB，服务器限制是 100GB，那么实际查询最多只能用 15GB。排查时务必检查所有层级。

常见报错与排查

错误 1：查询级别内存超限

报错信息：

DB::Exception: Memory limit (for query) exceeded: would use 10.50 GiB (attempt to allocate chunk of 4194304 bytes), maximum: 10.00 GiB.

根因：单次查询尝试分配超过 max_memory_usage（默认 10GB）的内存。常见于高基数 GROUP BY 或大 JOIN。

解决方案（按优先级排序）：

1. 临时增加限制（快速修复）：

   SQL
   SELECT ... FROM ... SETTINGS max_memory_usage = 20000000000;  -- 20GB
   

2. 优化查询：检查是否缺少 WHERE 过滤条件，或 GROUP BY 的基数是否过高（例如百万级唯一值）。添加更精确的过滤条件可大幅减少内存占用。
3. 启用外部聚合：当内存超过阈值时，将数据溢出到磁盘，避免 OOM：

   SQL
   SET max_bytes_before_external_group_by = 5000000000;  -- 5GB 阈值
   

   注意：外部聚合会显著降低查询性能（可能慢 10-100 倍），仅作为兜底方案。

错误 2：服务器级别内存超限

报错信息：

DB::Exception: Memory limit (total) exceeded: would use 105.00 GiB (attempt to allocate chunk of 1048576 bytes), maximum: 100.00 GiB.

根因：所有查询的总内存消耗超过了 max_server_memory_usage。

解决方案：

1. 增加服务器限制：编辑 config.xml 并重启 ClickHouse：

   XML
   <max_server_memory_usage>120000000000</max_server_memory_usage>  <!-- 120GB -->
   

2. 减少并发查询：使用 max_concurrent_queries 限制同时运行的查询数：

   SQL
   SET max_concurrent_queries = 10;
   

3. 监控内存泄漏：执行以下查询，观察内存使用趋势：

   SQL
   SELECT metric, value FROM system.metrics WHERE metric LIKE '%Memory%';
   

错误 3：用户级别内存超限

报错信息：

DB::Exception: Memory limit (for user) exceeded: would use 25.00 GiB (attempt to allocate chunk of 8388608 bytes), maximum: 20.00 GiB.

根因：指定用户的所有查询总内存超过 max_memory_usage_for_user。

解决方案：

1. 增加用户限制（需要管理员权限）：

   SQL
   ALTER USER analyst SETTINGS max_memory_usage = 30000000000;  -- 30GB
   

2. 排查高内存查询：从 system.query_log 中找出该用户的内存消耗大户：

   SQL
   SELECT query, memory_usage, query_duration_ms 
   FROM system.query_log 
   WHERE user = 'analyst' AND type = 'QueryFinish' 
   ORDER BY memory_usage DESC 
   LIMIT 10;
   

错误 4：外部聚合后查询极慢

现象：启用 max_bytes_before_external_group_by 后，查询耗时从几秒变成几分钟。

根因：大量数据被写入磁盘，磁盘 I/O 成为瓶颈。

解决方案：

1. 提高外部聚合阈值：让更多数据在内存中处理，减少磁盘溢出：

   SQL
   SET max_bytes_before_external_group_by = 10000000000;  -- 10GB
   

2. 优化数据分布：确保数据按 GROUP BY 键进行分区或排序，减少需要聚合的数据量。
3. 使用物化视图：预先聚合高频查询的结果，避免实时聚合大表。

生产环境实践与注意事项

1. 临时 vs 永久修改

• 临时修改：通过 SETTINGS 或 SET 命令修改，仅对当前会话有效。适合快速验证或临时修复。
• 永久修改：
  • 用户级别：ALTER USER <username> SETTINGS max_memory_usage = <value>;
  • 服务器级别：修改 config.xml 并重启服务。

2. 外部聚合的副作用

启用外部聚合虽然能避免 OOM，但性能代价巨大。建议作为兜底方案，而非默认配置。优先通过优化查询（添加过滤条件、使用物化视图）来减少内存需求。

3. 权限控制

• ALTER USER 需要管理员权限，普通用户不应被授予此权限。
• 如果通过自动化工具（如 MCP 服务）暴露调优接口，必须进行身份验证和授权，防止未授权修改。

4. 并发冲突

多个调优操作同时修改同一用户的设置可能导致状态不一致。建议使用分布式锁或队列来序列化修改操作。

5. 监控与告警

不应仅依赖被动修复。建立主动监控，当内存使用率超过阈值（如 80%）时自动告警：

SQL
-- 检查当前内存使用率
SELECT 
    value / (SELECT value FROM system.metrics WHERE metric = 'MaxServerMemoryUsage') * 100 AS memory_usage_percent
FROM system.metrics 
WHERE metric = 'MemoryTracking';

常见问题 FAQ

Q: 为什么我的查询在测试环境正常，但在生产环境就报内存超限？

A: 通常是因为生产环境的数据量远大于测试环境。即使查询逻辑相同，处理更多数据需要更多内存。此外，生产环境可能有多个查询并发执行，共享服务器内存。建议：

1. 在测试环境使用与生产环境规模相近的数据集进行测试。
2. 使用 system.query_log 分析生产环境查询的实际内存消耗。
3. 为生产环境设置更合理的 max_memory_usage 和 max_concurrent_queries。

Q: 我设置了 max_memory_usage 为 20GB，但查询仍然失败，显示只用了 10GB 就超限了，为什么？

A: 这可能是由于用户级别或服务器级别的限制更严格。ClickHouse 的内存限制是分层的，实际生效的是最小值。例如：

• 服务器限制：100GB
• 用户限制：15GB
• 查询限制：20GB 那么实际最大可用内存是 15GB。请检查 max_memory_usage_for_user 和 max_server_memory_usage 的设置。

Q: 启用外部聚合后，查询变慢了很多，有没有办法在避免 OOM 的同时保持性能？

A: 这是一个典型的权衡问题。以下策略可以尝试：

1. 优化查询：添加更精确的 WHERE 条件，减少处理的数据量。
2. 使用物化视图：创建物化视图来预先计算常见的聚合结果。
3. 调整外部聚合阈值：增加 max_bytes_before_external_group_by 的值，让更多数据在内存中处理，减少磁盘 I/O。
4. 增加服务器内存：如果业务增长迅速，增加物理内存是最根本的解决方案。
5. 使用分布式查询：如果 ClickHouse 是集群部署，确保查询被均匀分发到各个节点，避免单节点内存压力过大。