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".
Standardmäßig ist der Prompt ausgewählt, der zuerst die Quelle prüft. Sie können zu einem direkten Befehl wechseln oder eine lokale Kopie herunterladen.
Quelldateien prüfen
Lesen Sie SKILL.md und alle von SkillsMP angezeigten Begleitdateien, bevor Sie sich für eine Installation entscheiden.
Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.
Ein direkter Befehl überspringt den Prüf-Prompt. Prüfen Sie die Quelle, bevor Sie ihn ausführen.
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';
