Clickhouse debug bundle

ClickHouse Debug Bundle

Overview

Collect comprehensive diagnostic data from ClickHouse system.* tables for troubleshooting performance issues, merge problems, or support escalation. The skill runs a graduated set of queries — server health, disk and table health, query performance, and merge/mutation status — then packages the output into a single artifact you can attach to a support ticket.

Prerequisites

• Access to a ClickHouse server with SELECT permission on system.* tables (grant SELECT ON system.* to a restricted user if needed).
• Either curl (for the HTTP interface, port 8123) or clickhouse-client.
• Connection settings exported as environment variables so no credentials are hardcoded: CLICKHOUSE_HOST, CLICKHOUSE_USER, CLICKHOUSE_PASSWORD.
• For deep query-log analysis, log_queries = 1 must be enabled on the server.

Instructions

Work through the four diagnostic areas below. For an interactive investigation, run the query for the symptom you are chasing; to produce a full artifact, run the automated collector in Step 5. The complete query set for every step lives in references/diagnostic-queries.md.

Step 1: Server health overview

Confirm the server version, uptime, and current-load gauges first — this frames every later finding.

SELECT
    version()                       AS version,
    uptime()                        AS uptime_seconds,
    formatReadableTimeDelta(uptime()) AS uptime_human,
    currentDatabase()               AS current_db;

Then snapshot system.metrics for the key gauges (Query, Merge, MemoryTracking, connection counts). Full metric list in the reference.

Step 2: Disk and table health

Find the largest tables and any table under merge pressure (too many active parts). The full query set covers per-table disk usage, the parts > 100 merge-pressure check, and per-disk free space from system.disks.

-- Tables with too many parts (merge pressure)
SELECT database, table, count() AS parts
FROM system.parts WHERE active
GROUP BY database, table
HAVING parts > 100
ORDER BY parts DESC;

Step 3: Query performance analysis

Pull the slowest queries, failed queries, and normalized query patterns from system.query_log over the last 24 hours. See the reference for the slow-query, exception, and normalized_query_hash aggregation queries.

Step 4: Merge and mutation status

Inspect system.merges, pending system.mutations, and system.replicas to spot stuck merges, long-running mutations, or replicas that have fallen behind. Full queries in the reference.

Step 5: Run the automated collector

For a one-shot artifact, use the bash or Node.js collector in references/collectors.md. Both authenticate from the environment variables above and write one file per diagnostic area:

CLICKHOUSE_HOST=http://localhost:8123 \
CLICKHOUSE_USER=default \
CLICKHOUSE_PASSWORD=secret \
  ./clickhouse-debug-bundle.sh

Output

The automated collector produces a timestamped gzipped tarball ch-debug-YYYYMMDD-HHMMSS.tar.gz containing one TSV/TXT file per diagnostic area:

File	Contents
version.txt	Server version, uptime, current database
metrics.tsv	Full system.metrics snapshot (gauges)
events.tsv	Full system.events snapshot (cumulative counters)
tables.tsv	Per-table parts, rows, and on-disk size
merges.tsv	Currently running merges
errors.tsv	Exceptions from system.query_log (last hour)
replicas.tsv	Replication status (best-effort; empty if not replicated)

An interactive run instead returns the result set of each query directly. The Node.js collector returns a single JSON object keyed by diagnostic area, with a per-key { error } entry when an individual query fails.

Error Handling

Issue	Cause	Solution
system.query_log empty	Logging disabled	Set log_queries = 1
Permission denied on system tables	Restricted user	Grant SELECT ON system.*
Bundle too large	Too much history	Narrow the INTERVAL time window
system.replicas errors	Table not replicated	Expected — collector ignores it (|| true)
curl: (7) connection refused	Wrong host/port	Verify CLICKHOUSE_HOST (HTTP interface is 8123)

Examples

Investigate a slow dashboard (interactive). Run Step 1 to confirm the server is healthy, then Step 3's slow-query select to find the offending queries and Step 2's merge-pressure check to rule out a table with 100+ parts starving the merge pool.

Prepare a support ticket (artifact). Export the three connection variables and run the Step 5 bash collector. Attach the resulting ch-debug-YYYYMMDD-HHMMSS.tar.gz to the ticket — it gives ClickHouse support the version, metrics, table sizes, active merges, and recent exceptions in one file.

Collect from application code. Import collectDebugBundle from references/collectors.md, pass it an authenticated @clickhouse/client handle, and persist the returned JSON object alongside the error you are triaging.

Full, runnable query text and both collector scripts: references/diagnostic-queries.md and references/collectors.md.

Resources

• System Tables Reference
• Query Log
• Server Metrics
• references/diagnostic-queries.md — every diagnostic query, grouped by area
• references/collectors.md — bash + Node.js bundle collectors with auth notes

Next Steps

For connection and concurrency issues that show up as failed queries or exhausted connection gauges in this bundle, follow up with the clickhouse-rate-limits skill.