ClickHouse 生产运维实战：集群部署、副本分片、性能调优与故障排查

shard 数 ≈ ceil(日增数据量 × 保留天数 × 副本数 / (单机可用容量 × 安全水位))

日增 3e9 行 × 300 B ≈ 900 GB/日（未压缩）
ClickHouse 默认 LZ4 压缩比 ~5x，压缩后 ~180 GB/日
保留 180 天 → 32.4 TB/副本
2 副本 → 64.8 TB 总数据
单机按 NVMe 8 TB、安全水位 60% → 可用 4.8 TB
shard 数 = ceil(64.8 / 4.8) = 14

/data/clickhouse/            # 主数据盘，挂 NVMe
  ├── data/                  # parts 数据
  ├── metadata/              # 表结构 SQL
  ├── store/                 # UUID 化存储目录
  ├── tmp/                   # 临时文件（merge/insert）
  ├── user_files/            # file() 表函数读写
  └── format_schemas/        # Protobuf/CapnProto schema
/var/log/clickhouse-server/  # 日志
/etc/clickhouse-server/      # 配置

<path>/data/clickhouse/</path>
<tmp_path>/data/clickhouse/tmp/</tmp_path>
<user_files_path>/data/clickhouse/user_files/</user_files_path>
<format_schema_path>/data/clickhouse/format_schemas/</format_schema_path>

<clickhouse>
    <logger>
        <level>information</level>
        <log>/var/log/clickhouse-keeper/clickhouse-keeper.log</log>
        <errorlog>/var/log/clickhouse-keeper/clickhouse-keeper.err.log</errorlog>
        <size>1000M</size>
        <count>10</count>
    </logger>

    <keeper_server>
        <tcp_port>9181</tcp_port>
        <server_id>1</server_id> <!-- 三个节点分别为 1/2/3 -->
        <log_storage_path>/data/keeper/coordination/log</log_storage_path>
        <snapshot_storage_path>/data/keeper/coordination/snapshots</snapshot_storage_path>

        <coordination_settings>
            <operation_timeout_ms>10000</operation_timeout_ms>
            <session_timeout_ms>30000</session_timeout_ms>
            <raft_logs_level>information</raft_logs_level>
            <!-- 每 100000 条 log 做一次 snapshot，大集群可调大到 1000000 -->
            <snapshot_distance>100000</snapshot_distance>
            <!-- 自动压缩日志 -->
            <reserved_log_items>10000</reserved_log_items>
        </coordination_settings>

        <raft_configuration>
            <server>
                <id>1</id>
                <hostname>keeper-1.example.com</hostname>
                <port>9234</port>
            </server>
            <server>
                <id>2</id>
                <hostname>keeper-2.example.com</hostname>
                <port>9234</port>
            </server>
            <server>
                <id>3</id>
                <hostname>keeper-3.example.com</hostname>
                <port>9234</port>
            </server>
        </raft_configuration>
    </keeper_server>
</clickhouse>

<clickhouse>
    <zookeeper>
        <node index="1"><host>keeper-1.example.com</host><port>9181</port></node>
        <node index="2"><host>keeper-2.example.com</host><port>9181</port></node>
        <node index="3"><host>keeper-3.example.com</host><port>9181</port></node>
        <session_timeout_ms>30000</session_timeout_ms>
        <operation_timeout_ms>10000</operation_timeout_ms>
    </zookeeper>
</clickhouse>

# 健康检查
echo mntr | nc keeper-1.example.com 9181

# 看当前 leader
echo stat | nc keeper-1.example.com 9181 | grep Mode

# 看连接数、pending 请求
echo cons | nc keeper-1.example.com 9181
echo wchc | nc keeper-1.example.com 9181 | head

-- 先确认 Keeper 自身恢复
-- 然后在任意副本上触发重连
SYSTEM RESTART REPLICA db.table;

-- 如果一批表都挂了
SYSTEM RESTART REPLICAS;

-- 观察同步进度
SELECT database, table, is_readonly, absolute_delay, queue_size
FROM system.replicas WHERE is_readonly = 1 OR absolute_delay > 60;

-- 强制从其他副本拉取完整数据
SYSTEM DROP REPLICA 'replica_name' FROM TABLE db.table;
-- 然后在故障副本上 DETACH/ATTACH 触发全量同步
DETACH TABLE db.table;
ATTACH TABLE db.table;

CREATE TABLE events_raw
(
    event_date    Date,
    event_time    DateTime64(3),
    tenant_id     UInt32,
    user_id       UInt64,
    event_name    LowCardinality(String),
    properties    String,  -- JSON 字符串
    _ingest_time  DateTime DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, event_name, user_id)
SETTINGS index_granularity = 8192;

CREATE TABLE users_cdc
(
    user_id    UInt64,
    email      String,
    updated_at DateTime,
    _version   UInt64
)
ENGINE = ReplacingMergeTree(_version)
ORDER BY user_id;

-- 方式一：FINAL（性能差，单线程合并）
SELECT * FROM users_cdc FINAL WHERE user_id = 123;

-- 方式二：argMax（推荐，并行执行）
SELECT user_id,
       argMax(email, _version) AS email,
       max(_version) AS _version
FROM users_cdc
WHERE user_id = 123
GROUP BY user_id;

CREATE TABLE events_by_hour
(
    tenant_id   UInt32,
    event_hour  DateTime,
    event_name  LowCardinality(String),
    pv          UInt64,
    uv_hll      AggregateFunction(uniq, UInt64)
)
ENGINE = SummingMergeTree((pv))  -- 只对 pv 求和
PARTITION BY toYYYYMM(event_hour)
ORDER BY (tenant_id, event_hour, event_name);

CREATE TABLE events_agg
(
    tenant_id   UInt32,
    event_hour  DateTime,
    event_name  LowCardinality(String),
    pv_state    AggregateFunction(sum, UInt64),
    uv_state    AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(event_hour)
ORDER BY (tenant_id, event_hour, event_name);

-- 查询时用 -Merge 后缀还原最终值
SELECT tenant_id,
       event_hour,
       sumMerge(pv_state)  AS pv,
       uniqMerge(uv_state) AS uv
FROM events_agg
WHERE event_hour >= now() - INTERVAL 1 DAY
GROUP BY tenant_id, event_hour;

CREATE TABLE orders_collapsing
(
    order_id  UInt64,
    status    LowCardinality(String),
    amount    Decimal(18, 4),
    sign      Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY order_id;

数据是否需要更新？
├─ 否 → MergeTree（原始事实表）
└─ 是
   ├─ 同 key 只保留最新一条 → ReplacingMergeTree
   ├─ 同 key 数值求和         → SummingMergeTree
   ├─ 通用聚合（HLL/分位数）    → AggregatingMergeTree
   └─ 有明确的 insert/delete 对 → VersionedCollapsingMergeTree

CREATE TABLE events_local ON CLUSTER analytics_cluster
(
    event_date Date,
    tenant_id  UInt32,
    event_name LowCardinality(String),
    user_id    UInt64
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/{database}/events_local',
    '{replica}'
)
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, event_name);

<!-- /etc/clickhouse-server/config.d/macros.xml -->
<clickhouse>
    <macros>
        <cluster>analytics_cluster</cluster>
        <shard>01</shard>
        <replica>shard01-replica-a</replica>
    </macros>
</clickhouse>

SELECT database, table, replica_name, queue_size, inserts_in_queue,
       merges_in_queue, absolute_delay, total_replicas, active_replicas
FROM system.replicas
WHERE absolute_delay > 10 OR queue_size > 100;

-- 第一步：看原因
SELECT database, table, is_readonly, is_session_expired,
       last_exception, replica_is_active
FROM system.replicas WHERE is_readonly = 1;

-- 第二步：尝试 restart
SYSTEM RESTART REPLICA db.events_local;

-- 第三步：如果还是 readonly，且磁盘数据完整
DETACH TABLE db.events_local;
ATTACH TABLE db.events_local;

-- 第四步：如果本地数据损坏，放弃本地从其他副本重建
-- 在另一个健康副本上先清理当前副本的 Keeper 注册
SYSTEM DROP REPLICA 'shard01-replica-a' FROM TABLE db.events_local;
-- 然后在故障节点上重建表，会从其他副本拉取

-- 先创建本地副本表（每个 shard 的每个副本）
CREATE TABLE db.events_local ON CLUSTER analytics_cluster
(
    event_date Date,
    tenant_id  UInt32,
    event_name LowCardinality(String),
    user_id    UInt64,
    amount     Decimal(18, 4)
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/{database}/events_local',
    '{replica}'
)
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, event_name);

-- 再创建分布式表
CREATE TABLE db.events_dist ON CLUSTER analytics_cluster
AS db.events_local
ENGINE = Distributed(
    analytics_cluster,      -- 集群名（remote_servers 里定义）
    db,                     -- 本地库
    events_local,           -- 本地表
    cityHash64(tenant_id)   -- sharding key
);

<clickhouse>
    <remote_servers>
        <analytics_cluster>
            <shard>
                <internal_replication>true</internal_replication>
                <replica><host>shard01-a.example.com</host><port>9000</port></replica>
                <replica><host>shard01-b.example.com</host><port>9000</port></replica>
            </shard>
            <shard>
                <internal_replication>true</internal_replication>
                <replica><host>shard02-a.example.com</host><port>9000</port></replica>
                <replica><host>shard02-b.example.com</host><port>9000</port></replica>
            </shard>
        </analytics_cluster>
    </remote_servers>
</clickhouse>

SET insert_distributed_sync = 0;           -- 异步转发
SET distributed_background_insert_sleep_time_ms = 100;
SET distributed_background_insert_max_sleep_time_ms = 30000;

-- 错误：右表在每个 shard 上都是本 shard 的局部数据
SELECT a.tenant_id, b.company_name
FROM events_dist a
JOIN tenants_dist b ON a.tenant_id = b.tenant_id;

-- 正确：GLOBAL JOIN 会把右表结果广播到所有 shard
SELECT a.tenant_id, b.company_name
FROM events_dist a
GLOBAL JOIN tenants_dist b ON a.tenant_id = b.tenant_id;

# 伪代码：积攒到阈值或超时就 flush
buffer = []
MAX_ROWS = 500_000
MAX_WAIT_MS = 5000

def on_message(row):
    buffer.append(row)
    if len(buffer) >= MAX_ROWS or elapsed_ms() >= MAX_WAIT_MS:
        flush()

def flush():
    ch_client.insert("events_local", buffer)
    buffer.clear()

SET async_insert = 1;
SET wait_for_async_insert = 1;       -- 同步等待确认
SET async_insert_max_data_size = 10_000_000;   -- 10 MB 或
SET async_insert_busy_timeout_ms = 1000;       -- 1 秒 flush

CREATE TABLE events_buffer AS events_local
ENGINE = Buffer(db, events_local,
    16,           -- 并发 layer 数
    10, 60,       -- min/max 秒数
    10000, 1000000, -- min/max 行数
    10000000, 100000000); -- min/max 字节

-- 1. Kafka 引擎表，只是个消费者代理
CREATE TABLE kafka_events_source
(
    event_date Date,
    tenant_id  UInt32,
    event_name String,
    user_id    UInt64,
    amount     Decimal(18, 4)
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'kafka-1.example.com:9092,kafka-2.example.com:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'ch_events_consumer',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4,
    kafka_thread_per_consumer = 1,
    kafka_max_block_size = 1048576,
    kafka_poll_max_batch_size = 65536;

-- 2. 目标 ReplicatedMergeTree 表
CREATE TABLE events_local ON CLUSTER analytics_cluster
(
    event_date Date,
    tenant_id  UInt32,
    event_name LowCardinality(String),
    user_id    UInt64,
    amount     Decimal(18, 4)
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/{database}/events_local',
    '{replica}'
)
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, event_name);

-- 3. 物化视图作为"搬运工"
CREATE MATERIALIZED VIEW mv_kafka_to_events TO events_local AS
SELECT event_date, tenant_id, event_name, user_id, amount
FROM kafka_events_source;

-- 临时放宽（不推荐长期）
ALTER TABLE events_local MODIFY SETTING parts_to_throw_insert = 1000;

-- 触发所有分区 merge
OPTIMIZE TABLE events_local PARTITION '202603' FINAL;

EXPLAIN indexes = 1
SELECT count() FROM events_local
WHERE tenant_id = 123 AND event_date >= '2026-03-01';

ALTER TABLE events_local
ADD INDEX idx_user_id user_id TYPE minmax GRANULARITY 4;

ALTER TABLE events_local
ADD INDEX idx_event_name event_name TYPE set(100) GRANULARITY 4;

ALTER TABLE events_local
ADD INDEX idx_url url TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 4;

-- 新添加的索引只对新数据生效，对存量数据要物化
ALTER TABLE events_local MATERIALIZE INDEX idx_user_id;

-- 写法一：WHERE
SELECT tenant_id, user_id, properties
FROM events_local
WHERE event_date = '2026-03-15' AND amount > 1000;

-- 写法二：PREWHERE
SELECT tenant_id, user_id, properties
FROM events_local
PREWHERE event_date = '2026-03-15' AND amount > 1000;

ALTER TABLE events_local
ADD PROJECTION proj_user
(
    SELECT
        tenant_id,
        user_id,
        count() AS events_cnt,
        sum(amount) AS total_amount
    GROUP BY tenant_id, user_id
);

-- 物化存量数据
ALTER TABLE events_local MATERIALIZE PROJECTION proj_user;

CREATE TABLE events_with_tags
(
    event_id UInt64,
    tags     Array(String)
)
ENGINE = MergeTree ORDER BY event_id;

-- 查询每个 tag 的出现次数
SELECT tag, count()
FROM events_with_tags
ARRAY JOIN tags AS tag
GROUP BY tag;

-- 单查询最大内存，默认 10 GB
SET max_memory_usage = 20_000_000_000;

-- 单用户最大内存，多查询共享
SET max_memory_usage_for_user = 40_000_000_000;

-- 启用分布式聚合的中间状态合并优化
SET distributed_aggregation_memory_efficient = 1;

-- 查询优先级（数字越大优先级越低）
SET priority = 1;

-- 超时
SET max_execution_time = 300;

-- 对低基数字符串使用字典编码
SET low_cardinality_allow_in_native_format = 1;

-- 自动选择 PREWHERE 列
SET optimize_move_to_prewhere = 1;

-- 目标聚合表
CREATE TABLE events_hourly_agg ON CLUSTER analytics_cluster
(
    tenant_id  UInt32,
    event_hour DateTime,
    event_name LowCardinality(String),
    pv_state   AggregateFunction(sum,  UInt64),
    uv_state   AggregateFunction(uniq, UInt64),
    amt_state  AggregateFunction(sum,  Decimal(18, 4))
)
ENGINE = ReplicatedAggregatingMergeTree(
    '/clickhouse/tables/{shard}/{database}/events_hourly_agg',
    '{replica}'
)
PARTITION BY toYYYYMM(event_hour)
ORDER BY (tenant_id, event_hour, event_name);

-- MV：源表每次 insert 都会把这段 SELECT 跑一遍
CREATE MATERIALIZED VIEW mv_events_hourly ON CLUSTER analytics_cluster
TO events_hourly_agg AS
SELECT
    tenant_id,
    toStartOfHour(event_time) AS event_hour,
    event_name,
    sumState(toUInt64(1))     AS pv_state,
    uniqState(user_id)        AS uv_state,
    sumState(amount)          AS amt_state
FROM events_local
GROUP BY tenant_id, event_hour, event_name;

SELECT tenant_id, event_hour,
       sumMerge(pv_state)  AS pv,
       uniqMerge(uv_state) AS uv,
       sumMerge(amt_state) AS total_amount
FROM events_hourly_agg
WHERE event_hour >= now() - INTERVAL 7 DAY
GROUP BY tenant_id, event_hour
ORDER BY event_hour DESC;

CREATE MATERIALIZED VIEW mv_x TO target_table
POPULATE AS SELECT ... FROM source_table;

INSERT INTO events_hourly_agg
SELECT tenant_id,
       toStartOfHour(event_time) AS event_hour,
       event_name,
       sumState(toUInt64(1)),
       uniqState(user_id),
       sumState(amount)
FROM events_local
WHERE event_time >= '2026-01-01' AND event_time < '2026-02-01'
GROUP BY tenant_id, event_hour, event_name;

CREATE MATERIALIZED VIEW mv_tenant_daily
REFRESH EVERY 1 HOUR
TO tenant_daily AS
SELECT tenant_id,
       toDate(event_time) AS event_date,
       count()            AS events_cnt,
       uniq(user_id)      AS unique_users
FROM events_local
WHERE event_time >= now() - INTERVAL 7 DAY
GROUP BY tenant_id, event_date;

ALTER TABLE mv_events_hourly MODIFY QUERY ...;

ALTER TABLE events_local
MODIFY COLUMN properties String TTL event_date + INTERVAL 30 DAY;

ALTER TABLE events_local
MODIFY TTL event_date + INTERVAL 180 DAY;

<clickhouse>
    <storage_configuration>
        <disks>
            <hot>
                <path>/data/clickhouse/</path>
            </hot>
            <cold_s3>
                <type>s3</type>
                <endpoint>https://s3.example.com/ch-cold/</endpoint>
                <access_key_id>AKIAxxxxxxxx</access_key_id>
                <secret_access_key>xxxxxxxxxxxxxxxx</secret_access_key>
                <metadata_path>/data/clickhouse/disks/cold_s3/</metadata_path>
                <cache_enabled>true</cache_enabled>
                <data_cache_size>107374182400</data_cache_size>
            </cold_s3>
        </disks>

        <policies>
            <tiered>
                <volumes>
                    <hot>
                        <disk>hot</disk>
                    </hot>
                    <cold>
                        <disk>cold_s3</disk>
                    </cold>
                </volumes>
                <move_factor>0.2</move_factor>
            </tiered>
        </policies>
    </storage_configuration>
</clickhouse>

ALTER TABLE events_local
MODIFY TTL
    event_date + INTERVAL 30  DAY TO VOLUME 'cold',
    event_date + INTERVAL 180 DAY DELETE
SETTINGS storage_policy = 'tiered';

SELECT database, table, partition, rows, bytes_on_disk, move_ttl_info
FROM system.parts
WHERE active AND has(column_names, 'properties')
  AND bytes_on_disk > 0
ORDER BY bytes_on_disk DESC
LIMIT 20;

-- 正在跑的 TTL merge
SELECT * FROM system.merges WHERE merge_type = 'TTL_DELETE';

<query_log>
    <database>system</database>
    <table>query_log</table>
    <partition_by>toYYYYMM(event_date)</partition_by>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <max_size_rows>1048576</max_size_rows>
</query_log>

SELECT
    query_duration_ms,
    read_rows,
    formatReadableSize(read_bytes)          AS read_bytes,
    formatReadableSize(memory_usage)        AS memory,
    result_rows,
    user,
    client_hostname,
    substring(query, 1, 200)                AS query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 20;

SELECT
    normalized_query_hash,
    any(substring(query, 1, 200)) AS sample,
    count()                        AS cnt,
    avg(query_duration_ms)         AS avg_ms,
    quantile(0.95)(query_duration_ms) AS p95_ms,
    sum(read_rows)                 AS total_rows
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 DAY
GROUP BY normalized_query_hash
ORDER BY p95_ms DESC
LIMIT 20;

SELECT
    thread_id,
    query_duration_ms,
    memory_usage,
    ProfileEvents['RealTimeMicroseconds'] AS real_us,
    ProfileEvents['UserTimeMicroseconds'] AS user_us
FROM system.query_thread_log
WHERE event_time >= now() - INTERVAL 1 HOUR
  AND initial_query_id = 'xxxx-xxxx-xxxx-xxxx'
ORDER BY thread_id;

SELECT event_time,
       CurrentMetric_MemoryTracking / 1e9  AS mem_gb,
       CurrentMetric_Query                 AS running_queries,
       CurrentMetric_Merge                 AS running_merges,
       ProfileEvent_SelectedRows           AS selected_rows
FROM system.metric_log
WHERE event_time >= now() - INTERVAL 1 HOUR
ORDER BY event_time DESC;

-- 语法树
EXPLAIN AST SELECT ...;

-- 逻辑计划
EXPLAIN SYNTAX SELECT ...;

-- 执行计划
EXPLAIN PLAN SELECT ...;

-- 估计的索引命中情况（最有用）
EXPLAIN indexes = 1 SELECT ...;

-- 估计的数据读取
EXPLAIN estimate SELECT ...;

-- Pipeline 物理执行
EXPLAIN PIPELINE SELECT ...;

echo "SELECT count() FROM events_local WHERE tenant_id = 123 AND event_date >= '2026-03-01'" \
  | clickhouse-benchmark \
    --host=ch.example.com \
    --port=9000 \
    --user=default \
    --iterations=100 \
    --concurrency=8 \
    --continue_on_errors

SELECT event_time,
       trace_type,
       arrayStringConcat(
           arrayMap(x -> concat(addressToLine(x), '#', demangle(addressToSymbol(x))), trace),
           ';') AS stack
FROM system.trace_log
WHERE query_id = 'xxxx' AND trace_type = 'CPU'
ORDER BY event_time;

clickhouse-client --query="
SELECT arrayStringConcat(
    arrayMap(x -> concat(demangle(addressToSymbol(x)), '_[k]'), trace),
    ';') AS stack,
    count() AS cnt
FROM system.trace_log
WHERE query_id = 'xxxx' AND trace_type = 'CPU'
GROUP BY stack
" --format=TabSeparated \
  | flamegraph.pl > query.svg

<prometheus>
    <endpoint>/metrics</endpoint>
    <port>9363</port>
    <metrics>true</metrics>
    <events>true</events>
    <asynchronous_metrics>true</asynchronous_metrics>
    <status_info>true</status_info>
</prometheus>

- job_name: clickhouse
  static_configs:
    - targets:
        - shard01-a.example.com:9363
        - shard01-b.example.com:9363
        - shard02-a.example.com:9363
        - shard02-b.example.com:9363
  metrics_path: /metrics

groups:
- name: clickhouse
  rules:
  - alert: ClickHouseReplicaReadOnly
    expr: ClickHouseMetrics_ReadonlyReplica > 0
    for: 5m
    annotations:
      summary: "ClickHouse 副本进入只读 ({{ $labels.instance }})"

  - alert: ClickHouseReplicaLag
    expr: ClickHouseAsyncMetrics_ReplicasMaxAbsoluteDelay > 120
    for: 10m
    annotations:
      summary: "ClickHouse 副本延迟 > 2 分钟"

  - alert: ClickHousePartCountHigh
    expr: ClickHouseAsyncMetrics_MaxPartCountForPartition > 200
    for: 15m
    annotations:
      summary: "ClickHouse 单分区 part 数过多，即将触发 Too many parts"

  - alert: ClickHouseRejectedInserts
    expr: rate(ClickHouseProfileEvents_RejectedInserts[5m]) > 0
    for: 5m
    annotations:
      summary: "ClickHouse 有 insert 被拒绝"

  - alert: ClickHouseDistributedFilesToInsertHigh
    expr: ClickHouseMetrics_DistributedFilesToInsert > 10000
    for: 10m
    annotations:
      summary: "分布式表待转发文件数过高，下游可能挂了"

  - alert: ClickHouseKeeperConnectionLost
    expr: rate(ClickHouseProfileEvents_ZooKeeperHardwareExceptions[5m]) > 0
    for: 5m
    annotations:
      summary: "ClickHouse Keeper 连接异常"

-- 备份到本地路径（需要在 <backups> 配置中允许）
BACKUP TABLE db.events_local TO Disk('backups', 'events_local_20260315.zip');

-- 备份到 S3
BACKUP TABLE db.events_local TO S3(
    'https://s3.example.com/ch-backups/events_local/20260315/',
    'AKIAxxxxxxxx',
    'xxxxxxxxxxxxxxxx'
);

-- 全库备份
BACKUP DATABASE db TO S3(...);

-- 集群备份
BACKUP DATABASE db ON CLUSTER analytics_cluster TO S3(...);

RESTORE TABLE db.events_local FROM Disk('backups', 'events_local_20260315.zip');

RESTORE TABLE db.events_local AS db.events_local_restore
FROM S3('https://s3.example.com/ch-backups/events_local/20260315/', ...);

<backups>
    <allowed_path>/data/clickhouse/backups/</allowed_path>
    <allowed_disk>backups</allowed_disk>
</backups>

<storage_configuration>
    <disks>
        <backups>
            <type>local</type>
            <path>/data/clickhouse/backups/</path>
        </backups>
    </disks>
</storage_configuration>

general:
  remote_storage: s3
  max_file_size: 0
  backups_to_keep_local: 3
  backups_to_keep_remote: 30

clickhouse:
  username: backup_user
  password: xxxxxxxx
  host: localhost
  port: 9000

s3:
  access_key: AKIAxxxxxxxx
  secret_key: xxxxxxxxxxxxxxxx
  bucket: ch-backups
  endpoint: https://s3.example.com
  region: us-east-1
  path: /analytics-cluster/{shard}
  compression_level: 3
  compression_format: lz4

# 创建本地快照
clickhouse-backup create daily_$(date +%F)

# 上传到 S3
clickhouse-backup upload daily_$(date +%F)

# 列出备份
clickhouse-backup list

# 下载并恢复
clickhouse-backup download daily_20260315
clickhouse-backup restore daily_20260315 --rm

-- 临时放宽
ALTER TABLE events_local
MODIFY SETTING parts_to_throw_insert = 600,
               parts_to_delay_insert = 300;

-- 增大后台 merge 池（需要重启）
-- <background_pool_size>32</background_pool_size>

-- 强制合并
OPTIMIZE TABLE events_local PARTITION '202603' FINAL;

SELECT database, table, elapsed,
       progress, num_parts, total_size_bytes_compressed / 1024 / 1024 AS mb,
       merge_type
FROM system.merges
ORDER BY elapsed DESC;

-- 看哪个表最拖后腿
SELECT database, table, count() AS running
FROM system.merges
GROUP BY database, table
ORDER BY running DESC;

-- 查看 mutation 列表
SELECT database, table, mutation_id, command,
       create_time, is_done, parts_to_do, latest_failed_part, latest_fail_reason
FROM system.mutations
WHERE is_done = 0;

-- 尝试 kill
KILL MUTATION WHERE mutation_id = 'mutation_12345.txt';

-- 如果真的卡死，最后手段：手动删 Keeper 上的 mutation znode
-- 先 stop 掉副本表 replication
SYSTEM STOP REPLICATED SENDS db.events_local;
-- 删 znode（危险操作，先在测试集群练手）
-- /usr/bin/clickhouse-keeper-client ...
-- SYSTEM START REPLICATED SENDS db.events_local;

-- 查看 DDL 队列
SELECT * FROM system.distributed_ddl_queue
WHERE cluster = 'analytics_cluster'
ORDER BY query_create_time DESC
LIMIT 10;

-- 如果确认 DDL 已经在大多数节点完成，只是个别节点超时
-- 不需要做什么，超时的节点会在恢复后继续执行队列

-- 如果要强制跳过某个节点（危险）
-- 删除 Keeper 上对应节点的 finished 记录让它重新执行，或者
-- 把那个节点从 macros/remote_servers 临时移除

-- 临时放宽
SET max_partitions_per_insert_block = 1000;

-- 看会话状态
SELECT * FROM system.zookeeper_connection;

-- 重启副本
SYSTEM RESTART REPLICA db.events_local;

-- 看有没有正在跑的查询
SELECT query_id, user, query FROM system.processes WHERE has(tables, 'db.events_local');

-- kill 掉
KILL QUERY WHERE query_id = 'xxxx';

-- 也可以用 DROP TABLE ... SYNC 强制同步
DROP TABLE db.events_local SYNC;