ClickHouse系列（九）：慢查询、内存 OOM 与稳定性治理

系列定位：性能与稳定性 —— 解决线上慢查询、Merge 炸内存等核心痛点

ClickHouse 的查询速度令人印象深刻，但在生产环境中，慢查询和内存溢出（OOM）是最常见的两类稳定性杀手。本篇将从诊断工具、内存模型、治理手段三个维度，系统性地讲解如何让 ClickHouse 集群长期稳定运行。

一、ClickHouse 的慢查询体系

与 MySQL 的slow_query_log不同，ClickHouse 没有独立的慢查询日志文件，而是将所有查询信息统一写入系统表system.query_log。这张表本身就是一张 MergeTree 表，支持全部 SQL 分析能力。

1.1 核心配置

<!-- config.xml 或 users.xml --><query_log><database>system</database><table>query_log</table><flush_interval_milliseconds>7500</flush_interval_milliseconds></query_log>

用户级别可以控制日志行为：

SETlog_queries=1;-- 开启查询日志（默认开启）SETlog_queries_min_type='QUERY_FINISH';-- 只记录完成的查询

1.2 query_log 的生命周期

每条查询在query_log中最多产生 4 条记录，通过type字段区分：

实际诊断时，我们主要关注QueryFinish和ExceptionWhileProcessing。

二、system.query_log 的正确使用

2.1 找出 Top 慢查询

SELECTquery_duration_ms,read_rows,read_bytes,memory_usage,queryFROMsystem.query_logWHEREtype='QueryFinish'ANDevent_date=today()ANDquery_duration_ms>5000ORDERBYquery_duration_msDESCLIMIT20;

2.2 按用户 / 来源聚合

SELECTuser,client_hostname,count()ASquery_count,avg(query_duration_ms)ASavg_ms,max(memory_usage)ASmax_memFROMsystem.query_logWHEREtype='QueryFinish'ANDevent_date>=today()-7GROUPBYuser,client_hostnameORDERBYavg_msDESC;

2.3 捕获异常查询

SELECTevent_time,exception_code,exception,queryFROMsystem.query_logWHEREtype='ExceptionWhileProcessing'ANDevent_date=today()ORDERBYevent_timeDESCLIMIT10;

三、normalizeQuery 的聚合分析

生产环境中，同一类查询因参数不同会产生大量不同的 SQL 文本。normalizeQuery()函数将参数替换为占位符，使我们能够按查询模式聚合分析。

SELECTnormalizeQuery(query)ASnormalized,count()AScnt,avg(query_duration_ms)ASavg_ms,avg(read_rows)ASavg_rows,avg(memory_usage)ASavg_memFROMsystem.query_logWHEREtype='QueryFinish'ANDevent_date=today()GROUPBYnormalizedORDERBYcnt*avg_msDESCLIMIT20;

这条 SQL 的排序逻辑是cnt * avg_ms，即"总耗时贡献"最大的查询模式排在前面。这比单纯看最慢查询更有实际意义——一条 100ms 的查询如果每秒执行 1000 次，其影响远大于一条 10s 的偶发查询。

normalizeQuery的典型输出：

-- 原始 SQL SELECT * FROM events WHERE user_id = 12345 AND event_date = '2024-01-15' -- normalize 后 SELECT * FROM events WHERE user_id = ? AND event_date = ?

四、read_rows / read_bytes 的诊断意义

query_log中的read_rows和read_bytes是判断查询是否合理的关键指标。

4.1 诊断矩阵

4.2 实际案例

-- 查看某类查询的扫描效率SELECTnormalizeQuery(query)ASnq,avg(read_rows)ASavg_read,avg(result_rows)ASavg_result,avg(read_rows)/greatest(avg(result_rows),1)ASamplificationFROMsystem.query_logWHEREtype='QueryFinish'ANDevent_date=today()GROUPBYnqHAVINGamplification>1000ORDERBYamplificationDESCLIMIT10;

amplification（放大系数）超过 1000 意味着每返回 1 行结果需要扫描超过 1000 行原始数据，这类查询是优化的首要目标。

五、Merge / Aggregation 的内存模型

5.1 Merge 的内存消耗

ClickHouse 的后台 Merge 进程会将多个 data part 合并为更大的 part。Merge 过程中需要在内存中维护排序状态，内存消耗与以下因素正相关：

• 参与合并的 part 数量和大小
• 排序键的宽度（列数和类型）
• 是否涉及AggregatingMergeTree的聚合状态

关键配置：

-- 单次 Merge 的最大内存（默认无限制，建议设置）SETmax_bytes_to_merge_at_max_space_in_pool=161061273600;-- 150GB-- 后台 Merge 线程数SETbackground_pool_size=16;

5.2 Aggregation 的内存模型

GROUP BY是 ClickHouse 中最大的内存消费者。其内存模型分为两个阶段：

1. HashMap 构建阶段：将所有分组键和聚合状态存入内存 HashMap
2. 溢出阶段（如果启用）：当内存超过阈值时，将部分数据溢出到磁盘

-- 单次查询的内存上限SETmax_memory_usage=10000000000;-- 10GB-- GROUP BY 超过内存限制时的行为SETmax_memory_usage_for_user=20000000000;-- 用户级 20GB-- 允许 GROUP BY 溢出到磁盘（牺牲性能换稳定性）SETmax_bytes_before_external_group_by=5000000000;-- 5GB 后溢出SETmax_bytes_before_external_sort=5000000000;

5.3 内存消耗估算

对于GROUP BY查询，内存消耗的粗略公式：

内存 ≈ 分组基数 × (所有 GROUP BY 列的平均宽度 + 所有聚合状态的宽度)

例如：GROUP BY user_id (UInt64), event_type (String avg 20B)，聚合count(), sum(amount)：

每行 ≈ 8 + 20 + 8 + 8 = 44 字节 1 亿个不同的 user_id × 44B ≈ 4.1 GB

六、MEMORY_LIMIT_EXCEEDED 的典型场景与治理

6.1 典型触发场景

6.2 治理策略

策略一：设置多层内存防线

<!-- users.xml --><profiles><default><!-- 单查询上限 --><max_memory_usage>10000000000</max_memory_usage><!-- 单用户上限 --><max_memory_usage_for_user>30000000000</max_memory_usage_for_user><!-- 服务器总上限比例 --><max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio></default></profiles>

策略二：启用溢出到磁盘

SETmax_bytes_before_external_group_by=5000000000;SETmax_bytes_before_external_sort=5000000000;

这会在内存达到阈值时将中间结果写入临时目录，查询不会失败但速度会下降。

策略三：查询改写

-- ❌ 高基数 GROUP BYSELECTuser_id,count()FROMeventsGROUPBYuser_id;-- ✅ 使用近似算法SELECTuniqHLL12(user_id)FROMevents;-- ❌ 大表 JOINSELECT*FROMaJOINbONa.id=b.id;-- ✅ 使用 IN 替代（如果只需要过滤）SELECT*FROMaWHEREidIN(SELECTidFROMbWHERE...);

策略四：限制并发

<profiles><default><max_concurrent_queries_for_user>10</max_concurrent_queries_for_user></default></profiles>

6.3 监控告警

建议对以下指标建立告警：

-- 最近 1 小时 OOM 次数SELECTcount()FROMsystem.query_logWHEREtype='ExceptionWhileProcessing'ANDexception_code=241-- MEMORY_LIMIT_EXCEEDEDANDevent_time>now()-INTERVAL1HOUR;

-- 当前内存使用率SELECTformatReadableSize(value)AScurrent_memoryFROMsystem.metricsWHEREmetric='MemoryTracking';

小结

ClickHouse 的稳定性治理核心在于：可观测 → 可诊断 → 可防御。

1. 用system.query_log+normalizeQuery()建立查询画像
2. 用read_rows/memory_usage定位问题查询
3. 用内存限制 + 溢出机制 + 查询改写构建多层防线
4. 用监控告警实现事前预防而非事后救火

下一篇我们将进入系列的最后一篇，聚焦生产架构与最佳实践总结。