Alibabacloud Cksync Plan

ClickHouse Sync Plan (cksync-plan)

A skill for planning ClickHouse cluster data migration solutions, including migration plans, risks, and considerations.

When to Use

• Data migration between different ClickHouse clusters
• Horizontal scaling (adding/removing nodes) for ClickHouse clusters
• Disk downgrade operations
• Cross-availability zone migrations
• Upgrading to multi-replica, multi-AZ deployments

Workflow

Step 1: Gather Source Cluster Information

Ask user for source cluster type:

• Self-built ClickHouse or non-Alibaba Cloud ClickHouse
• Alibaba Cloud ClickHouse Community Edition
• Alibaba Cloud ClickHouse Enterprise Edition

Ask user for source cluster version (e.g., 20.8, 22.8, 23.8, 24.3):

• Version affects migration method compatibility
• BACKUP/RESTORE requires ≥22.8
• Incremental cksync migration requires target ≥20.8

Step 2: Gather Target Cluster Information

Ask user for target cluster type:

• Alibaba Cloud ClickHouse Community Edition
• Alibaba Cloud ClickHouse Enterprise Edition
• To be determined

Step 3: Collect Cluster Details (REQUIRED)

This step is mandatory. You MUST collect database and table information before proceeding to migration plan selection.

Required Information

1. Database list with engines 2. Table list with engines, partition counts, data sizes, and write speeds

Option A: User Executes SQL

Provide SQL queries from references/sql.md section 1 for user to execute:

1. Database Information - Query system.databases for database names and engines 2. Table Information - Comprehensive query including table names, engines, engine_full (for TTL), partition counts, data sizes, and write speeds

Key fields to collect:

• engine_full: Contains TTL clause (e.g., TTL event_time + INTERVAL 7 DAY)
• part_count: Partition count per table
• data_bytes: Data size per shard
• write_speed_bytes_per_sec: Write speed calculated from part_log

For complete SQL queries, see references/sql.md section 1.

Option B: Direct Query via HTTP

Request connection details from user:

• HOST_NAME: Cluster endpoint (e.g., cc-xxx.clickhouse.rds.aliyuncs.com)
• HTTP_PORT: HTTP port (default: 8123)
• USER_NAME: Database username
• PASSWORD: Database password

Use secure credential handling and HTTP query examples from references/sql.md section 5.

Analysis Checklist

After collecting data, verify:

• [ ] Required metadata is complete (database engine, table engine, engine_full, partitions, data size, write speed)
• [ ] Migration compatibility checks are completed using references/plans.md (method-specific conditions)
• [ ] Version and read-only window constraints are mapped to candidate methods
• [ ] Risks and mitigations are identified and recorded in the plan

Step 4: Business Requirements

Ask for allowed read-only time:

• 0 minutes
• Within 30 minutes
• Within 1 day
• Not sure yet

Step 5: Select and Present Migration Plan

Based on gathered information, analyze and recommend from these migration methods:

Hard requirement: MUST output a plan, never output empty content.

Even when information is incomplete, you MUST output a provisional migration plan. The provisional plan must include:

• assumptions used,
• missing-information checklist,
• confidence level and key uncertainties,
• next steps to finalize recommendation after user provides missing inputs.

Migration Methods Overview

1. Console (cksync) Migration

Default choice for most Alibaba Cloud migration scenarios, especially in-place operations. For support boundaries, engine constraints, TTL/write-speed checks, merge risk, and resource prerequisites, see references/plans.md section 1.

2. BACKUP/RESTORE Migration

Suitable for same-edition migrations where full backup/restore workflow is acceptable. For version/edition constraints, supported engines, command patterns, and progress monitoring, see references/plans.md section 2.

3. INSERT FROM REMOTE Migration

Best when fine-grained table/partition/time-range control is needed. For applicability boundaries and operational constraints, see references/plans.md section 3. For SQL templates and detailed steps, see references/sql.md section 2.

4. Business Double-Write

Use when zero downtime is required and application-side dual-write is feasible. For detailed conditions, see references/plans.md section 4.

5. Kafka Double-Write

Use when dual-consumer switchover via Kafka is feasible, including both existing Kafka pipelines and cases where business writes can be switched to Kafka first. For detailed conditions, see references/plans.md section 5.

6. Big Cluster Federation

Advanced option for large/complex migrations with strong business and technical collaboration.

• Community + Enterprise: See references/big-cluster-community-enterprise.md
• Self-built + Cloud: See references/big-cluster-self-built-community.md

Output Format

Default deliverable: Produce one migration plan only. Structure it using assets/migration-plan-template.md and include the key sections below (cluster facts and commands may appear inline in the plan; that counts as the single deliverable).

Additional files only on request: Do not create separate files for cluster-information documentation, scripts, or SQL unless the customer explicitly asks for them. When they do, use assets/cluster-info-template.md for cluster documentation and place scripts/SQL in clearly named files as requested.

Key sections in the migration plan: 1. Executive Summary - Method, data size, duration, downtime 2. Source Cluster Analysis - Databases, tables, compatibility check 3. Migration Method Selection - Rationale and alternatives 4. Migration Steps - Pre/execution/post with commands 5. Risks & Mitigations - With probability and impact 6. Rollback Plan - Trigger conditions and steps 7. Timeline - Phase schedule with owners 8. Reference Links - Documentation URLs

Method Selection Reference

For quick scenario-to-method mapping and method-specific constraints (including in-place migration priority and Enterprise → Enterprise options), see references/plans.md section "Method Selection Priority" and related method sections.

Additional Resources

• references/plans.md - Detailed migration plan conditions
• references/sql.md - SQL templates and commands
• references/stop-merge-storm.md - How to stop post-sync merge storm
• references/big-cluster-community-enterprise.md - Community + Enterprise federation
• references/big-cluster-self-built-community.md - Self-built + Cloud federation

Cluster Information Template

Use this template to document source cluster database and table information.

Template Constraints

IMPORTANT: When filling this template:

- Output language - If not explicitly specified, use the same language as the main conversation (e.g., if user speaks Chinese, output in Chinese; if English, output in English)

- Copy SQL query results directly into the tables below

- Keep all table formatting intact

- Fill in all applicable fields

- Local references → Read, analyze, write as needed - When referencing local files (e.g., references/*.md), read and analyze the content, then write relevant SQL/content into the output file as needed

- Public URLs → Link directly - Public web URLs (e.g., help.aliyun.com) can be directly linked

---

Cluster Overview

---

Database Information

Execute SQL from references/sql.md Section 1.1 and paste ALL results:

---

Table Information

Execute SQL from references/sql.md Section 1.2 and paste ALL results:

---

Formatted Table Summary

Status Legend:

• ✅ Supported - No issues
• ⚠️ Warning - Requires attention (e.g., TTL ≤3 days, high write speed)
• ❌ Not Supported - Cannot migrate with cksync (e.g., MaterializedMySQL, Kafka engine)

---

Compatibility Checklist

Migration Plan Template

Use this template when generating migration plans. Copy and fill in the sections below.

Template Constraints

IMPORTANT: When generating migration plans based on this template:

- Output language - If not explicitly specified, use the same language as the main conversation (e.g., if user speaks Chinese, output in Chinese; if English, output in English)

- DO NOT modify any hyperlinks - Keep all 'help.aliyun.com' URLs exactly as provided to avoid broken links

- Local references → Read, analyze, write as needed - When referencing local files (e.g., references/*.md), read and analyze the content, then write relevant parts into the output file as needed; referenceing local files is not allowed in the chapter Reference Links.

- Public URLs → Link directly - Public web URLs (e.g., help.aliyun.com) can be directly linked without copying content

- Non-Console migration + SQL/script - If the Recommended Method is not Console (cksync) and the migration relies on SQL or scripts (e.g., INSERT FROM REMOTE, BACKUP/RESTORE, double-write, big-cluster paths), the generated plan must include the SQL and/or scripts that correspond to that method and this migration (steps, commands, parameters; use placeholders for secrets). Derive accurate syntax via the Local references rule above. If the chosen path is Console (cksync) only, this requirement does not apply.

---

Migration Plan: [Cluster Name / Instance ID]

Generated: [YYYY-MM-DD] Planner: cksync-plan skill Source: [Source Cluster Type] v[Version] ([Instance ID]) Target: [Target Cluster Type] ([Instance ID or "To be created"])

---

1. Executive Summary

---

2. Source Cluster Analysis

2.1 Cluster Information

2.2 Database Summary

2.3 Table Details

2.4 Compatibility Check

2.5 Target Cluster Sizing (For cksync)

---

3. Migration Method Selection

3.1 Selected Method

[Method Name]

3.2 Selection Rationale

3.3 Alternatives Considered

---

4. Migration Steps

4.1 Pre-Migration Checklist

• [ ] Verify source cluster accessibility
• [ ] Create target cluster with appropriate specs
• [ ] Configure network connectivity between clusters
• [ ] Verify SQL compatibility (if version differs)
• [ ] Back up critical data
• [ ] Notify stakeholders of migration window

4.2 Migration Execution

4.2.1 SQL and Scripts (Required when NOT Console/cksync)

Skip this subsection if the Recommended Method is Console (cksync) and no manual SQL/scripts are used.

State briefly how SQL/scripts map to the chosen method, if not already fully covered by the Section 4.2 table. The Command/Details column must contain the actual SQL and/or script content (or clearly scoped snippets) needed for this migration, not only step titles.

4.3 Post-Migration Verification

Verify row counts using methods from references/sql.md Section 3:

• Section 3.1 Table-Level Row Count (for MergeTree/ReplicatedMergeTree without DROP/TRUNCATE/DELETE/TTL)
• Section 3.2 Partition-Level Row Count (when table-level may not match)
• Section 3.3 Accurate Count with FINAL (for merging engines, data operations, TTL)
• Section 3.4 Query Result Verification (hash comparison)

• [ ] Verify row counts match between source and target
• [ ] Run sample queries on target cluster
• [ ] Verify application connectivity to target
• [ ] Monitor target cluster performance

4.4 Merge Storm Mitigation (Only for Console/cksync)

Skip this section if NOT using Console (cksync) migration method.

IMPORTANT: cksync stops ALL merges during sync. After completion, pending merges start simultaneously causing high CPU/IO/memory.

Step 1: Pre-analyze part sizes on source cluster (before migration)

See references/stop-merge-storm.md Step 1 for SQL.

Step 2: Apply merge limits on target cluster (immediately after sync)

See references/stop-merge-storm.md Step 3 for SQL.

• [ ] Applied merge limits to top 5 tables
• [ ] Verified CPU/IO/memory stable

Step 3: Gradually restore merge settings (after stabilization)

See references/stop-merge-storm.md Step 4 for approach.

4.5 Traffic Switchover

• [ ] Stop writes to source cluster
• [ ] Wait for final sync completion
• [ ] Update application connection strings
• [ ] Verify application functionality
• [ ] Monitor for errors

---

5. Risks & Mitigations

---

6. Rollback Plan

6.1 Trigger Conditions

• Data verification fails (row count mismatch > X%)
• Application errors exceed threshold
• Performance degradation on target cluster
• Business-critical functionality broken

6.2 Rollback Steps

6.3 Rollback Time Estimate

[X minutes / hours]

---

7. Timeline

---

8. Reference Links

• Alibaba Cloud ClickHouse Documentation: [URL]
• Migration method guide: [URL]
• Compatibility verification: https://help.aliyun.com/zh/clickhouse/user-guide/analysis-and-solution-of-cloud-compatibility-and-performance-bottleneck-of-self-built-clickhouse

Community + Enterprise Big Cluster Federation

Architecture Overview

Cluster Definitions

• community: Community Edition ClickHouse cluster
• enterprise: Enterprise Edition ClickHouse cluster
• federation: Combined cluster of Community and Enterprise

Data Flow

• Read: Query from Community's federation distributed table (includes both clusters)
• Write: Can write through Community or Enterprise; gradually switch to Enterprise

Important Notes

1. Distributed DDL across Community and Enterprise is NOT supported - execute separately 2. Do NOT use optimize_skip_unused_shards=1 when querying

---

Migration Steps

Step 1: Compatibility Verification

1. Configure Enterprise compatibility parameter to match Community version:

CREATE SETTINGS PROFILE compatibility SETTINGS compatibility='24.3' TO ALL;

2. Verify Enterprise can execute Community queries correctly

Reference: https://help.aliyun.com/zh/clickhouse/user-guide/analysis-and-solution-of-cloud-compatibility-and-performance-bottleneck-of-self-built-clickhouse

3. (Optional) Modify business SQL for compatibility if needed

---

Step 2: Network Configuration

1. Enterprise Console: Add Community network segment to whitelist

2. Enterprise Keeper: Register Community node IPs for password-free access

set /clickhouse/networks '<ip>::1</ip><ip>127.0.0.1</ip><ip><community_node_ip_1></ip><ip><community_node_ip_2></ip><ip><community_node_ip_3></ip><ip><community_node_ip_4></ip>'

Warning: Enterprise CCU scaling operations will override this configuration. Do not scale during migration.

3. Community config.xml: Add Enterprise nodes to remote_servers

<remote_servers>
  <default>
    <shard>
      <!--Community shard info-->
    </shard>
    <shard>
      <!--Community shard info-->
    </shard>
    ...
    <shard> <!-- Add Enterprise VPC address or endpoint -->
      <replica>
        <host><enterprise_endpoint_or_vpc_ip></host>
        <port>9000</port>
      </replica>
      <internal_replication>true</internal_replication>
    </shard>
  </default>
</remote_servers>

---

Step 3: Business Switchover

1. Write data: Switch to Enterprise 2. Read data: Use Community's federation distributed table

---

Step 4: Node Decommission

1. Gradually remove Community nodes from config.xml 2. Note: Query aggregation still happens on Community, may affect performance 3. Evaluate minimum Community nodes needed for business

---

Step 5: Final Switchover

1. Wait for Community data to expire (based on TTL) 2. Switch read traffic to Enterprise

---

Rollback Plan

From Step 2

• Remove Enterprise nodes from Community config.xml
• Export Enterprise data back to Community using "INSERT FROM REMOTE" method

From Step 3

• Switch writes back to Community
• Export Enterprise data back to Community

From Step 5

• Switch reads back to Community

Self-Built + Cloud ClickHouse Big Cluster Federation

Background

Self-built ClickHouse migration has complexities:

• Large data volume → slow migration
• No time-based partitioning → cannot migrate by partition
• Double-write logic increases business complexity

Applicable Scenarios

• Large data volume
• Short allowed write-stop time (<20 min) or no write-stop allowed
• Other table engines besides MergeTree (e.g., Log engine)
• Acceptable cost of running two clusters
• High customer participation; willing to modify business read/write SQL

Architecture

Cluster Definitions

• customer: Self-built ClickHouse cluster
• cloud: Alibaba Cloud ClickHouse cluster
• federation: Combined cluster of self-built and cloud

Data Flow

• Write: To cloud cluster's distributed table
• Read: From federation cluster's distributed table

ReplacingMergeTree Considerations

For tables requiring data on same node: 1. After full data migration to cloud, query from cloud cluster instead of federation 2. Change FINAL to GROUP BY

---

Migration Steps

Step 1: Whitelist Configuration

Enable IP whitelist between self-built and cloud ClickHouse.

---

Step 2: Configure config.xml and users.xml

config.xml - Add federation cluster configuration:

<?xml version="1.0" ?>
<yandex>
  <!-- Other configurations... -->
  
  <listen_host>0.0.0.0</listen_host>

  <remote_servers>
    <!-- Federation cluster: includes both self-built and cloud nodes -->
    <federation>
       <!-- Self-built nodes -->
       <shard>
         <replica>
           <host><self_built_node_ip_1></host>
           <port>9000</port>
           <user>default</user>
           <password>password</password>
         </replica>
         <internal_replication>true</internal_replication>
       </shard>
       <shard>
         <replica>
           <host><self_built_node_ip_2></host>
           <port>9000</port>
           <user>default</user>
           <password>password</password>
         </replica>
         <internal_replication>true</internal_replication>
       </shard>
       <shard>
         <replica>
           <host><self_built_node_ip_3></host>
           <port>9000</port>
           <user>default</user>
           <password>password</password>
         </replica>
         <internal_replication>true</internal_replication>
       </shard>
       <!-- Cloud nodes -->
       <shard>
         <replica>
           <host><cloud_node_ip_1></host>
           <port>3003</port>
           <user>default</user>
           <password>password</password>
         </replica>
         <internal_replication>true</internal_replication>
       </shard>
       <shard>
         <replica>
           <host><cloud_node_ip_2></host>
           <port>3003</port>
           <user>default</user>
           <password>password</password>
         </replica>
         <internal_replication>true</internal_replication>
       </shard>
       <shard>
         <replica>
           <host><cloud_node_ip_3></host>
           <port>3003</port>
           <user>default</user>
           <password>password</password>
         </replica>
         <internal_replication>true</internal_replication>
       </shard>
     </federation>
  </remote_servers>
  
  <!-- Macros for Replicated tables -->
  <macros>
    <shard>s0</shard>
    <replica>r0</replica>
  </macros>
</yandex>

users.xml - Configure network access:

<yandex>
  <users>
    <default>
      <password/>
      <profile>default</profile>
      <quota>default</quota>
      <!-- Allow IPs from all nodes -->
      <networks>
        <host><self_built_access_ip_1></host>
        <host><self_built_access_ip_2></host>
        <host><self_built_access_ip_3></host>
        <host><self_built_node_ip_1></host>
        <host><self_built_node_ip_2></host>
        <host><self_built_node_ip_3></host>
      </networks>
    </default>
  </users>
</yandex>

---

Step 3: Create Tables

1. Cloud cluster: Create same local tables as self-built

2. Cloud cluster: Create distributed table for writes

-- Distributed table uses 'cloud' cluster
CREATE TABLE database.distributed_write ON CLUSTER cloud 
ENGINE = Distributed(cloud, database, local_table, sharding_key);

3. Both clusters: Create distributed table for reads

-- Execute on both clusters; replace <customer|cloud> with appropriate cluster name
-- Distributed table uses 'federation' cluster
CREATE TABLE database.distributed_read ON CLUSTER <customer|cloud> 
ENGINE = Distributed(federation, database, local_table, sharding_key);

---

Step 4: Verification

• Write via distributed_write → Data should go to cloud only
• Read via distributed_read → Should return data from both clusters

---

Step 5: First Business Switchover

• Write to distributed_write
• Read from distributed_read

---

Step 6: (Optional) Migrate Historical Data

Migrate existing data to cloud cluster temporary table.

Recommended: Use OSS as intermediate storage with BACKUP/RESTORE

---

Step 7: (Optional) Merge Historical and Incremental Data

1. Stop writes 2. Use MOVE PARTITION to merge data to temporary table 3. RENAME tables

---

Step 8: Decommission Self-Built Cluster

When all data is on cloud, remove self-built nodes from config.xml

---

Step 9: Second Business Switchover

Rebuild read distributed table to use cloud cluster only

---

Rollback Plan

• Switch writes back to self-built cluster
• Continue using federation distributed table for reads (can use self-built as entry point since cloud users.xml has self-built IPs whitelisted)
• Migrate cloud data back to self-built

Note: ReplacingMergeTree may have issues with rollback.

---

Special Considerations

External Table Engines

Kafka, MaterializedMySQL, etc.: After creating target tables, migrate Kafka engine tables to cloud cluster

Migration Plans - Detailed Conditions

Method Selection Priority

Quick Reference for Common Scenarios:

>

| Scenario | Recommended Method | Notes |

|----------|-------------------|-------|

| In-place AZ switch | Console (cksync) | Even if BACKUP/RESTORE is possible (v≥22.8), cksync is preferred |

| In-place horizontal scaling | Console (cksync) | Native console support, automatic endpoint handling |

| In-place disk downgrade | Console (cksync) | Simpler than manual alternatives |

| Community → Enterprise | Console (cksync) | BACKUP/RESTORE NOT supported (different engines) |

| Community → Community | Console (cksync) or BACKUP/RESTORE | BACKUP/RESTORE requires v≥22.8 |

| Enterprise → Enterprise | INSERT FROM REMOTE or BACKUP/RESTORE | cksync does NOT support E2E |

| Zero downtime required | Double-Write | Business must support dual writes |

1. Console (cksync) Migration

1.1 Migration Type Conditions

Supported (choose one):

• (Cross-cluster) Alibaba Cloud ClickHouse Community → Enterprise
• (Cross-cluster) Alibaba Cloud ClickHouse Community → Community
• (Cross-cluster) Self-built/non-Alibaba Cloud → Alibaba Cloud Community
• (Cross-cluster) Self-built/non-Alibaba Cloud → Alibaba Cloud Enterprise
• (In-place) Alibaba Cloud Community: horizontal scaling, disk downgrade, AZ switch, multi-replica upgrade

NOT Supported:

• ❌ Alibaba Cloud Enterprise → Enterprise (use INSERT FROM REMOTE)

Why cksync for In-place Migrations?

>

For in-place operations like AZ switch, horizontal scaling, or disk downgrade, always prefer Console (cksync) over BACKUP/RESTORE because:

1. Native Alibaba Cloud Console integration with built-in support

2. Automatic connection string handling (no manual endpoint updates)

3. Real-time progress monitoring and error handling

4. No OSS setup or manual data transfer required

5. Official Alibaba Cloud recommended approach

1.2 Business Impact Conditions

Must satisfy one of:

• Business allows read-only for 10+ minutes (cksync reaches 100%, then switch)
• Business allows partial data loss (switch at ~99%, accept some missing data)

Must satisfy all:

• No DDL changes during migration (no CREATE/DROP/ALTER on tables)
• Note: DELETE operations during migration may cause data count mismatch

1.3 Business Cooperation

For cross-cluster migrations:

• Business must modify connection strings
• If version changes, verify SQL compatibility

For in-place migrations:

• Connection string remains unchanged
• If using direct node IPs, must update IPs (node IPs change in background)

1.4 Database Engine Support

1.5 Table Engine Support

MergeTree family:

• TTL deletion: Check engine_full field for TTL clause (e.g., TTL event_time + INTERVAL 7 DAY)
• If engine_full contains no TTL clause → No TTL deletion (data permanently retained) ✅
• If TTL ≥3 days → Supported ✅
• If TTL <3 days → ⚠️ Warning: Data count in new cluster may be larger than source cluster. This is because source cluster's TTL merge continues deleting data during sync, while new cluster stops all merges until sync completes.
• Partition count per table should be <10,000
• Write speed must not exceed migration speed (see speed reference below)

View / MaterializedView:

• ✅ Supported - cksync automatically migrates View and MaterializedView definitions

External tables (MySQL, PostgreSQL, Redis, MaxCompute, OSS):

• ✅ Supported - Must ensure network connectivity from target cluster to external data source

Kafka/RabbitMQ tables:

• NOT supported (migrate manually)

Log family tables:

• Community → Community: Supported for schema migration; tables with no data (size_of_one_shard = 0) can be migrated directly
• Community → Enterprise: NOT supported (Enterprise Edition doesn't support Log engine)
• Self-built → Community: Supported for schema migration; tables with no data can be migrated directly
• Self-built → Enterprise: NOT supported (Enterprise Edition doesn't support Log engine)
• If Log tables contain data, use INSERT FROM REMOTE for data migration after cksync completes schema sync

1.6 Table-Level Migration

• New cluster is Community: NOT supported
• New cluster is Enterprise: Supported

1.7 Version Requirements

• Source: All versions
• Target ≥20.8: Supports incremental migration
• Target <20.8: Full migration only

1.8 Migration Speed Reference

Formula: Migration time = Data size / (Migration speed - Write speed)

1.9 High Write Speed Handling

Critical Rule: When total business write speed exceeds 20 MB/s or source cluster shows high CPU/memory utilization, you MUST consider upgrading source and target cluster specs to increase sync speed:

Recommended Specifications for High-Speed Migration:

Validation: If user provides cluster specifications, verify they meet these minimums for high-write scenarios.

Speed Factors:

• Part size (optimal range: 100MB ~ 10GB for faster migration)
• Instance specs (CPU, memory)
• Disk specs (PL1/PL2/PL3, ESSD tier)
• Data characteristics

Migration Feasibility Check:

Note: Actual migration speed varies by environment. Test in your environment to get accurate numbers. Monitor target cluster disk throughput during migration to verify actual speed.

1.10 Console (cksync) Resource Requirements

Minimum Requirements:

• CPU: At least 2 kernels
• Memory: At least 4 GB

Recommendation: Test in real environment to validate. You can ask user about available memory size and kernel count to verify.

1.11 Target Cluster Disk Size Requirements

1.12 Merge Risk Warning (IMPORTANT)

⚠️ Critical Risk: Console (cksync) stops ALL merges on the target cluster during synchronization. After sync completes, all pending merges will start simultaneously, which can cause:

• High CPU usage
• High I/O load
• High memory consumption

Large Merges Duration Estimation (per node):

Merge Duration = Storage Size / (IO Bandwidth / 2)

Disk RAID Configuration (Alibaba Cloud Community):

Merge Duration Examples:

Note: In all examples above, IO bandwidth is limited by ECS disk bandwidth, not by the ESSD disk specifications.

How To Stop Merge Storm: See the dedicated "stop-merge-storm" operational guide in this skill package.

Reference: https://help.aliyun.com/zh/clickhouse/user-guide/migrate-table-data-from-a-self-managed-clickhouse-cluster-to-an-apsaradb-for-clickhouse-cluster#d82cf49170zd4

---

2. BACKUP/RESTORE Migration

Note: BACKUP/RESTORE only works between same edition types (Community→Community or Enterprise→Enterprise) due to different underlying storage engines. For cross-edition migrations (e.g., Community→Enterprise), use Console (cksync) instead.

>

Even if version requirements are met (≥22.8), prefer Console (cksync) for in-place operations like AZ switch.

2.1 Migration Type Conditions

Supported (same edition type only):

• (Cross-cluster) Community → Community ✅
• (Cross-cluster) Enterprise → Enterprise ✅
• (Cross-cluster) Self-built → Self-built (if both use same engine type)

NOT Supported:

• ❌ In-place migrations (use cksync instead)
• ❌ Community → Enterprise (different underlying engines)
• ❌ Self-built → Alibaba Cloud (use cksync or INSERT FROM REMOTE)

2.2 Business Impact

• Business allows read-only during entire BACKUP/RESTORE process
• Duration depends on data size and cluster resources
• Table-level: read-only on related tables
• Cluster-level: read-only on entire cluster

2.3 Database Engine Support

2.4 Table Engine Support

• MergeTree family: Supported
• Distributed: Supported
• View: Supported
• MaterializedView: Supported
• External tables: Migrate manually
• Kafka/RabbitMQ: Migrate manually
• Log family: NOT supported (use INSERT FROM REMOTE)

2.5 Version Requirements

• Both clusters must be ≥22.8 to support BACKUP/RESTORE commands

2.6 Storage Medium Recommendation

Recommended: Alibaba Cloud OSS

• OSS (Object Storage Service) is Alibaba Cloud's object storage, S3-compatible
• ClickHouse can access OSS directly via S3 external engine (no additional drivers needed)
• Data is transferred via OSS, accessible by both source and target clusters
• No manual data copy between clusters required
• Speed: up to 2GB/s to OSS

Not Recommended: DISK

• Requires manual copy of backup files between clusters
• More complex and error-prone

2.7 BACKUP/RESTORE Commands

Complete Syntax Reference

BACKUP | RESTORE [ASYNC]
-- What to backup/restore
TABLE [db.]table_name           [AS [db.]table_name_in_backup] |
DICTIONARY [db.]dictionary_name [AS [db.]name_in_backup] |
DATABASE database_name          [AS database_name_in_backup] |
TEMPORARY TABLE table_name      [AS table_name_in_backup] |
VIEW view_name                  [AS view_name_in_backup] |
[EXCEPT TABLES ...] |
ALL [EXCEPT {TABLES|DATABASES}...] } [,...]
-- Cluster option
[ON CLUSTER 'cluster_name']
-- Storage destination
TO|FROM 
  File('<path>/<filename>') | 
  Disk('<disk_name>', '<path>/') | 
  S3('<S3 endpoint>/<path>', '<Access key ID>', '<Secret access key>') |
  AzureBlobStorage('<connection string>/<url>', '<container>', '<path>', '<account name>', '<account key>')
[SETTINGS ...]

Table-Level Backup/Restore

Community Edition (requires ON CLUSTER):

-- Backup
BACKUP TABLE <database>.<table> ON CLUSTER default 
TO S3('https://<yourBucketName>.<yourEndpoint>/<path>/', '<yourAccessKeyID>', '<yourAccessKeySecret>');

-- Restore
RESTORE TABLE <database>.<table> ON CLUSTER default 
FROM S3('https://<yourBucketName>.<yourEndpoint>/<path>/', '<yourAccessKeyID>', '<yourAccessKeySecret>');

Enterprise Edition (no ON CLUSTER needed):

-- Backup
BACKUP TABLE <database>.<table> 
TO S3('https://<yourBucketName>.<yourEndpoint>/<path>/<filename>.zip', '<yourAccessKeyID>', '<yourAccessKeySecret>');

-- Restore
RESTORE TABLE <database>.<table> 
FROM S3('https://<yourBucketName>.<yourEndpoint>/<path>/<filename>.zip', '<yourAccessKeyID>', '<yourAccessKeySecret>');

Database-Level Backup/Restore

Community Edition (requires ON CLUSTER):

-- Backup entire database
BACKUP DATABASE <database_name> ON CLUSTER default 
TO S3('https://<yourBucketName>.oss-cn-hangzhou.aliyuncs.com/backup/<database_name>_full/', '<yourAccessKeyID>', '<yourAccessKeySecret>');

-- Restore entire database
RESTORE DATABASE <database_name> ON CLUSTER default 
FROM S3('https://<yourBucketName>.oss-cn-hangzhou.aliyuncs.com/backup/<database_name>_full/', '<yourAccessKeyID>', '<yourAccessKeySecret>');

2.8 Progress Monitoring

Check BACKUP progress:

SELECT * FROM system.backups ORDER BY start_time DESC;
-- Expected status: 'BACKUP_CREATED'

Check RESTORE progress:

SELECT * FROM system.backups WHERE name LIKE '%restore%' ORDER BY start_time DESC;
-- Expected status: 'RESTORED'

Status values:

2.9 Documentation Links

• Alibaba Cloud: https://help.aliyun.com/zh/clickhouse/user-guide/use-the-backup-and-restore-commands-for-data-backup-and-restoration
• ClickHouse Official: https://clickhouse.com/docs/operations/backup/s3_endpoint

---

3. INSERT FROM REMOTE Migration

Use Case for Enterprise → Enterprise: Along with BACKUP/RESTORE, this is one of the two methods available for Enterprise → Enterprise migrations (cksync does NOT support this scenario). Prefer INSERT FROM REMOTE when you need fine-grained control over migration scope.

3.1 Migration Type Conditions

Supported:

• All cross-cluster migrations (including Enterprise → Enterprise)

NOT Supported:

• In-place migrations

3.2 Business Impact Conditions

Based on table design, choose one:

• Small tables (<20GB), 10min read-only: Migrate entire table during write stop
• Large tables (>20GB) with time partitions (latest <20GB), 10min read-only: Migrate historical partitions first, then stop writes for latest partition
• Large tables without time partitions: Requires full read-only during entire migration

3.3 Database/Table Engine Support

Same as cksync migration, except:

• Log family tables: Supported
• Enterprise → Enterprise: Supported

3.4 Version Requirements

• All versions supported

---

4. Business Double-Write

4.1 Migration Type Conditions

All cross-cluster migrations supported (NOT in-place)

4.2 Business Impact

• Zero impact during migration

4.3 Business Cooperation Requirements

• Must implement dual INSERT to both clusters
• Must implement dual DDL to both clusters
• Must handle exceptions for both clusters

4.4 Table Engine Conditions

MergeTree family:

• Double-write duration must cover minimum required data (TTL period)
• During double-write period (N days), pay for both clusters

External tables: Business creates manually

Kafka/RabbitMQ: Use new consumer group in new cluster

Log family: Supported

4.5 Version Requirements

All versions supported

---

5. Kafka Double-Write

5.1 Migration Type Conditions

All cross-cluster migrations supported (NOT in-place)

5.2 Business Impact

Zero impact during migration

5.3 Business Cooperation Requirements

• Transform INSERT requests to write to Kafka
• Both clusters consume from Kafka via Kafka engine + MaterializedView
• Must implement dual DDL to both clusters

5.4 Database Engine Support

• Supported: Ordinary, Atomic, Replicated
• NOT supported: MaterializedMySQL (use DTS)
• Use other methods: MySQL, PostgreSQL, SQLite, MaterializedPostgreSQL

5.5 Table Engine Conditions

MergeTree family:

• Must transform to receive data from Kafka
• Double-write duration must cover minimum required data

External tables: Business creates manually

Kafka/RabbitMQ: Supported

Log family: Must transform to receive from Kafka

5.6 Version Requirements

• Source cluster: ≥19.x
• Target cluster: ≥19.x (Alibaba Cloud clusters satisfy this)

---

6. Big Cluster Federation

Advanced method - high technical requirements.

6.1 Migration Type Conditions

Supported:

• Community → Enterprise
• Community → Community
• Self-built → Community/Enterprise

NOT Supported:

• In-place migrations
• Enterprise → Enterprise

6.2 Business Impact

Zero impact during migration

6.3 Business Cooperation Requirements

• INSERT to new cluster only
• SELECT from federation distributed table (contains both old and new cluster data)
• DDL to both clusters
• Eventually switch SELECT to new cluster

6.4 Table Engine Conditions

MergeTree family:

• Modify distributed table definition to include new cluster shard
• Double-run duration must cover minimum required data

External tables: Migrate manually

Kafka/RabbitMQ: Use new consumer group

Log family:

• New cluster is Community: Supported
• New cluster is Enterprise: NOT supported (Enterprise doesn't support Log engine)

6.5 Version Requirements

All versions supported

RAM Permission Declaration

Overview

This skill (alibabacloud-cksync-plan) is a planning and advisory skill that generates migration plans for ClickHouse clusters. It does NOT directly call any Alibaba Cloud OpenAPI.

Required Permissions

required_permissions: none

Explanation

Data Access Pattern

This skill operates in a read-only advisory mode:

1. Input: User provides cluster information (type, version, data size, etc.) 2. Processing: Skill analyzes requirements and selects appropriate migration method 3. Output: Migration plan document with SQL templates and step-by-step instructions

The skill does NOT:

• Connect to any Alibaba Cloud services
• Execute any API calls
• Store or transmit user credentials
• Access user's cloud resources directly

User Responsibility

When users execute the SQL queries or commands provided in the migration plan:

• Users are responsible for managing their own credentials securely
• Users should use environment variables or secure credential management for database access
• Users should avoid passing plaintext credentials directly in command history

SQL Reference

Query Settings

For cluster information gathering queries, use these SETTINGS to ensure safe, optimized execution:

SETTINGS readonly = 1, max_execution_time = 300, max_threads = 1

• readonly = 1: Prevents accidental writes
• max_execution_time = 300: 5-minute timeout
• max_threads = 1: Reduces cluster load during information gathering

1. Cluster Information Queries

1.1 Database Information

SELECT
    name AS database_name,
    engine
FROM system.databases
WHERE name NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA')
FORMAT TabSeparatedWithNames
SETTINGS readonly = 1, max_execution_time = 300, max_threads = 1;

1.2 Table Information (Comprehensive)

SELECT
    table_name,
    engine,
    engine_full,
    if(engine IN ('Log', 'TinyLog', 'StripeLog', 'Join'), 'UNKNOWN', toString(part_count)) AS part_count,
    if(engine IN ('Log', 'TinyLog', 'StripeLog', 'Join'), 'UNKNOWN', toString(data_bytes)) AS data_bytes,
    if(engine IN ('Log', 'TinyLog', 'StripeLog', 'Join'), 'UNKNOWN', toString(write_speed_bytes_per_sec)) AS write_speed_bytes_per_sec
FROM
(
    SELECT
        c.table_name,
        c.engine,
        c.engine_full,
        c.part_count_of_one_shard AS part_count,
        c.byte_size_of_one_shard AS data_bytes,
        d.byte_size_3_day / 259200 AS write_speed_bytes_per_sec
    FROM
    (
        SELECT
            a.table_name,
            a.engine,
            a.engine_full,
            b.part_count AS part_count_of_one_shard,
            b.byte_size AS byte_size_of_one_shard
        FROM
        (
            SELECT
                concat('`', database, '`.`', name, '`') AS table_name,
                engine,
                engine_full
            FROM system.tables
            WHERE database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA')
        ) AS a
        LEFT JOIN
        (
            SELECT
                concat('`', database, '`.`', table, '`') AS table_name,
                count(1) AS part_count,
                sum(bytes_on_disk) AS byte_size
            FROM system.parts
            WHERE (database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA')) AND (active = 1)
            GROUP BY table_name
        ) AS b ON a.table_name = b.table_name
    ) AS c
    LEFT JOIN
    (
        SELECT
            concat('`', database, '`.`', table, '`') AS table_name,
            sum(size_in_bytes) AS byte_size_3_day
        FROM system.part_log
        WHERE (database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA')) AND (event_date >= today() - 3) AND (event_time > (now() - toIntervalDay(3))) AND (event_type = 'NewPart')
        GROUP BY table_name
    ) AS d ON c.table_name = d.table_name
) AS e
ORDER BY engine, table_name
FORMAT TabSeparatedWithNames
SETTINGS readonly = 1, max_execution_time = 300, max_threads = 1;

Note: part_count, data_bytes, and write_speed_bytes_per_sec show 'UNKNOWN' for Join and Log family engines (Log, TinyLog, StripeLog) since system.parts doesn't track their data. Use SELECT count(*) FROM table to get row counts for these engines.

---

2. INSERT FROM REMOTE Migration SQL

2.1 Schema Migration

-- List databases
SHOW databases;

-- Show database definition
SHOW CREATE DATABASE <DATABASE>; 

-- List tables in database
SHOW tables from <DATABASE>;

-- Get table definitions
SELECT concat(create_table_query, ';') 
FROM system.tables 
WHERE database='<DATABASE>';

2.2 Partition-Based Migration

Best for: Large tables with time-based partitioning

-- Pull from old cluster (new cluster version ≤23.8)
INSERT INTO <new_database>.<new_table> 
SELECT * 
FROM remote('<old_endpoint>', <old_database>.<old_table>, '<username>', '<password>') 
WHERE _partition_id = '<partition_id>'
SETTINGS max_execution_time = 0, max_bytes_to_read = 0, log_query_threads = 0, max_result_rows = 0;

-- Push to new cluster (all versions)
INSERT INTO FUNCTION remote('<new_endpoint>', '<DATABASE>', '<TABLE>', '<USERNAME>', '<PASSWORD>')
SELECT * FROM <DATABASE>.<TABLE>
WHERE _partition_id = '<partition_id>'
SETTINGS max_execution_time = 0, max_bytes_to_read = 0, log_query_threads = 0, max_result_rows = 0;

-- Clean up partition (if migration failed, retry)
ALTER TABLE <DATABASE>.<TABLE> DROP PARTITION '<PARTITION>';

2.3 Full Table Migration

Best for: Small tables (<20GB)

-- Pull from old cluster (new cluster version ≤23.8)
INSERT INTO <new_database>.<new_table> 
SELECT * 
FROM remote('<old_endpoint>', <old_database>.<old_table>, '<username>', '<password>') 
SETTINGS max_execution_time = 0, max_bytes_to_read = 0, log_query_threads = 0, max_result_rows = 0;

-- Push to new cluster (all versions)
INSERT INTO FUNCTION remote('<new_endpoint>', '<DATABASE>', '<TABLE>', '<USERNAME>', '<PASSWORD>')
SELECT * FROM <DATABASE>.<TABLE>
SETTINGS max_execution_time = 0, max_bytes_to_read = 0, log_query_threads = 0, max_result_rows = 0;

-- Clean up table (if migration failed, retry)
TRUNCATE TABLE <DATABASE>.<TABLE>;

---

3. Data Verification SQL

3.1 Table-Level Row Count

Use when:

• Engine is MergeTree or ReplicatedMergeTree (NOT Replacing/Aggregating/Collapsing variants)
• No DROP PARTITION, TRUNCATE, DELETE operations executed
• No TTL data cleanup

SELECT `database`, `table`, sum(rows) 
FROM cluster(`default`, `system`, `parts`) 
WHERE (`database` != 'system') AND (active = 1) 
GROUP BY (`database`, `table`) 
ORDER BY (`database`, `table`) ASC;

3.2 Partition-Level Row Count

Use when table-level count may not match due to data operations.

SELECT
    partition_id,
    sum(rows) AS rows
FROM cluster(<CLUSTER>, system, parts)
WHERE (active = 1) AND (database = '<DATABASE>') AND (`table` = '<TABLE>')
GROUP BY partition_id
ORDER BY partition_id ASC;

3.3 Accurate Count with FINAL

Use when above methods don't work (merging engines, data operations, TTL).

SELECT
    _partition_id,
    count(1) AS cnt
FROM <DATABASE>.<TABLE>
FINAL
WHERE (_partition_id >= '<MIN_PARTITION>') AND (_partition_id <= '<MAX_PARTITION>')
GROUP BY _partition_id;

3.4 Query Result Verification

Run on both old and new clusters; results should match.

WITH result AS (<YOUR_QUERY>) SELECT sum(cityHash64(*)) FROM result;

---

4. DDL Change Detection SQL

Check if there were DDL changes in the past 7 days (affects migration).

Version ≥20.8

SELECT count(*) 
FROM clusterAllReplicas(default, system.query_log) 
WHERE `event_time` >= now() - interval 10080 minute 
    AND (type = 'QueryFinish' and is_initial_query = 1) 
    AND (
        (query_kind = 'Alter' and lower(query) not like '% update %' and lower(query) not like '% delete %') 
        OR (query_kind in ('Grant', 'Revoke')) 
        OR (query_kind in ('Create', 'Drop', 'Rename'))
    );

Version >20.3 and <20.8

SELECT count(*) 
FROM clusterAllReplicas(default, system.query_log) 
WHERE `event_time` >= now() - interval 10080 minute 
    AND (type = 'QueryFinish' and is_initial_query = 1) 
    AND (
        (ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SelectQuery')] != 1 
         and ProfileEvents.Values[indexOf(ProfileEvents.Names, 'InsertQuery')] != 1) 
        and (lower(query) not like '%grant %' and lower(query) not like '%revoke %') 
        and (lower(query) like '%alter %' and lower(query) not like '% update %' and lower(query) not like '% delete %')
    )
    OR (/* Grant/Revoke and Create/Drop patterns - see full SQL in source */);

Version ≤20.3

Use similar pattern to >20.3 version (see source documentation for full SQL).

---

5. HTTP Access to ClickHouse

Use HTTP protocol when direct SQL client access is not available.

Connection Parameters

• HOST_NAME: Cluster endpoint (e.g., cc-xxx.clickhouse.rds.aliyuncs.com)
• HTTP_PORT: HTTP port (default: 8123)
• USER_NAME: Database username
• PASSWORD: Database password

Credential Security Guidelines

⚠️ IMPORTANT: Never expose passwords directly in command line arguments. Use one of these secure methods:

Method 1: Environment Variables (Recommended)

# Set credentials as environment variables (add to ~/.bashrc or export in session)
export CLICKHOUSE_HOST="cc-xxx.clickhouse.rds.aliyuncs.com"
export CLICKHOUSE_PORT="8123"
export CLICKHOUSE_USER="your_username"
export CLICKHOUSE_PASSWORD="your_password"

Method 2: Using netrc file

# Create ~/.netrc file with restricted permissions
echo "machine ${CLICKHOUSE_HOST} login ${CLICKHOUSE_USER} password ${CLICKHOUSE_PASSWORD}" >> ~/.netrc
chmod 600 ~/.netrc
# Then use: curl --netrc ...

Timeout Settings

Always use timeout settings to prevent hanging connections:

• --connect-timeout 30: Maximum time to wait for connection (30 seconds)
• --max-time 300: Maximum time for entire operation (300 seconds for queries, 60 seconds for ping)

Connectivity Test

# Using environment variables (secure)
curl --connect-timeout 30 --max-time 60 \
  -u "${CLICKHOUSE_USER}:${CLICKHOUSE_PASSWORD}" \
  "http://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/ping"

Get Database Information

echo "SELECT name AS database_name, engine FROM system.databases WHERE name NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA') FORMAT TabSeparatedWithNames SETTINGS readonly = 1, max_execution_time = 300, max_threads = 1;" | \
curl --connect-timeout 30 --max-time 300 \
  -u "${CLICKHOUSE_USER}:${CLICKHOUSE_PASSWORD}" \
  "http://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/" -d @-

Get Table Information

cat << 'EOF' | curl --connect-timeout 30 --max-time 300 \
  -u "${CLICKHOUSE_USER}:${CLICKHOUSE_PASSWORD}" \
  "http://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/" -d @-
SELECT
    table_name,
    engine,
    engine_full,
    if(engine IN ('Log', 'TinyLog', 'StripeLog', 'Join'), 'UNKNOWN', toString(part_count)) AS part_count,
    if(engine IN ('Log', 'TinyLog', 'StripeLog', 'Join'), 'UNKNOWN', toString(data_bytes)) AS data_bytes,
    if(engine IN ('Log', 'TinyLog', 'StripeLog', 'Join'), 'UNKNOWN', toString(write_speed_bytes_per_sec)) AS write_speed_bytes_per_sec
FROM (
    SELECT 
        c.table_name, c.engine, c.engine_full,
        c.part_count_of_one_shard AS part_count,
        c.byte_size_of_one_shard AS data_bytes,
        d.byte_size_3_day / 259200 AS write_speed_bytes_per_sec
    FROM (
        SELECT a.table_name, a.engine, a.engine_full,
            b.part_count AS part_count_of_one_shard,
            b.byte_size AS byte_size_of_one_shard
        FROM (
            SELECT concat(database, '.', name) AS table_name, engine, engine_full
            FROM system.tables
            WHERE database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA')
        ) AS a
        LEFT JOIN (
            SELECT concat(database, '.', table) AS table_name, 
                count(1) AS part_count, sum(bytes_on_disk) AS byte_size
            FROM system.parts
            WHERE database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA') AND active = 1
            GROUP BY table_name
        ) AS b ON a.table_name = b.table_name
    ) AS c
    LEFT JOIN (
        SELECT concat(database, '.', table) AS table_name, 
            sum(size_in_bytes) AS byte_size_3_day
        FROM system.part_log
        WHERE database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA') 
            AND event_date >= today() - 3 AND event_time > now() - INTERVAL 3 DAY AND event_type = 'NewPart'
        GROUP BY table_name
    ) AS d ON c.table_name = d.table_name
) AS e
ORDER BY engine, table_name
FORMAT TabSeparatedWithNames
SETTINGS readonly = 1, max_execution_time = 300, max_threads = 1;
EOF

Execute Any Query via HTTP

echo '<YOUR_SQL_QUERY>' | curl --connect-timeout 30 --max-time 300 \
  -u "${CLICKHOUSE_USER}:${CLICKHOUSE_PASSWORD}" \
  "http://${CLICKHOUSE_HOST}:${CLICKHOUSE_PORT}/" -d @-

---

6. Documentation Links

Console Migration

• Community → Enterprise: https://help.aliyun.com/zh/clickhouse/user-guide/migrate-from-self-built-clickhouse-to-enterprise-edition
• Community → Community: https://help.aliyun.com/zh/clickhouse/user-guide/migrate-data-between-apsaradb-for-clickhouse-clusters
• Self-built → Community: https://help.aliyun.com/zh/clickhouse/user-guide/migrate-table-data-from-a-self-managed-clickhouse-cluster-to-an-apsaradb-for-clickhouse-cluster
• Self-built → Enterprise: https://help.aliyun.com/zh/clickhouse/user-guide/migrate-from-self-built-clickhouse-to-enterprise-edition
• Horizontal Scaling: https://help.aliyun.com/zh/clickhouse/user-guide/modify-the-configurations-of-an-apsaradb-for-clickhouse-cluster
• Disk Downgrade: https://help.aliyun.com/zh/clickhouse/user-guide/disk-downgrade
• AZ Switch: https://help.aliyun.com/zh/clickhouse/user-guide/modify-the-configurations-of-an-apsaradb-for-clickhouse-clusters

Compatibility Verification

• https://help.aliyun.com/zh/clickhouse/user-guide/analysis-and-solution-of-cloud-compatibility-and-performance-bottleneck-of-self-built-clickhouse

How To Stop Merge Storm

After cksync completes synchronization, all pending merges will start simultaneously. This guide explains how to identify large tables and configure merge settings to control the merge storm.

Step 1: Analyze Part Details Per Table

Run the following SQL to observe part size distribution for each table:

WITH (
    SELECT sum(data_compressed_bytes) AS total_cmp_size
    FROM system.parts
    WHERE (active = 1) AND (database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA'))
) AS total_cmp_size
SELECT
    database,
    table,
    count() AS part_cnt,
    countDistinct(partition_id) AS partition_cnt,
    arrayMap(x -> round(x), quantiles(0.05, 0.1, 0.3, 0.5, 0.99)((data_uncompressed_bytes / 1024) / 1024)) AS p05_10_30_50_99_uncmp_part_mb,
    sum(data_uncompressed_bytes) AS uncmp_total_bytes,
    formatReadableSize(uncmp_total_bytes) AS uncmp_total_size,
    arrayMap(x -> round(x), quantiles(0.05, 0.1, 0.3, 0.5, 0.99)((data_compressed_bytes / 1024) / 1024)) AS p05_10_30_50_99_cmp_part_mb,
    formatReadableSize(sum(data_compressed_bytes)) AS cmp_total_size,
    (sum(data_compressed_bytes) / total_cmp_size) * 100 AS cmp_size_percent
FROM system.parts
WHERE (active = 1) AND (database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA'))
GROUP BY
    database,
    table
ORDER BY uncmp_total_bytes DESC
LIMIT 10;

Output Columns Explained:

Step 2: Identify Target Tables and Calculate Merge Memory

Identify Target Tables:

• Focus on tables with highest cmp_size_percent values
• Typically, top 5 tables account for ~95% of total storage
• Controlling these tables effectively stops most merge activity

Calculate Required Merge Memory:

• Check the p05_10_30_50_99_uncmp_part_mb column (10th percentile - p10)
• If p10 uncompressed part size is ≥500MB, most parts (>90%) exceed 500MB when uncompressed
• Merging two parts requires memory ≥ sum of both parts' uncompressed sizes
• Rule of thumb: Set merge memory limit to 2 × p10 uncompressed part size

Example:

• If p10 = 500MB, set merge memory limit to ~1GB (1,073,741,824 bytes)
• This prevents old/large parts from merging while allowing small new parts to merge

Step 3: Configure Merge Memory Settings

SQL to limit merge memory per table:

-- Replace $DATABASE, $TABLE, and $MERGE_MAX_BYTES with actual values
ALTER TABLE `$DATABASE`.`$TABLE` ON CLUSTER default 
MODIFY SETTING 
    max_bytes_to_merge_at_max_space_in_pool = $MERGE_MAX_BYTES,
    max_bytes_to_merge_at_min_space_in_pool = 1048576;

Parameters:

Example with actual values:

-- For a table where p10 uncompressed = 500MB, set limit to 1GB
ALTER TABLE `default`.`large_table` ON CLUSTER default 
MODIFY SETTING 
    max_bytes_to_merge_at_max_space_in_pool = 1073741824,
    max_bytes_to_merge_at_min_space_in_pool = 1048576;

Step 4: Gradually Restore Merge Settings (After Stabilization)

After the post-sync merge storm subsides and system resources stabilize, gradually increase the merge limit instead of immediately restoring to a large value.

Recommended Approach: 1. Start by doubling the current limit 2. Monitor CPU, memory, and I/O for 1-2 hours 3. If stable, double again 4. Repeat until reaching target value

Target Values:

Note: Most businesses do NOT need values larger than 10GB. Only consider 30GB for exceptionally large datasets.

Example: Gradual Restoration

-- Step 1: Current limit is 1GB, double to 2GB
ALTER TABLE `$DATABASE`.`$TABLE` ON CLUSTER default 
MODIFY SETTING max_bytes_to_merge_at_max_space_in_pool = 2147483648;

-- Step 2: After 1-2 hours if stable, increase to 4GB
ALTER TABLE `$DATABASE`.`$TABLE` ON CLUSTER default 
MODIFY SETTING max_bytes_to_merge_at_max_space_in_pool = 4294967296;

-- Step 3: Continue doubling until reaching target (e.g., 10GB)
ALTER TABLE `$DATABASE`.`$TABLE` ON CLUSTER default 
MODIFY SETTING max_bytes_to_merge_at_max_space_in_pool = 10737418240;

Common Merge Memory Values

Notes

• Apply settings to all nodes in the cluster using ON CLUSTER default
• For Enterprise Edition, ON CLUSTER is not needed
• Monitor CPU, memory, and I/O after applying settings to verify effectiveness
• These settings only affect new merge operations; running merges will continue until completion