集群扩容与升级：分片重平衡、平滑升级与滚动重启

<clickhouse>
    <remote_servers>
        <analytics_cluster>
            <shard>
                <weight>1</weight>
                <internal_replication>true</internal_replication>
                <replica><host>ch01-01</host><port>9000</port></replica>
                <replica><host>ch01-02</host><port>9000</port></replica>
                <replica><host>ch01-03</host><port>9000</port></replica>
            </shard>
            <shard>
                <weight>1</weight>
                <internal_replication>true</internal_replication>
                <replica><host>ch02-01</host><port>9000</port></replica>
                <replica><host>ch02-02</host><port>9000</port></replica>
                <replica><host>ch02-03</host><port>9000</port></replica>
            </shard>
            <!-- shard03 至 shard12 同构，合计 12 分片 3 副本 -->
        </analytics_cluster>
    </remote_servers>
</clickhouse>

SELECT
    hostName() AS host,
    formatReadableSize(sum(bytes_on_disk)) AS disk_used,
    round(100 * sum(bytes_on_disk) / 2000000000000, 1) AS pct_of_2tb
FROM system.parts
WHERE active AND database = 'analytics'
GROUP BY host
ORDER BY disk_used DESC;

-- Distributed 表按分片键把写入路由到某个 shard 的某个 replica
CREATE TABLE analytics.events_dist ON CLUSTER analytics_cluster
(
    event_date Date,
    user_id UInt64,
    event_type LowCardinality(String),
    amount Decimal(18, 4)
)
ENGINE = Distributed('analytics_cluster', 'analytics', 'events_local', cityHash64(user_id));

-- 查看集群当前的分片副本布局，is_local 标记本地节点
SELECT cluster, shard_num, replica_num, host_name, is_local, weight
FROM system.clusters
WHERE cluster = 'analytics_cluster'
ORDER BY shard_num, replica_num;

<shard>
    <weight>2</weight>
    <internal_replication>true</internal_replication>
    <replica><host>ch01-01</host><port>9000</port></replica>
    <replica><host>ch01-02</host><port>9000</port></replica>
    <replica><host>ch01-03</host><port>9000</port></replica>
</shard>
<shard>
    <weight>2</weight>
    <internal_replication>true</internal_replication>
    <replica><host>ch13-01</host><port>9000</port></replica>
    <replica><host>ch13-02</host><port>9000</port></replica>
    <replica><host>ch13-03</host><port>9000</port></replica>
</shard>

SYSTEM RELOAD CONFIG;
SELECT shard_num, weight FROM system.clusters
WHERE cluster = 'analytics_cluster' ORDER BY shard_num;

-- 1. 在新集群上建好本地表与分布式表
CREATE TABLE analytics.events_local ON CLUSTER analytics_cluster_new
AS analytics.events_local
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/analytics/events', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

-- 2. 逐分片回填：按新分片键取模过滤后写入新集群
INSERT INTO analytics.events_local
SELECT * FROM cluster('analytics_cluster_old', 'analytics', 'events_local')
WHERE cityHash64(user_id) % 18 = 3;

-- 3. 校验行数与校验和，两侧必须完全一致
SELECT count(), sum(cityHash64(user_id, event_type)) AS chk
FROM cluster('analytics_cluster_old', 'analytics', 'events_local');
SELECT count(), sum(cityHash64(user_id, event_type)) AS chk
FROM analytics.events_local;

<clickhouse>
    <!-- 升级过渡期把兼容级别钉在旧版本，降低行为差异风险 -->
    <compatibility>24.3</compatibility>
    <!-- 去重窗口过小会导致重启后重复插入被误判为不重复 -->
    <replicated_deduplication_window>1000</replicated_deduplication_window>
    <replicated_deduplication_window_seconds>604800</replicated_deduplication_window_seconds>
</clickhouse>

SELECT version() AS current_version;
SELECT name, value, changed FROM system.settings WHERE name = 'compatibility';
SELECT name, value FROM system.merge_tree_settings
WHERE name LIKE 'replicated_deduplication%';

-- 重启前确认复制健康度，absolute_delay 是副本落后主库的秒数
SELECT database, table, is_leader, absolute_delay, queue_size, inserts_in_queue
FROM system.replicas
WHERE database = 'analytics'
ORDER BY absolute_delay DESC;

-- 单节点操作：重启前先让副本追平
SYSTEM SYNC REPLICA analytics.events_local;
SYSTEM RESTART REPLICA analytics.events_local;

#!/usr/bin/env bash
set -euo pipefail
for host in ch01-01 ch01-02 ch01-03; do
    ssh "$host" 'sudo systemctl restart clickhouse-server'
    until ssh "$host" "clickhouse-client -q \"SELECT count() FROM system.replicas WHERE absolute_delay > 60\" | grep -q '^0$'"; do
        sleep 10
    done
    echo "node $host replica caught up"
done

-- 升级前确认没有未完成的分布式 DDL
SELECT entry, host, status, query, exception_text
FROM system.distributed_ddl_queue
WHERE status != 'Finished'
ORDER BY query_create_time DESC
LIMIT 10;

-- 升级窗口内暂停重 Merge，降低磁盘与 CPU 竞争
SYSTEM STOP MERGES analytics.events_local;
-- 维护完成后恢复
SYSTEM START MERGES analytics.events_local;
SELECT database, table, elapsed, progress, num_parts
FROM system.merges WHERE database = 'analytics';

clickhouse-server --version
cp /usr/bin/clickhouse /opt/clickhouse/clickhouse-24.3
tar czf /backup/clickhouse-data-$(date +%F).tar.gz /var/lib/clickhouse
sudo systemctl stop clickhouse-server
cp /opt/clickhouse/clickhouse-24.3 /usr/bin/clickhouse
sudo systemctl start clickhouse-server

-- 回滚前确认没有残留的 mutation 与复制任务
SELECT database, table, mutation_id, is_done, latest_fail_reason
FROM system.mutations WHERE is_done = 0;
SELECT database, table, type, num_tries, last_exception
FROM system.replication_queue WHERE num_tries > 3;

SELECT
    hostName() AS host,
    formatReadableSize(sum(bytes_on_disk)) AS disk_used,
    (SELECT count() FROM system.replicas WHERE absolute_delay > 300) AS lagging,
    (SELECT count() FROM system.merges) AS active_merges
FROM system.parts
WHERE active
GROUP BY host;

-- 集群维度的健康基线
SELECT cluster, count() AS nodes,
       countIf(is_local) AS local_nodes
FROM system.clusters
WHERE cluster LIKE 'analytics%'
GROUP BY cluster;