ClickHouse 日常运维命令总结
一、性能监控与SQL优化
1. 定位高CPU消耗SQL
WITH (SELECT sum(ProfileEvents['OSCPUVirtualTimeMicroseconds']) FROM clusterAllReplicas('cluster_name', system.query_log) WHERE event_time > now() - INTERVAL 10 MINUTE) AS total_cpu
SELECT normalized_query_hash,any(query) AS sample_query,sum(ProfileEvents['OSCPUVirtualTimeMicroseconds']) AS cpu_microseconds,round(cpu_microseconds / total_cpu * 100, 2) AS cpu_percent
FROM clusterAllReplicas('cluster_name', system.query_log)
WHERE event_time > now() - INTERVAL 10 MINUTE
GROUP BY normalized_query_hash
ORDER BY cpu_microseconds DESC
LIMIT 10
SETTINGS max_threads=2;
2. 分析执行计划
EXPLAIN indexes=1
SELECT * FROM table WHERE date = today();
二、进程与任务管理
1. 终止长时间查询
-- 查找耗时查询
SELECT query_id, elapsed, query
FROM system.processes
WHERE elapsed > 300; -- 超过300秒-- 终止指定查询
KILL QUERY WHERE query_id = 'query_id';
2. 终止异步Mutation
-- 定位Mutation任务
SELECT database, table, mutation_id
FROM system.mutations
WHERE is_done=0;-- 终止Mutation
KILL MUTATION WHERE database='db' AND table='tbl' AND mutation_id='mutation_id';
三、资源与存储管理
1. 账号资源限制
-- 限制最大线程数
CREATE SETTINGS PROFILE max_threads_profile
SETTINGS max_threads = 10 TO ALL;-- 限制单查询超时
CREATE SETTINGS PROFILE timeout_profile
SETTINGS max_execution_time = 600 TO user_A;
2. 查看表存储详情
SELECT database,table,formatReadableSize(sum(bytes)) AS size,formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,sum(data_compressed_bytes) / sum(data_uncompressed_bytes) AS compress_ratio
FROM system.parts
WHERE active
GROUP BY database, table;
四、集群运维操作
1. 启停Merge操作
-- 暂停Merge（紧急维护）
ALTER TABLE tbl ON CLUSTER cluster MODIFY SETTING max_replicated_merges_in_queue=0,max_bytes_to_merge_at_max_space_in_pool=0;-- 恢复Merge（默认值参考官方文档）
ALTER TABLE tbl ON CLUSTER cluster RESET SETTING max_replicated_merges_in_queue,max_bytes_to_merge_at_max_space_in_pool;
2. 集群间数据迁移
INSERT INTO target_db.table
SELECT * FROM remote('source_host:port', 'source_db', 'table', 'user', 'pass');
五、备份与恢复
1. OSS备份与恢复
-- 全表备份
BACKUP TABLE db.table TO S3('https://oss-endpoint', 'access_key', 'secret_key');-- 分区恢复（跳过空表校验）
RESTORE TABLE db.table FROM S3('https://oss-endpoint', 'access_key', 'secret_key')
SETTINGS allow_non_empty_tables=1;
重要提示：备份/恢复仅针对本地表，分布式表需单独重建
六、故障集群修复指南
1. Zookeeper元数据损坏
# 停止所有CH节点
sudo systemctl stop clickhouse-server# 清空元数据缓存（所有节点）
rm -rf /var/lib/clickhouse/metadata/*# 从健康节点复制元数据
scp root@healthy_node:/var/lib/clickhouse/metadata/* /var/lib/clickhouse/metadata/# 重启集群并校验
sudo systemctl start clickhouse-server
2. 副本数据不同步
-- 检查副本状态
SELECT database, table, is_leader, is_readonly
FROM system.replicas
WHERE is_session_expired=1;-- 强制恢复副本
SYSTEM RESTART REPLICA db.table; -- 单表恢复
SYSTEM RESTART REPLICAS; -- 当前节点所有副本
3. 副本丢失后的重建
-- 1. 停止写入（避免新数据干扰）
ALTER TABLE db.table FREEZE;-- 2. 删除损坏副本数据
rm -rf /var/lib/clickhouse/data/db/table/replica_name-- 3. 从健康副本同步
CREATE TABLE db.table_replica AS db.table
ENGINE = ReplicatedMergeTree(...)
SETTINGS clean_deleted_rows_after_merge=0, -- 保留删除标记allow_remote_fs_zero_copy_replication=1;-- 4. 数据校验完成后切换流量
4. 磁盘空间耗尽紧急处理
-- 立即释放未完成Mutation
SYSTEM FLUSH LOGS; -- 强制写入系统日志
SYSTEM STOP MERGES; -- 暂停后台合并-- 清理突变残留文件
ALTER TABLE db.table
DELETE WHERE 1=1 SETTINGS mutations_sync=0; -- 异步删除
七、高级运维技巧
- TTL数据生命周期管理
CREATE TABLE event_log (id UUID,event_time DateTime,data String
) ENGINE = MergeTree()
ORDER BY event_time
TTL event_time + INTERVAL 3 MONTH; -- 自动清理3月前数据
- 跨引擎数据访问
-- 创建MySQL外表
CREATE TABLE mysql_external (id Int32,name String
) ENGINE = MySQL('mysql-host:3306', 'db', 'table', 'user', 'pass');
运维建议：
- 定期监控
system.metrics/system.events - 启用
query_log并保留7天以上日志 - 重大操作前执行
SYSTEM FLUSH LOGS确保日志落盘 - 集群扩容后使用
SYSTEM RELOAD CONFIG动态加载配置
本文命令基于ClickHouse 22.8+版本验证，执行高危操作前务必进行备份！