Debugging with System Tables

The mental model: work in progress, not just state

Every system table this course has used so far describes state — what a table looks like, what a query did, what's in a part. system.mutations and system.merges describe something different: work ClickHouse is actively doing, tracked with its own lifecycle, its own progress, and its own failure modes, exposed with the same SELECT you already know.

The relationship between the two is the whole mental model, and it's easy to get backwards:

system.mutations is the job ticket — one row per ALTER ... UPDATE/DELETE command, tracking overall progress across every part that command needs to touch, persisting for a while after it finishes. Its source-level backing, MergeTreeMutationEntry, is documented plainly: "A mutation entry for non-replicated MergeTree storage engines. Stores information about mutation in file mutation_*.txt." is_done tells you whether every part the command touches has been rewritten; parts_to_do tells you how many haven't yet; latest_fail_reason tells you what went wrong the last time ClickHouse tried and failed on one of them.

system.merges is the live work queue — one row per task ClickHouse is executing right now, merge or mutation alike, and the row vanishes the instant that task finishes, the same "live snapshot" behavior the-system-database already taught you for system.processes. Its own documentation states it plainly: it "contains information about merges and part mutations currently in process for tables in the MergeTree family." A row's is_mutation column tells you which kind of task it is — 1 for a mutation, 0 for an ordinary merge — but they're rows in the same table, which is the detail worth sitting with: a mutation isn't a separate mechanism from a merge. It's scheduled and executed through the exact same background pool.

That's not a coincidence of implementation — it's load-bearing. MergeTreeBackgroundExecutor, the class that actually runs both, describes itself as an "Executor for a background MergeTree related operations such as merges, mutations, fetches and so on." A shared pool means shared competition: a table under heavy insert load, already leaning on merges to keep part counts down, can leave a mutation waiting for a free slot for a long time — and ClickHouse deliberately throttles this in the other direction too, via number_of_free_entries_in_pool_to_execute_mutation: "when there is less than [the] specified number of free entries in [the] pool, do not execute part mutations. This is to leave free threads for regular merges and to avoid 'Too many parts' [errors]." A stuck-looking mutation and the "too many parts" failure mergetree-on-disk-format already covered aren't unrelated symptoms — they're two views of the same shared, finite resource.

Codebase tour: where this lives in ClickHouse's source

Debugging walkthrough: "the DELETE ran, but the data's still there"

Symptom. A team runs ALTER TABLE events DELETE WHERE tenant_id = 501 on a large, long-running table, expecting the matching rows to disappear. A day later, those rows are still queryable, and disk usage hasn't dropped. A follow-up attempt to run OPTIMIZE TABLE events FINAL fails outright with an error about the background pool being full.

Step 1 — confirm the mutation itself, not the symptom's surface. ALTER TABLE ... DELETE is not instant — it's a mutation, queued and applied in the background, exactly like the codec change mergetree-on-disk-format covered was metadata-only until a merge (or, here, the mutation itself) actually rewrote the affected parts. The first move isn't to guess — it's to read the job ticket:

SELECT mutation_id, command, is_done, parts_to_do, latest_fail_reason, latest_fail_time
FROM system.mutations
WHERE database = 'default' AND table = 'events' AND NOT is_done
ORDER BY create_time;

┌─mutation_id─┬─command────────────────────┬─is_done─┬─parts_to_do─┬─latest_fail_reason──────────────┬──────latest_fail_time─┐
│ 0000000012  │ DELETE WHERE tenant_id=501 │       0 │           4 │                                  │  1970-01-01 00:00:00   │
└─────────────┴────────────────────────────┴─────────┴─────────────┴──────────────────────────────────┴────────────────────────┘

is_done = 0 confirms this: the mutation genuinely hasn't finished. But latest_fail_reason is empty and latest_fail_time is the zero-value timestamp — this mutation hasn't actually failed on anything. It just hasn't finished. That distinction matters: an empty latest_fail_reason with parts_to_do > 0 points toward "still working, or not yet scheduled" rather than "broken and needs a fix," which is exactly the ambiguity Step 2 exists to resolve.

Step 2 — check whether it's actually running right now, or waiting its turn. system.mutations alone can't answer "is ClickHouse working on this at this exact moment" — for that, you need the live table:

SELECT database, table, mutation_id, is_mutation, progress, elapsed, memory_usage
FROM system.merges
WHERE table = 'events';

(0 rows)

Zero rows in system.merges for this table, combined with the OPTIMIZE ... FINAL failure citing a full background pool, points at the actual root cause: this mutation isn't broken — it's queued, waiting for a free slot in a pool that's currently saturated by something else. This is precisely the documented purpose of number_of_free_entries_in_pool_to_execute_mutation, deliberately holding mutations back when the pool is busy with regular merges, to avoid starving the merges a heavy-insert table needs to avoid its own "too many parts" failure.

Step 3 — confirm the pool really is the bottleneck, not guess. system.merges, queried without the table filter, shows what's actually consuming the shared pool right now:

SELECT database, table, is_mutation, num_parts, progress, elapsed
FROM system.merges
ORDER BY elapsed DESC
LIMIT 10;

┌─database─┬─table──────────┬─is_mutation─┬─num_parts─┬─progress─┬─elapsed─┐
│ default  │ ad_impressions │           0 │         6 │     0.71 │  184.2  │
│ default  │ ad_impressions │           0 │         4 │     0.35 │   92.6  │
│ default  │ ad_impressions │           0 │         9 │     0.12 │   41.0  │
│ ...      │ ...            │           0 │       ... │      ... │    ...  │
└──────────┴────────────────┴─────────────┴───────────┴──────────┴─────────┘

This is the actual root cause, confirmed rather than assumed: a separate, much higher-ingest table (ad_impressions) is keeping the entire shared background pool saturated with ordinary merges, and the throttling setting covered above is correctly, deliberately declining to schedule the events mutation until a slot frees up — protecting ad_impressions from a "too many parts" failure at the direct cost of events' DELETE sitting queued far longer than the team expected. This isn't a bug in either table's configuration; it's two tables competing for one shared, finite resource, invisible if you only ever look at the table you're debugging in isolation.

Step 4 — confirm the practical consequence back in familiar territory. system.mutations and system.merges explain why nothing has happened yet; system.parts — the table this course has used since Module 3 — shows what that delay actually costs events in the meantime:

SELECT count() AS active_parts, max(modification_time) AS most_recent_merge
FROM system.parts
WHERE table = 'events' AND active;

┌─active_parts─┬──most_recent_merge──┐
│          247 │  2026-07-11 09:14:02 │
└──────────────┴──────────────────────┘

An elevated part count with a most_recent_merge well behind the current time confirms the same story from a third angle: events isn't just waiting on this one DELETE — it's not getting any merges right now, mutation or otherwise, because the shared pool has no free capacity for it. All three tables now agree: the job ticket (system.mutations) says not done, the live queue (system.merges) says nothing for this table is executing, and the part count (system.parts) shows the visible consequence — one consistent narrative, not three isolated facts.

Failure mode gallery

A stuck mutation blocking every subsequent ALTER/OPTIMIZE. A real, publicly filed ClickHouse issue shows this exact shape: a user querying system.mutations found one mutation reading is_done: 0, is_killed: 0, with a real latest_fail_reason — "Unexpected size of dynamic path admin4_code: 127 != 121" — against one specific part, and a second mutation reading is_done: 0, is_killed: 1 with 956 parts still to do; every further OPTIMIZE/ALTER TABLE against that table failed until the team worked around it. The signature: a genuinely broken mutation (unlike this lesson's walkthrough, which was merely queued) doesn't just sit there quietly — it can block the entire table's future schema and optimization commands until it's explicitly resolved, typically via KILL MUTATION.

Merge starvation from mutation competition, not just insert volume. mergetree-on-disk-format's "too many parts" failure was framed around insert rate outpacing merges. This lesson's walkthrough shows the same shared-pool ceiling from the mutation side: a table running frequent, expensive mutations can starve its own merges of pool capacity just as effectively as another table's ingestion can starve a mutation, since both draw from the identical resource this lesson's mental model described.

TTL expiration silently not happening. designing-events-tables already established that TTL-expired rows are "removed when ClickHouse merges data parts" — not the instant they expire. If a table's background pool is chronically saturated (by mutations, by another table's merges, or simply by an ingestion rate that outpaces available pool capacity), the merges that would normally sweep up TTL-expired parts may simply not be getting scheduled promptly — the same system.merges emptiness this walkthrough diagnosed for a stuck mutation is exactly what you'd check to confirm this, rather than assuming TTL itself is broken.

MEMORY_LIMIT_EXCEEDED — one runaway query, or many ordinary ones stacking up. A failed query throwing this exception shows up in system.query_log with exception_code = 241 and a populated exception message.

SELECT event_time, query_id, normalized_query_hash, memory_usage, exception
FROM system.query_log
WHERE exception_code = 241 AND event_date = today()
ORDER BY event_time DESC;

A single row, for a normalized_query_hash you've never seen fail before, with a memory_usage value far larger than anything else in the same time window, is one runaway query — a one-off, usually traceable to a specific expensive join or an unexpectedly high-cardinality GROUP BY in that one query's plan. A cluster of different queries (different normalized_query_hash values) failing in the same short window, each with a comparatively ordinary memory_usage, is a different diagnosis entirely: sustained memory pressure from many concurrent queries competing for a shared limit, none of them individually unreasonable. The fix for the first case is usually query-specific (rewrite, filter earlier, or raise a per-query limit deliberately); the fix for the second is capacity or concurrency, not any single query's SQL.

One caution worth being explicit about here, because it's a genuinely common mistake in blog posts and forum answers about this exact topic: system.processes has a real peak_memory_usage column, tracking the running peak of a query still in flight — but system.query_log does not have a peak_memory_usage column at all, only memory_usage. Several secondary sources describe querying query_log.peak_memory_usage directly — that column doesn't exist there, and a query written against it fails outright. query_log.memory_usage already is that completed query's peak: it's assigned from info.peak_memory_usage when the query's status is written to the log — so a finished query's peak footprint is already sitting in query_log.memory_usage, with no need to have caught it live from system.processes while the query was still running.

Hands-on forensics: a mental checklist for "is this background job actually working?"

1. Check the job ticket. SELECT * FROM system.mutations WHERE NOT is_done for the table in question — is parts_to_do shrinking over repeated checks, or holding steady? Is latest_fail_reason populated (a real failure) or empty (not yet failed, possibly not yet scheduled)?
2. Check whether it's an active task right now. SELECT * FROM system.merges WHERE table = '...' — a matching row with is_mutation = 1 means it's genuinely executing; no row at all means it's queued, not broken.
3. If queued, check what's actually holding the pool. SELECT * FROM system.merges ORDER BY elapsed DESC with no table filter — is the pool full of long-running tasks on a different table entirely?
4. If genuinely failed, read latest_fail_reason as a real error message, not a generic timeout — it names the specific exception from the specific part that failed, which is usually enough to point at root cause without guessing.
5. Only after 1–4, consider KILL MUTATION (for a genuinely broken mutation) or waiting/freeing pool capacity (for one that's merely queued) — treating these as the same fix is how a queued-but-healthy mutation gets killed unnecessarily, or a genuinely broken one gets left to block the table indefinitely.