Imported from jeremylongshore/tons-of-skills-marketplace (plugins/saas-packs/clickhouse-pack/skills/clickhouse-incident-runbook/SKILL.md). Install upstream with npx skills add jeremylongshore/tons-of-skills-marketplace --skill clickhouse-incident-runbook. Copyright stays with the author (MIT).

ClickHouse Incident Runbook

Overview

Step-by-step procedures for triaging and resolving ClickHouse incidents using built-in system tables and SQL commands. Start here: assess severity, run quick triage, walk the decision tree, then jump to the matching remediation procedure.

Prerequisites

• Network access to the ClickHouse HTTP interface (default port 8123) or a working clickhouse-client.
• A user with rights to read system.* tables and issue KILL QUERY / ALTER.
• Shell access to the host or container for P1 restarts (systemctl, docker, or kubectl).

Severity Levels

Instructions

Work the incident top to bottom: triage, classify with the decision tree, then apply the matching procedure.

1. Quick triage (run first)

# 1. Is ClickHouse alive? (8123 is the default ClickHouse HTTP interface port)
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,
    (SELECT count() FROM system.processes) AS running_queries,
    (SELECT value FROM system.metrics WHERE metric = 'MemoryTracking')
        AS memory_bytes,
    (SELECT count() FROM system.merges) AS active_merges;

-- 5. Recent errors
SELECT event_time, exception_code, exception, substring(query, 1, 200) AS q
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND event_time >= now() - INTERVAL 10 MINUTE
ORDER BY event_time DESC
LIMIT 10;

2. Decision tree — classify the failure

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

3. Apply the matching remediation

Each branch maps to a full procedure (SQL + shell, copy-paste ready) in references/remediation-procedures.md:

• P1: Server down / OOM — inspect dmesg/journalctl, restart, verify.
• P1: Disk full — find largest tables, drop old partitions, check system.disks.
• P2: Stuck queries — inspect system.processes, KILL QUERY by id/user/elapsed.
• P2: Too many parts — check part counts, raise parts_to_throw_insert, batch inserts.
• P2: Memory pressure — rank by memory_usage, kill the largest, cap max_memory_usage.
• P3: Replication lag — inspect system.replicas for queue and replica gaps.

4. Collect evidence and communicate

Once mitigated, export the error window and post status updates. Templates and INTO OUTFILE exports are in references/evidence-and-comms.md.

Output

Working through this runbook produces:

• A severity classification (P1–P4) and the identified failure class.
• Remediation actions applied — killed query ids, dropped partitions, restarted service, or adjusted settings.
• A recovery confirmation (SELECT version() / SELECT 1 succeeds again).
• Forensic artifacts for the postmortem: /tmp/incident-queries.json and /tmp/incident-metrics.tsv, plus a filled-in postmortem document.

Error Handling

If the server does not respond to ping at all, do not keep issuing SQL — move straight to the P1 host-level checks (process, disk, OOM logs) before anything else.

Examples

Kill a runaway query (P2). Triage shows one query pinning memory; classify as "queries succeeding but slow", then from the stuck-query procedure:

KILL QUERY WHERE query_id = 'abc-123-def';

Emergency disk reclaim (P1). Ping fails and the host is out of disk; the disk-full procedure drops the oldest partition to restore writes:

ALTER TABLE analytics.events DROP PARTITION '202301';

Full multi-step walkthroughs for every severity live in references/remediation-procedures.md; the post-incident export and comms templates live in references/evidence-and-comms.md.

Resources

• ClickHouse Cloud Status
• System Tables Reference
• KILL QUERY
• Related skill: clickhouse-data-handling for data-compliance follow-up.