ClickHouse 中的 CPU 80% 诊断

诊断 ClickHouse 中的 CPU 负载：定位问题查询

SELECT
    elapsed,
    formatReadableSize(memory_usage) AS ram,
    formatReadableSize(read_bytes) AS read_size,
    read_rows,
    query
FROM system.processes
ORDER BY elapsed DESC;

若发现异常查询，可通过 KILL QUERY WHERE query_id = 'xxx'; 命令终止。如需立即同步终止，使用 KILL QUERY WHERE query_id = 'xxx' SYNC;，可快速释放峰值负载，无需等待。

分析 query_log 中已完成的查询

system.query_log 表记录了所有完成查询的完整性能数据：执行时长、CPU 占用、内存消耗及读取数据量。分析前请先运行 SYSTEM FLUSH LOGS;，确保日志数据为最新状态。

以下为获取过去一小时内资源消耗最高的 20 条查询的基准查询语句：

SELECT
    event_time,
    query_duration_ms / 1000 AS duration_sec,
    formatReadableSize(memory_usage) AS ram,
    formatReadableSize(read_bytes) AS read_size,
    read_rows,
    ProfileEvents['UserTimeMicroseconds'] / 1000000 AS user_cpu_sec,
    ProfileEvents['SystemTimeMicroseconds'] / 1000000 AS system_cpu_sec,
    round(user_cpu_sec + system_cpu_sec, 2) AS total_cpu_sec,
    substring(query, 1, 200) AS query_short
FROM system.query_log
WHERE event_date >= today()
  AND event_time >= now() - INTERVAL 1 HOUR
  AND type = 'QueryFinish'
  AND NOT has(tables, 'system.query_log')
ORDER BY duration_sec DESC
LIMIT 20;

通过 type = 'QueryFinish' 筛选仅包含完整执行结果的查询。如需排除异常中断的查询，可添加 AND NOT type = 'ExceptionWhileProcessing'。ProfileEvents 提供详细计数器，如 SelectedMarks（选中标记数）、SelectedParts（选中部分数）和 MergedRows（合并行数）。SelectedMarks 值过高通常表明 WHERE 条件设计不佳，导致扫描过多无效数据。

按不同资源维度排序：

• 慢查询：ORDER BY duration_sec DESC
• 高 CPU：ORDER BY total_cpu_sec DESC
• 高内存：ORDER BY memory_usage DESC
• 高磁盘 I/O：ORDER BY read_bytes DESC

建议缩小分析时间窗口并使用 LIMIT 避免对 MergeTree 表本身造成额外压力。

识别负载来源

面对多个客户端连接时，可按 user、client_hostname 或 initial_user 进行分组统计：

SELECT
    user,
    client_hostname,
    count() AS queries,
    round(sum(query_duration_ms) / 1000, 1) AS total_sec,
    formatReadableSize(sum(read_bytes)) AS total_read
FROM system.query_log
WHERE event_date >= today()
  AND type = 'QueryFinish'
GROUP BY user, client_hostname
ORDER BY total_sec DESC
LIMIT 10;

对于 Airflow、Dagster 等调度工具，可在客户端代码中加入 log_comment 标记：

client.execute(query, settings={'log_comment': 'dag:report/step:aggregate_daily'})

随后按注释标签分析：

SELECT
    log_comment,
    count() AS queries,
    round(sum(query_duration_ms) / 1000, 1) AS total_sec,
    formatReadableSize(sum(read_bytes)) AS total_read
FROM system.query_log
WHERE event_date >= today()
  AND type = 'QueryFinish'
  AND user = 'airflow'
GROUP BY log_comment
ORDER BY total_sec DESC
LIMIT 20;

另一种方式是使用 SQL 注释，如 -- source:sales_dashboard，并通过 query ILIKE '%source:sales_dashboard%' 进行搜索。

检查执行计划

使用 EXPLAIN indexes = 1 可在不实际运行查询的情况下评估索引效率。例如 Granules: 10/1043 表示过滤条件命中 ORDER BY 字段，使扫描数据量压缩至约 1%。

若出现高全表扫描（如 Granules: 95000/98400），说明需优化 WHERE 条件或排序键。手动估算：在 1 亿行的表中，若需匹配 N 行，预计需读取约 N × 8192 行数据。

实际案例分析

一条导致 CPU >80% 的查询：

SELECT
    user_id,
    uniqExact(event_id),
    sum(amount)
FROM events final
WHERE event_date > '2024-01-01'
GROUP BY user_id
ORDER BY sum(amount) DESC
LIMIT 100;

表结构：ReplacingMergeTree，分区键为 toYYYYMM(event_date)，排序键为 (event_id, user_id, event_date)。存在问题如下：

• 查询范围覆盖两年数据，扫描量过大；
• 使用 uniqExact 而非更高效的 uniq，资源开销更高；
• 缺少 PREWHERE 提前过滤；
• 排序键不合理，应调整为 (event_date, user_id, event_id)。

对比优化前后性能指标差异，验证改进效果。

向监控体系演进

基于 normalized_query_hash 实现每日查询分析：

SELECT
    toDate(event_time) AS day,
    normalized_query_hash,
    count() AS executions,
    round(avg(query_duration_ms) / 1000, 2) AS avg_sec,
    round(max(query_duration_ms) / 1000, 2) AS max_sec,
    formatReadableSize(sum(read_bytes)) AS total_read,
    formatReadableSize(max(memory_usage)) AS peak_ram,
    substring(any(query), 1, 150) AS example
FROM system.query_log
WHERE event_date >= today() - 7
  AND type = 'QueryFinish'
GROUP BY day, normalized_query_hash
ORDER BY day DESC, sum(query_duration_ms) DESC
LIMIT 30;

建立异常检测告警机制，实现主动运维。

诊断检查清单

• SYSTEM FLUSH LOGS
• 检查 system.processes —— 当前活跃查询
• 审视 system.query_log —— 资源消耗大户
• 执行 EXPLAIN indexes = 1 —— 查看执行计划
• 验证 WHERE 条件与 ORDER BY 键合理性
• 优化 uniqExact、JOIN 和日期范围逻辑
• 应用修复后，对比优化前后性能数据

核心要点

• 利用 system.processes 快速终止异常查询。
• query_log 搭配 ProfileEvents 可全面掌握 CPU、内存与磁盘使用情况。
• 在多租户环境中，log_comment 是追踪来源的关键手段。
• EXPLAIN indexes = 1 能揭示排序键设计缺陷。
• 从临时排查转向基于查询哈希的日常性能监控，提升系统稳定性。

— Editorial Team