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>

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

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

1.2 query_log 的生命周期

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

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

二、system.query_log 的正确使用

2.1 找出 Top 慢查询

SELECT
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date = today()
  AND query_duration_ms > 5000
ORDER BY query_duration_ms DESC
LIMIT 20;

2.2 按用户 / 来源聚合

SELECT
    user,
    client_hostname,
    count() AS query_count,
    avg(query_duration_ms) AS avg_ms,
    max(memory_usage) AS max_mem
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today() - 7
GROUP BY user, client_hostname
ORDER BY avg_ms DESC;

2.3 捕获异常查询

SELECT
    event_time,
    exception_code,
    exception,
    query
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND event_date = today()
ORDER BY event_time DESC
LIMIT 10;

三、normalizeQuery 的聚合分析

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

SELECT
    normalizeQuery(query) AS normalized,
    count() AS cnt,
    avg(query_duration_ms) AS avg_ms,
    avg(read_rows) AS avg_rows,
    avg(memory_usage) AS avg_mem
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date = today()
GROUP BY normalized
ORDER BY cnt * avg_ms DESC
LIMIT 20;

这条 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 实际案例

-- 查看某类查询的扫描效率
SELECT
    normalizeQuery(query) AS nq,
    avg(read_rows) AS avg_read,
    avg(result_rows) AS avg_result,
    avg(read_rows) / greatest(avg(result_rows), 1) AS amplification
FROM system.query_log
WHERE type = 'QueryFinish' AND event_date = today()
GROUP BY nq
HAVING amplification > 1000
ORDER BY amplification DESC
LIMIT 10;

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

五、Merge / Aggregation 的内存模型

5.1 Merge 的内存消耗

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

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

关键配置：

-- 单次 Merge 的最大内存（默认无限制，建议设置）
SET max_bytes_to_merge_at_max_space_in_pool = 161061273600;  -- 150GB

-- 后台 Merge 线程数
SET background_pool_size = 16;

5.2 Aggregation 的内存模型

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

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

-- 单次查询的内存上限
SET max_memory_usage = 10000000000;  -- 10GB

-- GROUP BY 超过内存限制时的行为
SET max_memory_usage_for_user = 20000000000;  -- 用户级 20GB

-- 允许 GROUP BY 溢出到磁盘（牺牲性能换稳定性）
SET max_bytes_before_external_group_by = 5000000000;  -- 5GB 后溢出
SET max_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>

策略二：启用溢出到磁盘

SET max_bytes_before_external_group_by = 5000000000;
SET max_bytes_before_external_sort = 5000000000;

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

策略三：查询改写

-- ❌ 高基数 GROUP BY
SELECT user_id, count() FROM events GROUP BY user_id;

-- ✅ 使用近似算法
SELECT uniqHLL12(user_id) FROM events;

-- ❌ 大表 JOIN
SELECT * FROM a JOIN b ON a.id = b.id;

-- ✅ 使用 IN 替代（如果只需要过滤）
SELECT * FROM a WHERE id IN (SELECT id FROM b WHERE ...);

策略四：限制并发

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

6.3 监控告警

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

-- 最近 1 小时 OOM 次数
SELECT count()
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND exception_code = 241  -- MEMORY_LIMIT_EXCEEDED
  AND event_time > now() - INTERVAL 1 HOUR;

-- 当前内存使用率
SELECT
    formatReadableSize(value) AS current_memory
FROM system.metrics
WHERE metric = 'MemoryTracking';

小结

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

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

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