ClickHouse Merge Performance Bottleneck causing slower insertions with High-Volume Time-Series Ingestion

[bhaskarblur]

Merge System Bottleneck Causing 35-Minute Write Lag at 55K rows/sec - Merge Throughput Unable to Keep Up with Insert Rate**

Environment

ClickHouse Altinity Version: 25.3.6
Cluster: 3-node cluster (replication enabled)
Table Engine: ReplicatedMergeTree
Workload: High-volume time-series ingestion
Operating System: Rocky Linux 9
Memory: 128GB, 32GB, 32GB
CPU: 16 cores per node
Kafka: 10 Partitions, 10 Kafka Connectors writing 100K batches to DB from Kafka Topic

We discovered a merge system performance bottleneck when ingesting high-volume time-series data into a replicated 3-node ClickHouse cluster.

Data pipeline stalled due to merge system bottleneck and eventually consumer lag being increased and slower insertions:

Metric	Value
Insert Rate	51.6K rows/sec
Merge Rate	~41K rows/sec
Deficit	10K rows/sec backing up
Write Lag	35 minutes
Kafka Backlog	22M rows
System Status	Stable but at ceiling

After 156 minutes: 485M rows ingested, but 35-minute delay before data is queryable.

Key Findings

Performance Metrics

CPU Usage:

• Peak: 491% (4.9 cores equivalent)
• Active merge threads: 3.07 cores (19% utilization)
• Observation: NOT CPU-bound, system has 16 cores but only using 3

Memory Usage:

• Peak: 6.49 GB RSS (20% of 32GB available)
• Status: NOT memory-constrained, plenty of headroom

I/O Performance:

• 306 parts being managed (stable, not growing)
• 32 concurrent merges (saturated at max limit)
• Replication factor 3 = 918 total part files across cluster
• Average part size: 1.59M rows per part
• Disk throughput: Bottleneck identified

Others:

• Insert Rate: 55.6K rows/sec (sustained for 156 minutes)
• Write Lag: 35 minutes (data queued in Kafka consumer lag around 22-28M rows)
• Consumer Backlog: 22M rows accumulating

Merge Activity Progression

Time	Total Rows	Rate	Write Lag	Merges	Parts
T+30m	~150M	52K/s	5-10m	10-15	~150
T+60m	~280M	54K/s	15-20m	25-30	~250
T+90m	~330M	52K/s	~25m	30-32	~290
T+120m	403.5M	56K/s	~32m	32	305
T+156m	485.4M	51.6K/s	~35m	32	306

Critical observation: After T+90min, merge count plateaus at 32 (max concurrent limit), part count stabilizes around 300-310, and write lag continues accumulating.

Root Cause

Replication serialization blocks merge completion:

Merge Operation Timeline:
1. Merge part locally          → 100-500ms
2. Write merged part to disk   → 200-800ms
3. Replicate to Node 2         → 1000-2000ms (network/disk wait)
4. Replicate to Node 3         → 1000-2000ms (sequential or parallel?)
5. Keeper coordination sync    → 50-100ms
6. Acknowledge completion      → 50-100ms

Total: ~2000-3500ms per merge operation

With 32 concurrent merges × 2.5 seconds average = 80 seconds per full merge cycle
Kafka batches arrive every 30 seconds

Result: Queue builds faster than merges complete → lag accumulates

Why CPU/Memory are Low but System is Bottlenecked:

• Disk I/O operations are blocking (waiting for network replication)
• CPU has nothing to do while waiting for disk/network
• Memory is allocated but unused (merge buffers waiting on I/O)
• Replication latency is the constraint, not compute resources

System Equilibrium Reached:

• Merge throughput: ~41K rows/sec (calculated from sustained 10K rows/sec deficit)
• Insert throughput: 55.6K rows/sec (from Kafka)
• Deficit: 10K rows/sec accumulates as write lag
• At 35 minutes: 10K × 2,100 sec = 21M rows in backlog (matches observed 22M)

Current Table Schema

CREATE TABLE default.`tdf-data` ON CLUSTER 'clickhouse' (
    name String,
    value Variant(Float64,Int64,String,BOOLEAN),
    status BOOLEAN,
    timestamp DateTime64(6),
    received_at DateTime64(6) DEFAULT now()
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/tdf-data', '{replica}')
PARTITION BY toStartOfDay(timestamp)
PRIMARY KEY (name, toStartOfHour(timestamp))
ORDER BY (name, toStartOfHour(timestamp), timestamp)
TTL toDateTime(timestamp) + INTERVAL 1 DAY
SETTINGS
    max_partitions_per_insert_block = 100,
    max_replicated_merges_in_queue = 32,
    max_parts_in_total = 300,
    parts_to_delay_insert = 100,
    parts_to_throw_insert = 150,
    max_bytes_to_merge_at_max_space_in_pool = 536870912,
    max_bytes_to_merge_at_min_space_in_pool = 10737418240,
    max_insert_block_size = 100000,
    merge_max_block_size = 100000,
    max_delay_to_insert = 10,
    min_delay_to_insert_ms = 100,
    index_granularity = 32768,
    old_parts_lifetime = 120;

We almost tried every configuration and came to conclusion that the data merge is slow and cannot keep up with the speed of insertions and hence slows insertions and results.

Looking for someone to share their thoughts/solution on this

[bhaskarblur]

We think that the merge process is single threaded so it is taking lot of time to merge data, we performed multiple benchmarks and all lead to this observartion.

We also tried Sharding, that worked well for like 10 minutes and then started slowing down and throwed some exceptions in the clickhouse-keeper as well

[den-crane]

What is the reason behind these settings

parts_to_delay_insert = 100,
parts_to_throw_insert = 150,

default settings are much higher?

---

value Variant(Float64,Int64,String,BOOLEAN),

How do insert data in this column? What format do you use?

---

Can you share excerpts from system.part_log with the information about merges?

---

Check how big are the inserts avg(rows):

select toStartOfMinute(event_time) t, database, table, count() c, round(avg(rows)) 
from system.part_log
where event_date >= today()
  and event_type = 'NewPart'
  --and event_time > now() - 3600
group by database, table, t
order by t

https://kb.altinity.com/altinity-kb-useful-queries/ingestion-rate-part_log/

[den-crane]

Also try to reduce TTL pressure

PARTITION BY toStartOfDay(timestamp)
TTL toDateTime(timestamp) + INTERVAL 1 DAY

By changing TTL to TTL toStartOfDay(toDateTime(timestamp)) + INTERVAL 1 DAY
Or by a tables' setting ttl_only_drop_parts=1.

[bhaskarblur]

Hi, Thanks for replying @den-crane
Can you tell what improvements will the TTL change do?

Answer to your questions:
Settings: parts_to_delay_insert=100, parts_to_throw_insert=150

• Our use case: Continuous 55k/sec for hours
• Purpose: Apply early backpressure when parts exceed 100 to slow inserts; hard reject at 150
• These are symptom management, not root cause (merge bottleneck is the real issue)

Variant Column (value Variant(Float64,Int64,String,BOOLEAN))

• Union type storing one type per row across 4 possible types
• Kafka sends Avro serialized data → Clickhouse Kafka Connector → ClickHouse native format DB
• Connector converts Avro to ClickHouse binary protocol (port 9000)

Kafka Connector Configuration

• Format: Avro (Kafka topics) → Connector → ClickHouse
• Connector tasks: 10 parallel workers
• Batch size: 100,000 rows per batch
• Frequency: ~30 seconds per batch across all workers
• Rate: 52k-55.6K rows/sec sustained for 156 minutes

Average Insert Batch Size (from system.part_log) From 156-minute benchmark:

• Total rows created: 485.4M
• Total parts at end: 306
• Average rows per part: 1.59M (healthy, well above 100K minimum)
• Progress: T+120 had 1.32M avg → T+156 had 1.59M (consolidation improving)

Insert Pattern

• Worker 1-10: 100K rows each → 1M rows/batch
• Every 30 sec: 1M rows arrive
• Result: 10-13 parts created per minute at ~100K rows each
• Merges: 32 concurrent, consolidating into larger parts
• System at T+156 (485M rows)

Ingestion rate: 55K rows/sec (unchanged for 156 min)

• CPU: 491% (3.07 cores, NOT CPU-bound)
• Memory: 6.49 GB (20% of 32GB, NOT memory-constrained)
• Active parts: 306 (only +1 from T+120, consolidation winning)
• Write lag: 35 minutes (replication serialization bottleneck) and consumer lag: 22M
• Replication: 0-second lag (3-way replication works, just slow merges)