数据库

ClickHouse 监控与运维

ClickHouse 提供了丰富的系统表和指标用于监控集群健康。本文详解运维查询、磁盘管理、TTL 策略、备份恢复和生产环境的维护最佳实践。

Plume 4 分钟阅读 1988 字

1. 系统监控表

ClickHouse 内置了大量系统表，是运维诊断的主要信息来源。

1.1 核心监控指标

-- 查看表大小和行数
SELECT
    database,
    table,
    formatReadableSize(sum(bytes)) AS total_size,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    sum(rows) AS total_rows,
    count() AS parts_count
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes) DESC;

-- 查看 merge 状态
SELECT
    database,
    table,
    num_parts,
    rows_read,
    rows_written,
    bytes_read_uncompressed,
    bytes_written_uncompressed,
    progress
FROM system.merges;

-- 查看当前执行的查询
SELECT
    query_id,
    user,
    query,
    elapsed,
    read_rows,
    read_bytes,
    memory_usage
FROM system.processes
ORDER BY elapsed DESC;

-- 查看磁盘使用
SELECT
    name,
    path,
    formatReadableSize(free_space) AS free,
    formatReadableSize(total_space) AS total,
    formatReadableSize(keep_free_space) AS keep_free
FROM system.disks;

1.2 查询日志分析

-- 启用查询日志（通常在 users.xml 或 config.xml 中配置）
-- <log_queries>1</log_queries>

-- 分析最近慢查询
SELECT
    query,
    event_time,
    query_duration_ms,
    read_rows,
    read_bytes,
    result_rows,
    memory_usage,
    exception
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
  AND event_time > now() - INTERVAL 1 DAY
ORDER BY query_duration_ms DESC
LIMIT 20;

-- 分析查询频率
SELECT
    normalized_query_hash,
    count() AS query_count,
    avg(query_duration_ms) AS avg_duration,
    max(query_duration_ms) AS max_duration,
    sum(read_rows) AS total_rows_read
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
GROUP BY normalized_query_hash
ORDER BY query_count DESC
LIMIT 20;

2. 磁盘与存储管理

2.1 存储策略

ClickHouse 支持分层存储（hot/cold）：

<!-- /etc/clickhouse-server/config.d/storage.xml -->
<clickhouse>
    <storage_configuration>
        <disks>
            <hot>
                <path>/var/lib/clickhouse/hot/</path>
            </hot>
            <cold>
                <path>/mnt/cold-storage/clickhouse/</path>
            </cold>
        </disks>
        <policies>
            <hot_cold>
                <volumes>
                    <hot>
                        <disk>hot</disk>
                    </hot>
                    <cold>
                        <disk>cold</disk>
                    </cold>
                </volumes>
                <move_factor>0.2</move_factor>
            </hot_cold>
        </policies>
    </storage_configuration>
</clickhouse>

-- 使用分层存储策略
CREATE TABLE events (
    event_time DateTime,
    user_id UInt64
) ENGINE = MergeTree()
ORDER BY (event_time, user_id)
SETTINGS storage_policy = 'hot_cold';

-- 手动移动分区到冷存储
ALTER TABLE events MOVE PARTITION '202401' TO VOLUME 'cold';

2.2 TTL 策略

-- 自动删除旧数据
CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type String
) ENGINE = MergeTree()
ORDER BY (event_time, user_id)
TTL event_time + INTERVAL 3 MONTH;  -- 3 个月后删除

-- TTL 移动到冷存储
CREATE TABLE logs (
    log_time DateTime,
    message String
) ENGINE = MergeTree()
ORDER BY log_time
TTL log_time + INTERVAL 1 MONTH TO VOLUME 'cold',
    log_time + INTERVAL 6 MONTH DELETE;  -- 1 月后移到冷盘，6 月后删除

-- 查看 TTL 状态
SELECT * FROM system.parts WHERE table = 'events' AND delete_ttl_info_min > now();

2.3 Part 管理

-- 查看 part 详情
SELECT
    partition,
    name,
    active,
    rows,
    formatReadableSize(bytes_on_disk) AS size,
    modification_time
FROM system.parts
WHERE table = 'events'
ORDER BY partition, name;

-- 手动优化（强制合并）
OPTIMIZE TABLE events FINAL;

-- 只优化特定分区
OPTIMIZE TABLE events PARTITION '202401' FINAL;

-- 查看合并历史
SELECT
    event_time,
    database,
    table,
    event_type,
    duration_ms,
    rows,
    size_in_bytes
FROM system.part_log
WHERE event_type IN ('MergeParts', 'NewPart')
ORDER BY event_time DESC
LIMIT 20;

3. 备份与恢复

3.1 ClickHouse 备份工具

# 安装 clickhouse-backup
wget https://github.com/Altinity/clickhouse-backup/releases/download/v2.4.0/clickhouse-backup-linux-amd64.tar.gz

# 创建备份
clickhouse-backup create my_backup_2024

# 列出备份
clickhouse-backup list

# 上传到 S3
clickhouse-backup upload my_backup_2024

# 从备份恢复
clickhouse-backup restore my_backup_2024

3.2 配置远程备份

# /etc/clickhouse-backup/config.yml
general:
  remote_storage: s3
  max_file_size: 1073741824

clickhouse:
  username: default
  password: ""
  host: localhost
  port: 9000

s3:
  access_key: AKIAIOSFODNN7EXAMPLE
  secret_key: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
  bucket: clickhouse-backups
  region: us-east-1
  path: backups/

3.3 手动备份方法

# 冻结表（创建硬链接快照）
clickhouse-client --query="ALTER TABLE events FREEZE"

# 备份元数据和配置文件
tar czvf clickhouse-config-backup.tar.gz /etc/clickhouse-server/

# 备份用户数据
tar czvf clickhouse-data-backup.tar.gz /var/lib/clickhouse/shadow/

4. 性能优化监控

4.1 缓存命中分析

-- 查看缓存使用情况
SELECT
    metric,
    value
FROM system.metrics
WHERE metric LIKE '%Cache%';

-- Mark Cache（索引缓存）
-- Uncompressed Cache（解压后数据缓存）
-- OS Page Cache（操作系统缓存）

4.2 连接和线程监控

-- 查看当前连接
SELECT * FROM system.processes WHERE is_initial_query;

-- 查看线程池状态
SELECT
    metric,
    value
FROM system.metrics
WHERE metric LIKE '%Thread%';

-- 查看 HTTP/TCP 连接数
SELECT
    metric,
    value
FROM system.metrics
WHERE metric IN ('TCPConnection', 'HTTPConnection', 'InterserverConnection');

5. 生产运维最佳实践

5.1 日常检查清单

-- 1. 检查集群状态
SELECT * FROM system.clusters WHERE is_local;

-- 2. 检查副本同步状态
SELECT
    table,
    is_leader,
    is_readonly,
    absolute_delay,
    queue_size,
    total_replicas,
    active_replicas
FROM system.replicas
ORDER BY absolute_delay DESC;

-- 3. 检查磁盘空间
SELECT
    name,
    formatReadableSize(free_space) AS free,
    formatReadableSize(total_space) AS total,
    round(free_space / total_space * 100, 2) AS free_pct
FROM system.disks;

-- 4. 检查 ZooKeeper 连接
SELECT * FROM system.zookeeper WHERE path = '/';

5.2 告警设置

# Prometheus 告警规则示例
groups:
  - name: clickhouse
    rules:
      - alert: ClickHouseReplicaLag
        expr: ClickHouseAsyncMetrics_ReplicasMaxAbsoluteDelay > 300
        for: 5m
        annotations:
          summary: "ClickHouse replica lag > 5 minutes"
      
      - alert: ClickHouseDiskSpaceLow
        expr: ClickHouseAsyncMetrics_DiskAvailable / ClickHouseAsyncMetrics_DiskTotal < 0.2
        for: 5m
        annotations:
          summary: "ClickHouse disk space < 20%"
      
      - alert: ClickHouseSlowQueries
        expr: rate(ClickHouseProfileEvents_SlowQuery[5m]) > 10
        annotations:
          summary: "High rate of slow queries"

5.5 生产环境巡检脚本

-- 每日巡检查询：表大小和行数统计
WITH table_stats AS (
    SELECT
        database,
        table,
        sum(rows) AS total_rows,
        formatReadableSize(sum(bytes)) AS total_size,
        count() AS part_count
    FROM system.parts
    WHERE active
    GROUP BY database, table
)
SELECT * FROM table_stats ORDER BY sum(bytes) DESC LIMIT 20;

-- 检查未优化的表（part 数量过多）
SELECT
    database,
    table,
    count() AS active_parts,
    max(modification_time) - min(modification_time) AS age_range
FROM system.parts
WHERE active
GROUP BY database, table
HAVING count() > 100
ORDER BY count() DESC;

-- 检查长期运行的查询
SELECT
    query_id,
    user,
    elapsed,
    read_rows,
    memory_usage,
    query
FROM system.processes
WHERE elapsed > INTERVAL 5 MINUTE
ORDER BY elapsed DESC;

-- 检查 ZooKeeper 压力
SELECT
    path,
    name,
    value_short,
    czxid,
    mzxid
FROM system.zookeeper
WHERE path = '/clickhouse';

定期执行这些巡检脚本可以及早发现潜在问题。建议将巡检结果输出到监控看板或发送到运维告警通道，实现异常情况的主动发现而非被动响应。生产环境中，这类自动化巡检比手工排查效率高出十倍以上。

5.6 连接管理与线程监控

在高并发生产环境中，连接数和线程池的使用情况需要密切关注：

-- 查看当前活跃的查询和连接
SELECT
    user,
    count() AS active_queries,
    sum(read_rows) AS total_read_rows,
    sum(memory_usage) AS total_memory
FROM system.processes
GROUP BY user
ORDER BY active_queries DESC;

-- 查看 HTTP 和 TCP 连接统计
SELECT
    metric,
    value
FROM system.metrics
WHERE metric LIKE '%Connection%'
   OR metric LIKE '%Thread%';

-- 慢查询 Top 10
SELECT
    query,
    query_duration_ms / 1000 AS duration_sec,
    read_rows,
    read_bytes,
    result_rows,
    memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 5000
  AND event_time > now() - INTERVAL 1 DAY
ORDER BY query_duration_ms DESC
LIMIT 10;

连接泄露或线程池耗尽是 ClickHouse 生产环境常见的问题来源。通过上述查询可以快速定位问题连接和异常查询。

5.7 自动化监控脚本

在实际运维中，手动执行监控查询效率低下。建议将以下监控逻辑封装为定时脚本，输出到监控看板或告警系统：

-- 自动化表增长趋势监控
SELECT
    database,
    table,
    formatReadableSize(sum(bytes)) AS size_now,
    formatReadableSize(sum(bytes) - lagInFrame(sum(bytes)) OVER (PARTITION BY database, table ORDER BY event_time)) AS growth_24h,
    round((sum(bytes) - lagInFrame(sum(bytes)) OVER (PARTITION BY database, table ORDER BY event_time)) / lagInFrame(sum(bytes)) OVER (PARTITION BY database, table ORDER BY event_time) * 100, 2) AS growth_pct
FROM system.part_log
WHERE event_type = 'NewPart'
  AND event_time > now() - INTERVAL 48 HOUR
GROUP BY database, table, toStartOfHour(event_time) AS event_time
ORDER BY growth_pct DESC;

-- 自动化慢查询趋势分析
SELECT
    toStartOfHour(event_time) AS hour,
    countIf(query_duration_ms > 5000) AS slow_count,
    avgIf(query_duration_ms, query_duration_ms > 5000) AS avg_slow_ms,
    max(query_duration_ms) AS max_ms
FROM system.query_log
WHERE event_time > now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour;

将监控脚本输出到 Grafana 或 DataDog 等可视化平台可以大幅提升故障发现速度。建议在告警阈值设计上采用动态基线（如过去七天同一时间段的平均值加减两个标准差），避免静态阈值导致的误报或漏报。合理配置告警策略能显著减轻运维团队的响应负担，让工程师专注于真正需要人工介入的故障排查工作。

6. 升级策略

# 1. 备份配置和数据
clickhouse-backup create pre_upgrade

# 2. 检查新版本变更日志
# https://github.com/ClickHouse/ClickHouse/blob/master/CHANGELOG.md

# 3. 升级副本（滚动升级，一次升级一个副本）
# 在 replica2 上操作
systemctl stop clickhouse-server
apt-get install clickhouse-server=新版本
systemctl start clickhouse-server

# 4. 检查副本状态
clickhouse-client --query "SELECT * FROM system.replicas WHERE absolute_delay > 0"

# 5. 确认正常后升级下一个副本

7. 总结

ClickHouse 运维的核心要点：

运维任务关键指标/命令频率
健康检查system.replicas, system.clusters实时
磁盘监控system.disks实时
查询性能system.query_log每日
数据备份clickhouse-backup每日
Part 合并system.parts, system.merges每周
版本升级CHANGELOG每季度
TTL 清理system.parts (delete_ttl)每周

ClickHouse 的运维相对简单，因为大部分维护操作（merge、TTL、副本同步）都是自动进行的。运维工程师的主要职责是监控资源使用、处理异常和规划容量扩展。

继续阅读

探索更多技术文章

浏览归档，发现更多关于系统设计、工具链和工程实践的内容。

全部文章 返回首页

「数据库」更多文章

  1. ClickHouse 表引擎详解
  2. ClickHouse 生产案例与最佳实践
  3. ClickHouse 架构与设计原理