ClickHouse Merge Performance: Diagnosing Too Many Parts, Slow Merges, and Stuck Mutations

ClickHouse merge performance is the hinge on which every high-ingest MergeTree deployment turns. When background merges keep pace with inserts, part counts stay flat and SELECT latency stays predictable. When they fall behind, the failure arrives in a consistent order: read latency creeps up, inserts begin sleeping, and the server finally raises Too many parts. This is a diagnostic walkthrough for senior engineers: every claim is anchored in system.* telemetry, every setting is pinned to the release that defines its current behaviour, and every corrective action is bracketed by a verification query before it and a validation query after it.

This class of incident is mishandled so often because the symptom sits on the read path while the defect sits on the write path. Teams tune queries or add replicas when ClickHouse merge performance is limited by a scheduler that simply cannot consolidate parts as fast as ingest creates them.

The Part Lifecycle Behind Every Too Many Parts Error

ClickHouse merge performance begins with the part. Every INSERT produces one or more immutable data parts. A part name encodes its partition, its minimum and maximum block numbers, and its merge level, which is why all_1_7_1 tells you seven source parts were folded into a level-1 result. A background scheduler selects candidate sets, writes a merged part, marks the sources inactive, and lets garbage collection remove them. Nothing is edited in place.

Two per-partition guardrails protect the server when ingest outruns that machinery. parts_to_delay_insert defaults to 1000: past that many active parts in one partition, ClickHouse deliberately injects sleep into INSERT execution. parts_to_throw_insert defaults to 3000, and crossing it aborts the INSERT with Too many parts (N). Merges are processing significantly slower than inserts. That default is a critical version pin: before release 23.6 the throw threshold was 300, an order of magnitude lower. Runbooks and alert thresholds written against pre-23.6 behaviour are badly calibrated after an upgrade, and an older cluster will fail far earlier than current documentation implies.

A third pair of guardrails covers a different ClickHouse merge performance failure. inactive_parts_to_delay_insert and inactive_parts_to_throw_insert both default to 0, and surface a distinct error mentioning Too many inactive parts. Reading the error string carefully saves an hour, because one points at the merge scheduler and the other at part removal.

Begin any ClickHouse merge performance investigation with a baseline, not a hypothesis, and confirm effective values rather than trusting defaults:

SELECT database, table, partition,
       countIf(active) AS active_parts,
       countIf(NOT active) AS inactive_parts,
       formatReadableSize(sumIf(bytes_on_disk, active)) AS active_size,
       max(level) AS max_level
FROM system.parts
WHERE database NOT IN ('system', 'INFORMATION_SCHEMA')
GROUP BY database, table, partition
ORDER BY active_parts DESC LIMIT 25;

SELECT name, value, changed FROM system.merge_tree_settings
WHERE name IN ('parts_to_delay_insert','parts_to_throw_insert',
  'inactive_parts_to_throw_insert','max_bytes_to_merge_at_max_space_in_pool',
  'number_of_mutations_to_delay','number_of_mutations_to_throw');

SELECT version();

Monitoring ClickHouse Merge Performance with system.merges

The live view of ClickHouse merge performance is system.merges, holding one row per merge or part mutation currently in flight. The triage columns are elapsed in seconds, progress from 0 to 1, num_parts, rows_read, rows_written, memory_usage, total_size_bytes_compressed, and is_mutation, which is 1 when the operation is a part mutation rather than a regular merge. merge_type and merge_algorithm are documented as empty for mutations, so they corroborate is_mutation independently.

SELECT database, table, is_mutation, merge_algorithm, num_parts,
       round(elapsed, 1) AS elapsed_s, round(progress, 3) AS progress,
       rows_read, rows_written,
       formatReadableSize(total_size_bytes_compressed) AS src_size,
       formatReadableSize(memory_usage) AS mem
FROM system.merges ORDER BY elapsed DESC;

Interpretation matters more than the query. For ClickHouse merge performance, high elapsed with progress still climbing is healthy work that must not be interrupted; high elapsed with progress frozen across repeated samples signals I/O saturation or disk-space booking contention. When rows with is_mutation = 1 dominate, remember they consume the same threads as regular merges, so a mutation backlog is a leading cause of a merge backlog. This table is node-local, so wrap it in clusterAllReplicas for a cluster-wide view.

Tracking ClickHouse Merge Performance Over Time with system.part_log

Live sampling cannot tell you whether ClickHouse merge performance regressed over the last fortnight. system.part_log can, but only if it exists: the table is created only when the part_log server setting is configured. Discovering it disabled mid-incident is a familiar and avoidable frustration.

The event_type enumeration covers NewPart, MergePartsStart, MergeParts, MutatePartStart, MutatePart, DownloadPart, RemovePart, and MovePart; paired start and finish events separate queue latency from execution time. duration_ms gives merge duration, read_rows the volume consumed, peak_memory_usage the memory high-water mark, and merge_reason distinguishes RegularMerge from TTLDeleteMerge, TTLRecompressMerge, and TTLDropMerge. The mutation_ids array links mutate events to system.mutations, while error and exception record failures that produced no result part. One naming trap costs real time: the live table exposes rows_read, the historical one read_rows.

SELECT toStartOfDay(event_time) AS day, table, event_type, merge_reason,
       count() AS ops,
       quantile(0.5)(duration_ms) AS p50_ms,
       quantile(0.99)(duration_ms) AS p99_ms,
       formatReadableSize(max(peak_memory_usage)) AS peak_mem,
       sum(read_rows) AS rows_consumed,
       countIf(error != 0) AS failures
FROM system.part_log
WHERE event_date >= today() - 14
  AND event_type IN ('MergeParts', 'MutatePart')
GROUP BY day, table, event_type, merge_reason
ORDER BY day DESC, p99_ms DESC;

Rising p99_ms at constant rows_consumed means the storage layer degraded rather than that ClickHouse merge performance logic changed. Rising rows_consumed at constant duration means ingest grew and the pool absorbed it. A non-zero failures count with populated exception text is the highest-value signal in the table, because it names the defect outright. Structured review of these trends is what a ClickHouse performance audit is designed to produce.

Background Pool Tuning for ClickHouse Merge Performance: Reload Versus Restart

Pool capacity is where well-intentioned ClickHouse merge performance tuning goes wrong, because reload semantics are asymmetric.

background_pool_size is a server setting defaulting to 16 that governs the threads performing background merges and mutations for MergeTree tables. Its documented behaviour is the most important operational fact in ClickHouse merge performance tuning: the value can only be increased at runtime, and lowering it requires a server restart. Raising the pool during an incident is a legitimate reload-time action; backing that change out is a scheduled maintenance event, not a rollback.

background_merges_mutations_concurrency_ratio defaults to 2 and multiplies the pool into a concurrent operation count, because background operations can be suspended and resumed to give small merges priority; the documentation illustrates a pool of 16 with a ratio of 2 permitting 32 concurrent merges. It follows the same increase-only-at-runtime rule.

background_merges_mutations_scheduling_policy defaults to round_robin and may be changed at runtime, but choose shortest_task_first deliberately: it drains small parts fastest, yet the documentation warns it can indefinitely starve large merges in partitions saturated by inserts.

Two MergeTree-level settings cap merge size and shape ClickHouse merge performance per table. Unlike the server settings above, they apply through ALTER TABLE ... MODIFY SETTING with no restart. max_bytes_to_merge_at_max_space_in_pool defaults to 161061273600 bytes and bounds the total source size folded into one part when resources are plentiful; 0 disables merges entirely. Two behaviours are frequently forgotten.

Merges initiated by OPTIMIZE FINAL ignore this cap and are bounded only by free disk space, and max_bytes_to_merge_at_min_space_in_pool, default 1048576 bytes, exists because merges book disk space by doubling the total source size. A nearly full disk can therefore reach a state where space looks free but is entirely reserved by in-flight large merges while small parts accumulate on every insert.

Three companion thresholds coordinate the pool against those caps. number_of_free_entries_in_pool_to_lower_max_size_of_merge (default 8) shrinks the maximum merge size as the pool fills. number_of_free_entries_in_pool_to_execute_mutation (default 20) withholds mutations when the pool is congested, specifically to avoid Too many parts. number_of_free_entries_in_pool_to_execute_optimize_entire_partition (default 25) does the same for whole-partition optimisation. The latter two must stay below background_pool_size multiplied by the concurrency ratio or ClickHouse throws an exception. See the MergeTree settings reference, and confirm live values in system.server_settings.

Mutation Internals: How ALTER TABLE UPDATE Erodes ClickHouse Merge Performance

ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE are implemented as mutations, and the documentation is blunt about why the syntax is deliberately awkward: the prefix signals that, unlike the similar statement in an OLTP database, this is a heavy operation not designed for frequent use. Columns in the primary key or partition key cannot be updated at all.

The cost model follows from immutability. Because a part cannot be edited in place, satisfying a mutation means writing a new one: for each affected part the engine rewrites the column files the mutation touches and carries the rest forward. Mutation cost therefore scales with the size of the affected parts, not the number of rows matched, and that single fact explains most collapses in ClickHouse merge performance. A one-row update on a table partitioned by month can rewrite a month of column data, so predicates aligned to the partition key, ideally with an explicit IN PARTITION clause, separate bounded work from unbounded work.

Synchronicity is governed by mutations_sync, which is asynchronous by default, so the statement returns long before the work completes. Mutations on one table also execute sequentially, so a single pathological command at the head of the queue blocks everything behind it. Two thresholds bound the accumulation: number_of_mutations_to_delay defaults to 500 and slows mutations artificially, and number_of_mutations_to_throw defaults to 1000 and raises a Too many mutations exception. Both are disabled at 0, and disabling them is almost always the wrong instinct.

Tracking and Detecting Stuck Mutations

system.mutations holds one row per submitted command and is the second pillar of ClickHouse merge performance telemetry. The diagnostic columns are mutation_id, command, create_time, parts_to_do with parts_to_do_names, parts_in_progress_names, the parts_postpone_reasons map, is_done, and the failure triple latest_failed_part, latest_fail_time, and latest_fail_reason, alongside latest_fail_error_code_name. Full semantics are in the system.mutations documentation.

Three documented subtleties separate a real incident from a false alarm. parts_to_do = 0 with is_done = 0 on a replicated table is not necessarily a fault, because a long-running INSERT may still be creating a part the mutation must process. In parts_postpone_reasons the pseudo-name all_parts represents every part not yet mutated. And is_killed exists only in ClickHouse Cloud, where is_killed = 1 alongside is_done = 0 can persist when another long-running mutation blocks the killed one; that is documented as normal, not evidence the kill failed.

For ClickHouse merge performance triage, a genuinely stuck mutation shows a populated latest_fail_reason with latest_fail_time advancing while parts_to_do does not fall. That is a retry loop consuming pool threads indefinitely.

SELECT database, table, mutation_id, command, create_time,
       now() - create_time AS age_s, parts_to_do,
       length(parts_in_progress_names) AS in_progress, is_done,
       latest_failed_part, latest_fail_time,
       latest_fail_error_code_name, latest_fail_reason
FROM system.mutations
WHERE is_done = 0 ORDER BY create_time ASC;

Read latest_fail_reason literally before acting on a ClickHouse merge performance incident. A memory-limit exception, a type-cast failure, a missing column, and a corrupt part demand different responses, and only one is resolved by killing the mutation. Avoiding the problem outright is schema and workload work, the substance of ClickHouse performance tuning and optimization.

Lightweight Deletes, Pinned to the Releases That Changed Them

Lightweight deletes change the ClickHouse merge performance profile of a table, and their behaviour has shifted materially across releases, so advice that is not version-pinned is worse than none.

• 23.3 LTS (2023-03-30): the upgrade notes state that lightweight DELETEs are production ready and enabled by default, and that DELETE for MergeTree tables is available by default. This is the baseline.
• 23.6: parts_to_throw_insert moved from 300 to 3000, changing when a delete-driven part explosion becomes fatal rather than merely slow.
• 23.12 (2023-12-28): introduced ALTER TABLE ... APPLY DELETED MASK, which forces application of the mask written by a lightweight delete and removes marked rows from disk instead of waiting for a natural merge.
• 24.9: added lightweight deletes scoped to a partition through IN PARTITION on DELETE FROM — the cleanest way to bound blast radius.
• 25.7: added lightweight updates for MergeTree-family tables via UPDATE ... SET ... WHERE built on patch parts, plus lightweight deletes routed through that machinery with lightweight_delete_mode = 'lightweight_update'.
• 25.8: lightweight updates and deletes were promoted from experimental to beta. They remain beta, and beta features do not belong in an unattended production change window.

The mechanism explains the ClickHouse merge performance cost. A lightweight delete writes a mask stored as the hidden _row_exists column, present in a part only when some of its rows were deleted; DELETE FROM t WHERE cond is translated into ALTER TABLE t UPDATE _row_exists = 0 WHERE cond, and reads are rewritten with PREWHERE _row_exists.

For wide parts only _row_exists is written while other column files are hardlinked, which is where the efficiency comes from; for compact parts every column is rewritten because they share one file, so a table dominated by compact parts loses most of the advantage. The documentation lists that alongside heavy WHERE conditions, a saturated mutation queue, and very large part counts as the main drags on ClickHouse merge performance.

Two settings complete the picture. lightweight_deletes_sync defaults to 2, waiting for completion on all replicas, with a documented Cloud default of 1; and DELETE does not work on tables with projections by default, which the MergeTree setting lightweight_mutation_projection_mode exists to change.

A Safe Remediation Runbook for ClickHouse Merge Performance

One rule governs everything below: no destructive statement is issued without a verification query immediately before it proving the target is what you believe it to be, and a validation query immediately after it proving the intended state was reached. Verification prevents acting on a stale mental model; validation prevents declaring victory on an operation that silently did nothing.

Runbook 1: Part Explosion from Micro-Batched Inserts

-- VERIFY BEFORE
SELECT table, partition, count() AS active_parts,
       round(avg(rows)) AS avg_rows_per_part,
       formatReadableSize(avg(bytes_on_disk)) AS avg_part_size
FROM system.parts WHERE active AND database = 'my_db'
GROUP BY table, partition ORDER BY active_parts DESC LIMIT 10;

Low avg_rows_per_part with a high part count identifies the writer, not the server, as the cause of degraded ClickHouse merge performance. Remediation is client-side: enlarge insert batches, or enable asynchronous inserts so the server buffers small writes into fewer parts. Nothing is dropped or killed.

-- VALIDATE AFTER
SELECT toStartOfHour(event_time) AS hour,
       countIf(event_type = 'NewPart') AS parts_created,
       countIf(event_type = 'MergeParts') AS merges_done,
       round(avg(rows)) AS avg_rows_per_new_part
FROM system.part_log
WHERE event_date >= today() - 1 AND database = 'my_db'
GROUP BY hour ORDER BY hour DESC;

Success is parts_created falling and avg_rows_per_new_part rising, with active part counts trending down across subsequent samples.

Runbook 2: ClickHouse Merge Performance Starvation from Oversized Candidate Sets

-- VERIFY BEFORE: are a few huge merges holding every slot?
SELECT count() AS running, sum(is_mutation) AS mutations,
       formatReadableSize(max(total_size_bytes_compressed)) AS largest_src
FROM system.merges;

SELECT value FROM system.merge_tree_settings
WHERE name = 'max_bytes_to_merge_at_max_space_in_pool';

If so, restore ClickHouse merge performance by lowering the cap for the affected table only. This is table-scoped, applies without a restart, and is trivially reversible.

ALTER TABLE my_db.events
    MODIFY SETTING max_bytes_to_merge_at_max_space_in_pool = 10737418240;
-- rollback: ALTER TABLE my_db.events
--     RESET SETTING max_bytes_to_merge_at_max_space_in_pool;

-- VALIDATE AFTER
SELECT name, value, changed FROM system.merge_tree_settings
WHERE name = 'max_bytes_to_merge_at_max_space_in_pool';

SELECT countIf(event_type = 'MergeParts') AS merges_last_10m,
       quantile(0.99)(duration_ms) AS p99_ms
FROM system.part_log
WHERE event_time > now() - INTERVAL 10 MINUTE
  AND database = 'my_db' AND table = 'events';

Runbook 3: Terminating a Genuinely Stuck Mutation

This is the one ClickHouse merge performance runbook containing a destructive command, so the bracketing is mandatory. Verify first: confirm the mutation is failing rather than merely slow, and capture the exact identity you intend to terminate.

-- VERIFY BEFORE: is it failing, and which one is it?
SELECT database, table, mutation_id, command, create_time,
       parts_to_do, is_done, latest_fail_time, latest_fail_reason
FROM system.mutations
WHERE is_done = 0 AND database = 'my_db' AND table = 'events'
ORDER BY create_time ASC;

-- VERIFY BEFORE: run the above twice, 60s apart.
-- Proceed only if parts_to_do is NOT decreasing.

-- VERIFY BEFORE: dry run. TEST only checks rights and lists targets.
KILL MUTATION TEST
WHERE database = 'my_db' AND table = 'events'
  AND mutation_id = 'mutation_42.txt';

Proceed only when latest_fail_reason is populated, parts_to_do is static across samples, and the TEST run lists exactly the one mutation you intend to remove. Changes a mutation has already made are not rolled back, so this narrows the queue rather than restoring a prior state.

KILL MUTATION
WHERE database = 'my_db' AND table = 'events'
  AND mutation_id = 'mutation_42.txt';

-- VALIDATE AFTER: the target is gone and nothing else was removed
SELECT mutation_id, command, is_done, parts_to_do, latest_fail_reason
FROM system.mutations
WHERE database = 'my_db' AND table = 'events' AND is_done = 0
ORDER BY create_time ASC;

-- VALIDATE AFTER: mutations no longer monopolise merge slots
SELECT count() AS running, sum(is_mutation) AS mutations FROM system.merges;

-- VALIDATE AFTER: part count is trending down again
SELECT partition, count() AS active_parts FROM system.parts
WHERE active AND database = 'my_db' AND table = 'events'
GROUP BY partition ORDER BY active_parts DESC LIMIT 5;

Runbook 4: Retiring a Partition Instead of Mass Deleting Rows

When a delete aligns with the partition key, retiring the partition is far cheaper for ClickHouse merge performance than a mutation. It is also destructive, so it gets the same treatment. Verify that the partition holds only what you expect, and record the counts you will compare against.

-- VERIFY BEFORE: what exactly is in this partition?
SELECT partition, sum(rows) AS rows, count() AS parts,
       formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active AND database = 'my_db' AND table = 'events'
  AND partition = '202401'
GROUP BY partition;

-- VERIFY BEFORE: prove no out-of-range rows are present (expected: 0)
SELECT count() FROM my_db.events
WHERE toYYYYMM(event_time) = 202401
  AND event_time NOT BETWEEN '2024-01-01' AND '2024-02-01';

Detach rather than drop, so the data stays recoverable while you confirm the application is unaffected.

ALTER TABLE my_db.events DETACH PARTITION '202401';

-- VALIDATE AFTER: gone from active parts (expected: 0)
SELECT count() AS still_active FROM system.parts
WHERE active AND database = 'my_db' AND table = 'events'
  AND partition = '202401';

-- VALIDATE AFTER: present and intact in detached parts
SELECT partition_id, count() AS detached_parts, any(reason)
FROM system.detached_parts
WHERE database = 'my_db' AND table = 'events' GROUP BY partition_id;

-- VALIDATE AFTER: the rest of the table is untouched
SELECT count() AS remaining_rows FROM my_db.events;

Only after the retention window elapses and the application is confirmed healthy should the detached data be released permanently. Verify the exact target again, then validate the reclaim.

-- VERIFY BEFORE: confirm the exact detached target still exists
SELECT partition_id, name, disk, reason FROM system.detached_parts
WHERE database = 'my_db' AND table = 'events' AND partition_id = '202401';

ALTER TABLE my_db.events DROP DETACHED PARTITION '202401'
SETTINGS allow_drop_detached = 1;

-- VALIDATE AFTER: the detached entry is gone (expected: 0)
SELECT count() AS remaining_detached FROM system.detached_parts
WHERE database = 'my_db' AND table = 'events' AND partition_id = '202401';

-- VALIDATE AFTER: disk usage reflects the reclaim
SELECT name, formatReadableSize(free_space) AS free,
       formatReadableSize(total_space) AS total FROM system.disks;

Note that OPTIMIZE FINAL is not a remediation for a part backlog. It ignores max_bytes_to_merge_at_max_space_in_pool and is bounded only by free disk space, so on a struggling cluster it reliably makes ClickHouse merge performance worse.

Validate ClickHouse Merge Performance Changes in Staging First

Every ClickHouse merge performance change described above — settings, mutations, and partition operations alike — must be rehearsed on a staging environment mirroring production schema, partitioning, part-type distribution, and ingest rate before it touches a live cluster.

The reasons are specific rather than ceremonial. Compact and wide parts behave differently under lightweight deletes; replicated and non-replicated tables assign mutation block numbers differently; reload-versus-restart asymmetry turns a pool reduction you assume is reversible into a maintenance window; and beta features such as lightweight updates in 25.7 and 25.8 lack the production mileage that justifies unattended use.

Confirm both environments with SELECT version() first, because a behavioural difference across one minor release is enough to invalidate the rehearsal.

Get Expert Help with ClickHouse Merge Performance

Merge and mutation pathologies rarely announce themselves until they already limit ingest, and the diagnostic window during an incident is narrow. ChistaDATA provides 24×7 ClickHouse consultative support for exactly these situations, backed by ClickHouse managed services that keep part counts, merge latency, and mutation queues within engineered bounds rather than discovered under pressure. If your cluster is throwing Too many parts, accumulating mutations that never complete, or spending more of its background pool on mutations than on merges, our ClickHouse merge performance engineers can work through the telemetry with you and build a remediation plan that is verified before execution and validated after.