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).

5. Cluster Health Sweep

Triggers: routine check-in, "how is the cluster", pre-maintenance review, post-deploy verification.

Run these in order. Each surfaces a different failure class.

Step 1 — Server uptime and version

Use get_metrics or:

SELECT version(), uptime(), now()

Unexpected recent uptime means a crash restart occurred.

Step 2 — Disk headroom

Run step 1 of recipe 1. Flag any disk above 80 %.

Step 3 — Recent slow queries

SELECT query_id,
       round(query_duration_ms / 1000, 1) AS secs,
       read_rows, formatReadableSize(read_bytes) AS read,
       memory_usage, query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 15 MINUTE
  AND query_duration_ms > 5000
ORDER BY query_duration_ms DESC
LIMIT 10

Step 4 — Recent errors

Use get_failed_queries or run step 1 of recipe 2.

Step 5 — Merge backlog

SELECT count() AS active_merges,
       sum(elapsed) AS total_elapsed_s,
       max(elapsed) AS longest_s
FROM system.merges

active_merges > 50 or longest_s > 600 warrants investigation (recipe 4).

Step 6 — Replication status

SELECT count() AS lagging_tables,
       max(absolute_delay) AS max_delay_s,
       sum(queue_size) AS total_queue
FROM system.replicas
WHERE absolute_delay > 30 OR queue_size > 10

Any lagging_tables > 0 with growing max_delay_s → recipe 3.

Step 7 — Summarize

Report findings in priority order: disk → replication → merges → errors → slow queries. Use update_plan to record findings and track follow-up actions.

Cross-references: anomaly-detection (automated sweep), plan-and-verify (track remediation steps).

6. Slow Query Investigation

Triggers: a specific query is slow, user reports latency, query_duration_ms alert.

Step 1 — Currently running queries

Use get_running_queries tool or:

SELECT query_id, user, elapsed,
       read_rows, formatReadableSize(read_bytes) AS read,
       memory_usage,
       query
FROM system.processes
ORDER BY elapsed DESC

A query running for minutes is usually either doing a full scan or waiting for a merge/mutation to release a lock.

Step 2 — Historical slow queries

SELECT query_id,
       event_time,
       round(query_duration_ms / 1000, 1) AS secs,
       read_rows,
       formatReadableSize(read_bytes) AS read,
       ProfileEvents['SelectedMarks'] AS marks_selected,
       ProfileEvents['MergeTreeDataSelectExecutorReadRows'] AS rows_from_disk,
       exception,
       query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
  AND query_duration_ms > 3000
ORDER BY query_duration_ms DESC
LIMIT 20

marks_selected high relative to actual result rows → full granule scan, missing primary key filtering.

Step 3 — Explain the query plan

Use the explain_query tool. Look for:

• ReadFromMergeTree with no key condition → full table scan
• Filter after ReadFromMergeTree instead of before → PREWHERE opportunity
• PartialSortingTransform with huge row counts → missing ORDER BY key alignment
• Large HashJoin build side → consider join_algorithm = 'partial_merge'

Step 4 — Check table schema and sorting key

Use get_table_schema tool. Verify:

• The WHERE columns are in the primary key (in order).
• The time column is first or second in the key for time-range queries.
• Partition by granularity matches query patterns.

Step 5 — Remediation

• Add PREWHERE for selective low-cardinality filters applied before reading full columns.
• Add a skipping index (INDEX idx col TYPE bloom_filter) for high-cardinality equality lookups.
• Rewrite JOIN to use a smaller build side; filter before joining.
• For aggregate-only queries, consider a materialized view that pre-aggregates.
• Kill the query if it is consuming excessive resources and a fix is ready: KILL QUERY WHERE query_id = '...' ASYNC.

Cross-references: query-tuning-advisor (index selection, MV design), plan-and-verify (test the rewrite safely before production).

General Guidance

• Always scope the time window first — narrow event_time filters prevent query_log scans from timing out.
• Use update_plan to record what you found, what you changed, and what to verify — especially for multi-step incidents.
• After any remediation, re-run the diagnostic query from that recipe step to confirm the metric improved.
• For incidents that span multiple categories (e.g., replication lag caused by disk full), resolve in dependency order: disk → Keeper → replication.
• Load the anomaly-detection skill to set up proactive alerting so the same incident does not recur silently.