clickhouse.guru
Tutorials· advanced

The ClickHouse Production Incident Playbook

Eight incident classes, each as symptom, first checks in SQL, root-cause mechanism, fix and what to watch: memory kills, too many parts, replication backlog, stuck mutations, hanging metadata queries, one saturated node, detached parts and rising error rates.

This is the page to open when a ClickHouse node is misbehaving and you do not yet know why. It is organised by what you see, not by subsystem. Each incident class has the same parts: symptom, first checks in SQL, the mechanism behind it, the fix, and what to watch so it does not return. Most carry an In production box: a failure worked through on a real estate, retold with identifying detail removed and numbers reduced to orders of magnitude.

Two habits apply everywhere. Find the node first: most “the cluster is slow” incidents are one node, so run each check there and on a healthy peer and read the difference (system tables are per node; clusterAllReplicas('my_cluster', system.parts) asks all of them). Separate victims from causes: the query that fails is rarely the one that caused the failure. That method is in Investigating a rising ClickHouse error rate and is not repeated here.

How the SQL was checked. Queries on tables that exist in clickhouse local (system.parts, mutations, detached_parts, dropped_tables, errors, view_refreshes, events) were run on 26.4 and their output is real. query_log, part_log, trace_log, metric_log, the contents of replication_queue and anything Keeper-related exist only on a server: those queries were syntax-checked with clickhouse format, and any output shown comes from stand-in tables with the real column names and synthetic data, built in the appendix. Read such numbers as shapes, not measurements.

Companions: the runbooks for parts, merges and mutations, replication and cluster, queries and errors, storage and disk and users, settings and security (more under /snippets/), how MergeTree stores data, how replication works and the part pressure calculator.

Contents

1. Memory: OOM kills and limit errors

Symptom. Queries fail with MEMORY_LIMIT_EXCEEDED (code 241). Or the server process vanishes and restarts (exit code 137 in a container) and every running query dies with it.

First checks. The wording of the error says which of three limits fired. Same 60-million-key aggregation, three different limits:

Q="SELECT number % 20000000 AS k, uniqExact(number) FROM numbers(60000000) GROUP BY k FORMAT Null"
clickhouse local --max_memory_usage=200000000 -q "$Q"
clickhouse local --max_memory_usage_for_user=200000000 -q "$Q"
clickhouse local --config-file c.xml -q "$Q"   # c.xml sets <max_server_memory_usage>300000000</max_server_memory_usage>
Code: 241. DB::Exception: Query memory limit exceeded: would use 321.27 MiB (attempt to allocate chunk of 130.56 MiB), maximum: 190.73 MiB: ...
Code: 241. DB::Exception: User memory limit exceeded: would use 199.83 MiB (attempt to allocate chunk of 4.13 MiB), maximum: 190.73 MiB. ...
Code: 241. DB::Exception: (total) memory limit exceeded: would use 361.14 MiB (attempt to allocate chunk of 0.00 B), current RSS: 360.64 MiB, maximum: 286.10 MiB: ...
Message starts with Limit Blame
Query memory limit exceeded max_memory_usage This query.
User memory limit exceeded max_memory_usage_for_user The user’s queries together; this one may be a victim.
(total) memory limit exceeded max_server_memory_usage Whoever allocated last, probably a victim.

For the total limit, find who holds memory now. This closes the books on the tracked total; the remainder is caches and buffers:

WITH (SELECT toUInt64(value) FROM system.metrics WHERE metric = 'MemoryTracking') AS tracked
SELECT consumer, formatReadableSize(bytes) AS size, round(100 * bytes / tracked) AS pct
FROM
(
    SELECT 'running queries' AS consumer, toUInt64(sum(memory_usage)) AS bytes FROM system.processes
    UNION ALL SELECT 'merges and mutations', toUInt64(sum(value)) FROM system.metrics WHERE metric = 'MergesMutationsMemoryTracking'
    UNION ALL SELECT 'dictionaries', toUInt64(sum(bytes_allocated)) FROM system.dictionaries
    UNION ALL SELECT 'primary keys', toUInt64(sum(primary_key_bytes_in_memory)) FROM system.parts WHERE active
    UNION ALL SELECT 'tracked total', tracked
)
ORDER BY bytes DESC;

On one production node measured this way the books closed within a few percent: queries about two thirds, dictionaries a fifth, primary keys a tenth, merges under 2%, and at the busiest second 90% of memory was queries. Replication fetches cost no measurable memory.

For history, use query_log around the failure (stand-in output). The user with all the errors has the smallest queries; the cause never failed:

SELECT user, log_comment, countIf(exception_code = 241) AS oom_errors, count() AS finished,
       formatReadableSize(max(memory_usage)) AS biggest_query
FROM query_log_demo
WHERE type != 'QueryStart' AND event_time BETWEEN '2026-01-01 10:07:00' AND '2026-01-01 10:08:00'
GROUP BY user, log_comment
ORDER BY max(memory_usage) DESC;
   ┌─user─┬─log_comment───┬─oom_errors─┬─finished─┬─biggest_query─┐
1. │ etl  │ job=rollup    │          0 │        2 │ 27.94 GiB     │
2. │ bi   │ app=dashboard │          0 │        5 │ 1.86 GiB      │
3. │ api  │ app=web       │         15 │       76 │ 286.10 MiB    │
   └──────┴───────────────┴────────────┴──────────┴───────────────┘

This needs a log_comment (or at least a distinct user) on every job and service. If the server was killed, the holder never wrote a finish row, so look for queries that started and never finished:

SELECT query_id, user, log_comment, event_time
FROM query_log_demo
WHERE type = 'QueryStart' AND query_id NOT IN (SELECT query_id FROM query_log_demo WHERE type != 'QueryStart');
   ┌─query_id─┬─user─┬─log_comment────────┬──────────event_time─┐
1. │ orphan-1 │ etl  │ job=delta-backfill │ 2026-01-01 10:07:41 │
   └──────────┴──────┴────────────────────┴─────────────────────┘

Log rows are buffered, so a SIGKILL loses the last seconds, and a query whose whole life fits in that window leaves nothing. Then use the memory profiler: system.trace_log rows with trace_type = 'MemoryPeak' carry query_id and size.

SELECT query_id, formatReadableSize(max(size)) AS peak, min(event_time) AS first_seen, max(event_time) AS last_seen
FROM system.trace_log
WHERE trace_type = 'MemoryPeak' AND event_time > now() - INTERVAL 1 HOUR
GROUP BY query_id ORDER BY max(size) DESC LIMIT 5;

Mechanism.

  1. Two counters. ClickHouse enforces its limit on tracked memory, by default max_server_memory_usage_to_ram_ratio = 0.9 of RAM or of the container limit. The kernel kills on the container’s total, which includes page cache. It can drop cache instantly but only at a finite rate, so an allocation ramp faster than reclaim hits the wall while the server is still inside its budget. The gap is the 10% you left. cgroups_memory_usage_observer_wait_time (15 s) is not a polling interval for usage: its description says it controls how often the server’s maximum is adjusted to the cgroup threshold.
  2. The limit fires on the next allocation. Refusing new allocations does not make the holder release what it has, so a node can throw hundreds of 241s and still be killed.
  3. Spilling is partial. On 26.4, max_bytes_ratio_before_external_group_by and its sort twin default to 0.5, and there is no such ratio for joins (use grace_hash or partial_merge; see joins, memory and algorithms). The ratio is taken against available memory at the start of each query, so N concurrent queries each believe they may use half. Same aggregation, 300 MB limit, spilling off, an absolute threshold, then the default:
Q="SELECT count() FROM (SELECT number % 5000000 AS k, sum(number) s FROM numbers(30000000) GROUP BY k)"
clickhouse local --max_memory_usage=300000000 --max_bytes_ratio_before_external_group_by=0 -q "$Q"
clickhouse local --max_memory_usage=300000000 --max_bytes_before_external_group_by=100000000 -q "$Q"
clickhouse local --max_memory_usage=300000000 -q "$Q"
Code: 241. DB::Exception: Query memory limit exceeded: would use 320.25 MiB (attempt to allocate chunk of 256.00 MiB), maximum: 286.10 MiB: ...
5000000
5000000

With a limit of 0 (the default) the ratio is applied against server memory, which is why it rarely engages. Some states cannot spill at all: SELECT uniqExact(x) without GROUP BY has one huge state and nothing to split; the spill setting does not save it. 4. Everything shares the pool. Merges (more columns, more memory), the query inside a materialized view (it runs within the triggering INSERT), dictionaries and primary keys all draw from the same budget.

Fix. Kill the holder (KILL QUERY WHERE query_id = '...'). Make the next one fail alone, with limits in a profile so they follow the user:

ALTER SETTINGS PROFILE etl SETTINGS max_memory_usage = 40000000000, max_bytes_before_external_group_by = 10000000000;

Keep the spill threshold below the cap so a query degrades before it fails. Lowering the server ratio converts kills into 241s, a good trade: a failed query costs one retry, a restart costs every running query plus the caches. Spread heavy jobs across nodes (settings profiles runbook).

Prevent and watch. Alert on tracked memory as a fraction of the limit, per node, and on restarts: Uptime going backwards in asynchronous_metric_log. One node restarting is a kill; every node within a minute is a deploy.

SELECT host, event_time, prev_uptime, value AS uptime
FROM (SELECT hostName() AS host, event_time, value, lagInFrame(value) OVER (PARTITION BY host ORDER BY event_time) AS prev_uptime
      FROM clusterAllReplicas('my_cluster', system.asynchronous_metric_log) WHERE metric = 'Uptime')
WHERE value < prev_uptime;

In production: A node in a containerised cluster was killed by the kernel several times within weeks, after none in the previous year, while ClickHouse’s own limit never fired: at the last sample before two of three kills the server was tracking tens of GB below it. The cause was an allocation ramp of about 1 GB/s for roughly 100 seconds from a batch job with no per-query cap, outrunning cache reclaim inside the 10% gap. Only one node was hit because the scheduler pinned the heavy jobs to it. A proposed first fix, polling the container more often, was struck once the setting’s description showed it only adjusts the limit. A later kill on a smaller read-serving cluster did hit the limiter first, yet the holder was absent from query_log and showed only in trace_log peaks; an attribution made from the log alone had to be withdrawn.

2. Too many parts: inserts delayed or rejected

Symptom. INSERTs that took milliseconds take a second or more, or fail with TOO_MANY_PARTS (code 252, “Merges are processing significantly slower than inserts”). Queries on the table slow down in step.

First checks. The limits apply per partition, so count active parts per partition, then check whether the server is already delaying or refusing inserts and whether merges are running:

SELECT partition, count() AS parts, sum(rows) AS total_rows, round(avg(rows)) AS avg_rows_per_part
FROM system.parts WHERE database = 'default' AND `table` = 'events' AND active
GROUP BY partition ORDER BY parts DESC LIMIT 10;

SELECT event, value FROM system.events WHERE event IN ('DelayedInserts', 'RejectedInserts', 'DelayedInsertsMilliseconds') ORDER BY event;
SELECT table, count() AS running, max(elapsed) AS longest_s FROM system.merges GROUP BY table;
SELECT metric, value FROM system.metrics WHERE metric LIKE 'BackgroundMergesAndMutationsPool%';

To see it, create events with parts_to_delay_insert = 5, parts_to_throw_insert = 8, run SYSTEM STOP MERGES events and insert ten rows at a time. After seven inserts:

   ┌─event──────────────────────┬─value─┐
1. │ DelayedInserts             │     2 │
2. │ DelayedInsertsMilliseconds │   999 │
   └────────────────────────────┴───────┘
   ┌─partition─┬─parts─┬─total_rows─┬─avg_rows_per_part─┐
1. │ tuple()   │     7 │         70 │                10 │
   └───────────┴───────┴────────────┴───────────────────┘

The eighth insert fails: Code: 252. DB::Exception: Too many parts (8 with average size of 865.00 B) ... (TOO_MANY_PARTS). A non-zero DelayedInserts means you are already in the delay band, before any error. Thousands of parts of a few hundred rows means tiny inserts; parts of healthy size mean merges are the problem. Who creates the parts is a part_log question (NewPart by table and hour), covered in the parts runbook.

Mechanism. Each INSERT writes at least one part per partition it touches, and merges combine them. When parts arrive faster than they merge, the count climbs. On 26.4 the defaults are parts_to_delay_insert = 1000 (inserts slowed, from min_delay_to_insert_ms = 10 ms towards max_delay_to_insert = 1 s), parts_to_throw_insert = 3000 (inserts fail) and max_parts_in_total = 100000. Merges fall behind when inserts are too small, when the partition key is so fine that each batch spans many partitions, when the pool is full of something long (section 4), when the disk cannot sustain merge throughput (sections 3 and 6), or when merges were stopped and never restarted.

Parts also tax reads. The same 200 000 rows sat in a one-part table and a 100-part table (merges stopped); the same point query on sensor_id ran against each, and FileOpen in system.events was read around it:

   ┌─file_opens_1_part─┬─file_opens_100_parts─┐
1. │                 3 │                  201 │
   └───────────────────┴──────────────────────┘

About 70 times the file operations for identical logical work, which on a busy disk is the difference that matters.

Fix. Find the writer creating the parts and batch it (insert batching, async inserts). Raising parts_to_throw_insert to let a burst through is a loan: the parts must still be merged and every query pays meanwhile. If the pool is held by one huge merge or mutation, see section 4; if the disk is the limit, no MergeTree setting helps. The part pressure calculator estimates the parts an insert pattern generates.

Prevent and watch. Alert on the maximum parts per partition at 30 to 50% of parts_to_delay_insert, and on any growth of DelayedInserts. Both fire hours before the first error.

In production: On a replica that had fallen behind, partitions held about half as many parts again as on its peers, roughly a hundred per partition against seventy. That was a symptom: merges on the node ran two orders of magnitude slower because its disk was the bottleneck. More parts meant more scattered reads per query, which spent more of the disk’s small operation budget per useful byte, which slowed merges further. The loop sustained itself, so the node could not recover alone and the fix had to reduce the work it was given.

3. Replication backlog, queue stall and read-only replicas

Symptom. absolute_delay on one replica grows from seconds to minutes or hours, queue_size is in the thousands, reads from it are stale. Or a replica goes read-only and INSERTs fail with TABLE_IS_READ_ONLY (code 242).

First checks. Ask system.replicas for anything unhealthy (the replica health query has a one-query version, and the replication runbook has the rest):

SELECT database, `table`, is_readonly, is_session_expired, queue_size, inserts_in_queue, merges_in_queue,
       absolute_delay, log_pointer, log_max_index, active_replicas, total_replicas, last_queue_update_exception, zookeeper_exception
FROM system.replicas
WHERE is_readonly OR is_session_expired OR queue_size > 100 OR absolute_delay > 60
FORMAT Vertical;

If the replica is not read-only, group its queue; what the entries say decides the class of problem (stand-in data):

SELECT `table`, type, count() AS entries, max(num_tries) AS max_tries, max(num_postponed) AS max_postponed,
       round((now() - min(create_time)) / 3600, 1) AS oldest_h,
       any(substring(last_exception, 1, 38)) AS exception, any(substring(postpone_reason, 1, 26)) AS postponed
FROM rq_demo GROUP BY `table`, type ORDER BY entries DESC;
   ┌─table───┬─type────────┬─entries─┬─max_tries─┬─max_postponed─┬─oldest_h─┬─exception──────────────────────────────┬─postponed──────────────────┐
1. │ events  │ GET_PART    │     300 │         0 │             0 │      3.3 │                                        │                            │
2. │ events  │ MERGE_PARTS │      40 │         3 │           180 │     30.5 │                                        │ Not executing log entry: s │
3. │ metrics │ GET_PART    │       3 │      5002 │             0 │       72 │ Code: 234. No active replica has part  │                            │
   └─────────┴─────────────┴─────────┴───────────┴───────────────┴──────────┴────────────────────────────────────────┴────────────────────────────┘

Row 1 is a healthy backlog: many entries, no exceptions, few retries. Row 2 is postponed work: merges rescheduled because a limit stops them starting. Row 3 is a failing entry: fetches retried thousands of times because no replica has the part. Then compare with a peer: the same summary there, and median MergeParts duration_ms per hostName() from part_log.

Mechanism. Every replica copies entries from a shared log in Keeper into its own queue (fetch this part, do this merge, apply this mutation, drop this range) and executes them as pool slots and bandwidth allow. A backlog means the execution rate is below the rate the log grows:

  • Capacity. Deep queue, no exceptions, slow merges. The disk, CPU or network cannot keep up. One replica lagging usually means it is given more work than its peers (more writers, more reads) or has slower storage; all replicas lagging means the shard is under-provisioned.
  • A limit. postpone_reason is set. TTL merges, for example, are capped by max_number_of_merges_with_ttl_in_pool (2); see TTL merges postponed. It clears when pressure drops.
  • A broken entry. last_exception set, num_tries large: a part missing everywhere, a checksum mismatch, a disk error. It does not block other entries, but the oldest unfinished one sets the lag floor.
  • Keeper trouble. is_session_expired or zookeeper_exception is set and the replica goes read-only: it has lost coordination, not fallen behind.

Fix. Read-only: check the Keeper ensemble first; SYSTEM RESTART REPLICA db.table re-creates the session, and SYSTEM RESTORE REPLICA rebuilds Keeper metadata from local parts when it is gone. Neither clears a backlog. For a capacity backlog take work off the replica in order of reversibility: move or re-route writers concentrated on it (free, reversible, and a test of your attribution: if the queue drains, you were right), then change how clients pick a host, then upgrade storage. A failing entry whose part is gone from every replica is data loss to handle deliberately, not retry forever. SYSTEM SYNC REPLICA only waits.

Prevent and watch. Alert on queue depth and absolute_delay relative to peer replicas, since one lagging replica among healthy ones is what an absolute threshold hides. Watch the age of the oldest entry: a short queue with a three-day-old entry is the failing-entry case.

In production: On a test cluster one replica carried the whole backlog for months while its peers sat at zero: a queue in the thousands, a delay of about twenty minutes, merges roughly a hundred times slower, no exceptions in any entry. The first theory, that a client library always wrote to the first host, was wrong and cost two colleagues an afternoon: the checkout it was read from was thousands of commits behind, and current code already pinned each scheduled job to a host by hashing its name. A ten-second sample suggested a peer was idle; over a day the peers were equivalent. What held up was a data volume with a flat operation cap, all three nodes within about 12% of it, and one node pushed over because several of the heaviest writers landed on it: by hash, plus one hard-coded exception. Comparing OS-level device operations with the volume’s billed limit, different units, had flipped the conclusion twice.

4. Stuck mutations and postponed TTL merges

Symptom. An ALTER TABLE ... DELETE or UPDATE that never finishes: is_done = 0 for hours while disk usage grows. Or data that should have expired by TTL is still there. Or a replica queue fills with MUTATE_PART entries.

First checks.

SELECT mutation_id, parts_to_do, is_done, substring(latest_fail_reason, 1, 70) AS fail, latest_failed_part
FROM system.mutations WHERE `table` = 'events' AND NOT is_done ORDER BY create_time;

A non-empty latest_fail_reason is the whole story. To reproduce it, load two parts into events (url values like /p0 in one, /ok in the other), issue an UPDATE that parses a number from url, then a harmless DELETE behind it:

ALTER TABLE events UPDATE duration_ms = toUInt32(substring(url, 2)) WHERE 1 SETTINGS mutations_sync = 0;
ALTER TABLE events DELETE WHERE country = 'XX' SETTINGS mutations_sync = 0;
   ┌─mutation_id────┬─parts_to_do─┬─is_done─┬─fail───────────────────────────────────────────────────────────────────┬─latest_failed_part─┐
1. │ mutation_3.txt │           2 │       0 │ Code: 6. DB::Exception: Cannot parse string 'ok' as UInt32: syntax err │ all_2_2_0          │
2. │ mutation_4.txt │           2 │       0 │ Code: 6. DB::Exception: Cannot parse string 'ok' as UInt32: syntax err │ all_2_2_0          │
   └────────────────┴─────────────┴─────────┴────────────────────────────────────────────────────────────────────────┴────────────────────┘

The DELETE is fine yet cannot proceed. Killing the failing mutation releases it:

KILL MUTATION WHERE database = 'default' AND `table` = 'events' AND mutation_id = 'mutation_3.txt';

A few seconds later only mutation 4 is listed, with is_done = 1. With an empty latest_fail_reason the mutation is waiting, not failing: check system.merges (is_mutation), BackgroundMergesAndMutationsPoolTask against ...PoolSize in system.metrics, and on replicated tables the queue’s MUTATE_PART rows and postpone_reason.

For TTL, ask which parts still hold expired rows; system.parts carries the TTL range:

SELECT name, rows, delete_ttl_info_max < now() AS fully_expired FROM system.parts WHERE `table` = 'metrics' AND active ORDER BY name;

A demo table with TTL ts + INTERVAL 30 DAY, one part of 90-day-old rows and one fresh, and SYSTEM STOP TTL MERGES in force: OPTIMIZE TABLE metrics FINAL changes nothing (202607_1_1_1 stays at 1000 rows with fully_expired = 1) and count() still returns 2000. After SYSTEM START TTL MERGES and another OPTIMIZE ... FINAL, count() returns 1000 and the expired part has 0 rows. Someone stopping TTL merges for maintenance and forgetting is a classic cause.

Mechanism. A mutation is a rewrite. Mutations are queued per table and applied to each part in submission order, so one failing mutation blocks every one behind it, as above. They share a pool with merges (background_pool_size 16 times background_merges_mutations_concurrency_ratio 2 gives 32 slots on 26.4), and a mutation does not start while fewer than number_of_free_entries_in_pool_to_execute_mutation (20) slots are free. A pool full of big merges starves mutations, and a campaign of small DELETEs rewriting the same big parts fills the pool.

TTL is not background deletion; it happens as a side effect of merging, when a part is next rewritten. The limits: merge_with_ttl_timeout (14400 s between TTL merges of a partition), max_number_of_merges_with_ttl_in_pool (2) and the stop switch. ttl_only_drop_parts (0 by default) lets a fully expired part be dropped whole, which works well when the partition key lines up with the TTL.

Fix. Kill a failing mutation, fix the expression, resubmit. A waiting mutation with parts_to_do falling is progress: wait or free the pool. Submit one mutation for a whole batch of deletions instead of one per row set, or use lightweight DELETE (mutations, lightweight DELETE and TTL). For perpetual retention, partition by time and drop partitions: metadata only. MATERIALIZE TTL forces TTL onto old parts but is itself a mutation.

Prevent and watch. Alert on mutations that are old and have a latest_fail_reason, and separately on mutations older than a day without one (starved). See watching mutation progress.

In production: A retention job issued DELETE mutations continuously against a large table and the node it ran on fell far behind on merges (the same incident is in section 6). Separately, when an upstream bug report described a merge pool wedged permanently at full, the question was whether the estate had it. The check was the per-second pool metric retained in metric_log: the pool filled and drained repeatedly, the longest continuous full stretch was about twenty minutes, and no mutation was open anywhere. Busy, not wedged; the concurrency setting stayed at its default, and the remedy was to stop issuing the mutations.

5. A metadata query hangs

Symptom. SHOW DATABASES, SHOW TABLES or a query on system.tables or system.columns hangs for seconds or minutes while SELECT 1 is instant. It may be intermittent, or only from some clients.

First checks. Establish whether the query reached the server; everything depends on it. On 26.4 log_queries_min_type defaults to QUERY_START, so a query that arrived has a row even if it never finished:

SELECT type, event_time, query_duration_ms FROM system.query_log
WHERE query ILIKE 'show databases%' ORDER BY event_time DESC LIMIT 20;

If the hung attempt has no row, the problem is before the server: the connection, a proxy or the client. If it does, see what the server is doing (distributed_ddl_queue needs a server with Keeper):

SELECT query_id, user, elapsed, substring(query, 1, 80) AS q FROM system.processes ORDER BY elapsed DESC LIMIT 10;
SELECT entry, host, status, query_create_time, substring(query, 1, 60) AS q
FROM system.distributed_ddl_queue WHERE status != 'Finished' ORDER BY query_create_time DESC LIMIT 10;
SELECT `table`, engine, dateDiff('second', now(), table_dropped_time) AS seconds_until_data_removed FROM system.dropped_tables;

The last query lists tables dropped but not yet gone. Immediately after DROP TABLE big_table, when system.tables already returned nothing for it:

   ┌─table─────┬─engine────┬─seconds_until_data_removed─┐
1. │ big_table │ MergeTree │                        480 │
   └───────────┴───────────┴────────────────────────────┘

Mechanism. Four causes.

  • Before the server. A TCP connect proves little: a gateway or load balancer can accept it and then stall or reset the ClickHouse handshake. An interactive clickhouse-client also opens a second connection at startup to load autocompletion suggestions, and if that is the one that stalls, your SHOW DATABASES is never sent. clickhouse-client -A (--disable_suggestion) turns the loader off. Failures uniform across nodes and absent from server logs point at the shared path.
  • Locks. Statements that read table structure can take a shared lock per table, and a long ALTER, DROP, RENAME or EXCHANGE can hold a conflicting one. The waiter gives up after lock_acquire_timeout (120 s) with DEADLOCK_AVOIDED (473). Listings that touch every table, like system.columns, are the likely victims. Find the DDL in system.processes and the waiting stack in system.stack_trace (symbolising it needs allow_introspection_functions).
  • Slow DDL. ON CLUSTER statements wait for every host up to distributed_ddl_task_timeout (180 s), then report a timeout while the work continues.
  • Dropped but not gone. In an Atomic database DROP TABLE leaves the catalog at once but deletes data only after database_atomic_delay_before_drop_table_sec (480 s), so space is not freed, and a tool that recreates the same name races the deletion. DROP TABLE ... SYNC waits for the real removal, which takes as long as the table is big (drop table ON CLUSTER SYNC). UNDROP TABLE can restore within the window on a server; it failed in clickhouse local, so test it where you will rely on it.

Fix. Per cause: repair or bypass the connection path (and use -A while debugging it); kill the DDL holding the lock, or wait; find the host that has not finished an ON CLUSTER statement; use SYNC where you need the space now.

Prevent and watch. Run a synthetic SELECT count() FROM system.tables from the path your people use, so a connection fault shows as a failed probe rather than a hang in someone’s terminal. Do not stack DDL on one table.

In production: Someone’s SHOW DATABASES against one node hung. The first theory was lock contention, because that node had the most databases and the query was mostly fast and occasionally took ten seconds. It was wrong. Every SHOW DATABASES that executed finished in milliseconds, and the hung attempt had no QueryStart row, so it never reached the server. About one connection in ten stalled or was reset during the handshake, uniformly on all nodes, on a gateway path in front of them, and the interactive client’s suggestion loader made it visible. The direct per-node hostnames in use were a second, unrelated fault: a dead endpoint, TCP open with nothing behind it.

6. CPU saturation on one node

Symptom. One node’s load average and query latency are high while its peers are fine; dashboards say “saturated”.

First checks. Decide what is saturated first. Load average counts runnable and uninterruptible threads, so a node waiting on a slow disk shows high load with idle CPUs. top and iostat -x answer it on the host; so do the profile events, per window:

SELECT toStartOfMinute(event_time) AS minute,
       round(sum(ProfileEvent_OSCPUVirtualTimeMicroseconds) / 1e6) AS cpu_s,
       round(sum(ProfileEvent_OSCPUWaitMicroseconds) / 1e6) AS runnable_wait_s,
       round(sum(ProfileEvent_OSIOWaitMicroseconds) / 1e6) AS io_wait_s
FROM system.metric_log WHERE event_time > now() - INTERVAL 30 MINUTE GROUP BY minute ORDER BY minute;

High cpu_s is compute; high runnable_wait_s means more runnable threads than cores; high io_wait_s with modest CPU is a storage-bound node, which nothing below fixes (go to section 3 and the storage runbook). If it is CPU, rank query shapes by CPU seconds (stand-in output: a dashboard repeating a heavy query is 86% of the CPU, 600 API calls are 1%):

SELECT normalized_query_hash AS hash, any(log_comment) AS comment, count() AS runs,
       round(sum(ProfileEvents['OSCPUVirtualTimeMicroseconds']) / 1e6) AS cpu_seconds,
       round(100 * cpu_seconds / sum(cpu_seconds) OVER ()) AS pct
FROM query_log_demo WHERE type = 'QueryFinish' GROUP BY hash ORDER BY cpu_seconds DESC;
   ┌─hash─┬─comment───────┬─runs─┬─cpu_seconds─┬─pct─┐
1. │    3 │ app=dashboard │   40 │        1200 │  86 │
2. │    7 │ job=rollup    │    2 │         180 │  13 │
3. │    1 │ app=web       │  600 │          12 │   1 │
   └──────┴───────────────┴──────┴─────────────┴─────┘

Then the recurring work nobody owns. Refreshable materialized views run on a timer whether or not anyone needs them:

SELECT view, status, last_success_duration_ms, formatReadableSize(read_bytes) AS read_per_refresh,
       next_refresh_time > now() AS scheduled, exception
FROM system.view_refreshes;
   ┌─view─────────────┬─status────┬─last_success_duration_ms─┬─read_per_refresh─┬─scheduled─┬─exception─┐
1. │ mv_distinct_scan │ Scheduled │                       18 │ 152.59 MiB       │         1 │           │
   └──────────────────┴───────────┴──────────────────────────┴──────────────────┴───────────┴───────────┘

(A uniqExact over 20 million demo rows.) Multiply by the refresh frequency: a view scanning a big table every five minutes is constant read load that no one sees as a query. Finally compare with a peer: the CPU-per-hash query through clusterAllReplicas grouped by hostName(), plus the parts-per-partition comparison from section 2.

Mechanism. Saturation has three faces that look alike on a dashboard. Compute: sorting, aggregation, decompression, parsing, merges of wide tables. Storage: the device queue is deep, threads wait, CPUs idle. Background tax: merge and mutation slots, refreshable views and consumers compete with foreground queries and are invisible in the per-query view. Skew between nodes comes from routing (clients that take the first host, jobs pinned to a host by hash) and from state: a node with more parts does more file operations for the same query (section 2).

Fix. Cap what one workload can take in its profile: max_threads, max_concurrent_queries_for_user, max_execution_time. Move or reschedule the hog. Lengthen a refreshable view’s period or make its query read only the new window. Background pool: section 4. Storage: change it, or reduce the work assigned to the node.

Prevent and watch. Keep a daily rollup of CPU seconds and bytes read per query shape so “what changed” is a diff (tracking query and resource trends), and put every node beside its peers on every dashboard.

In production: One node was described as saturated. The first hypothesis, the write-pinning pattern of an earlier incident, was refuted: reads and writes were balanced. What was anomalous was that node’s merge throughput, a few MB/s against more than a hundred on its peers, about a fortieth, with a deep device queue on a throughput-oriented network disk. It was storage, not CPU. Two things had pushed it over: a long run of DELETE mutations had left it behind on merges, and two refreshable views, each running every five minutes, scanned the recent window of the largest table with a DISTINCT and an anti-join. The views were dropped (definitions saved first) and a request for faster volumes went to the infrastructure team with the evidence.

7. Detached and broken parts housekeeping

Symptom. Disk use exceeds what the data explains, system.detached_parts has thousands of rows, a table has fewer rows after a restart or rebuild, or the log shows “broken part” lines at startup.

First checks. Group detached parts by reason, because the reason separates garbage from decisions:

SELECT database, `table`, reason, count() AS parts, formatReadableSize(sum(bytes_on_disk)) AS size, min(modification_time) AS oldest
FROM system.detached_parts GROUP BY database, `table`, reason ORDER BY sum(bytes_on_disk) DESC;

In the demo, three monthly parts were inserted, one partition detached by hand (ALTER TABLE metrics DETACH PARTITION 202601), and one part’s data file overwritten with garbage before restarting on the same path:

   ┌─database─┬─table───┬─reason──────────┬─parts─┬─size─────┬──────────────oldest─┐
1. │ default  │ metrics │                 │     1 │ 9.29 KiB │ 2026-10-14 16:10:21 │
2. │ default  │ metrics │ broken-on-start │     1 │ 1.28 KiB │ 2026-10-14 16:10:21 │
   └──────────┴─────────┴─────────────────┴───────┴──────────┴─────────────────────┘

The manual detach has an empty reason; the broken part carries one, the prefix of its directory (broken-on-start_202603_3_3_0). Now the check people forget: SELECT count() returned 2000 before the restart and 1000 after it. The broken part was set aside at startup and its rows vanished from the table.

Mechanism. DETACH moves a part into the table’s detached directory, and ClickHouse never deletes from there itself. Parts get there when a person detaches them (empty reason: a decision) or when the server sets one aside and records why in the prefix:

Reason Typically means
broken-on-start The part failed its checks when the table loaded.
covered-by-broken A broken part covers its range, so it was set aside too.
ignored Skipped at startup, typically because other parts already cover the same data.
clone Set aside when a replica was re-cloned from another.
noquorum, unexpected Left by a failed quorum insert, or not expected by the replica’s metadata.

A replicated table refetches a broken part from another replica. A table with no other copy has lost the rows, and the detached copy is the only evidence, so read before you drop. max_suspicious_broken_parts (100 by default) is the guard against silently discarding too many parts at startup.

Fix. Script it per node, since detached parts are node-local. Generate the statements from the system table so they reflect current state, and restrict them to system reasons so manual detaches stay for a human:

SELECT concat('ALTER TABLE `', database, '`.`', `table`, '` DROP DETACHED PART ''', name, ''';') AS stmt
FROM system.detached_parts WHERE reason != '' FORMAT TSVRaw;
ALTER TABLE `default`.`metrics` DROP DETACHED PART 'broken-on-start_202603_3_3_0';

Run the output with allow_drop_detached = 1. Dropping is irreversible: for broken parts on a replicated table, first confirm another replica has the data (lost_part_count in system.replicas, a row-count comparison through clusterAllReplicas), then verify afterwards that only empty-reason parts remain. A longer recipe is in the detached-parts recipe. For leftover tables use DROP TABLE IF EXISTS ... ON CLUSTER ... SYNC; IF EXISTS matters because a cluster can include nodes that never had the replicated table.

Prevent and watch. Alert on the count and size of detached parts per node and on its growth after any restart, rebuild or failover. Rebuilding a node deliberately produces a pile of clone parts. When marking a replicated table for deletion by renaming it, rename every replica: a table renamed on only some replicas is missed by a DROP on the new name and its Keeper path lingers.

In production: A housekeeping pass over a production estate found more than ten thousand detached parts, tens of GiB, concentrated on the few nodes that had been rebuilt, plus a handful of tables named for deletion. Over 95% of the parts had system reasons and were safe to drop; about three percent were deliberate manual detaches, some years old, and were left for a person to judge. The statements were generated from system.detached_parts per node rather than from a stale export, and the first pass was report-only with the SQL staged for review. One replicated table had been renamed on only two of its five replicas; renaming the rest first let a single DROP ... SYNC clean all of them and the Keeper path, and IF EXISTS covered the node that never hosted it.

8. A rising error rate

Symptom. The failed-query count on a dashboard climbs, or a code grows in system.errors, with no deploy.

The method for narrowing that to culprit queries is Investigating a rising ClickHouse error rate: signal versus noise, new versus old query shapes, victims versus causes. This section only routes: find which error it is and jump to the section that owns it.

First checks. system.errors counts every error since start, with the last message and time:

SELECT name, code, value, last_error_time, substring(last_error_message, 1, 40) AS msg
FROM system.errors ORDER BY value DESC LIMIT 10;
   ┌─name────────────────────────────────┬─code─┬─value─┬─────last_error_time─┬─msg──────────────────────────────────────┐
1. │ UNKNOWN_TABLE                       │   60 │     2 │ 2026-10-14 16:10:25 │ Unknown table expression identifier 'mis │
2. │ CANNOT_PARSE_TEXT                   │    6 │     1 │ 2026-10-14 16:10:25 │ Cannot parse string 'x' as UInt32: synta │
3. │ FUNCTION_THROW_IF_VALUE_IS_NON_ZERO │  395 │     1 │ 2026-10-14 16:10:25 │ boom: while executing 'FUNCTION throwIf( │
   └─────────────────────────────────────┴──────┴───────┴─────────────────────┴──────────────────────────────────────────┘

(Errors triggered on purpose.) The counters are cumulative: sample twice and subtract, or chart the rate from query_log. The code that grows sends you on:

Code and name Go to
241 MEMORY_LIMIT_EXCEEDED section 1
252 TOO_MANY_PARTS section 2
242 TABLE_IS_READ_ONLY, 285 TOO_FEW_LIVE_REPLICAS, 999 KEEPER_EXCEPTION section 3
473 DEADLOCK_AVOIDED, 159 TIMEOUT_EXCEEDED section 5, then 4
202 TOO_MANY_SIMULTANEOUS_QUERIES section 6
209 SOCKET_TIMEOUT, 210 NETWORK_ERROR section 5, the connection path
243 NOT_ENOUGH_SPACE the storage runbook and section 7

What to remember. An error rate means something only against its own baseline. Two clusters on similar workloads can differ by an order of magnitude in routine MEMORY_LIMIT_EXCEEDED counts and both be healthy; the change, and which user or query shape owns it, is what matters. Counts also hide magnitude: one query that eats most of a node’s budget produces one error, or none. Pair counts with memory held at failure and keep a per-user and per-log_comment breakdown from day one. The fix belongs to the section the code sent you to; what you can prevent is the delay in finding it. Stamp every service with log_comment, keep query_log long enough to cover a weekend, and snapshot system.errors into monitoring so “since when” has an answer.

In production: A read-serving cluster threw a steady background of memory-limit errors, on the order of a hundred a day, driven by ad-hoc queries; a batch cluster in the same estate threw roughly a twentieth as many. Neither count was a problem, and neither would have revealed that a single exploratory aggregation on the batch cluster had taken close to nine tenths of one node’s memory budget in one go, with nothing to spill. That was found by ranking query_log by peak memory per query, not by counting errors.

Appendix: stand-in tables

query_log and replication_queue hold real data only on a server. These definitions reproduce their columns so the diagnostics above run on a laptop. All values are invented.

CREATE TABLE query_log_demo
(
    type Enum8('QueryStart' = 1, 'QueryFinish' = 2, 'ExceptionWhileProcessing' = 4),
    event_time DateTime, query_id String, user LowCardinality(String), log_comment String,
    normalized_query_hash UInt64, query_duration_ms UInt64, memory_usage UInt64, exception_code Int32,
    ProfileEvents Map(String, UInt64)
) ENGINE = MergeTree ORDER BY event_time;

INSERT INTO query_log_demo  -- 600 small API queries, 20 ms of CPU each
SELECT 'QueryFinish', toDateTime('2026-01-01 10:00:00') + number, concat('api-', toString(number)), 'api', 'app=web', 1,
       40, 50000000, 0, map('OSCPUVirtualTimeMicroseconds', 20000) FROM numbers(600);
INSERT INTO query_log_demo  -- a dashboard burning 30 s of CPU per query
SELECT 'QueryFinish', toDateTime('2026-01-01 10:00:00') + number * 14, concat('dash-', toString(number)), 'bi', 'app=dashboard', 3,
       9000, 2000000000, 0, map('OSCPUVirtualTimeMicroseconds', 30000000) FROM numbers(40);
INSERT INTO query_log_demo  -- a rollup job at 30 GB per query
SELECT 'QueryFinish', toDateTime('2026-01-01 10:07:30') + number, concat('rollup-', toString(number)), 'etl', 'job=rollup', 7,
       45000, 30000000000, 0, map('OSCPUVirtualTimeMicroseconds', 90000000) FROM numbers(2);
INSERT INTO query_log_demo  -- API queries failing beside it
SELECT 'ExceptionWhileProcessing', toDateTime('2026-01-01 10:07:20') + intDiv(number, 2), concat('apifail-', toString(number)), 'api', 'app=web', 2,
       800, 300000000, 241, map('OSCPUVirtualTimeMicroseconds', 5000) FROM numbers(15);
INSERT INTO query_log_demo VALUES ('QueryStart', '2026-01-01 10:07:41', 'orphan-1', 'etl', 'job=delta-backfill', 9, 0, 0, 0, map());
INSERT INTO query_log_demo  -- a QueryStart row for every finished query
SELECT 'QueryStart', event_time, query_id, user, log_comment, normalized_query_hash, 0, 0, 0, map()
FROM query_log_demo WHERE type != 'QueryStart' AND query_id NOT LIKE 'orphan%';

CREATE TABLE rq_demo ENGINE = Memory AS SELECT * FROM system.replication_queue LIMIT 0;
INSERT INTO rq_demo (database, `table`, replica_name, position, type, create_time, new_part_name, num_tries, last_exception, num_postponed, postpone_reason)
SELECT 'analytics', if(n < 340, 'events', 'metrics'), 'r1', n,
       if(n >= 300 AND n < 340, 'MERGE_PARTS', 'GET_PART'),
       now() - multiIf(n < 300, n * 40, n < 340, 86400 + (n - 300) * 600, 3 * 86400),
       concat('p', toString(n)),
       multiIf(n < 300, 0, n < 340, 3, 5000 + n - 340),
       if(n >= 340, 'Code: 234. No active replica has part 202610_900_900_0 or covering part', ''),
       if(n >= 300 AND n < 340, 180, 0),
       if(n >= 300 AND n < 340, 'Not executing log entry: source parts size is greater than the current maximum', '')
FROM (SELECT number AS n FROM numbers(343));

operationsincidentstroubleshooting

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.