Incident Response

Incident Response

Structured, step-by-step recipes for the most common ClickHouse incidents. Each recipe names the exact tool or system table query, what to look for, and the standard remediation. For multi-step incidents, pair with update_plan + the plan-and-verify skill to track progress and verify each fix before moving to the next step.

1. Disk Filling Up

Triggers: disk usage > 80 %, insert errors mentioning DB::Exception: Not enough space, alerts on free bytes.

Step 1 — Check free space per disk

Use the get_disk_usage tool or query directly:

SELECT name, path,
       formatReadableSize(free_space) AS free,
       formatReadableSize(total_space) AS total,
       round(100 - free_space * 100.0 / total_space, 1) AS used_pct
FROM system.disks
ORDER BY used_pct DESC

Alert threshold: used_pct > 85. Note which disk is filling and which storage policy it belongs to.

Step 2 — Find the biggest tables and parts

Use list_tables (or query system.parts) or:

SELECT database, table,
       formatReadableSize(sum(bytes_on_disk)) AS disk,
       sum(rows) AS rows,
       count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC
LIMIT 20

Also look at system.parts with high modification_time age — old unmerged parts waste space.

Step 3 — Estimate insert rate and time-to-full

Use the forecast_disk_capacity tool. Manual estimate:

SELECT toStartOfHour(event_time) AS hour,
       sum(rows) AS rows_inserted,
       formatReadableSize(sum(bytes_written_to_disk)) AS written
FROM system.part_log
WHERE event_type = 'NewPart'
  AND event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour

Divide current free bytes by hourly write rate → hours until full.

Step 4 — Remediation

• TTL: add or tighten TTL on high-volume tables (ALTER TABLE t MODIFY TTL date + INTERVAL 30 DAY).
• Drop partitions: ALTER TABLE t DROP PARTITION '2024-01' for old data (irreversible — confirm first).
• Move to cold tier: if tiered storage is configured, ALTER TABLE t MOVE PARTITION '...' TO DISK 'cold'.
• DETACH + DROP: for tables no longer needed.
• After any action, re-run step 1 to confirm free space recovered.

Cross-references: storage-optimization (TTL syntax, tiered storage), plan-and-verify (track multi-partition drops safely).

2. High Error Rate

Triggers: spike in failed queries, user-facing 500s, alert on system.errors.

Step 1 — Recent errors from system.errors

SELECT name, code, value, remote,
       last_error_time, last_error_message
FROM system.errors
WHERE last_error_time >= now() - INTERVAL 1 HOUR
ORDER BY value DESC
LIMIT 20

value is the cumulative error count since startup; look for codes with recent last_error_time and high value.

Step 2 — Failed queries with exceptions

Use get_failed_queries or:

SELECT exception_code,
       count() AS cnt,
       topK(5)(exception) AS samples,
       topK(5)(query) AS queries
FROM system.query_log
WHERE type IN ('ExceptionWhileProcessing', 'ExceptionBeforeStart')
  AND event_time >= now() - INTERVAL 1 HOUR
GROUP BY exception_code
ORDER BY cnt DESC

Step 3 — Interpret error codes

Step 4 — Check resource pressure

Use get_metrics or:

SELECT metric, value
FROM system.metrics
WHERE metric IN (
  'Query', 'BackgroundMergesAndMutationsPoolTask',
  'ZooKeeperRequest', 'MemoryTracking'
)

Correlate a memory or Keeper spike with the error burst.

Step 5 — Remediation

Fix the specific error code (table, column, limit, Keeper). If a single bad query is causing the spike, kill it:

KILL QUERY WHERE query_id = '...' ASYNC

Cross-references: troubleshooting (error code details, OOM), anomaly-detection (automated spike detection).

3. Replication Lag / Failover

Triggers: replica is behind, reads from replica return stale data, absolute_delay alert.

Step 1 — Check per-table replication lag

Use get_replication_status or query system.replication_queue after get_table_schema:

SELECT database, table, replica_name, replica_path,
       is_leader, is_readonly,
       absolute_delay,
       queue_size, inserts_in_queue, merges_in_queue,
       last_queue_update
FROM system.replicas
WHERE absolute_delay > 0 OR queue_size > 0
ORDER BY absolute_delay DESC

absolute_delay > 300 (5 min) is a concern. is_readonly = 1 means the replica cannot write — usually a Keeper connectivity problem.

Step 2 — Inspect the replication queue

SELECT database, table, type, source_replica,
       parts_to_merge, create_time,
       last_attempt_time, last_exception,
       num_tries
FROM system.replication_queue
WHERE last_exception != ''
ORDER BY num_tries DESC
LIMIT 20

Repeated failures with the same last_exception point to a stuck task. High num_tries means the replica has been retrying for a while.

Step 3 — Keeper health

SELECT *
FROM system.zookeeper
WHERE path = '/clickhouse'

Or query system.zookeeper with a path filter after get_table_schema. Look for high zookeeper_sessions in system.metrics and watch for ZooKeeperRequest latency spikes in system.asynchronous_metrics.

Also check the distributed DDL queue for stuck operations:

SELECT *
FROM system.distributed_ddl_queue
WHERE status != 'Finished'
ORDER BY entry_time

Step 4 — Remediation

• High lag, queue draining: wait; lag recovers automatically once the queue drains.
• is_readonly: restore Keeper connectivity; then SYSTEM RESTART REPLICA db.table.
• Stuck queue task: SYSTEM DROP REPLICA 'bad_host' FROM TABLE db.table to remove a dead peer, then let the replica re-sync.
• Detach + reattach: last resort — DETACH TABLE, restore from another replica's data, ATTACH TABLE.

Cross-references: replication-guide (full recovery playbook, detach/reattach steps, Keeper tuning).

4. Stuck Mutations / Merges

Triggers: ALTER TABLE ... UPDATE/DELETE never completes, parts_to_do stays non-zero, merge backlog growing.

Step 1 — Find stuck mutations

Query system.mutations (columns in system-tables-reference) or:

SELECT database, table, mutation_id,
       command, create_time,
       parts_to_do, is_done,
       latest_fail_reason, latest_fail_time
FROM system.mutations
WHERE is_done = 0
ORDER BY create_time

latest_fail_reason != '' tells you exactly why it is stuck (disk space, missing column, Keeper timeout).

Step 2 — Find long-running merges

SELECT database, table, elapsed,
       formatReadableSize(total_size_bytes_compressed) AS size,
       progress, merge_type,
       partition_id
FROM system.merges
ORDER BY elapsed DESC
LIMIT 10

elapsed > 3600 (1 hour) for a merge is unusual. progress not advancing over several minutes means the merge is likely stuck.

Step 3 — Check the part count

SELECT database, table, partition_id,
       count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table, partition_id
HAVING parts > 100
ORDER BY parts DESC

Excessive parts (>300 in a partition) cause mutations and merges to slow or hang.

Step 4 — Remediation

• Kill a stuck mutation: KILL MUTATION WHERE mutation_id = 'mutation_8.txt'
• Kill a stuck merge: mutations and merges cannot be killed directly; reduce background_pool_size load by pausing inserts temporarily.
• Fix the root cause: if latest_fail_reason mentions disk space, run recipe 1 first.
• Too many parts: throttle inserts; increase max_bytes_to_merge_at_max_space_in_pool or temporarily OPTIMIZE TABLE t PARTITION 'part' during off-peak.
• Prefer non-mutating patterns: use ReplacingMergeTree + CollapsingMergeTree over heavy UPDATE/DELETE mutations.

Cross-references: troubleshooting (mutation + merge details), storage-optimization (part count management).