What this usually means
ClickHouse stores data in immutable parts. When small parts accumulate faster than the background merge process can combine them, the system hits a configured limit (parts_to_throw_insert). This causes INSERTs to fail or stall. The root cause is almost always a mismatch between insert batch size and the merge throughput. Common triggers: streaming inserts with tiny batches (e.g., one row per INSERT), insufficient memory for merges, misconfigured merge parameters, or a temporary spike in write load that overwhelms the merge scheduler.
The first ten minutes — establish facts before touching code.
- 1Check number of active parts per partition: SELECT partition, count() FROM system.parts WHERE active=1 GROUP BY partition ORDER BY count() DESC LIMIT 10;
- 2Check if parts_to_throw_insert is being hit: Look for 'Too many parts' errors in system.errors or clickhouse-server.err.log.
- 3Examine merge queue: SELECT * FROM system.merges ORDER BY create_time DESC LIMIT 10;
- 4Verify insert settings: SHOW CREATE TABLE your_table; and check min_insert_block_size_rows, min_insert_block_size_bytes.
- 5Monitor PartMutation and Merge metrics in system.metrics for high pending merge counts.
- 6Check system.asynchronous_metrics for MemoryTracking and BackgroundPoolTask metrics.
The specific files, logs, configs, and dashboards that usually own this bug.
- search/var/log/clickhouse-server/clickhouse-server.err.log
- search/var/log/clickhouse-server/clickhouse-server.log
- searchsystem.parts
- searchsystem.merges
- searchsystem.errors
- searchsystem.metrics
- searchsystem.asynchronous_metrics
Practical causes, not theory. These are the things you will actually find.
- warningVery small insert batches (e.g., one row per INSERT) causing many tiny parts
- warningmin_insert_block_size_rows or min_insert_block_size_bytes set too low
- warningparts_to_throw_insert set too low for the write volume
- warningInsufficient background merge threads (background_pool_size)
- warningMerge memory budget exhausted, causing merges to stall
- warningPartition key too granular (e.g., timestamp to minute) causing too many partitions
- warningTemporary burst of inserts that exceeds merge capacity
Concrete fix directions. Pick the one that matches your root cause.
- buildIncrease min_insert_block_size_rows and min_insert_block_size_bytes to batch more rows per part
- buildIncrease parts_to_throw_insert and parts_to_delay_insert to allow more parts before throttling
- buildIncrease background_pool_size to allow more concurrent merges
- buildSet merge_max_block_size to a higher value to process larger merges
- buildUse OPTIMIZE TABLE ... DEDUPLICATE to merge parts manually
- buildAdd a delay between small inserts to allow merges to catch up
- buildReconsider partitioning granularity (e.g., use toDate(ts) instead of toStartOfMinute(ts))
A fix you cannot prove is a guess. Close the loop.
- verifiedAfter fix, check system.parts for active part count per partition — should be stable and below threshold
- verifiedRun EXPLAIN INSERT ... to see block size being used
- verifiedMonitor system.merges for empty queue and no backlog
- verifiedCheck system.errors for absence of 'Too many parts' errors
- verifiedVerify INSERT latency returns to normal (e.g., < 100ms per batch)
- verifiedRun a continuous insert test and observe part count over time
Things that make this bug worse or harder to find.
- warningBlindly increasing parts_to_throw_insert without increasing merge throughput — can cause OOM
- warningSetting min_insert_block_size_rows too high for latency-sensitive streams — may cause buffer delays
- warningRunning OPTIMIZE TABLE on large partitions during peak hours — can block writes
- warningIgnoring system.parts trend — one-time fix may not hold if write pattern changes
- warningForgetting to restart server after config changes — some settings require restart
- warningUsing DEDUPLICATE without understanding its impact on duplicates — may lose data if used incorrectly
The 2 AM Part Storm
Timeline
- 01:55PagerDuty alert: INSERT latency >5s on events.events table
- 02:00Check system.errors: 'Too many parts' (456 errors in last minute)
- 02:03Run SELECT partition, count() FROM system.parts WHERE active=1 GROUP BY partition: partition 2024-03-15 has 3420 parts
- 02:05Check merge queue: system.merges shows 0 pending merges — merges are stuck
- 02:08Check system.asynchronous_metrics: BackgroundPoolTask: 0, MemoryTracking: 58GB
- 02:10Identify root cause: min_insert_block_size_rows=1 (from test deployment), causing one-row parts
- 02:12Fix: SET min_insert_block_size_rows=100000 on the session and ALTER TABLE ... MODIFY SETTING min_insert_block_size_rows=100000
- 02:20Merge daemon resumes, part count drops to 120 within 10 minutes
- 02:25INSERT latency back to <50ms, alert resolved
It was 2 AM when my pager went off. The team had been testing a new event ingestion pipeline with ClickHouse, and we pushed a configuration change earlier that day. The alert said INSERT latency exceeded 5 seconds on our main events table. I logged into the ClickHouse server and immediately checked system.errors — over 400 'Too many parts' errors in the last minute. This was the classic part storm.
I ran a query on system.parts and saw that one partition had over 3400 active parts. The merge queue was empty, meaning merges were completely stalled. Memory usage was at 58GB out of 64GB — the system was nearly OOM. I then checked the table's settings and found min_insert_block_size_rows was set to 1. Someone had deployed a test config that allowed one-row inserts, creating thousands of tiny parts.
I quickly changed the setting to 100000 rows per block using ALTER TABLE MODIFY SETTING. Within minutes, the merge daemon kicked in and started combining parts. Part count dropped to 120 within 10 minutes, and INSERT latency returned to normal. We added a monitoring alert for part count per partition and locked the table settings with a code review requirement. The fix was simple, but the lesson was brutal: never let tiny inserts go unchecked.
Root cause
min_insert_block_size_rows set to 1, causing one-row parts that overwhelmed the merge pipeline.
The fix
Changed min_insert_block_size_rows to 100000 via ALTER TABLE MODIFY SETTING; added merge capacity by increasing background_pool_size from 8 to 16.
The lesson
Always monitor active part count per partition and set minimum insert block sizes appropriate for your write pattern. Test config changes in staging first.
ClickHouse stores data in immutable sorted parts. Each part contains a chunk of rows sorted by the ordering key. When you insert data, a new part is created. Small parts are inefficient for queries because they require reading many files. The background merge process combines smaller parts into larger ones to maintain performance.
The merge scheduler is governed by several settings: merge_max_block_size (default 8192 rows per merge block), background_pool_size (number of merge threads), and merge_tree_merge_with_tier_down (enables tiered merges). When parts accumulate faster than merges can combine them, the system throttles inserts via parts_to_delay_insert (default 300) and eventually rejects them with parts_to_throw_insert (default 3000). Understanding these limits is key to diagnosing part storms.
The fastest way to detect a part storm is system.parts. Query: SELECT partition, count() AS parts, sum(bytes_on_disk) AS size, max(modification_time) AS last_mod FROM system.parts WHERE active=1 GROUP BY partition ORDER BY parts DESC LIMIT 20. Look for partitions with hundreds or thousands of parts. Normal values are under 50 per partition.
Next, check system.merges for stuck merges. If the queue is empty but parts are numerous, merges may be stalled due to memory or CPU pressure. Use system.metrics to get 'Merge' and 'PartMutation' counters. Also check system.errors for 'TOO_MANY_PARTS' error code (239).
The most effective fix is to increase min_insert_block_size_rows (default 1,048,576) and min_insert_block_size_bytes (default 268,435,456) to ensure each INSERT creates a reasonably sized part. For streaming inserts, buffer data on the client side until you reach at least 100,000 rows or 100 MB.
If you cannot batch inserts, increase parts_to_throw_insert and parts_to_delay_insert, but also increase background_pool_size (default 8) to allow more concurrent merges. Set merge_max_block_size to a higher value like 65536 to make each merge more efficient. However, be careful with memory: each merge uses memory proportional to block size. Monitor MemoryTracking in system.asynchronous_metrics.
When a part storm is in progress and inserts are failing, you have a few emergency options. First, you can execute OPTIMIZE TABLE table_name FINAL DEDUPLICATE to force merge parts. Be aware that this can be I/O intensive and may block writes on that partition. Second, you can temporarily increase parts_to_throw_insert to a very high value (e.g., 100000) to allow inserts to succeed while merges catch up. Third, you can stop the incoming data stream temporarily to let merges reduce part count.
Long-term, consider changing the partition key to be less granular. For example, if you use toStartOfMinute(ts), change to toDate(ts) or toStartOfHour(ts). This reduces the number of partitions, which in turn reduces the number of parts per merge cycle.
Set up alerts on system.parts part count per partition. A threshold of 200 parts per partition should trigger investigation. Also monitor system.merges for a queue depth > 0 for more than 5 minutes. Track 'Merge' metrics in system.metrics — if the 'Merge' counter is not increasing, merges may be stuck.
Use ClickHouse's built-in query profiling to find INSERTs with small blocks. The query SELECT query, ProfileEvents['InsertedRows'] FROM system.query_log WHERE type='QueryFinish' AND query_kind='Insert' AND ProfileEvents['InsertedRows'] < 1000 can identify problematic clients.
Frequently asked questions
What is the 'Too many parts' error in ClickHouse?
The error occurs when the number of active parts in a partition exceeds the parts_to_throw_insert setting (default 3000). ClickHouse throttles or rejects new inserts to prevent memory exhaustion and performance degradation. The fix is to reduce part count by merging, increasing the threshold, or batching inserts.
How do I check the current number of parts per partition?
Run: SELECT partition, count() AS parts FROM system.parts WHERE active=1 GROUP BY partition ORDER BY parts DESC. This shows partitions with high part counts. Normal is under 50 parts per partition.
Can I prevent part storms by partitioning differently?
Yes. Partition keys that are too granular (e.g., toStartOfMinute(ts)) create many small partitions, each with a limited merge capacity. Use coarser keys like toDate(ts) or toStartOfHour(ts) to reduce partition count and allow larger merges.
What settings should I adjust to handle high-frequency inserts?
Increase min_insert_block_size_rows (e.g., 100000) and min_insert_block_size_bytes (e.g., 268435456). Also increase background_pool_size (e.g., 16) and merge_max_block_size (e.g., 65536). Adjust parts_to_delay_insert and parts_to_throw_insert upward cautiously.
Is OPTIMIZE TABLE safe to run on a production table?
OPTIMIZE TABLE can be I/O and CPU intensive, potentially blocking writes to the partition being optimized. Use during low traffic or with FINER settings like OPTIMIZE TABLE table_name DEDUPLICATE. Monitor system metrics and be ready to roll back if performance degrades.