ClickHouse磁盘告急？别慌，手把手教你清理system日志（query_log/asynchronous_metric_log等）
ClickHouse磁盘空间告急：精准定位与高效清理system日志实战指南
凌晨三点，手机突然响起刺耳的报警声——ClickHouse集群某节点磁盘使用率突破95%。作为值班DBA，这种场景并不陌生。但这次排查发现，占据20GB空间的并非业务数据，而是system库中堆积如山的日志表。本文将还原完整的故障响应流程，从预警信号解析到长效治理方案，手把手带你化解这类"隐形空间杀手"。
1. 危机识别：磁盘告警背后的真相
当监控系统发出磁盘空间预警时，多数运维人员的第一反应是检查业务数据表。但经验丰富的DBA会优先确认一个常被忽视的"空间黑洞"——system系统库。通过以下命令快速定位空间占用Top10的表：
SELECT table, formatReadableSize(sum(bytes_on_disk)) AS disk_size, count() AS parts_count FROM system.parts WHERE database = 'system' GROUP BY table ORDER BY sum(bytes_on_disk) DESC LIMIT 10;典型输出示例显示日志表的空间消耗情况：
| table | disk_size | parts_count |
|---|---|---|
| query_log | 12.45 GiB | 24 |
| asynchronous_metric_log | 5.67 GiB | 8 |
| part_log | 1.23 GiB | 15 |
关键诊断点：当发现query_log、asynchronous_metric_log等日志表合计占用空间超过业务数据时，说明系统已长期未执行日志清理。这些表默认配置为永久保存，主要用于：
- 查询性能分析（query_log）
- 系统指标监控（asynchronous_metric_log）
- 分区操作追踪（part_log）
- 会话管理审计（session_log）
注意：在紧急清理前，建议先备份关键日志。可通过
SELECT * FROM system.query_log WHERE event_time > now() - 30导出近期重要记录。
2. 紧急处置：双管齐下的清理策略
2.1 SQL命令即时清理
对于已产生的历史日志，最快速的清理方式是执行ALTER DELETE语句。以下是针对各日志表的清理命令模板：
-- 清理30天前的查询日志（按时间条件过滤） ALTER TABLE system.query_log DELETE WHERE event_date < today() - 30; -- 清理指标日志（保留最近两周） ALTER TABLE system.asynchronous_metric_log DELETE WHERE event_date < toDate(now() - interval 14 day); -- 级联清理相关日志表 SET allow_experimental_lightweight_delete = 1; ALTER TABLE system.query_thread_log DELETE WHERE 1;执行优化技巧：
- 添加
SET allow_experimental_lightweight_delete = 1加速删除过程 - 分批次删除避免长时间锁表：
DELETE WHERE event_date BETWEEN '2023-01-01' AND '2023-03-01' - 优先清理体积最大的表，快速释放空间
2.2 配置文件永久优化
通过修改config.xml可从根本上解决问题。以下是关键配置示例：
<!-- 在/etc/clickhouse-server/config.xml中添加或修改 --> <query_log> <database>system</database> <table>query_log</table> <partition_by>toYYYYMM(event_date)</partition_by> <ttl>event_date + INTERVAL 14 DAY DELETE</ttl> <flush_interval_milliseconds>7500</flush_interval_milliseconds> </query_log> <asynchronous_metric_log> <ttl>event_date + INTERVAL 7 DAY DELETE</ttl> </asynchronous_metric_log>配置生效步骤：
- 修改配置文件后执行
sudo systemctl restart clickhouse-server - 验证配置加载：
SELECT * FROM system.merge_tree_settings WHERE name = 'ttl_only_drop_parts' - 监控后台合并进程：
SHOW PROCESSLIST
3. 长效治理：自动化运维体系搭建
3.1 TTL策略深度优化
除了基础的时间维度TTL，还可实现更精细化的留存策略：
-- 根据日志级别设置差异化保留策略 ALTER TABLE system.query_log MODIFY TTL event_date + INTERVAL 30 DAY WHERE type = 'QueryFinish', event_date + INTERVAL 7 DAY WHERE type = 'Exception';3.2 监控体系配置
创建专属的磁盘空间监控看板，关键指标包括：
- 日志表空间占比：
sumIf(bytes_on_disk, database='system') / sum(bytes_on_disk) - TTL执行效率：
SELECT event_time, table, rows_deleted FROM system.ttl_log - 异常增长检测：
rate(clickhouse_metric_log_size[1h]) > 100MB
3.3 日志分级存储方案
对于需要长期保留的审计日志，可采用分层存储架构：
- 热数据：当前节点原始日志（保留7天）
- 温数据：分布式表同步到廉价存储（保留30天）
- 冷数据：定期导出到对象存储（保留1年）
-- 创建分布式日志存储 CREATE TABLE system.dist_query_log AS system.query_log ENGINE = Distributed(cluster_archive, system, query_log);4. 高阶技巧：日志价值最大化实践
在控制存储成本的同时，如何充分发挥日志价值？以下是两个实战案例：
案例一：查询性能分析流水线
# 日志分析脚本示例 from clickhouse_driver import Client client = Client('localhost') query = ''' SELECT query, avg(query_duration_ms) as avg_time, count() as executions FROM system.query_log WHERE type = 'QueryFinish' GROUP BY query ORDER BY avg_time DESC LIMIT 10 ''' slow_queries = client.execute(query)案例二：异常检测自动化告警
-- 建立异常查询监控规则 CREATE MATERIALIZED VIEW system.query_alert ENGINE = MergeTree ORDER BY (event_date, event_time) AS SELECT event_date, event_time, query, query_duration_ms FROM system.query_log WHERE type = 'QueryFinish' AND query_duration_ms > 5000 AND normalizedQueryHash(query) NOT IN ( SELECT hash FROM system.allowed_slow_queries );在实施日志清理方案三个月后，我们的生产集群再未出现因日志堆积导致的磁盘告警。最关键的收获是：与其被动应对空间危机，不如建立预防性的日志治理体系。现在我会在每个新集群部署时，首先检查config.xml中的TTL配置——这个习惯已经帮我避免了至少三次深夜告警。