Fixing insert shape so merges can keep up
Kubernetes
PrometheusInsert rejections stopped and part counts fell back into a range where merges keep pace during peak traffic. Query performance improved as a side effect, since scans no longer had to open thousands of…
Overview
A measurement platform started rejecting inserts with too-many-parts errors during peak traffic. Each application instance was inserting small row batches on a short timer, creating far more parts than the merge scheduler could retire. AceMQ engaged under the emergency SLA.
Challenge
The parts limit is a guardrail rather than the problem — raising it lets the table degrade further until queries slow to a crawl reading across thousands of parts. The real causes were insert batch size, insert frequency, and a partition key with high enough cardinality that every insert touched many partitions at once, multiplying part creation.
Environment
ClickHouse cluster on AWS ingesting event streams from Kafka for real-time campaign measurement.
Approach
AceMQ measured part creation rate against merge throughput per table to quantify the actual gap, then addressed insert shape first because no amount of merge tuning compensates for inserts arriving in the wrong form. The partition key change was staged so existing data remained queryable throughout.
Solution
- 1Measured part creation rate against merge throughput per table to quantify the real deficit
- 2Consolidated many small application inserts into fewer large batches through a buffering ingestion layer
- 3Reduced partition key cardinality so each insert touched a bounded number of partitions
- 4Retuned background merge pool size and merge settings against the available CPU and I/O
- 5Staged the partition key change so existing data remained queryable through the transition
- 6Added part count per table and merge backlog to alerting well ahead of the rejection threshold
Outcome
Insert rejections stopped and part counts fell back into a range where merges keep pace during peak traffic. Query performance improved as a side effect, since scans no longer had to open thousands of parts per table.
Technologies
KubernetesPrometheusRelated Use Cases
ClickHouse MergeTree and Replication Support
Ongoing support for ReplicatedMergeTree clusters covering replication queue stalls, memory limit failures on large queries, and mutation backlogs.
ClickHouse Schema and Sort Key Design Consulting
Redesigning ORDER BY keys, partitioning, codecs, and materialized views so dashboard queries read a small fraction of the data instead of full scans.
Facing a ClickHouse Production Issue?
AceMQ's senior ClickHouse engineers have handled this exact type of engagement before. Whether you need architectural guidance, hands-on remediation, or an ongoing managed partnership, we're ready to help.