Skip to content
Navigation

Type at least two characters. Search covers page titles, headings, tags and database names.

↑ ↓ to navigateEnter to openEsc to close0 pages
ClickHouseintermediate

ClickHouse Monitoring

The system tables that diagnose ClickHouse, and the metrics that predict merge, memory and replication problems.

2 min readIntermediateUpdated Edit this page
ClickHouse

ClickHouse exposes almost everything through system tables. These are the ones that matter in production.

Core system tables

-- Parts and merge pressure per table.
SELECT table, count() AS parts, sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts WHERE active GROUP BY table ORDER BY parts DESC;
 
-- Merges in progress.
SELECT table, elapsed, progress, formatReadableSize(memory_usage) AS memory
FROM system.merges;
 
-- Mutations that have not finished.
SELECT table, mutation_id, parts_to_do, latest_fail_reason
FROM system.mutations WHERE NOT is_done;
 
-- Replication health.
SELECT table, is_readonly, absolute_delay, queue_size, active_replicas, total_replicas
FROM system.replicas;
 
-- Failing replication tasks.
SELECT table, type, num_tries, last_exception
FROM system.replication_queue WHERE num_tries > 3;
 
-- Running queries.
SELECT query_id, user, elapsed, formatReadableSize(memory_usage) AS memory, left(query, 80)
FROM system.processes ORDER BY elapsed DESC;

Metrics to alert on

Parts per partition

Source: system.parts

Approaching parts_to_delay_insert means the merge scheduler is losing to the insert rate. Alert well before the throw threshold.

system.replicas.absolute_delay

Source: system.replicas

Seconds a replica is behind. Combine with queue_size, which shows whether it is catching up or falling further behind.

is_readonly

Source: system.replicas

A replica that lost its Keeper session accepts no writes. Silent from the client's perspective until inserts start failing elsewhere.

MemoryTracking

Source: system.metrics

Current tracked memory across queries and caches, against max_server_memory_usage.

DiskUsed / DiskAvailable

Source: system.disks

Merges need free space. Treat 70–80% as the action threshold, not 95%.

QueryTimeMicroseconds

Source: system.events

Aggregate query time; combine with system.query_log percentiles for a latency view.

Query log

-- Latency percentiles per hour.
SELECT toStartOfHour(event_time) AS hour,
       count() AS queries,
       quantile(0.5)(query_duration_ms)  AS p50,
       quantile(0.95)(query_duration_ms) AS p95,
       quantile(0.99)(query_duration_ms) AS p99
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 DAY
GROUP BY hour ORDER BY hour;
 
-- Failures, grouped by exception.
SELECT exception_code, any(exception) AS example, count()
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing' AND event_time > now() - INTERVAL 1 HOUR
GROUP BY exception_code ORDER BY count() DESC;

Prometheus

ClickHouse can expose metrics directly:

<prometheus>
    <endpoint>/metrics</endpoint>
    <port>9363</port>
    <metrics>true</metrics>
    <events>true</events>
    <asynchronous_metrics>true</asynchronous_metrics>
</prometheus>

This covers server-level counters. Table-level facts — parts per partition, replication delay, unfinished mutations — need queries against system tables, which most exporters support as custom metrics. See Prometheus Monitoring.