14. Failure modes

Every failure here is common. Each is written the same way: what you see, why, how to confirm it, what to do now, and how to stop it happening again.

1. TOO_MANY_PARTS

Symptom.

DB::Exception: Too many parts (326). Merges are processing significantly
slower than inserts.

Before the error, inserts were being artificially delayed and queries were getting steadily slower.

Cause. Inserts are too small and too frequent (chapter 6), or too many partitions are being touched per insert, or merges are starved of resources.

Diagnose.

SELECT database, table, partition, count() AS parts, sum(rows), avg(rows) AS avg_rows_per_part
FROM system.parts WHERE active
GROUP BY database, table, partition
ORDER BY parts DESC LIMIT 20;

SELECT count(), avg(rows) FROM system.part_log
WHERE event_type = 'NewPart' AND event_time > now() - INTERVAL 1 HOUR;

avg_rows_per_part in the hundreds or thousands confirms it: the inserts are too small.

Fix now. Stop or throttle the ingestion. Let merges catch up — watch system.merges and the part count fall. If a table is badly fragmented, OPTIMIZE TABLE … PARTITION … on the worst partitions, one at a time.

Prevent. Batch inserts to 10,000–1,000,000 rows. If clients cannot batch, async_insert = 1. Check max_partitions_per_insert_block if inserts span many partitions. Alert on part count per table — the error is a lagging indicator of a problem that was visible for hours.

Do not raise parts_to_throw_insert. It converts a loud failure into a slow one.

2. Memory limit exceeded

Symptom.

DB::Exception: Memory limit (for query) exceeded: would use 10.05 GiB,
maximum: 9.31 GiB

Causes, in order of likelihood:

• GROUP BY on a very high-cardinality key — the hash table does not fit.
• uniqExact on a high-cardinality column.
• A JOIN with a large table on the right (chapter 10).
• ORDER BY on a huge un-aggregated result.
• groupArray unbounded.

Diagnose.

SELECT query, formatReadableSize(memory_usage) AS mem, read_rows, exception
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing' AND event_time > now() - INTERVAL 1 HOUR
ORDER BY memory_usage DESC LIMIT 10;

Fix now. Enable spilling as a safety net:

SET max_bytes_before_external_group_by = '4G';
SET max_bytes_before_external_sort = '4G';

Prevent. uniqCombined instead of uniqExact. Smaller table on the right of joins. Aggregate before sorting. Bound groupArray(N). And if the query is one people run repeatedly, pre-aggregate it in a materialised view (chapter 11) — the query should not exist in that form.

3. A query reads the whole table

Symptom. A query that “should be fast” takes minutes. read_rows is close to the row count of the table.

Cause. The filter does not match a prefix of the ORDER BY key, or the key column is wrapped in a function.

Diagnose.

EXPLAIN indexes = 1 SELECT … ;
-- Granules: 4882312/4882312   ← reading everything

Fix now. Rewrite the filter if it is a function-wrapping problem:

-- ❌ toDate(ts) prevents index use
WHERE toDate(ts) = '2026-08-20'
-- ✅ a range on the raw column
WHERE ts >= '2026-08-20' AND ts < '2026-08-21'

Otherwise, add a LIMIT and accept it for now.

Prevent. This is chapter 5. Either the ordering key is wrong for the workload — in which case create a new table with the right key and migrate — or this access pattern needs a projection or a materialised view with a different ordering.

4. Mutations stuck forever

Symptom. An ALTER TABLE … UPDATE or DELETE never finishes. Later ALTERs on the same table queue behind it and also never run.

Cause. The mutation failed on some part and retries forever — commonly a type conversion error, a missing column on one replica, or memory exhaustion on a very large part.

Diagnose.

SELECT database, table, mutation_id, command, parts_to_do, is_done,
       latest_fail_reason, latest_fail_time
FROM system.mutations WHERE NOT is_done;

latest_fail_reason tells you exactly what is wrong. parts_to_do not decreasing over minutes confirms it is stuck rather than slow.

Fix now.

KILL MUTATION WHERE mutation_id = '0000000042' AND database = 'default' AND table = 'page_views';

Then fix the underlying issue and re-issue if you still need it.

Prevent. Avoid mutations (chapter 6). Use DROP PARTITION for retention and ReplicatedMergeTree for corrections. When you must mutate, do it partition by partition so a failure is bounded.

5. Replication lag and stuck replicas

Symptom. system.replicas shows absolute_delay climbing, or queue_size growing, or is_readonly = 1.

Causes.

• Keeper unavailable or slow → replicas cannot register or fetch.
• A slow disk on one replica.
• A very large merge occupying the queue.
• A network problem between replicas.
• A replica that lost its Keeper session and went read-only.

Diagnose.

SELECT database, table, is_readonly, absolute_delay, queue_size,
       inserts_in_queue, merges_in_queue, last_queue_update_exception,
       zookeeper_exception
FROM system.replicas WHERE absolute_delay > 10 OR is_readonly;

SELECT * FROM system.replication_queue WHERE num_tries > 5;

Fix now.

SYSTEM SYNC REPLICA default.page_views;      -- wait for it to catch up
SYSTEM RESTART REPLICA default.page_views;   -- reinitialise from Keeper
SYSTEM RESTORE REPLICA default.page_views;   -- if metadata in Keeper was lost

Check Keeper’s own health first — a Keeper problem presents as a ClickHouse problem.

Prevent. Keeper on its own nodes with fast disks. Identical hardware across replicas. Monitor absolute_delay and queue_size.

6. Keeper is down and inserts fail

Symptom.

DB::Exception: Cannot allocate block number … Cannot connect to ZooKeeper

Every insert into a replicated table fails. Reads still work.

Cause. Keeper quorum lost — fewer than a majority of Keeper nodes available — or Keeper out of memory, or its disk full.

Diagnose. Check the Keeper nodes directly (mntr four-letter command, or the Keeper logs). From ClickHouse:

SELECT * FROM system.zookeeper WHERE path = '/' LIMIT 1;   -- errors if unreachable

Fix now. Restore Keeper quorum. This is the priority — nothing else matters until it is back.

Prevent. Three or five Keeper nodes on separate machines, with monitoring, dedicated disks, and enough memory. Do not co-locate Keeper with heavy query servers. Keep an eye on Keeper’s znode count — an enormous number of parts creates an enormous number of znodes, which is another reason part count matters.

7. Disk full

Symptom. Inserts and merges fail with no space left.

Causes.

• system.query_log with no TTL. Extremely common.
• Merges needing temporary space equal to the parts being merged.
• Old parts not yet cleaned up (old_parts_lifetime).
• Data growing faster than the retention policy removes it — or no retention policy.
• shadow/ directories left behind by FREEZE that were never cleaned up.

Diagnose.

SELECT name, formatReadableSize(free_space), formatReadableSize(total_space)
FROM system.disks;

SELECT database, table, formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts WHERE active GROUP BY database, table ORDER BY sum(bytes_on_disk) DESC;

Then look at the filesystem for shadow/ and system table sizes.

Fix now. TRUNCATE TABLE system.query_log buys time immediately. DROP PARTITION on the oldest data. Remove stale shadow/ directories.

Prevent. TTL on every system log table. A retention policy (DROP PARTITION on a schedule, or table TTL). Alert at 75%, not 90% — merges need headroom, and a ClickHouse server with a full disk cannot merge its way out.

8. The SELECT * dashboard

Symptom. One dashboard makes the whole cluster slow whenever it refreshes.

Cause. A BI tool generating SELECT *, or a query with no time filter, running every 30 seconds, reading billions of rows each time.

Diagnose.

SELECT normalized_query_hash, count() AS runs, sum(query_duration_ms) AS total_ms,
       formatReadableQuantity(sum(read_rows)) AS rows,
       any(substring(query,1,120)) AS sample
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 DAY
GROUP BY normalized_query_hash ORDER BY total_ms DESC LIMIT 10;

Fix now. Put the dashboard’s user on a profile with max_execution_time and max_rows_to_read.

Prevent. Point dashboards at a materialised view, not at raw tables (chapter 11). Give BI tools a restricted user. Expose views with sensible defaults rather than raw tables — a view with a mandatory date filter prevents an entire class of accident.

9. Duplicated data after a retry

Symptom. Counts are higher than expected. Rows appear twice.

Causes.

• An insert retried after a timeout where the first attempt actually succeeded, and the block was not byte-identical so deduplication did not apply.
• Kafka consumer offsets not committed after a successful write, so messages were reprocessed.
• Deduplication applies only to replicated tables and only within insert_deduplication_window (~100 blocks).

Diagnose.

SELECT event_id, count() FROM events
GROUP BY event_id HAVING count() > 1 LIMIT 10;

Fix now. Deduplicate with argMax, or OPTIMIZE ... DEDUPLICATE for a one-off, or rebuild the affected partition from source.

Prevent. Use ReplacingMergeTree with a version column so duplicates converge (chapter 4). Set an explicit insert_deduplication_token per logical batch. Design queries to be duplicate-tolerant where you can.

10. Merges never catch up under sustained load

Symptom. Part count slowly climbing over days. Queries getting steadily slower. No error yet.

Cause. The server is at capacity: merges and queries and inserts are all competing, and merges lose. Often triggered by adding materialised views (each insert now does more work), or by ingestion growing gradually past what the disk can sustain.

Diagnose.

SELECT count() FROM system.merges;
SELECT value FROM system.metrics WHERE metric = 'BackgroundMergesAndMutationsPoolTask';

SELECT toStartOfHour(event_time) AS h, count()
FROM system.part_log WHERE event_type = 'MergeParts' AND event_time > now() - INTERVAL 1 DAY
GROUP BY h ORDER BY h;

Compare merge rate against insert rate over the same window.

Fix now. Reduce ingestion, increase batch size, or temporarily raise background_pool_size if the disk has headroom.

Prevent. Capacity planning: merges cost roughly as much I/O as the data being written, again. Budget for it. Fewer, larger inserts reduce merge work substantially. Fewer materialised views on the hottest tables.

Honourable mentions

A dictionary silently stale — system.dictionaries.last_exception is set and nobody looks. Queries return old values with no error.

LEFT JOIN returning 0 instead of NULL — join_use_nulls = 0 by default, and zeros look like data.

Summing pre-aggregated uniques. Plausible, wrong numbers.

OPTIMIZE FINAL in a cron job. Rewrites the table repeatedly, competing with everything.

A Kafka engine table read by a curious human — the messages are consumed and gone.

Reading an AggregatingMergeTree column without -Merge and getting binary blobs.

A setting changed years ago by someone who left. SELECT * FROM system.settings WHERE changed.

Timezone confusion in toStartOfDay — the server timezone, the column’s declared timezone and the client’s may all differ.

The general method

   1. system.errors                    what is failing, and how often
   2. system.parts (count per table)   is the part count healthy?
   3. system.processes                 what is running right now
   4. system.merges                    are merges keeping up?
   5. system.replicas                  lag, queue, readonly
   6. system.mutations                 anything stuck?
   7. system.query_log                 what has been slow, by total time
   8. system.disks                     space

Those eight queries take two minutes and identify nearly everything.

What to take from this chapter

• The two failures you will actually meet are TOO_MANY_PARTS (batch bigger) and memory limit exceeded (approximate aggregates, smaller right-hand joins, pre-aggregate).
• A query reading the whole table means the ordering key does not match the query — check EXPLAIN indexes = 1, and remember that wrapping a key column in a function disables the index.
• Mutations get stuck and block later ones. system.mutations, latest_fail_reason, KILL MUTATION.
• Keeper down means no inserts, reads unaffected. Restore quorum first, diagnose second.
• Disk fills from unbounded system.query_log more often than from data. TTL the log tables; alert at 75%.
• Merges falling behind is a slow-motion failure visible days before the error. Alert on part count.
• Give dashboards materialised views and a restricted profile, not raw tables.
• The eight-query triage above covers nearly every incident.

Next: the honest argument about when not to use ClickHouse at all.