ClickHouse incident response — triage, diagnose, and remediate server issues
using system tables, kill stuck queries, and execute recovery procedures.
Use when ClickHouse is slow, unresponsive, or producing errors in production.
Trigger: "clickhouse incident", "clickhouse outage", "clickhouse down",
"clickhouse emergency", "clickhouse on-call", "clickhouse broken".
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
ClickHouse incident response — triage, diagnose, and remediate server issues
using system tables, kill stuck queries, and execute recovery procedures.
Use when ClickHouse is slow, unresponsive, or producing errors in production.
Trigger: "clickhouse incident", "clickhouse outage", "clickhouse down",
"clickhouse emergency", "clickhouse on-call", "clickhouse broken".
Step-by-step procedures for triaging and resolving ClickHouse incidents
using built-in system tables and SQL commands.
Severity Levels
Level
Definition
Response
Examples
P1
ClickHouse unreachable / all queries failing
< 15 min
Server down, OOM, disk full
P2
Degraded performance / partial failures
< 1 hour
Slow queries, merge backlog
P3
Minor impact / non-critical errors
< 4 hours
Single table issue, warnings
P4
No user impact
Next business day
Monitoring gaps, optimization
Quick Triage (Run First)
# 1. Is ClickHouse alive?
curl -sf 'http://localhost:8123/ping' && echo"UP" || echo"DOWN"# 2. Can it answer a query?
curl -sf 'http://localhost:8123/?query=SELECT+1' && echo"OK" || echo"QUERY FAILED"# 3. Check ClickHouse Cloud status
curl -sf 'https://status.clickhouse.cloud' | head -5
-- 4. Server health snapshot (run if server responds)SELECT
version() AS version,
formatReadableTimeDelta(uptime()) AS uptime,
(SELECTcount() FROM system.processes) AS running_queries,
(SELECTvalueFROM system.metrics WHERE metric = )
memory_bytes,
( () system.merges) active_merges;
event_time, exception_code, exception, (query, , ) q
system.query_log
type
event_time now()
event_time
LIMIT ;
'MemoryTracking'
AS
SELECT
count
FROM
AS
-- 5. Recent errors
SELECT
substring
1
200
AS
FROM
WHERE
=
'ExceptionWhileProcessing'
AND
>=
-
INTERVAL
10
MINUTE
ORDER
BY
DESC
10
Decision Tree
Server responds to ping?
├─ NO → Check process/container status, disk space, OOM killer logs
│ └─ Container/process dead → Restart, check logs
│ └─ Disk full → Emergency: drop old partitions, expand disk
│ └─ OOM killed → Reduce max_memory_usage, add RAM
└─ YES → Queries succeeding?
├─ NO → Check error codes below
│ └─ Auth errors (516) → Verify credentials, check user exists
│ └─ Too many queries (202) → Kill stuck queries, reduce concurrency
│ └─ Memory exceeded (241) → Kill large queries, reduce max_threads
└─ YES but slow → Performance triage below
Remediation Procedures
P1: Server Down / OOM
# Check if process was OOM-killed
dmesg | grep -i "out of memory" | tail -5
journalctl -u clickhouse-server --since "10 minutes ago" | tail -20
# Restartsudo systemctl restart clickhouse-server
# or for Docker:
docker restart clickhouse
# Verify recovery
curl 'http://localhost:8123/?query=SELECT+version()'
P1: Disk Full
-- Find largest tablesSELECT database, table,
formatReadableSize(sum(bytes_on_disk)) AS size,
sum(rows) ASrowsFROM system.parts WHERE active
GROUPBY database, tableORDERBYsum(bytes_on_disk) DESC
LIMIT 10;
-- Emergency: drop old partitionsALTER TABLE analytics.events DROPPARTITION'202301';
ALTER TABLE analytics.events DROPPARTITION'202302';
-- Check free spaceSELECT name, formatReadableSize(free_space) ASfree,
formatReadableSize(total_space) AS total
FROM system.disks;
P2: Stuck / Long-Running Queries
-- Find stuck queriesSELECT
query_id,
user,
elapsed,
formatReadableSize(memory_usage) AS memory,
substring(query, 1, 200) AS query_preview
FROM system.processes
ORDERBY elapsed DESC;
-- Kill a specific query
KILL QUERY WHERE query_id ='abc-123-def';
-- Kill all queries from a user
KILL QUERY WHEREuser='runaway_user';
-- Kill all queries running longer than 5 minutes
KILL QUERY WHERE elapsed >300;
P2: Too Many Parts (Merge Backlog)
-- Check part countsSELECT database, table, count() AS parts
FROM system.parts WHERE active
GROUPBY database, tableHAVING parts >200ORDERBY parts DESC;
-- Check active mergesSELECT database, table, progress, elapsed,
formatReadableSize(total_size_bytes_compressed) AS size
FROM system.merges;
-- Temporary: raise the limit to prevent INSERT failuresALTER TABLE analytics.events MODIFY SETTING parts_to_throw_insert =1000;
-- Wait for merges to catch up, then lower back-- Root cause: too many small inserts — batch them
P2: Memory Pressure
-- Who's using the most memory?SELECTuser, query_id, elapsed,
formatReadableSize(memory_usage) AS memory,
substring(query, 1, 200) AS q
FROM system.processes
ORDERBY memory_usage DESC;
-- Kill the largest query
KILL QUERY WHERE query_id ='<largest_query_id>';
-- Reduce per-query memory for all usersALTERUSER app_writer SETTINGS max_memory_usage =5000000000; -- 5GB
P3: Replication Lag (Clustered/Cloud)
-- Check replica statusSELECT
database, table,
is_leader,
total_replicas,
active_replicas,
queue_size,
inserts_in_queue,
merges_in_queue,
log_pointer,
last_queue_update
FROM system.replicas
WHERE active_replicas < total_replicas OR queue_size >0;
Post-Incident Evidence Collection
-- Export error window from query logSELECT*FROM system.query_log
WHERE event_time BETWEEN'2025-01-15 14:00:00'AND'2025-01-15 15:00:00'AND (type ='ExceptionWhileProcessing'OR query_duration_ms >10000)
FORMAT JSONEachRow
INTO OUTFILE '/tmp/incident-queries.json';
-- Metrics snapshot during incident windowSELECT metric, valueFROM system.metrics
FORMAT TabSeparatedWithNames
INTO OUTFILE '/tmp/incident-metrics.tsv';
