Deploying ClickHouse in Containers

1. ClickHouse Single Node

1.1 Configure Environment Variables

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 Generate the Configuration Files

 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 Start the Service

 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 Test the Connection

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

1.5 Print the Delivery Result

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 Multi-Node

Below we use 3 nodes, 1 shard with 3 replicas as the example. Each machine runs both ClickHouse Keeper (coordination) and ClickHouse Server (storage), which is the smallest production topology with replicated high availability. Keeper needs an odd number of nodes (3) to form a Raft majority.

Node	IP (example)	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

All three machines must be able to reach each other on the following ports:

Port	Purpose
9000	Native protocol (client / replica sync)
8123	HTTP
9009	Interserver communication between replicas
9181	Keeper client port
9234	Keeper Raft port
9363	Prometheus metrics (built-in endpoint)

2.1 Configure Environment Variables on Each Node

The cluster IPs stay the same on all three machines, while NODE_ID differs per machine (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

Check whether the ports are in use (no output means the ports are free, so continue with 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 Generate the Configuration Files

 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

Logs

2.3 Start the Service

Once every port check in 2.1 is OK, run this once on each of the three machines:

 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

Start them in the order node1 → node2 → node3, with an interval of 10–20 seconds, so that Keeper Raft finishes its leader election first.

2.4 Verify the Cluster

View the logs

1
$CONTAINER_CLI logs $CLICKHOUSE_INSTANCE_NAME -f

Check the status

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;

If all three appear in system.clusters and the Keeper query returns no error, the cluster is ready.

2.5 Print the Delivery Result

 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. Monitoring

Both single-node and multi-node setups enable the built-in metrics endpoint (:9363) through prometheus.xml, so Prometheus can scrape /metrics directly with no external exporter to deploy.

3.1 Verify the Metrics

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

The metric prefixes are ClickHouseMetrics_, ClickHouseProfileEvents_, and ClickHouseAsyncMetrics_.

3.2 Configure Prometheus Scraping

Single node:

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

Multi-node:

 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. Stress Testing

The test database name is set by BENCH_DB, exported before each clickhouse command; when switching cases, only the database name changes (for example bench_write_case2).

4.1 Prepare the Tool

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

Query benchmark (works for both single-node and multi-node):

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

Characteristics: the default OLAP engine, stored sorted by ORDER BY, supporting partitions and a sparse primary-key index; suited to large-scale append writes and aggregation analysis.

Differences between single-node and multi-node:

Dimension	Single Node	Multi-Node
Engine	`MergeTree`	In production, a local `ReplicatedMergeTree` table plus a `Distributed` table
Data placement	All on the local machine	Replica tables: after a write, every node eventually holds the full dataset; if a local `MergeTree` is used by mistake, then only the writing node has data
Write return	Returns once written to disk	Returns once written to disk; replication between replicas is asynchronous, check `system.replicas.absolute_delay`
Background merge	Runs asynchronously on the local machine	Each replica merges independently, without affecting the others

Single node
Create:

 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

Test:

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')"

Multi-node

test_mergetree_local holds the data, test_mergetree_dist handles routing. Without ON CLUSTER the table is created on only one node, and neither replica synchronization nor Distributed routing works correctly.

 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

Confirm the database and tables were created (only run the benchmark once there is output):

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 (writing to the test_mergetree_dist distributed table, with requests hitting 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')"

Verify (query the test_mergetree_local local replica table; the row counts on all three machines should match; replication is asynchronous, so wait a few seconds before querying):

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

The data on all three nodes is identical

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

4.3 ReplacingMergeTree

Characteristics: deduplicates by the sorting key; suited to dimension tables, state snapshots, and other scenarios that need eventual deduplication.

Differences between single-node and multi-node:

Dimension	Single Node	Multi-Node
Engine	`ReplacingMergeTree`	`ReplicatedReplacingMergeTree` + `Distributed`
Dedup timing	Deduplicates during the local background merge	Each replica deduplicates on its own during its local merge; what is synchronized between replicas is parts, not a “query-level deduplicated result”
Query caveats	Before a merge you may see duplicate rows; use `FINAL` or `argMax`	Same as the left; the query logic is identical on any node, but replica merge progress may differ slightly
Data placement	All on the local machine	Every replica eventually holds all parts

Single node
Create:

 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

Test:

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')"

Multi-node
Create:

 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

Test:

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')"

Verify (query test_replacing_local; the row counts on all three machines match; inspect the deduplicated result with 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

The data on all three nodes is identical

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

4.4 SummingMergeTree

Characteristics: automatically sums the non-sorting-key numeric columns during the background merge; suited to metrics pre-aggregated by dimension.

Differences between single-node and multi-node:

Dimension	Single Node	Multi-Node
Engine	`SummingMergeTree`	`ReplicatedSummingMergeTree` + `Distributed`
Summing timing	Merges rows with the same key during the local merge	Each replica merges independently; parts are synchronized between replicas, with no cross-node “merged sum”
Query caveats	Before a merge you need `GROUP BY` or `FINAL`	Same as the left
Data placement	All on the local machine	Every replica eventually holds all parts

Single node
Create:

 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

Test:

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)"

Multi-node
Create:

 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

Test:

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)"

Verify (query test_summing_local; the aggregated values on all three machines should match):

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

Characteristics: a lightweight append-only engine with no index, and no support for concurrent writes to the same table.

Differences between single-node and multi-node:

Dimension	Single Node	Multi-Node
Engine	`TinyLog`	Still `TinyLog`, *there is no `Replicated` version**
Data placement	All on the local machine	Only the writing node has data; the other nodes are empty
Concurrent writes	Needs `--concurrency 1`	Same as the left; also unsuited to being shared storage for a cluster
Use cases	Temporary import, staging	Suitable only for single nodes; not recommended for multi-node clusters

Single node
Create:

 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

Test:

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')"

Multi-node
Create (ON CLUSTER creates an independent test_tinylog_local on each machine, with no synchronization between them):

 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

Test (writes only to node1’s 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')"

Verify (only node1’s test_tinylog_local has data; node2/node3 are 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

Characteristics: data is kept in memory, making reads and writes extremely fast, but it is lost on restart.

Differences between single-node and multi-node:

Dimension	Single Node	Multi-Node
Engine	`Memory`	Still `Memory`, there is no replica engine
Data placement	In the local machine’s memory	Only the writing node’s memory has data; the other nodes are empty
Persistence	None	None; that node’s data is cleared after a restart
Use cases	Temporary analysis, caching	Suitable only for temporary computation on a single node, not for cluster sharing

Single node
Create:

 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

Test:

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')"

Multi-node
Create (an independent test_memory_local is created on each machine, with no synchronization between them):

 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

Test (writes only to node1’s 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')"

Verify (only node1’s test_memory_local has data):

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 Clean Up the Test Data

Single node:

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}"

Multi-node (run on node1; ON CLUSTER removes it everywhere):

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"