Monitoring, Observability & Operations

Every ClickHouse analytics pipeline degrades silently before it fails loudly: a slow query shape creeps into a dashboard, background merges fall behind ingestion, a replica drifts read-only, and memory tightens until the first INSERT is rejected. This subsystem is how analytics platform teams and DevOps engineers make those failure modes visible — turning the server’s own system.* log and metric tables into latency percentiles, part-count alerts, merge-lag graphs, and replication-health checks that fire before an on-call engineer is paged rather than after.

ClickHouse is unusually observable from the inside. It records finished queries in system.query_log, part lifecycle events in system.part_log, a periodic snapshot of every counter in system.metric_log, and exposes live gauges through system.metrics, system.asynchronous_metrics, system.parts, system.merges, and system.replicas. The engineering work is not gathering data — it is retaining the right slices cheaply, rolling them up into stable time series, and alerting on the two or three numbers that actually predict an incident. This page is the working map of that layer: the tables that matter, how they wire into a metrics store and dashboards, the settings that govern log volume, the operational runbook for standing the whole thing up, and the failure modes the monitoring itself must survive.

The observability topology

Monitoring data flows out of ClickHouse along two paths that meet in the same dashboards. Live gauges (system.metrics, system.parts, system.replicas) are scraped on a short interval; historical event logs (system.query_log, system.part_log) are flushed to disk, aggregated by a rollup materialized view, and queried over longer windows. Both feed a metrics store, then Grafana panels and an alert router that closes the loop back to whoever tunes the pipeline.

The three practical areas this subsystem owns each get a focused topic guide: query performance monitoring for latency and heavy-query analysis, part and merge health monitoring for the storage engine’s insert/merge balance, and replication and availability monitoring for multi-replica correctness and memory headroom.

The system tables that matter

ClickHouse exposes two kinds of introspection surface, and confusing them is the first mistake teams make. Gauge tables report the instantaneous state of the server and are re-read every time you query them; log tables append a row per event and are asynchronously flushed from an in-memory buffer to a MergeTree on disk.

-- Live gauges: instantaneous. Cheap to read, no history.
SELECT metric, value FROM system.metrics       WHERE value != 0 ORDER BY metric;
SELECT metric, value FROM system.asynchronous_metrics ORDER BY metric;

-- Storage-engine state: one row per active part / in-flight merge / replica.
SELECT database, table, count() AS parts FROM system.parts WHERE active GROUP BY database, table;
SELECT * FROM system.merges;
SELECT database, table, is_readonly, absolute_delay, queue_size FROM system.replicas;

Log tables are what let you answer “what happened at 03:14?”. They are themselves MergeTree tables, so they cost disk and merge capacity like any other table — which is exactly why retention has to be engineered rather than left at defaults.

-- Historical events: append-only, flushed on an interval. Query with time predicates.
SELECT type, query_duration_ms, read_rows, memory_usage, query
FROM system.query_log
WHERE event_time > now() - INTERVAL 15 MINUTE AND type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 20;

Because log rows are buffered in memory before being written, a freshly finished query is not queryable in system.query_log for up to flush_interval_milliseconds (7500 ms by default). During an incident you force the write:

SYSTEM FLUSH LOGS;   -- drains all *_log buffers to disk immediately

Storage and retention mechanics

Left unmanaged, system.query_log on a busy cluster grows by millions of rows a day and becomes the single largest consumer of merge capacity — monitoring that hurts the thing it watches. Every *_log table is configured in config.xml, and the two levers that matter are a TTL for automatic expiry and a partition_by that lets whole partitions drop cheaply.

<!-- config.xml — bound query_log growth. -->
<query_log>
    <database>system</database>
    <table>query_log</table>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <!-- Drop rows older than 30 days; DELETE runs as a lightweight TTL merge. -->
    <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
    <!-- Daily partitions so expiry is a partition drop, not a row scan. -->
    <partition_by>event_date</partition_by>
    <max_size_rows>1048576</max_size_rows>
</query_log>

You can also apply a TTL to an existing log table without a restart, and confirm the on-disk footprint of every system log at a glance:

ALTER TABLE system.query_log MODIFY TTL event_date + INTERVAL 30 DAY;

SELECT table,
       formatReadableSize(sum(bytes_on_disk)) AS size,
       sum(rows) AS rows
FROM system.parts
WHERE database = 'system' AND table LIKE '%_log' AND active
GROUP BY table
ORDER BY sum(bytes_on_disk) DESC;

For long-horizon trends, keep the raw log short (7–30 days) and roll the durable series into a compact table with a materialized view, so a year of one-minute latency percentiles costs kilobytes instead of the raw log’s gigabytes. The full pattern lives in building query log retention and dashboards.

Pipeline integration patterns

Monitoring is not a bolt-on; each metric maps to a decision made elsewhere in the pipeline, which is why the alert thresholds have to be negotiated with the teams that own those layers.

• Ingestion → part health. Insert batch size and buffer flush behaviour directly set the part-creation rate. A part-count alert is really an early warning that batch insert optimization or the async buffer thresholds are producing undersized parts faster than background merges can drain them.
• Storage → merge lag. The MergeTree engine merges parts on a bounded background pool. Merge-lag graphs read directly off system.merges and system.part_log, and the interpretation depends on how background merging actually schedules work.
• Views → execution cost. Every attached materialized view runs on the insert path, so its cost surfaces in system.query_log as write amplification. Alert thresholds here tie back to the ceilings in threshold tuning and performance limits.
• Availability → replication. Read routing and failover assume replicas are in sync. Replication-lag monitoring is the signal the fallback routing and high availability layer needs before it trusts a replica for reads.

Cluster-scale configuration

A handful of server settings govern both how much monitoring data you produce and how much background capacity is available to keep the tables you monitor healthy. Set them deliberately per node profile.

The single most common configuration error is leaving system.query_log unbounded while tuning everything else. On a 20k-QPS cluster that table alone can consume more merge capacity than the analytical tables it exists to observe — so set its TTL in the same change where you set your alert thresholds.

Operational runbook

Standing up monitoring is itself a deploy with verify and teardown steps. The sequence below brings a node from “logs on disk” to “alerting into Grafana.”

1. Confirm the log tables exist and are being written.

SYSTEM FLUSH LOGS;
SELECT name FROM system.tables WHERE database = 'system' AND name LIKE '%_log';
SELECT max(event_time) AS latest FROM system.query_log;   -- should be within seconds of now()

2. Bound retention before enabling scrapes (a scrape against an unbounded log can itself be the heaviest query on the box).

ALTER TABLE system.query_log  MODIFY TTL event_date + INTERVAL 30 DAY;
ALTER TABLE system.part_log   MODIFY TTL event_date + INTERVAL 14 DAY;
ALTER TABLE system.metric_log MODIFY TTL event_date + INTERVAL 14 DAY;

3. Create a read-only monitoring role so the exporter cannot mutate data — the least-privilege pattern from managing RBAC roles and grants.

CREATE ROLE IF NOT EXISTS monitoring;
GRANT SELECT ON system.* TO monitoring;
GRANT SELECT ON analytics.* TO monitoring;
CREATE USER IF NOT EXISTS exporter IDENTIFIED WITH sha256_password BY '<secret>' DEFAULT ROLE monitoring;

4. Verify the exporter can read every surface it needs, then wire the dashboards.

clickhouse-client --user exporter --query \
  "SELECT count() FROM system.metrics; SELECT count() FROM system.replicas;"

5. Teardown / rotation. When retiring a node, stop the scrape target first, then optionally truncate logs to reclaim disk immediately rather than waiting for TTL merges:

TRUNCATE TABLE system.query_log;   -- reclaims space now; history is gone

Failure modes & diagnostics

Monitoring gap after a restart. system.metric_log and the *_log buffers reset on restart, and any events buffered but not flushed are lost. Detect a gap by looking for a hole in the metric series:

SELECT toStartOfMinute(event_time) AS m, count()
FROM system.metric_log
WHERE event_time > now() - INTERVAL 1 HOUR
GROUP BY m ORDER BY m;   -- a missing minute is a collection gap

The query_log is the top query. If monitoring queries dominate query_duration_ms, your scrape interval is too aggressive or the log is unbounded.

SELECT normalized_query_hash, any(substr(query,1,60)) AS shape,
       count() AS runs, round(avg(query_duration_ms)) AS avg_ms
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 HOUR
  AND query ILIKE '%system.%'
GROUP BY normalized_query_hash ORDER BY runs * avg_ms DESC LIMIT 10;

Part explosion hidden behind averages. A single averaged part count looks healthy while one hot partition is about to hit parts_to_throw_insert. Always aggregate per partition, as detailed in alerting on part count thresholds.

Silent stale reads. A replica with a growing absolute_delay still answers SELECTs, returning old data with no error. This is caught only by monitoring replication lag with system.replicas, not by any query error.

Performance benchmarks

Reading introspection tables is not free, and knowing the rough cost prevents monitoring from becoming the bottleneck. A SELECT from system.parts on a node with 50k active parts scans those 50k rows in memory — sub-10 ms — but recomputes on every call, so a 1-second scrape of a 200-table cluster is real load. Prefer the pre-aggregated system.asynchronous_metrics gauge NumberOfActiveParts for high-frequency scraping and reserve the full system.parts scan for per-partition drill-downs.

-- Cheap high-frequency gauge (recomputed by the server, not scanned per call):
SELECT value FROM system.asynchronous_metrics WHERE metric = 'NumberOfActiveParts';

-- EXPLAIN confirms a query_log analysis only reads the partitions in range:
EXPLAIN indexes = 1
SELECT count() FROM system.query_log WHERE event_date = today() AND type = 'QueryFinish';

A well-tuned query_log analysis over a single day’s partition on a busy node reads tens of millions of rows in under a second because the daily partition_by prunes every other day. If that same query scans the whole table, retention or partitioning is misconfigured — the fix is the partition_by shown above, not a bigger box.

Frequently asked questions

How long should I keep system.query_log? Keep the raw log 7–30 days for incident forensics and roll durable trends into a compact metrics table for anything longer. A year of raw query_log on a busy cluster is terabytes; a year of one-minute rollups is megabytes.

Do I need Prometheus, or can ClickHouse store its own metrics? Both work. ClickHouse ships a Prometheus endpoint and integrates with Grafana directly as a datasource, so many teams store rolled-up metrics in ClickHouse itself and skip a separate time-series database. Choose based on what your alerting stack already speaks.

Why is a finished query missing from query_log? Log rows buffer in memory for up to flush_interval_milliseconds before hitting disk. Run SYSTEM FLUSH LOGS to force the write during debugging.

Will enabling all the log tables slow down my queries? The write path is a cheap append to an in-memory buffer, so per-query overhead is negligible. The real cost is disk and background-merge capacity for the log tables themselves — which is why retention, not the logging switch, is the lever to manage.

Related

• Query Performance Monitoring — turning system.query_log into latency percentiles and heavy-query analysis.
• Part & Merge Health Monitoring — keeping the insert-and-merge balance out of the TOO_MANY_PARTS danger zone.
• Replication & Availability Monitoring — catching read-only replicas, replication lag, and memory pressure.
• MergeTree Engine Deep Dive — the storage engine whose health these metrics report on.
• Threshold Tuning & Performance Limits — the execution ceilings your alerts should sit beneath.

Up: Home