Part & Merge Health Monitoring

A MergeTree table degrades quietly: active parts accumulate faster than the background merge pool can compact them, insert latency creeps up, and then one afternoon every writer starts failing with TOO_MANY_PARTS and the pipeline stops. Part-count explosion and merge backlog are the two most common causes of a self-inflicted ClickHouse outage, and both are fully observable hours before they page anyone — if you are watching the right system.* tables. This reference is the working playbook for the platform or DevOps engineer who owns MergeTree health: how active parts, in-flight merges, and the parts_to_delay_insert / parts_to_throw_insert guardrails interact, and how to instrument all of it before a backlog turns into rejected writes.

The failure is structural, not incidental. Every INSERT creates at least one new part on disk, and a background thread pool merges those parts into progressively larger ones so that reads touch few files and the sparse index stays effective. When the insert rate — measured in parts created per second, not rows — outruns merge throughput, active parts pile up per partition. ClickHouse first slows inserts artificially at parts_to_delay_insert, then rejects them outright at parts_to_throw_insert. Everything on this page is about seeing that gap between part creation and merge drain early, quantifying it, and closing it before the throw threshold is hit. It sits inside the broader monitoring, observability & operations surface alongside query-cost and replication health.

How parts, the merge pool, and insert guardrails interact

Inserts land as parts. A fixed-size background pool merges them. Two per-partition thresholds sit on the insert path as backpressure: parts_to_delay_insert throttles, parts_to_throw_insert rejects. Monitoring lives on the observation edge, reading system.parts, system.merges, and system.part_log.

The number that matters is not rows and not bytes — it is active parts per partition, because that is exactly what the guardrails count. A partition holding 300 active parts is in danger regardless of whether those parts hold a thousand rows or a billion. The merge pool works this backlog down continuously, but it has a fixed thread budget (background_pool_size) and it deprioritizes very large merges, so a burst of tiny inserts can outpace it for minutes. The entire discipline is keeping the active-part count comfortably below parts_to_delay_insert on every partition of every table, and detecting merge starvation — the pool having nothing scheduled while parts accumulate — distinctly from large-merge stalls, where the pool is busy but a single multi-hour merge is holding capacity.

Core diagnostic query reference

These are the copy-ready queries that form the backbone of any part-and-merge monitor. Each reads a system.* table directly; wire them into a scheduled collector rather than running them by hand.

-- Active parts per (table, partition): the primary health signal.
-- Compare active_parts against parts_to_delay_insert / parts_to_throw_insert.
SELECT
    database,
    table,
    partition,
    count()                              AS active_parts,
    sum(rows)                            AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    round(avg(bytes_on_disk))            AS avg_part_bytes
FROM system.parts
WHERE active                              -- inactive parts are pre-merge originals awaiting cleanup
GROUP BY database, table, partition
ORDER BY active_parts DESC
LIMIT 20;

system.parts has one row per part; filtering on active is essential because a just-merged partition briefly holds both the new large part and its now-inactive sources until the cleanup thread removes them. A partition near the top of this list with a small avg_part_bytes is the classic small-part explosion — many tiny parts the merge pool has not yet coalesced.

-- In-flight merges right now: what the pool is actually doing.
SELECT
    database,
    table,
    elapsed,                              -- seconds this merge has been running
    round(progress, 3)                   AS progress,
    num_parts,                            -- source parts being merged
    formatReadableSize(total_size_bytes_compressed) AS merge_size,
    formatReadableSize(memory_usage)     AS mem,
    is_mutation
FROM system.merges
ORDER BY elapsed DESC;

An empty system.merges while the active-part query shows a growing backlog is the signature of merge starvation: the pool is idle but work exists, usually because every thread is blocked or background_pool_size is exhausted by another table. A single row with a very high elapsed and a huge merge_size is a large-merge stall holding a pool slot.

-- Completed and failed merge history from system.part_log.
-- part_log must be enabled (config.xml <part_log>) — it is on by default in recent builds.
SELECT
    table,
    event_type,                          -- MergeParts, MergePartsStart, DownloadPart, RemovePart...
    count()                              AS events,
    round(avg(duration_ms))              AS avg_ms,
    round(quantile(0.99)(duration_ms))   AS p99_ms,
    sum(error > 0)                       AS errors
FROM system.part_log
WHERE event_time > now() - INTERVAL 1 HOUR
  AND event_type = 'MergeParts'
GROUP BY table, event_type
ORDER BY events DESC;

system.part_log is the historical record the live tables lack: it captures every merge that completed or failed in the retention window, letting you compute merge throughput and catch a table whose merges are silently erroring (errors > 0) rather than simply running slowly.

Step-by-step implementation

1. Confirm the guardrail thresholds in force

Before you can alert on part counts you need the numbers you are alerting against. The delay and throw thresholds are MergeTree settings that can be overridden per table, so read the effective values rather than assuming defaults.

SELECT name, value, changed
FROM system.merge_tree_settings
WHERE name IN (
    'parts_to_delay_insert',
    'parts_to_throw_insert',
    'max_parts_in_total',
    'inactive_parts_to_delay_insert',
    'inactive_parts_to_throw_insert'
);

Expected output on a recent default install:

┌─name──────────────────────────┬─value─┬─changed─┐
│ parts_to_delay_insert          │ 150   │       0 │
│ parts_to_throw_insert          │ 300   │       0 │
│ max_parts_in_total             │ 100000│       0 │
│ inactive_parts_to_delay_insert │ 0     │       0 │
│ inactive_parts_to_throw_insert │ 0     │       0 │
└────────────────────────────────┴───────┴─────────┘

If a specific table overrides these in its SETTINGS clause, check its CREATE TABLE in system.tables, because a per-table parts_to_throw_insert silently overrides the server value.

2. Establish the active-part baseline per table

Run the active-parts query from the reference section and record the steady-state maximum per partition for each high-traffic table. A healthy MergeTree fed by well-sized batches typically holds single-digit to low-tens of active parts per active partition; anything routinely above ~50 on the current partition means merges are lagging your insert rate even though you are nowhere near the throw threshold yet.

SELECT 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;

3. Wire a scheduled collector

Ship these counts to your metrics store on a fixed interval. A minimal clickhouse-connect collector that emits one gauge per table is enough to drive both dashboards and alerts:

import clickhouse_connect

client = clickhouse_connect.get_client(host="clickhouse", port=8123)

PART_HEALTH_SQL = """
SELECT table,
       max(active_parts)     AS worst_partition_parts,
       sum(active_parts)     AS total_active_parts
FROM (
    SELECT table, partition, count() AS active_parts
    FROM system.parts
    WHERE active AND database = %(db)s
    GROUP BY table, partition
)
GROUP BY table
"""

def collect(db: str = "analytics") -> list[dict]:
    result = client.query(PART_HEALTH_SQL, parameters={"db": db})
    return [
        {"table": row[0], "worst_partition_parts": row[1], "total_active_parts": row[2]}
        for row in result.result_rows
    ]

# emit collect() rows as gauges: parts_worst_partition{table="..."} etc.

Poll every 15–30 seconds. Part counts move fast under a burst, so a 5-minute scrape interval will miss the ramp that precedes a TOO_MANY_PARTS rejection. The full alert-rule wiring, including how to express thresholds as a fraction of parts_to_delay_insert, is covered in alerting on part count thresholds.

4. Add a merge-progress collector

Part counts tell you that a backlog exists; system.merges and system.part_log tell you why. Emit the count of in-flight merges, the oldest merge’s elapsed, and the last hour’s merge throughput so a rising part count can be attributed to starvation or a stall.

SELECT
    count()                          AS active_merges,
    max(elapsed)                     AS oldest_merge_s,
    countIf(is_mutation)             AS active_mutations,
    sum(total_size_bytes_compressed) AS bytes_being_merged
FROM system.merges;
-- active_merges = 0 while part counts climb ⇒ starvation
-- oldest_merge_s very high with active_merges low ⇒ large-merge stall

Interpreting these two signals against each other is the core of diagnosing background merge lag, which walks the full decision tree from system.merges and system.part_log numbers to a specific remediation.

Integration touchpoints

Part-and-merge health does not exist in isolation; it is downstream of how you insert and upstream of how you query.

• Insert sizing is the root cause lever. Almost every part explosion traces back to inserts that are too small or too frequent. The block-sizing discipline in batch insert optimization is the primary preventative control — larger, less frequent batches create fewer parts per second and give the merge pool a fatter margin. Monitoring tells you when that sizing has drifted out of spec.
• The merge machinery itself. What the background pool is doing when it drains parts — how it selects parts to merge, why it defers very large merges, how merged parts supersede their sources — is the mechanism this page observes. The how MergeTree handles background merging walkthrough is the mechanical companion to these metrics.
• Engine choice changes merge cost. ReplacingMergeTree, AggregatingMergeTree, and SummingMergeTree do extra work during a merge, so their merges run longer and their part backlogs drain more slowly under equal insert pressure — context the MergeTree engine deep dive sets out in full.
• Query monitoring shares the same collector. The scheduled process that reads system.parts is the same kind of interval collector that powers query performance monitoring; running both from one agent keeps a single view of cluster health and one alert-routing path.

Tuning parameters

The two settings that most often get raised in a panic are parts_to_delay_insert and parts_to_throw_insert, and raising them without adding merge capacity just moves the wall further back — the backlog still grows, reads still slow as they touch more parts, and the eventual failure is larger. Treat the throw threshold as a fixed safety limit and spend your effort on the insert side (bigger batches) and the merge side (background_pool_size, merge-size caps) instead. The number_of_free_entries_in_pool_to_lower_max_size_of_merge valve is the one that prevents a single large table from starving every other table’s small merges, and it is the first thing to check when starvation is the diagnosis.

Troubleshooting

TOO_MANY_PARTS on insert (error 252). Inserts are rejected because a partition crossed parts_to_throw_insert. Confirm which partition and how far over:

SELECT database, table, partition, count() AS active_parts
FROM system.parts
WHERE active
GROUP BY database, table, partition
HAVING active_parts > 200
ORDER BY active_parts DESC;

Fix: stop the bleeding by pausing the offending writer, then let merges catch up (watch the count fall in system.merges). Permanently, enlarge insert batches per batch insert optimization so fewer parts are created, and only then consider a modest raise of the thresholds with matching background_pool_size.

Merge starvation — parts climb but nothing is merging. The pool is idle while backlog grows.

SELECT
    (SELECT count() FROM system.merges) AS active_merges,
    (SELECT max(c) FROM (SELECT count() c FROM system.parts WHERE active GROUP BY table, partition)) AS worst_partition;
-- active_merges = 0 with worst_partition rising is the starvation signature

Fix: check background_pool_size is not exhausted by another table’s mutations, confirm number_of_free_entries_in_pool_to_lower_max_size_of_merge is at its default so small merges still schedule, and inspect system.part_log for MergeParts rows with error > 0 that indicate merges failing rather than never starting.

A single large merge holds the pool. One multi-hour merge occupies a slot and everything behind it waits.

SELECT table, elapsed, round(progress,3) AS progress,
       formatReadableSize(total_size_bytes_compressed) AS size
FROM system.merges
WHERE elapsed > 600
ORDER BY elapsed DESC;

Fix: lower max_bytes_to_merge_at_max_space_in_pool so the engine stops scheduling merges that large during peak hours, and confirm the anti-starvation valve is capping merge size when the pool runs low. Full decision tree in diagnosing background merge lag.

Inactive parts accumulate and never disappear. Merged-away source parts should be cleaned up within minutes; if system.parts shows a large active = 0 population, the cleanup thread is behind or a part is referenced by an in-progress backup or FREEZE.

SELECT table, count() AS inactive_parts, formatReadableSize(sum(bytes_on_disk)) AS wasted
FROM system.parts
WHERE NOT active
GROUP BY table
ORDER BY inactive_parts DESC;

Fix: these are usually harmless and clear on their own, but a persistent pile inflates disk usage and can trip inactive_parts_to_throw_insert if it has been enabled — release any lingering FREEZE/backup holds and let old_parts_lifetime expire them.

Insert latency rising with no rejections yet. Writes are being delayed, not thrown — you are between the delay and throw thresholds.

SELECT event_time, query_duration_ms, written_rows
FROM system.query_log
WHERE type = 'QueryFinish' AND query_kind = 'Insert'
  AND event_time > now() - INTERVAL 30 MINUTE
ORDER BY query_duration_ms DESC
LIMIT 10;

Fix: this is the early-warning band the delay threshold exists to create — treat rising insert query_duration_ms as the cue to act before the throw, exactly the signal alerting on part count thresholds turns into a page.

Related

• Alerting on Part Count Thresholds — turning active-part counts into Prometheus rules keyed off the insert guardrails.
• Diagnosing Background Merge Lag — separating merge starvation from large-merge stalls in system.merges and system.part_log.
• How MergeTree Handles Background Merging — the merge mechanism these metrics observe.
• Batch Insert Optimization — the root-cause lever that keeps part creation below merge throughput.
• Query Performance Monitoring — the companion collector for query cost and slow-query detection.

Up: Monitoring, Observability & Operations