Ubuntu 22.04下ClickHouse集群搭建实战：3节点配置与分布式表测试全流程

1. 集群架构设计与环境准备

ClickHouse作为一款高性能的列式数据库，其集群部署需要精心规划架构。我们采用2个数据节点+1个协调节点的混合部署方案，兼顾资源利用率与高可用性要求。

典型生产环境架构拓扑：

节点1 (chnode1): ClickHouse Server + Keeper (server_id=1)
节点2 (chnode2): ClickHouse Server + Keeper (server_id=2)  
节点3 (chnode3): ClickHouse Keeper专用节点 (server_id=3)

硬件配置建议：

• 数据节点：8核CPU/32GB内存/500GB SSD（根据数据量调整）
• Keeper节点：4核CPU/16GB内存/100GB SSD
• 网络：节点间延迟<1ms，建议10Gbps内网互联

系统初始化步骤：

# 所有节点执行
sudo apt update && sudo apt upgrade -y
sudo timedatectl set-timezone Asia/Shanghai
sudo systemctl restart systemd-timesyncd

# 设置主机名（分别执行）
hostnamectl set-hostname chnode1
hostnamectl set-hostname chnode2 
hostnamectl set-hostname chnode3

# 编辑/etc/hosts添加解析
cat >> /etc/hosts <<EOF
192.168.72.51 chnode1
192.168.72.52 chnode2
192.168.72.53 chnode3
EOF

提示：生产环境建议禁用swap并优化内核参数，参考命令：

sudo swapoff -a
echo 'vm.swappiness = 1' >> /etc/sysctl.conf
sysctl -p

2. ClickHouse组件安装与配置

2.1 软件包安装

数据节点安装（chnode1/chnode2）：

# 添加官方仓库
sudo apt-get install -y apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

# 安装核心组件
sudo apt-get install -y clickhouse-server clickhouse-client

# 设置默认用户密码
sudo clickhouse-client --query "ALTER USER default IDENTIFIED BY 'YourSecurePassword'"

Keeper专用节点安装（chnode3）：

sudo apt-get install -y clickhouse-keeper

# 创建必要目录
sudo mkdir -p /var/lib/clickhouse-keeper/{coordination/log,coordination/snapshots,cores}
sudo chown -R clickhouse:clickhouse /var/lib/clickhouse-keeper

2.2 关键配置文件详解

网络与日志配置（所有数据节点）

<!-- /etc/clickhouse-server/config.d/network.xml -->
<clickhouse>
    <listen_host>0.0.0.0</listen_host>
    <http_port>8123</http_port>
    <tcp_port>9000</tcp_port>
    <interserver_http_port>9009</interserver_http_port>
    <logger>
        <level>information</level>
        <log>/var/log/clickhouse-server/clickhouse-server.log</log>
        <size>1G</size>
        <count>10</count>
    </logger>
</clickhouse>

Keeper配置（差异化部分）

<!-- chnode1的keeper配置 -->
<keeper_server>
    <server_id>1</server_id>
    <raft_configuration>
        <server>
            <id>1</id>
            <hostname>chnode1</hostname>
            <port>9234</port>
        </server>
        <!-- 其他节点配置... -->
    </raft_configuration>
</keeper_server>

<!-- chnode3需额外配置 -->
<listen_host>0.0.0.0</listen_host>
<interserver_listen_host>0.0.0.0</interserver_listen_host>

分片与副本宏定义

<!-- chnode1的macros.xml -->
<macros>
    <shard>1</shard>
    <replica>replica_1</replica>
</macros>

<!-- chnode2的macros.xml -->  
<macros>
    <shard>2</shard>
    <replica>replica_1</replica>
</macros>

3. 集群服务启动与验证

3.1 服务启动顺序

# 先启动Keeper节点
ssh chnode3 "sudo systemctl start clickhouse-keeper"

# 再启动数据节点  
ssh chnode1 "sudo systemctl start clickhouse-server"
ssh chnode2 "sudo systemctl start clickhouse-server"

# 检查服务状态
for node in {1..3}; do
    ssh chnode$node "sudo systemctl status clickhouse-* | grep Active"
done

3.2 集群健康检查

验证Keeper角色：

# 查看leader/follower状态
echo mntr | nc chnode1 9181 | grep zk_server_state
echo mntr | nc chnode2 9181 | grep zk_server_state  
echo mntr | nc chnode3 9181 | grep zk_server_state

集群拓扑验证：

-- 在任意数据节点执行
SELECT cluster, host_name, host_address, port 
FROM system.clusters
WHERE cluster = 'cluster_2S_1R';

/*
┌─cluster─────┬─host_name─┬─host_address──┬─port─┐
│ cluster_2S_1R │ chnode1   │ 192.168.72.51 │ 9000 │
│ cluster_2S_1R │ chnode2   │ 192.168.72.52 │ 9000 │
└──────────────┴───────────┴───────────────┴──────┘
*/

4. 分布式表实战测试

4.1 基础表结构创建

-- 在集群上创建测试数据库
CREATE DATABASE test ON CLUSTER cluster_2S_1R;

-- 创建本地MergeTree表
CREATE TABLE test.local_data ON CLUSTER cluster_2S_1R
(
    event_date Date,
    user_id UInt64,
    event_type String,
    value Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

-- 创建分布式表
CREATE TABLE test.distributed_data ON CLUSTER cluster_2S_1R
AS test.local_data
ENGINE = Distributed(cluster_2S_1R, test, local_data, rand());

4.2 数据操作验证

写入测试：

-- 插入测试数据（自动分布到不同分片）
INSERT INTO test.distributed_data VALUES
    ('2024-01-01', 1001, 'view', 5.5),
    ('2024-01-02', 1002, 'click', 2.3);

-- 检查数据分布
SELECT hostName() as node, count() FROM test.local_data
GROUP BY node;

跨分片查询：

-- 复杂聚合查询示例
SELECT 
    event_date,
    event_type,
    count() as events,
    avg(value) as avg_value
FROM test.distributed_data
WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY event_date, event_type
ORDER BY event_date;

4.3 性能优化建议

配置调整：

<!-- 增加并发处理能力 -->
<max_concurrent_queries>200</max_concurrent_queries>
<background_pool_size>16</background_pool_size>

<!-- 内存限制优化 -->  
<max_memory_usage>16000000000</max_memory_usage>
<max_bytes_before_external_group_by>8000000000</max_bytes_before_external_group_by>

查询优化技巧：

-- 使用PREWHERE替代WHERE
SELECT * FROM distributed_data 
PREWHERE user_id = 1001;

-- 利用物化视图
CREATE MATERIALIZED VIEW test.mv_daily_stats
ENGINE = AggregatingMergeTree()
PARTITION BY date
ORDER BY (date, event_type)
AS SELECT
    event_date as date,
    event_type,
    countState() as counts,
    avgState(value) as avg_values
FROM test.local_data
GROUP BY date, event_type;

5. 生产环境注意事项

监控指标推荐：

• 关键指标：ReplicatedTableStatus, QueryThreadCount, MemoryUsage
• 告警阈值：ReplicaDelay > 30s, ZooKeeperWaitTime > 1s

备份策略示例：

# 使用clickhouse-backup工具
clickhouse-backup create full_backup
clickhouse-backup upload full_backup s3://your-bucket

# 定期备份配置
0 2 * * * /usr/bin/clickhouse-backup create incremental_backup

扩展建议：

• 当单个分片数据超过5TB时考虑增加分片
• 查询QPS超过500时建议增加副本
• 定期执行OPTIMIZE TABLE FINAL合并小分区