Diagnosing Background Merge Lag
When active parts pile up, the question is never just “are merges behind?” but “which kind of behind?” — because the two failure modes have opposite fixes. Merge starvation, where the pool sits idle while parts accumulate, calls for more scheduling capacity; a large-merge stall, where one multi-hour merge monopolizes the pool, calls for the opposite, a cap on merge size. This walkthrough reads system.merges and system.part_log to tell the two apart, quantifies merge throughput against your insert rate, and maps each diagnosis to the specific setting — background_pool_size, number_of_free_entries_in_pool_to_lower_max_size_of_merge, or max_bytes_to_merge_at_max_space_in_pool — that resolves it.
Prerequisites
The two failure modes, and the signal that separates them
Both failure modes present identically at the part-count layer: system.parts shows a growing active-part backlog. The distinguishing signal lives in system.merges. Starvation means system.merges is empty or nearly so — the pool has capacity but is not scheduling work, usually because every thread is consumed by another table’s merges or mutations, or the anti-starvation valve has been misconfigured. A large-merge stall means system.merges holds one (or few) rows with a very high elapsed and a huge total_size_bytes_compressed — the pool is busy, but on a single merge so large that smaller, more urgent merges wait behind it.
The rest of this page is the queries that populate each branch of that tree and the remediation for each leaf.
Step 1 — Snapshot what the pool is doing right now
system.merges is a live view — one row per merge in progress. Read it first; its emptiness or contents decides the whole diagnosis.
SELECT
database,
table,
round(elapsed, 1) AS elapsed_s,
round(progress, 3) AS progress,
num_parts, -- source parts in this merge
formatReadableSize(total_size_bytes_compressed) AS merge_size,
formatReadableSize(bytes_read_uncompressed) AS read_so_far,
formatReadableSize(memory_usage) AS mem,
is_mutation,
merge_type
FROM system.merges
ORDER BY elapsed_s DESC;
Read it two ways. An empty result while parts climb is starvation. A single row with elapsed_s in the thousands and a large merge_size — while progress inches — is a large-merge stall. Several rows all progressing normally means the pool is working as designed and the backlog is an insert-rate problem.
┌─database──┬─table──────┬─elapsed_s─┬─progress─┬─num_parts─┬─merge_size─┬─mem──────┬─is_mutation─┐
│ analytics │ events_raw │ 3184.0 │ 0.412 │ 11 │ 128.44 GiB │ 6.10 GiB │ 0 │
└───────────┴────────────┴───────────┴──────────┴───────────┴────────────┴──────────┴─────────────┘
That row — one merge, 53 minutes in, 128 GiB, 41% done — is a textbook large-merge stall holding a pool slot.
Step 2 — Measure merge throughput from part_log
system.merges shows the present; system.part_log shows the recent past, which is where throughput lives. Compute how many merges completed, their durations, and any failures over the last window.
SELECT
table,
count() AS merges_done,
round(avg(duration_ms)) AS avg_ms,
round(quantile(0.99)(duration_ms)) AS p99_ms,
formatReadableSize(sum(size_in_bytes)) AS merged_bytes,
countIf(error != 0) AS failed_merges
FROM system.part_log
WHERE event_type = 'MergeParts'
AND event_time > now() - INTERVAL 1 HOUR
GROUP BY table
ORDER BY merges_done DESC;
Two readings matter. A high failed_merges count means merges are erroring, not lagging — the pool keeps retrying a merge that cannot complete (often a disk-space or corrupt-part issue), which looks like starvation because active parts never drop. And comparing merges_done against the part-creation rate from your insert monitor tells you directly whether the pool is keeping up: if inserts create 400 parts an hour and only 120 merges complete, the backlog is arithmetic.
-- Merge throughput vs. part creation over the same window, per table.
SELECT
table,
countIf(event_type = 'NewPart') AS parts_created,
countIf(event_type = 'MergeParts') AS merges_completed
FROM system.part_log
WHERE event_time > now() - INTERVAL 1 HOUR
GROUP BY table
HAVING parts_created > 0
ORDER BY parts_created DESC;
When parts_created sits far above merges_completed and system.merges is not stalled, you are in the insert-overrun leaf — merges are healthy but simply outnumbered.
Step 3 — Confirm pool capacity and the anti-starvation valve
If Step 1 showed starvation, quantify why the pool is not scheduling. Read the effective pool settings and how many slots are actually free.
SELECT name, value
FROM system.merge_tree_settings
WHERE name IN (
'number_of_free_entries_in_pool_to_lower_max_size_of_merge',
'number_of_free_entries_in_pool_to_execute_mutation',
'max_bytes_to_merge_at_max_space_in_pool',
'max_bytes_to_merge_at_min_space_in_pool'
);
-- Global background pool sizing and current occupancy.
SELECT metric, value
FROM system.metrics
WHERE metric IN ('BackgroundMergesAndMutationsPoolTask', 'BackgroundMergesAndMutationsPoolSize');
number_of_free_entries_in_pool_to_lower_max_size_of_merge is the anti-starvation valve: when the pool has this few free slots, ClickHouse refuses to start new large merges so that small, cheap merges keep getting scheduled. If someone raised it aggressively or set it near the pool size, large merges get blocked but so does throughput — and if it was lowered to zero, one giant merge can consume the pool and starve everything else. Confirm it sits at its default (8) unless you have a deliberate reason otherwise.
Step 4 — Apply the matching remediation
Each leaf of the decision tree has one lever. Apply the one your diagnosis points to, not all three.
-- Starvation: give the pool more threads (server setting; needs restart or
-- config reload). background_pool_size is the classic lever.
-- In config.xml / users.xml:
-- <background_pool_size>32</background_pool_size>
-- Verify the mutation valve is not consuming every slot:
SELECT count() AS active_mutations FROM system.merges WHERE is_mutation;
-- Large-merge stall: cap the size of a single merge so the pool is not
-- monopolized during peak hours. Applied per table via ALTER.
ALTER TABLE analytics.events_raw
MODIFY SETTING max_bytes_to_merge_at_max_space_in_pool = 53687091200; -- 50 GiB
-- Insert overrun: no server lever fixes it — enlarge insert batches upstream.
-- Confirm the current per-insert part creation to size the fix.
SELECT round(count() / 60.0, 1) AS parts_per_min
FROM system.part_log
WHERE table = 'events_raw' AND event_type = 'NewPart'
AND event_time > now() - INTERVAL 1 HOUR;
For the insert-overrun case the durable fix lives upstream in batch insert optimization: fewer, larger inserts create fewer parts per minute and the existing pool drains them without any server change. For starvation and stalls, the mechanics of why the pool behaves this way — how it selects and sizes merges — are set out in how MergeTree handles background merging.
Verification
After remediation, confirm the backlog is actually draining rather than merely holding. Watch the active-part count trend down over several minutes:
SELECT now() AS t, table, max(active_parts) AS worst_partition
FROM (
SELECT table, partition, count() AS active_parts
FROM system.parts WHERE active
GROUP BY table, partition
)
GROUP BY table
ORDER BY worst_partition DESC
LIMIT 5;
-- Run every 30s; worst_partition should fall steadily after the fix.
Confirm merges are now completing at a rate that exceeds part creation — the arithmetic that guarantees the backlog shrinks:
SELECT
countIf(event_type = 'NewPart') AS created_last_15m,
countIf(event_type = 'MergeParts') AS merged_last_15m
FROM system.part_log
WHERE table = 'events_raw'
AND event_time > now() - INTERVAL 15 MINUTE;
-- merged_last_15m should now be catching or exceeding created_last_15m.
Gotchas and edge cases
- Failing merges masquerade as starvation. A merge that errors — out of disk, a corrupt part, a checksum mismatch — is retried indefinitely, so
system.mergesmay look busy or briefly empty while the active-part count never drops. Always checkcountIf(error != 0)insystem.part_log; a persistent non-zero there means you have a broken merge, not a slow one, and the fix is resolving the underlying error (free disk,DETACH/ATTACHthe bad part), not tuning the pool. - Mutations and merges share one pool.
ALTER ... UPDATE/DELETEmutations run in the same background pool as merges, so a heavy mutation campaign can starve ordinary merges even though the table looks quiet.is_mutation = 1rows insystem.mergesreveal this;number_of_free_entries_in_pool_to_execute_mutationreserves slots so mutations cannot consume the entire pool. - The large-merge stall is self-correcting but slow. A 128 GiB merge will finish eventually and the pool frees up, so a stall is less dangerous than starvation — but “eventually” can be an hour during which parts accumulate. Lowering
max_bytes_to_merge_at_max_space_in_poolprevents the engine from starting such merges during peak load; it does not abort one already running. - part_log retention bounds your throughput window.
system.part_logis itself a MergeTree table with a TTL. If its retention is short, a long incident can age out theNewPartrows you need to compute the creation rate. Confirm the log’s TTL covers your diagnostic window before trusting a throughput comparison. - Raising background_pool_size steals from queries. Merge threads and query threads compete for CPU. Doubling the pool to drain a backlog can slow user-facing queries measurably, so treat a pool increase as a deliberate trade rather than a free win — and pair it with the alerting in alerting on part count thresholds so you can back it out once the backlog clears.
Related
- Part & Merge Health Monitoring — the parent pattern covering parts, merges, and the insert guardrails end to end.
- Alerting on Part Count Thresholds — the alert that tells you a merge-lag investigation is needed.
- How MergeTree Handles Background Merging — the merge-selection and sizing mechanics behind these diagnostics.