容器部署 ClickHouse

1. ClickHouse 单节点

1.1 配置环境变量

1
2
3
4
5
6
7
8
export CONTAINER_CLI=nerdctl
export IMAGE=clickhouse/clickhouse-server:24
export CLICKHOUSE_INSTANCE_NAME=clickhouse
export CH_DATA=/data/ops/clickhouse/$CLICKHOUSE_INSTANCE_NAME
export CLICKHOUSE_PORT=9000
export CLICKHOUSE_PROMETHEUS_PORT=9363
export CLICKHOUSE_USER=default
export CLICKHOUSE_PASSWORD=xxxxxx

1.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
mkdir -p $CH_DATA/data $CH_DATA/log $CH_DATA/config.d

cat > $CH_DATA/config.d/port.xml <<EOF
<clickhouse>
    <tcp_port>$CLICKHOUSE_PORT</tcp_port>
</clickhouse>
EOF

cat > $CH_DATA/config.d/prometheus.xml <<EOF
<clickhouse>
    <listen_host>0.0.0.0</listen_host>
    <prometheus>
        <endpoint>/metrics</endpoint>
        <port>$CLICKHOUSE_PROMETHEUS_PORT</port>
        <metrics>true</metrics>
        <events>true</events>
        <asynchronous_metrics>true</asynchronous_metrics>
    </prometheus>
</clickhouse>
EOF

cat > $CH_DATA/config.d/logger.xml <<EOF
<clickhouse>
    <logger>
        <level>information</level>
        <console>true</console>
        <log remove="remove"/>
        <errorlog remove="remove"/>
    </logger>
</clickhouse>
EOF

1.3 启动服务

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
$CONTAINER_CLI run -d \
  --name $CLICKHOUSE_INSTANCE_NAME \
  --restart always \
  --security-opt apparmor=unconfined \
  --security-opt seccomp=unconfined \
  --network host \
  --ulimit memlock=-1 \
  --ulimit stack=67108864 \
  --ulimit nofile=1048576:1048576 \
  --memory-swappiness=0 \
  --cap-add=SYS_NICE \
  --cap-add=SYS_RESOURCE \
  -v $CH_DATA/data:/var/lib/clickhouse \
  -v $CH_DATA/log:/var/log/clickhouse-server \
  -v $CH_DATA/config.d:/etc/clickhouse-server/config.d \
  -e CLICKHOUSE_USER=$CLICKHOUSE_USER \
  -e CLICKHOUSE_PASSWORD=$CLICKHOUSE_PASSWORD \
  -e CLICKHOUSE_PORT=$CLICKHOUSE_PORT \
  $IMAGE

1.4 测试连接

1
$CONTAINER_CLI exec -it $CLICKHOUSE_INSTANCE_NAME clickhouse client --host 127.0.0.1 --port $CLICKHOUSE_PORT

1.5 打印交付结果

1
2
3
4
5
6
7
8
9
cat <<EOF
ClickHouse:
  Instance Name:  $CLICKHOUSE_INSTANCE_NAME
  IP:             $(hostname -I | awk '{print $1}')
  Port:           $CLICKHOUSE_PORT
  Metrics URL:    http://$(hostname -I | awk '{print $1}'):$CLICKHOUSE_PROMETHEUS_PORT/metrics
  User:           $CLICKHOUSE_USER
  Password:       $CLICKHOUSE_PASSWORD
EOF

2. ClickHouse 多节点

下面以 3 节点、1 分片 3 副本 为例。每台机器同时跑 ClickHouse Keeper（协调）和 ClickHouse Server（存储），这是带副本高可用的最小生产拓扑。Keeper 需要奇数节点（3 个）才能形成 Raft 多数派。

节点	IP（示例）	Keeper ID	shard	replica
node1	10.0.0.1	1	01	node1
node2	10.0.0.2	2	01	node2
node3	10.0.0.3	3	01	node3

三台机器上都要能互相访问以下端口：

端口	用途
9000	Native 协议（客户端 / 副本同步）
8123	HTTP
9009	副本间 interserver 通信
9181	Keeper 客户端端口
9234	Keeper Raft 端口
9363	Prometheus 指标（内置端点）

2.1 在每个节点配置环境变量

三台机器上的集群 IP 保持一致，NODE_ID 每台不同（1 / 2 / 3）。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
export CONTAINER_CLI=nerdctl
export IMAGE=clickhouse/clickhouse-server:24

export CH_CLUSTER_NAME=cluster_1s3r
export CH_NODE1_IP=10.0.0.1
export CH_NODE2_IP=10.0.0.2
export CH_NODE3_IP=10.0.0.3

export NODE_ID=1                              # node2 改为 2，node3 改为 3
export CH_NODE_IP=$CH_NODE1_IP                # 本机 IP：node1/2/3 分别填 $CH_NODE1_IP / $CH_NODE2_IP / $CH_NODE3_IP
export CLICKHOUSE_INSTANCE_NAME=clickhouse-$CH_CLUSTER_NAME-$NODE_ID

export CH_DATA=/data/ops/clickhouse/$CLICKHOUSE_INSTANCE_NAME
export CLICKHOUSE_PORT=9000                 # Native 端口；三台必须相同。改端口需写入 2.2 的 port.xml
export CH_INTERSERVER_PORT=9009
export CH_KEEPER_PORT=9181
export CH_KEEPER_RAFT_PORT=9234
export CLICKHOUSE_PROMETHEUS_PORT=9363

export CLICKHOUSE_USER=default
export CLICKHOUSE_PASSWORD=xxxxxx

检查端口是否被占用（无输出表示端口空闲，再执行 2.2）：

1
lsof -i :$CLICKHOUSE_PORT -i :$CH_INTERSERVER_PORT -i :$CH_KEEPER_PORT -i :$CH_KEEPER_RAFT_PORT -i :$CLICKHOUSE_PROMETHEUS_PORT

2.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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
mkdir -p $CH_DATA/data $CH_DATA/log $CH_DATA/config.d

cat > $CH_DATA/config.d/port.xml <<EOF
<clickhouse>
    <tcp_port>$CLICKHOUSE_PORT</tcp_port>
</clickhouse>
EOF

cat > $CH_DATA/config.d/cluster.xml <<EOF
<clickhouse>
    <remote_servers>
        <$CH_CLUSTER_NAME>
            <shard>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>$CH_NODE1_IP</host>
                    <port>$CLICKHOUSE_PORT</port>
                </replica>
                <replica>
                    <host>$CH_NODE2_IP</host>
                    <port>$CLICKHOUSE_PORT</port>
                </replica>
                <replica>
                    <host>$CH_NODE3_IP</host>
                    <port>$CLICKHOUSE_PORT</port>
                </replica>
            </shard>
        </$CH_CLUSTER_NAME>
    </remote_servers>
</clickhouse>
EOF

cat > $CH_DATA/config.d/coordination.xml <<EOF
<clickhouse>
    <zookeeper>
        <node><host>$CH_NODE1_IP</host><port>$CH_KEEPER_PORT</port></node>
        <node><host>$CH_NODE2_IP</host><port>$CH_KEEPER_PORT</port></node>
        <node><host>$CH_NODE3_IP</host><port>$CH_KEEPER_PORT</port></node>
    </zookeeper>
</clickhouse>
EOF

cat > $CH_DATA/config.d/macros.xml <<EOF
<clickhouse>
    <macros>
        <cluster>$CH_CLUSTER_NAME</cluster>
        <shard>01</shard>
        <replica>node$NODE_ID</replica>
    </macros>
</clickhouse>
EOF

cat > $CH_DATA/config.d/network-and-keeper.xml <<EOF
<clickhouse>
    <listen_host>0.0.0.0</listen_host>
    <interserver_http_host>$CH_NODE_IP</interserver_http_host>
    <interserver_http_port>$CH_INTERSERVER_PORT</interserver_http_port>
    <keeper_server>
        <tcp_port>$CH_KEEPER_PORT</tcp_port>
        <server_id>$NODE_ID</server_id>
        <log_storage_path>/var/lib/clickhouse/coordination/log</log_storage_path>
        <snapshot_storage_path>/var/lib/clickhouse/coordination/snapshots</snapshot_storage_path>
        <coordination_settings>
            <operation_timeout_ms>10000</operation_timeout_ms>
            <session_timeout_ms>30000</session_timeout_ms>
        </coordination_settings>
        <raft_configuration>
            <server><id>1</id><hostname>$CH_NODE1_IP</hostname><port>$CH_KEEPER_RAFT_PORT</port></server>
            <server><id>2</id><hostname>$CH_NODE2_IP</hostname><port>$CH_KEEPER_RAFT_PORT</port></server>
            <server><id>3</id><hostname>$CH_NODE3_IP</hostname><port>$CH_KEEPER_RAFT_PORT</port></server>
        </raft_configuration>
    </keeper_server>
</clickhouse>
EOF

cat > $CH_DATA/config.d/prometheus.xml <<EOF
<clickhouse>
    <listen_host>0.0.0.0</listen_host>
    <prometheus>
        <endpoint>/metrics</endpoint>
        <port>$CLICKHOUSE_PROMETHEUS_PORT</port>
        <metrics>true</metrics>
        <events>true</events>
        <asynchronous_metrics>true</asynchronous_metrics>
    </prometheus>
</clickhouse>
EOF

cat > $CH_DATA/config.d/logger.xml <<EOF
<clickhouse>
    <logger>
        <level>information</level>
        <console>true</console>
        <log remove="remove"/>
        <errorlog remove="remove"/>
    </logger>
</clickhouse>
EOF

日志

2.3 启动服务

2.1 端口检查全部 OK 后，三台机器各执行一次：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
$CONTAINER_CLI run -d \
  --name $CLICKHOUSE_INSTANCE_NAME \
  --restart always \
  --security-opt apparmor=unconfined \
  --security-opt seccomp=unconfined \
  --network host \
  --ulimit memlock=-1 \
  --ulimit stack=67108864 \
  --ulimit nofile=1048576:1048576 \
  --memory-swappiness=0 \
  --cap-add=SYS_NICE \
  --cap-add=SYS_RESOURCE \
  -v $CH_DATA/data:/var/lib/clickhouse \
  -v $CH_DATA/log:/var/log/clickhouse-server \
  -v $CH_DATA/config.d:/etc/clickhouse-server/config.d \
  -e CLICKHOUSE_USER=$CLICKHOUSE_USER \
  -e CLICKHOUSE_PASSWORD=$CLICKHOUSE_PASSWORD \
  -e CLICKHOUSE_PORT=$CLICKHOUSE_PORT \
  $IMAGE

建议 node1 → node2 → node3 顺序启动，间隔 10～20 秒，让 Keeper Raft 先完成选主。

2.4 验证集群

查看日志

1
$CONTAINER_CLI logs $CLICKHOUSE_INSTANCE_NAME -f

检查状态

1
2
3
$CONTAINER_CLI exec -it $CLICKHOUSE_INSTANCE_NAME clickhouse client \
  --host 127.0.0.1 --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 集群拓扑
SELECT cluster, shard_num, replica_num, host_name, port
FROM system.clusters
WHERE cluster = 'cluster_1s3r'
ORDER BY shard_num, replica_num;

-- Keeper 连通性
SELECT * FROM system.zookeeper WHERE path = '/';

-- 副本状态（建表后才有数据）
SELECT database, table, is_leader, is_readonly, absolute_delay
FROM system.replicas;

三台都出现在 system.clusters 且 Keeper 查询不报错，说明集群就绪。

2.5 打印交付结果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
cat <<EOF
ClickHouse Cluster:
  Cluster Name:     $CH_CLUSTER_NAME
  Node ID:          $NODE_ID
  Node IPs:
    node1:          $CH_NODE1_IP
    node2:          $CH_NODE2_IP
    node3:          $CH_NODE3_IP
  Native Port:      $CLICKHOUSE_PORT
  Interserver Port: $CH_INTERSERVER_PORT
  Keeper Port:      $CH_KEEPER_PORT
  Metrics URLs:
    http://$CH_NODE1_IP:$CLICKHOUSE_PROMETHEUS_PORT/metrics
    http://$CH_NODE2_IP:$CLICKHOUSE_PROMETHEUS_PORT/metrics
    http://$CH_NODE3_IP:$CLICKHOUSE_PROMETHEUS_PORT/metrics
  User:             $CLICKHOUSE_USER
  Password:         $CLICKHOUSE_PASSWORD
EOF

3. 监控

单节点与多节点均通过 prometheus.xml 开启内置指标端点（:9363），Prometheus 直接 scrape /metrics，无需部署外部 exporter。

3.1 验证指标

1
2
curl -s http://127.0.0.1:9363/metrics | head
curl -s http://127.0.0.1:9363/metrics | grep ClickHouseMetrics | head

指标前缀为 ClickHouseMetrics_、ClickHouseProfileEvents_、ClickHouseAsyncMetrics_。

3.2 配置 Prometheus 抓取

单节点：

1
2
3
4
5
6
7
scrape_configs:
  - job_name: clickhouse
    scrape_interval: 15s
    metrics_path: /metrics
    static_configs:
      - targets:
          - 10.0.0.10:9363

多节点：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
scrape_configs:
  - job_name: clickhouse
    scrape_interval: 15s
    metrics_path: /metrics
    static_configs:
      - targets:
          - 10.0.0.1:9363
          - 10.0.0.2:9363
          - 10.0.0.3:9363
        labels:
          cluster: cluster_1s3r

4. 压力测试

测试库名由 BENCH_DB 指定，每条 clickhouse 命令前 export，换 case 时只改库名即可（如 bench_write_case2）。

4.1 准备工具

1
curl -fsSL https://builds.clickhouse.com/master/amd64/clickhouse -o /usr/local/bin/clickhouse && chmod +x /usr/local/bin/clickhouse

查询基准（单节点 / 多节点通用）：

1
2
3
4
5
6
7
export BENCH_DB=bench_write_case1
clickhouse benchmark \
    --host 127.0.0.1 \
    --port $CLICKHOUSE_PORT \
    --concurrency 10 \
    --iterations 1000000 \
    --query "SELECT 1"

4.2 MergeTree

特点：默认 OLAP 引擎，按 ORDER BY 排序存储，支持分区与主键稀疏索引，适合大规模追加写入和聚合分析。

单节点与多节点差异：

维度	单节点	多节点
引擎	`MergeTree`	生产用 `ReplicatedMergeTree` 本地表 + `Distributed` 分布式表
数据分布	全部在本机	副本表：写入后各节点最终都有全量数据；若误用本地 `MergeTree`，则仅写入节点有数据
写入返回	落盘即返回	落盘即返回；副本间复制异步，可查 `system.replicas.absolute_delay`
后台 merge	本机异步执行	各副本独立 merge，互不影响

单节点
创建：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
export BENCH_DB=bench_write_case1
clickhouse client --host 127.0.0.1 --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --multiquery <<SQL
CREATE DATABASE IF NOT EXISTS ${BENCH_DB};
CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_mergetree_single (
    ts DateTime,
    id UInt64,
    value String
) ENGINE = MergeTree ORDER BY (ts, id);
SQL

测试：

1
2
3
4
5
6
7
8
9
export BENCH_DB=bench_write_case1
clickhouse benchmark \
  --host 127.0.0.1 \
  --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER \
  --password $CLICKHOUSE_PASSWORD \
  --concurrency 10 \
  --iterations 10000 \
  --query "INSERT INTO ${BENCH_DB}.test_mergetree_single VALUES (now(), rand(), 'test')"

多节点

test_mergetree_local 存数据，test_mergetree_dist 负责路由。如果没有采用 ON CLUSTER 只会在一个节点创建，副本同步和 Distributed 路由都无法正常工作。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
export BENCH_DB=bench_write_case1
clickhouse client --host $CH_NODE1_IP --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --multiquery <<SQL
CREATE DATABASE IF NOT EXISTS ${BENCH_DB} ON CLUSTER $CH_CLUSTER_NAME;

CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_mergetree_local ON CLUSTER $CH_CLUSTER_NAME (
    ts DateTime,
    id UInt64,
    value String
) ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/${BENCH_DB}/test_mergetree_local',
    '{replica}'
) ORDER BY (ts, id);

CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_mergetree_dist ON CLUSTER $CH_CLUSTER_NAME
AS ${BENCH_DB}.test_mergetree_local
ENGINE = Distributed($CH_CLUSTER_NAME, ${BENCH_DB}, test_mergetree_local, rand());
SQL

确认库表已创建（有输出再跑压测）：

1
2
3
4
export BENCH_DB=bench_write_case1
clickhouse client --host $CH_NODE1_IP --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD \
  --query "SHOW TABLES FROM ${BENCH_DB}"

测试（写入 test_mergetree_dist 分布式表，请求打到 node1）：

1
2
3
4
5
6
7
8
9
export BENCH_DB=bench_write_case1
clickhouse benchmark \
  --host $CH_NODE1_IP \
  --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER \
  --password $CLICKHOUSE_PASSWORD \
  --concurrency 10 \
  --iterations 10000 \
  --query "INSERT INTO ${BENCH_DB}.test_mergetree_dist VALUES (now(), rand(), 'test')"

验证（查 test_mergetree_local 本地副本表，三台行数应一致；复制为异步，稍等几秒再查）：

1
2
3
4
5
6
7
export BENCH_DB=bench_write_case1
for ip in $CH_NODE1_IP $CH_NODE2_IP $CH_NODE3_IP; do
  echo "=== $ip ==="
  clickhouse client --host $ip --port $CLICKHOUSE_PORT \
    --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD \
    --query "SELECT hostName() AS node, count() AS rows FROM ${BENCH_DB}.test_mergetree_local"
done

三个节点的数据都是一样的

=== 10.0.0.1 ===
38bb07f90b20	10000
=== 10.0.0.2 ===
2990a155cb1c	10000
=== 10.0.0.3 ===
f4763c503316	10000

4.3 ReplacingMergeTree

特点：按排序键去重，适合维度表、状态快照等需要最终去重的场景。

单节点与多节点差异：

维度	单节点	多节点
引擎	`ReplacingMergeTree`	`ReplicatedReplacingMergeTree` + `Distributed`
去重时机	本机后台 merge 时去重	各副本在本机 merge 时各自去重；副本间同步的是 part，不是"查询级去重结果"
查询注意	merge 前可能看到重复行，需 `FINAL` 或 `argMax`	同左；任一节点查询逻辑一致，但各副本 merge 进度可能略有差异
数据分布	全在本机	各副本最终都有全量 part

单节点
创建：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
export BENCH_DB=bench_write_case1
clickhouse client --host 127.0.0.1 --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --multiquery <<SQL
CREATE DATABASE IF NOT EXISTS ${BENCH_DB};
CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_replacing_single (
    ts DateTime,
    id UInt64,
    value String
) ENGINE = ReplacingMergeTree ORDER BY (ts, id);
SQL

测试：

1
2
3
4
5
6
7
8
9
export BENCH_DB=bench_write_case1
clickhouse benchmark \
  --host 127.0.0.1 \
  --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER \
  --password $CLICKHOUSE_PASSWORD \
  --concurrency 10 \
  --iterations 10000 \
  --query "INSERT INTO ${BENCH_DB}.test_replacing_single VALUES (now(), rand(), 'test')"

多节点
创建：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
export BENCH_DB=bench_write_case1
clickhouse client --host $CH_NODE1_IP --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --multiquery <<SQL
CREATE DATABASE IF NOT EXISTS ${BENCH_DB} ON CLUSTER $CH_CLUSTER_NAME;

CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_replacing_local ON CLUSTER $CH_CLUSTER_NAME (
    ts DateTime,
    id UInt64,
    value String
) ENGINE = ReplicatedReplacingMergeTree(
    '/clickhouse/tables/{shard}/${BENCH_DB}/test_replacing_local',
    '{replica}'
) ORDER BY (ts, id);

CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_replacing_dist ON CLUSTER $CH_CLUSTER_NAME
AS ${BENCH_DB}.test_replacing_local
ENGINE = Distributed($CH_CLUSTER_NAME, ${BENCH_DB}, test_replacing_local, rand());
SQL

测试：

1
2
3
4
5
6
7
8
9
export BENCH_DB=bench_write_case1
clickhouse benchmark \
  --host $CH_NODE1_IP \
  --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER \
  --password $CLICKHOUSE_PASSWORD \
  --concurrency 10 \
  --iterations 10000 \
  --query "INSERT INTO ${BENCH_DB}.test_replacing_dist VALUES (now(), rand(), 'test')"

验证（查 test_replacing_local，行数三台一致；去重结果用 FINAL 查看）：

1
2
3
4
5
6
7
export BENCH_DB=bench_write_case1
for ip in $CH_NODE1_IP $CH_NODE2_IP $CH_NODE3_IP; do
  echo "=== $ip ==="
  clickhouse client --host $ip --port $CLICKHOUSE_PORT \
    --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD \
    --query "SELECT hostName() AS node, count() AS raw_rows, (SELECT count() FROM ${BENCH_DB}.test_replacing_local FINAL) AS dedup_rows FROM ${BENCH_DB}.test_replacing_local"
done

三个节点的数据都是一样的

=== 10.0.0.1 ===
38bb07f90b20	10000
=== 10.0.0.2 ===
2990a155cb1c	10000
=== 10.0.0.3 ===
f4763c503316	10000

4.4 SummingMergeTree

特点：后台 merge 时对非排序键数值列自动求和，适合按维度预聚合指标。

单节点与多节点差异：

维度	单节点	多节点
引擎	`SummingMergeTree`	`ReplicatedSummingMergeTree` + `Distributed`
求和时机	本机 merge 时合并同键行	各副本独立 merge；副本间同步 part，不跨节点"合并求和"
查询注意	merge 前需 `GROUP BY` 或 `FINAL`	同左
数据分布	全在本机	各副本最终都有全量 part

单节点
创建：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
export BENCH_DB=bench_write_case1
clickhouse client --host 127.0.0.1 --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --multiquery <<SQL
CREATE DATABASE IF NOT EXISTS ${BENCH_DB};
CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_summing_single (
    dt Date,
    category String,
    amount UInt64
) ENGINE = SummingMergeTree ORDER BY (dt, category);
SQL

测试：

1
2
3
4
5
6
7
8
9
export BENCH_DB=bench_write_case1
clickhouse benchmark \
  --host 127.0.0.1 \
  --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER \
  --password $CLICKHOUSE_PASSWORD \
  --concurrency 10 \
  --iterations 10000 \
  --query "INSERT INTO ${BENCH_DB}.test_summing_single VALUES (today(), 'cat', 1)"

多节点
创建：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
export BENCH_DB=bench_write_case1
clickhouse client --host $CH_NODE1_IP --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --multiquery <<SQL
CREATE DATABASE IF NOT EXISTS ${BENCH_DB} ON CLUSTER $CH_CLUSTER_NAME;

CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_summing_local ON CLUSTER $CH_CLUSTER_NAME (
    dt Date,
    category String,
    amount UInt64
) ENGINE = ReplicatedSummingMergeTree(
    '/clickhouse/tables/{shard}/${BENCH_DB}/test_summing_local',
    '{replica}'
) ORDER BY (dt, category);

CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_summing_dist ON CLUSTER $CH_CLUSTER_NAME
AS ${BENCH_DB}.test_summing_local
ENGINE = Distributed($CH_CLUSTER_NAME, ${BENCH_DB}, test_summing_local, rand());
SQL

测试：

1
2
3
4
5
6
7
8
9
export BENCH_DB=bench_write_case1
clickhouse benchmark \
  --host $CH_NODE1_IP \
  --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER \
  --password $CLICKHOUSE_PASSWORD \
  --concurrency 10 \
  --iterations 10000 \
  --query "INSERT INTO ${BENCH_DB}.test_summing_dist VALUES (today(), 'cat', 1)"

验证（查 test_summing_local，三台汇总值应一致）：

1
2
3
4
5
6
7
export BENCH_DB=bench_write_case1
for ip in $CH_NODE1_IP $CH_NODE2_IP $CH_NODE3_IP; do
  echo "=== $ip ==="
  clickhouse client --host $ip --port $CLICKHOUSE_PORT \
    --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD \
    --query "SELECT hostName() AS node, sum(amount) AS total FROM ${BENCH_DB}.test_summing_local GROUP BY node"
done

4.5 TinyLog

特点：轻量追加写引擎，无索引，不支持对同一表的并发写入。

单节点与多节点差异：

维度	单节点	多节点
引擎	`TinyLog`	仍为 `TinyLog`，*无 `Replicated` 版本**
数据分布	全在本机	仅写入节点有数据，其他节点为空
并发写入	需 `--concurrency 1`	同左；且不适合作为集群共享存储
适用场景	临时导入、中转	仅适合单节点；多节点集群不推荐

单节点
创建：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
export BENCH_DB=bench_write_case1
clickhouse client --host 127.0.0.1 --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --multiquery <<SQL
CREATE DATABASE IF NOT EXISTS ${BENCH_DB};
CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_tinylog_single (
    ts DateTime,
    id UInt64,
    value String
) ENGINE = TinyLog;
SQL

测试：

1
2
3
4
5
6
7
8
9
export BENCH_DB=bench_write_case1
clickhouse benchmark \
  --host 127.0.0.1 \
  --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER \
  --password $CLICKHOUSE_PASSWORD \
  --concurrency 1 \
  --iterations 10000 \
  --query "INSERT INTO ${BENCH_DB}.test_tinylog_single VALUES (now(), rand(), 'test')"

多节点
创建（ON CLUSTER 会在每台各建一张独立 test_tinylog_local，互不同步）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
export BENCH_DB=bench_write_case1
clickhouse client --host $CH_NODE1_IP --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --multiquery <<SQL
CREATE DATABASE IF NOT EXISTS ${BENCH_DB} ON CLUSTER $CH_CLUSTER_NAME;
CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_tinylog_local ON CLUSTER $CH_CLUSTER_NAME (
    ts DateTime,
    id UInt64,
    value String
) ENGINE = TinyLog;
SQL

测试（只写 node1 的 test_tinylog_local）：

1
2
3
4
5
6
7
8
9
export BENCH_DB=bench_write_case1
clickhouse benchmark \
  --host $CH_NODE1_IP \
  --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER \
  --password $CLICKHOUSE_PASSWORD \
  --concurrency 1 \
  --iterations 10000 \
  --query "INSERT INTO ${BENCH_DB}.test_tinylog_local VALUES (now(), rand(), 'test')"

验证（仅 node1 的 test_tinylog_local 有数据，node2/node3 为 0）：

1
2
3
4
5
6
7
export BENCH_DB=bench_write_case1
for ip in $CH_NODE1_IP $CH_NODE2_IP $CH_NODE3_IP; do
  echo "=== $ip ==="
  clickhouse client --host $ip --port $CLICKHOUSE_PORT \
    --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD \
    --query "SELECT hostName() AS node, count() AS rows FROM ${BENCH_DB}.test_tinylog_local"
done

4.6 Memory

特点：数据存于内存，读写极快，重启即丢失。

单节点与多节点差异：

维度	单节点	多节点
引擎	`Memory`	仍为 `Memory`，无副本引擎
数据分布	在本机内存	仅写入节点内存有数据，其他节点为空
持久化	无	无；节点重启后该节点数据清空
适用场景	临时分析、缓存	仅适合单节点临时计算，不适合集群共享

单节点
创建：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
export BENCH_DB=bench_write_case1
clickhouse client --host 127.0.0.1 --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --multiquery <<SQL
CREATE DATABASE IF NOT EXISTS ${BENCH_DB};
CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_memory_single (
    ts DateTime,
    id UInt64,
    value String
) ENGINE = Memory;
SQL

测试：

1
2
3
4
5
6
7
8
9
export BENCH_DB=bench_write_case1
clickhouse benchmark \
  --host 127.0.0.1 \
  --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER \
  --password $CLICKHOUSE_PASSWORD \
  --concurrency 10 \
  --iterations 10000 \
  --query "INSERT INTO ${BENCH_DB}.test_memory_single VALUES (now(), rand(), 'test')"

多节点
创建（每台各建独立 test_memory_local，互不同步）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
export BENCH_DB=bench_write_case1
clickhouse client --host $CH_NODE1_IP --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD --multiquery <<SQL
CREATE DATABASE IF NOT EXISTS ${BENCH_DB} ON CLUSTER $CH_CLUSTER_NAME;
CREATE TABLE IF NOT EXISTS ${BENCH_DB}.test_memory_local ON CLUSTER $CH_CLUSTER_NAME (
    ts DateTime,
    id UInt64,
    value String
) ENGINE = Memory;
SQL

测试（只写 node1 的 test_memory_local）：

1
2
3
4
5
6
7
8
9
export BENCH_DB=bench_write_case1
clickhouse benchmark \
  --host $CH_NODE1_IP \
  --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER \
  --password $CLICKHOUSE_PASSWORD \
  --concurrency 10 \
  --iterations 10000 \
  --query "INSERT INTO ${BENCH_DB}.test_memory_local VALUES (now(), rand(), 'test')"

验证（仅 node1 的 test_memory_local 有数据）：

1
2
3
4
5
6
7
export BENCH_DB=bench_write_case1
for ip in $CH_NODE1_IP $CH_NODE2_IP $CH_NODE3_IP; do
  echo "=== $ip ==="
  clickhouse client --host $ip --port $CLICKHOUSE_PORT \
    --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD \
    --query "SELECT hostName() AS node, count() AS rows FROM ${BENCH_DB}.test_memory_local"
done

4.7 清理测试数据

单节点：

1
2
3
4
export BENCH_DB=bench_write_case1
clickhouse client --host 127.0.0.1 --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD \
  --query "DROP DATABASE IF EXISTS ${BENCH_DB}"

多节点（在 node1 执行，ON CLUSTER 统一删除）：

1
2
3
4
export BENCH_DB=bench_write_case1
clickhouse client --host $CH_NODE1_IP --port $CLICKHOUSE_PORT \
  --user $CLICKHOUSE_USER --password $CLICKHOUSE_PASSWORD \
  --query "DROP DATABASE IF EXISTS ${BENCH_DB} ON CLUSTER $CH_CLUSTER_NAME SYNC"