ClickHouse 日志分析平台集群部署与运维实战

一、概述

ClickHouse 是 Yandex 开源的列式存储 OLAP 数据库，以其卓越的查询性能和压缩比在日志分析领域广受欢迎。相比 ELK 栈，ClickHouse 在海量日志查询场景下具有更高的性价比和更低的资源消耗。

1.1 核心特性

  • 列式存储: 适合聚合分析查询，压缩比高达 10:1
  • 向量化执行: 充分利用 CPU SIMD 指令，查询性能卓越
  • 分布式架构: 原生支持分片集群和副本高可用
  • SQL 兼容: 支持标准 SQL 语法，学习成本低
  • 实时写入: 支持高吞吐实时数据写入

1.2 适用场景

  • 日志检索与分析
  • 用户行为分析
  • 监控指标存储
  • 实时数据报表

二、环境规划

2.1 硬件配置建议

节点角色 数量 CPU 内存 磁盘 网络
ClickHouse Server 3+ 8 核 + 32GB+ SSD 1TB+ 10GbE
ZooKeeper 3 4 核 8GB SSD 100GB 1GbE

2.2 软件版本

  • ClickHouse: 24.8 LTS
  • ZooKeeper: 3.8+
  • 操作系统：Ubuntu 22.04 LTS / CentOS Stream 9

2.3 网络规划

1
2
3
4
节点 1 (192.168.1.101): shard1, replica1
节点 2 (192.168.1.102): shard1, replica2
节点 3 (192.168.1.103): shard2, replica1
节点 4 (192.168.1.104): shard2, replica2

三、ClickHouse 集群部署

3.1 安装 ClickHouse

1
2
3
4
5
6
7
# Ubuntu/Debian
curl https://clickhouse.com/ | sh
sudo apt-get install -y clickhouse-server clickhouse-client

# CentOS/RHEL
curl https://clickhouse.com/ | sh
sudo yum install -y clickhouse-server clickhouse-client

3.2 配置 ZooKeeper

ClickHouse 集群依赖 ZooKeeper 进行元数据管理和分布式 DDL 同步。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<!-- /etc/clickhouse-server/config.d/zookeeper.xml -->
<clickhouse>
    <zookeeper>
        <node index="1">
            <host>zk1.example.com</host>
            <port>2181</port>
        </node>
        <node index="2">
            <host>zk2.example.com</host>
            <port>2181</port>
        </node>
        <node index="3">
            <host>zk3.example.com</host>
            <port>2181</port>
        </node>
    </zookeeper>
</clickhouse>

3.3 配置集群拓扑

1
2
3
4
5
6
7
<!-- /etc/clickhouse-server/config.d/macros.xml -->
<clickhouse>
    <macros>
        <shard>01</shard>
        <replica>01</replica>
    </macros>
</clickhouse>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<!-- /etc/clickhouse-server/config.d/cluster.xml -->
<clickhouse>
    <remote_servers>
        <logs_cluster>
            <shard>
                <replica>
                    <host>192.168.1.101</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>192.168.1.102</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <replica>
                    <host>192.168.1.103</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>192.168.1.104</host>
                    <port>9000</port>
                </replica>
            </shard>
        </logs_cluster>
    </remote_servers>
</clickhouse>

3.4 配置用户权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<!-- /etc/clickhouse-server/users.d/logging_user.xml -->
<clickhouse>
    <users>
        <logging_user>
            <password>your_secure_password</password>
            <networks>
                <ip>::/0</ip>
            </networks>
            <profile>logging_profile</profile>
            <quota>logging_quota</quota>
            <access_management>1</access_management>
        </logging_user>
    </users>
    <profiles>
        <logging_profile>
            <max_memory_usage>16000000000</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
            <max_execution_time>300</max_execution_time>
        </logging_profile>
    </profiles>
    <quotas>
        <logging_quota>
            <interval>
                <duration>3600</duration>
                <queries>1000</queries>
                <result_rows>1000000</result_rows>
                <read_rows>100000000</read_rows>
            </interval>
        </logging_quota>
    </quotas>
</clickhouse>

3.5 启动服务

1
2
3
4
5
6
7
8
9
# 启动 ClickHouse
sudo systemctl start clickhouse-server
sudo systemctl enable clickhouse-server

# 检查状态
sudo systemctl status clickhouse-server

# 测试连接
clickhouse-client --query "SELECT version()"

四、日志表结构设计

4.1 创建日志数据库

1
2
3
4
5
-- 创建数据库
CREATE DATABASE IF NOT EXISTS logs
ENGINE = Atomic;

USE logs;

4.2 创建分布式表和本地表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 本地表 (每个节点执行)
CREATE TABLE logs_local ON CLUSTER logs_cluster
(
    `timestamp` DateTime64(3) DEFAULT now64(3),
    `trace_id` String,
    `span_id` String,
    `service_name` LowCardinality(String),
    `log_level` LowCardinality(String),
    `message` String,
    `host` LowCardinality(String),
    `container_id` String,
    `namespace` LowCardinality(String),
    `pod_name` LowCardinality(String),
    `labels` Map(String, String),
    `attributes` Map(String, String)
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/logs',
    '{replica}'
)
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (service_name, timestamp)
TTL timestamp + INTERVAL 30 DAY
SETTINGS
    index_granularity = 8192,
    compress_part_header = 1;

-- 分布式表 (任一节点执行)
CREATE TABLE logs ON CLUSTER logs_cluster AS logs_local
ENGINE = Distributed(logs_cluster, logs, logs_local, rand());

4.3 创建物化视图 (可选)

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 按服务聚合错误数
CREATE MATERIALIZED VIEW logs_error_stats ON CLUSTER logs_cluster
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (service_name, log_level, toStartOfHour(timestamp))
AS SELECT
    toStartOfHour(timestamp) AS timestamp,
    service_name,
    log_level,
    count() AS log_count
FROM logs_local
WHERE log_level IN ('ERROR', 'FATAL')
GROUP BY timestamp, service_name, log_level;

五、日志采集与写入

5.1 Vector 采集配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# vector.yaml
sources:
  kubernetes_logs:
    type: kubernetes_logs
    pod_annotation_fields:
      container_name: container_name
      pod_labels: pod_labels
      pod_name: pod_name

sinks:
  clickhouse:
    type: clickhouse
    inputs:
      - kubernetes_logs
    endpoint: http://192.168.1.101:8123
    database: logs
    table: logs_local
    encoding:
      codec: json
    healthcheck:
      enabled: true
    batch:
      max_events: 10000
      timeout_secs: 5
    buffer:
      type: memory
      max_events: 500000
    request:
      retry_attempts: 3
      retry_max_duration_secs: 300

5.2 Fluent Bit 采集配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# fluent-bit.conf
[INPUT]
    Name              tail
    Path              /var/log/containers/*.log
    Parser            docker
    Tag               kube.*
    Refresh_Interval  10
    Mem_Buf_Limit     500MB

[FILTER]
    Name                kubernetes
    Match               kube.*
    Kube_URL            https://kubernetes.default.svc:443
    Kube_CA_File        /var/run/secrets/kubernetes.io/serviceaccount/ca.crt
    Kube_Token_File     /var/run/secrets/kubernetes.io/serviceaccount/token
    Kube_Tag_Prefix     kube.var.log.containers.
    Merge_Log           On
    Keep_Log            Off
    K8S-Logging.Parser  On
    K8S-Logging.Exclude On

[OUTPUT]
    Name            clickhouse
    Match           *
    Host            192.168.1.101
    Port            8123
    Database        logs
    Table           logs_local
    User            logging_user
    Password        your_secure_password
    Format          json
    Batch_Size      10000
    Flush_Interval  5

5.3 写入性能优化

1
2
3
4
5
6
7
8
9
10
-- 调整写入参数
ALTER TABLE logs_local MODIFY SETTING
    max_insert_block_size = 1000000,
    min_insert_block_size_rows = 100000,
    min_insert_block_size_bytes = 104857600;

-- 异步插入 (高吞吐场景)
SET async_insert = 1;
SET wait_for_async_insert = 0;
SET max_async_insert_size = 1048576;

六、查询优化与实践

6.1 常用查询示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- 查询最近 1 小时的错误日志
SELECT
    timestamp,
    service_name,
    message
FROM logs
WHERE log_level = 'ERROR'
  AND timestamp >= now() - INTERVAL 1 HOUR
ORDER BY timestamp DESC
LIMIT 100;

-- 按服务统计错误数量
SELECT
    service_name,
    count() AS error_count
FROM logs
WHERE log_level IN ('ERROR', 'FATAL')
  AND timestamp >= now() - INTERVAL 24 HOUR
GROUP BY service_name
ORDER BY error_count DESC;

-- 全文搜索 (使用 LIKE)
SELECT
    timestamp,
    service_name,
    message
FROM logs
WHERE message LIKE '%Connection refused%'
  AND timestamp >= now() - INTERVAL 1 HOUR
LIMIT 50;

-- 追踪完整调用链
SELECT
    timestamp,
    service_name,
    log_level,
    message
FROM logs
WHERE trace_id = 'abc123xyz'
ORDER BY timestamp ASC;

6.2 性能优化技巧

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 1. 使用投影优化高频查询
ALTER TABLE logs_local ADD PROJECTION service_hour_stats
(
    SELECT
        service_name,
        toStartOfHour(timestamp) AS hour,
        log_level,
        count() AS cnt
    GROUP BY service_name, hour, log_level
);

-- 2. 使用跳数索引加速过滤
ALTER TABLE logs_local ADD INDEX idx_message message TYPE tokenbf_v1(10240, 3, 0) GRANULARITY 4;

-- 3. 使用采样加速大数据量查询
SELECT
    service_name,
    count() AS cnt
FROM logs SAMPLE 0.1
WHERE timestamp >= now() - INTERVAL 7 DAY
GROUP BY service_name;

-- 4. 使用 FINAL 获取最新数据 (去重)
SELECT * FROM logs_local FINAL WHERE trace_id = 'xxx';

6.3 查询性能监控

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 查看慢查询
SELECT
    query,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
  AND event_date >= today() - 1
ORDER BY query_duration_ms DESC
LIMIT 20;

-- 查看表大小
SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS compression_ratio,
    sum(rows) AS rows
FROM system.parts
WHERE table = 'logs_local'
GROUP BY table;

七、运维管理

7.1 备份策略

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#!/bin/bash
# clickhouse_backup.sh

BACKUP_DIR="/backup/clickhouse"
DATE=$(date +%Y%m%d_%H%M%S)
CLICKHOUSE_HOST="localhost"
CLICKHOUSE_USER="default"

# 创建备份目录
mkdir -p ${BACKUP_DIR}/${DATE}

# 使用 clickhouse-backup 工具
clickhouse-backup create logs_backup_${DATE} \
    --tables="logs.*" \
    --user=${CLICKHOUSE_USER}

# 上传到对象存储 (可选)
clickhouse-backup upload logs_backup_${DATE}

# 清理 7 天前的备份
clickhouse-backup delete remote $(clickhouse-backup list remote | grep logs_backup | head -n -7 | awk '{print $1}')

7.2 监控指标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- 集群健康状态
SELECT
    shard_num,
    replica_num,
    host_name,
    is_local,
    is_read_only,
    is_session_expired,
    errors_count
FROM system.clusters
WHERE cluster = 'logs_cluster';

-- 副本同步状态
SELECT
    table,
    is_read_only,
    is_session_expired,
    zookeeper_exception,
    total_replicas,
    active_replicas,
    has_lost_parts,
    has_mutated_parts
FROM system.replicas
WHERE database = 'logs';

-- 分区状态
SELECT
    table,
    partition,
    active,
    marks,
    rows,
    bytes_on_disk,
    modification_time
FROM system.parts
WHERE table = 'logs_local'
ORDER BY modification_time DESC;

7.3 常见故障处理

故障 1: ZooKeeper 连接丢失

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

-- 解决方案：检查网络连通性，重启 ClickHouse
sudo systemctl restart clickhouse-server

故障 2: 副本不同步

1
2
3
4
5
6
7
-- 查看副本状态
SELECT * FROM system.replicas WHERE is_readonly = 0;

-- 强制同步
SYSTEM SYNC REPLICA logs.logs_local;

-- 如果仍然失败，可能需要重新创建副本

故障 3: 磁盘空间不足

1
2
3
4
5
6
7
8
9
10
11
-- 查看磁盘使用
SELECT
    name,
    path,
    formatReadableSize(free_space) AS free,
    formatReadableSize(total_space) AS total,
    round((free_space / total_space) * 100, 2) AS free_percent
FROM system.disks;

-- 紧急清理旧数据
ALTER TABLE logs_local DELETE WHERE timestamp < now() - INTERVAL 7 DAY;

7.4 扩容操作

1
2
3
4
5
6
7
8
9
10
11
-- 1. 添加新节点到集群配置 (所有节点)
-- 编辑 /etc/clickhouse-server/config.d/cluster.xml 添加新节点

-- 2. 重启 ClickHouse 服务
sudo systemctl restart clickhouse-server

-- 3. 验证集群状态
SELECT * FROM system.clusters WHERE cluster = 'logs_cluster';

-- 4. 数据重平衡 (可选)
-- ClickHouse 不会自动重平衡，需要手动迁移数据

八、与 Grafana 集成

8.1 安装 ClickHouse 数据源

1
2
3
4
5
# 安装 Grafana ClickHouse 插件
grafana-cli plugins install grafana-clickhouse-datasource

# 重启 Grafana
sudo systemctl restart grafana-server

8.2 配置数据源

在 Grafana 中添加 ClickHouse 数据源:

  • URL: http://192.168.1.101:8123
  • Database: logs
  • User: logging_user
  • Password: your_secure_password

8.3 常用面板查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 错误日志趋势
SELECT
    toStartOfMinute(timestamp) AS time,
    service_name,
    count() AS errors
FROM logs
WHERE log_level IN ('ERROR', 'FATAL')
  AND timestamp >= $__timeFrom()
  AND timestamp <= $__timeTo()
GROUP BY time, service_name
ORDER BY time ASC;

-- 服务响应时间分布
SELECT
    histogramQuantile(0.95, attributes['response_time']) AS p95_latency
FROM logs
WHERE timestamp >= $__timeFrom()
  AND timestamp <= $__timeTo()
GROUP BY service_name;

九、最佳实践总结

9.1 表设计

  1. 使用 LowCardinality 优化低基数字段
  2. 合理设置 TTL 自动清理过期数据
  3. 选择合适的主键顺序 (高频过滤字段在前)
  4. 使用物化视图预计算常用聚合

9.2 写入优化

  1. 批量写入，避免单条插入
  2. 使用异步插入提高吞吐
  3. 合理设置缓冲大小
  4. 监控写入队列积压

9.3 查询优化

  1. 避免 SELECT *
  2. 利用分区裁剪
  3. 使用采样处理大数据量
  4. 为高频查询创建投影

9.4 运维要点

  1. 定期监控磁盘使用率
  2. 设置合理的 TTL 策略
  3. 建立备份和恢复流程
  4. 监控 ZooKeeper 健康状态
  5. 定期分析慢查询日志

十、参考资源