ClickHouse后台合并周期性波动及CPU负载异常问题咨询

疑问解答 #

1. 每月1日合并操作突增的原因 #

你设置的系统日志表TTL为event_time + toIntervalMonth(3)，同时表按toYYYYMM(event_date)按月分区。当每月1日到来时，恰好有一批满足TTL条件的旧数据（比如12月1日时，9月1日及之前的event_time数据）到期，ClickHouse会触发后台合并操作清理这些过期数据。如果是近期才修改的TTL规则，之前积累的旧数据会在对应月份的1日集中进入清理周期，直接导致合并次数突然上升。

2. 每4小时合并周期性增加的原因 #

ClickHouse默认通过merge_scheduling_interval参数控制合并任务的调度间隔，该参数默认值为14400秒（4小时）。每隔4小时，ClickHouse会批量扫描并调度待合并的数据片段，而query_log/query_thread_log这类系统日志表持续写入会生成大量小片段，集中调度时就会触发批量合并，进而导致CPU负载周期性升高。

3. 合并次数逐月下降的原因 #

由于你修改了TTL为3个月，旧数据会逐月被清理（10月清理7月数据、11月清理8月数据、12月清理9月数据），积累的历史数据片段逐渐减少，需要合并的任务量也随之降低。同时，随着旧数据被清理，系统日志表的整体数据规模缩小，新写入产生的小片段合并压力也逐步减轻，因此合并次数整体呈下降趋势。

解决方案 #

针对CPU负载异常问题，可从以下维度优化：

1. 调整系统日志表的分区与TTL策略 #

• 将分区键从toYYYYMM(event_date)改为toYYYYMMDD(event_date)按天分区，让过期数据按天粒度删除，避免每月集中合并带来的CPU峰值。
• 若业务允许，可适当缩短TTL周期（比如改为2个月），减少需要维护的历史数据量，具体需结合日志留存需求调整。

2. 优化合并调度与资源控制参数 #

针对2核CPU的服务器，调整ClickHouse配置文件（config.xml或users.xml）中的参数：

• max_merge_threads: 设置为1，限制合并任务使用的线程数，避免占用过多CPU资源影响业务查询。
• merge_scheduling_interval: 调整为21600（6小时）或更长，分散合并调度频率，降低周期性CPU峰值。
• merge_max_block_size: 减小该值（比如改为1048576），降低单次合并处理的数据量，减少CPU消耗。
• merge_with_ttl_timeout: 设置合理值（比如86400，1天），让TTL合并任务分散执行，避免集中触发。

3. 减少小数据片段的生成 #

修改系统日志表的设置，降低频繁合并的触发概率：

ALTER TABLE system.query_log MODIFY SETTINGS 
    min_rows_for_wide_part = 100000,
    min_bytes_for_wide_part = 100000000; -- 100MB

通过设置最小行数/字节数，让小数据片段先合并成较大的片段再写入，减少后续合并的频率。

4. 手动清理过期分区 #

每月定时手动删除已过期的分区，替代自动合并清理，避免CPU突发负载：

ALTER TABLE system.query_log DROP PARTITION '202309'; -- 示例：删除2023年9月的分区

可结合crontab定时执行该命令，实现自动化清理。

5. 升级ClickHouse版本 #

你当前使用的21.11.4.14版本较旧，后续版本（如22.8+）对TTL合并、合并调度逻辑做了大量优化，能更高效地处理过期数据清理，减少CPU占用。建议在业务低峰期进行版本升级。

内容的提问来源于stack exchange，提问作者wzq.615