ClickHouse Monitoring: Metrics That Matter

ClickHouse is unusually honest about its own state. Every counter, gauge, and log line the server produces is queryable with SQL, and most incidents - insert rejections, replica lag, OOM-killed queries - announce themselves in a system table long before users notice. The hard part isn't getting metrics out of ClickHouse. It's that the server exposes over a thousand of them, and maybe twenty actually predict outages.

This post covers those twenty or so: where each one lives, what healthy looks like, and where to set the alert. If you only build one ClickHouse dashboard, build it from this list.

Where the Metrics Live

ClickHouse ships its own instrumentation as tables. Three of them form the core, and it's worth being precise about what each one holds:

• `system.metrics` contains gauges - values that go up and down, sampled now: running queries, open TCP connections, bytes of tracked memory, busy background pool slots.
• `system.events` contains counters - monotonically increasing totals since server start: queries executed, rows inserted, inserts delayed, inserts rejected. You graph their rate, not their value.
• `system.asynchronous_metrics` contains values recomputed periodically in the background because they're expensive to calculate on demand: max parts per partition, worst replica delay, filesystem free space, OS-level CPU and memory. Our asynchronous metrics guide walks through the full table.

Behind those sit the log tables - `system.query_log` records every query with duration, rows read, and memory used; `system.part_log` records every part created, merged, or dropped; system.replication_queue shows pending replication work per table. The system tables overview maps the whole family.

You don't need an agent to export any of this. ClickHouse has an embedded Prometheus endpoint - a config block, not a plugin:

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

That serves all three metric tables in Prometheus format on port 9363. Two HTTP endpoints round out basic availability checks: `/ping` returns 200 when the server is up, and /replicas_status returns 503 when a replica has fallen too far behind - which makes it a ready-made load balancer health check.

The Shortlist: What to Alert On

Here is the reference table. Sources are the tables above; thresholds are starting points drawn from ClickHouse defaults and what we've seen across the many production clusters NeverBlink monitors - tune them against your own baseline.

Three failure domains produce most real ClickHouse incidents: ingestion (parts and merges), replication (queues and Keeper), and resources (memory, disk, and the host underneath) - plus whatever your own architecture layers on top: object storage caches, Kafka pipelines, materialized views. The next sections take them in that order.

Ingestion: Parts and the Merge Backlog

Every insert into a MergeTree table writes a new immutable part; background merges then combine small parts into larger ones. Monitoring ingestion health means monitoring one race: are merges keeping up with inserts? When they don't, part counts climb, and ClickHouse defends itself - by default it throttles inserts once a partition holds 1,000 active parts (`parts_to_delay_insert`) and rejects them outright at 3,000 (`parts_to_throw_insert`) with the notorious Too many parts error.

That's why MaxPartCountForPartition is the single best leading indicator in ClickHouse. It reports the worst partition on the server, it moves minutes to hours before inserts start failing, and a healthy steady state sits comfortably under 100. Alerting at 300 leaves room to act; waiting until 1,000 means your producers are already seeing latency.

Watch the throttling events themselves too. DelayedInserts and RejectedInserts in system.events should be flat lines at zero - any sustained rate is the defense mechanism already firing. And when part counts do climb, system.part_log tells you which side of the race broke: a flood of tiny NewPart entries means clients are inserting in batches that are too small (aim for tens of thousands of rows per insert, not hundreds), while sparse MergeParts entries mean merges are starved - check whether BackgroundMergesAndMutationsPoolTask is pinned at the pool ceiling. Computing your ingestion rate from part_log makes the comparison direct. We covered the mechanics of this race in Inside ClickHouse MergeTree.

Queries: Percentiles from query_log, Not Averages

Average query latency hides everything interesting - a dashboard average of 40 ms is fully compatible with a p99 of 30 seconds. system.query_log has per-query truth, so compute percentiles from it directly:

SELECT
    quantiles(0.5, 0.95, 0.99)(query_duration_ms) AS p50_p95_p99,
    count() AS queries,
    countIf(exception != '') AS failed
FROM system.query_log
WHERE type > 1
  AND event_time > now() - INTERVAL 15 MINUTE;

Alert on p99 drifting 2-3x above your baseline, and on FailedQuery rate separately - error spikes and latency spikes have different root causes. When latency does drift, query_log also carries the explanation: read_rows and read_bytes ballooning means a filter stopped pruning parts; memory_usage climbing toward max_memory_usage means aggregations are about to start spilling or failing. Sort by query_duration_ms over the incident window and the offending query pattern is usually obvious. Our query_log recipe collection has ready-made queries for exactly this.

One pitfall: query_log is itself a MergeTree table with a TTL. If you want week-over-week latency comparisons, confirm its retention covers the comparison window before you need it.

Replication and Keeper

Replicated tables coordinate through ZooKeeper or ClickHouse Keeper, and this dependency is the part of the system most teams under-monitor. Two asynchronous metrics summarize replica health server-wide: ReplicasMaxAbsoluteDelay (the worst replica lag, in seconds) and ReplicasMaxQueueSize (the deepest per-table replication queue). Lag past max_replica_delay_for_distributed_queries (default 300 s) gets replicas excluded from distributed reads, so 300 seconds is the natural alert line - and a queue that grows while num_tries climbs on one entry means a stuck task, with last_exception in system.replication_queue naming the reason.

The metric that means drop-everything is ReadonlyReplica in system.metrics. A nonzero value means tables have lost their coordination session and are refusing writes. Which is why Keeper itself belongs on the same dashboard: the `mntr` four-letter command (echo mntr | nc keeper-host 9181) reports zk_avg_latency, zk_outstanding_requests, and the leader/follower state, and Keeper has shipped its own Prometheus endpoint since ClickHouse 22.12. Keeper average latency above ~50 ms shows up downstream as slow inserts into replicated tables - Keeper participates in every part commit, so its latency is a floor under your write latency.

Resources: The Server and the Host Underneath It

For the server itself, the pairing that matters is MemoryTracking against max_server_memory_usage (alert at 90%) and FilesystemMainPathAvailableBytes against real disk (alert at 20% free - merges need scratch space, and a disk-full ClickHouse fails in ugly, sometimes unrecoverable ways).

But ClickHouse's own metrics stop at the process boundary, and plenty of incidents start outside it. Conveniently, system.asynchronous_metrics doubles as a node exporter - it samples host-level OS state alongside the server's own, so the same Prometheus endpoint covers both:

• CPU and load. LoadAverage15 above the core count means the box is oversubscribed. OSUserTimeNormalized and OSSystemTimeNormalized report CPU utilization on a 0-1 scale regardless of core count, so one alert rule works across differently-sized nodes. Sustained high CPU with flat query volume usually points at merges or mutations - our high CPU diagnosis guide walks the triage.
• IO wait. OSIOWaitTimeNormalized above ~0.2 sustained means queries and merges are stalled on disk - and it shows up as a growing merge backlog before it shows up as slow queries. The per-device counters BlockReadBytes_<device> and BlockWriteBytes_<device> tell you which disk. If merges starve while pool slots sit free, check IO before anything else.
• Memory the tracker can't see. OSMemoryAvailable covers what MemoryTracking doesn't: page cache pressure and other processes on the box. When it trends toward zero, the next OOM kill comes from the kernel - abrupt and unlogged - rather than from ClickHouse's own graceful memory limits.

S3-Backed Tables: Watch the Cache

If your MergeTree data lives on S3 or other object storage, one more number joins the shortlist, and it's the one that decides query latency: the filesystem cache hit rate. Every read that misses the local cache becomes an S3 GET - orders of magnitude slower than local disk - so latency on these tables is mostly a function of how many reads run cold. ClickHouse doesn't expose a hit rate directly, but the counters to derive it are recorded per query:

SELECT
    sum(ProfileEvents['CachedReadBufferReadFromCacheBytes']) AS from_cache,
    sum(ProfileEvents['CachedReadBufferReadFromSourceBytes']) AS from_s3,
    from_cache / (from_cache + from_s3) AS cache_hit_ratio,
    countIf(ProfileEvents['CachedReadBufferReadFromSourceBytes'] > 0) / count() AS cold_query_ratio
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 HOUR;

Baseline both numbers, then alert on a sustained drop in hit ratio or rise in cold queries. A hit rate that degrades over weeks usually means the working set has outgrown the cache - compare FilesystemCacheSize against FilesystemCacheSizeLimit in system.metrics, and watch the FilesystemCacheEvictedBytes rate for eviction churn.

S3 itself is also a dependency that can fail or throttle you. DiskS3ReadRequestsErrors and DiskS3ReadRequestsThrottling (429/503 responses) in system.events should be flat zero - the DiskS3* family counts only object-storage-disk traffic, which is exactly the traffic your tables depend on.

Integrations: The Failure Modes You Opted Into

Everything above applies to any ClickHouse cluster. But each integration you add brings its own failure modes, and each one surfaces them in a different place:

• Materialized views fail inside the INSERT that feeds them. By default an exception in any materialized view fails the client's insert; with materialized_views_ignore_errors = 1 the insert succeeds and the error becomes a quiet log warning - which makes monitoring mandatory, because nothing else will tell you a view is silently dropping data. system.query_views_log records one row per view per insert with status and exception (enable the query_views_log server config section - it's not in the default config), and any sustained rate of ExceptionWhileProcessing rows is the alert.
• Kafka engine tables report through system.kafka_consumers (since 23.8): per-consumer partition assignments, poll and commit times, and the last ten exceptions. In system.events, watch KafkaConsumerErrors (librdkafka-level errors), KafkaMessagesFailed (parse failures), KafkaRebalanceErrors, and KafkaCommitFailures - the last one usually means duplicate data. Consumer lag still needs broker-side measurement; ClickHouse only knows its own offsets. Our Kafka engine guide covers the setup side.
• And the list keeps going. S3Queue logs per-file failures to system.s3queue_log; Distributed tables queue broken async inserts in system.distribution_queue (broken_data_files, last_exception); dead-lettered stream messages land in system.dead_letter_queue; and system.errors counts every error code the server has ever raised, with the last message and stack trace for each.

The pattern is consistent: every moving part has a system table, but nothing aggregates them into "is my pipeline healthy?" Monitoring your use case - as opposed to monitoring ClickHouse - is where the real effort lives: enumerating what you've integrated, finding each piece's failure signal, baselining it, and keeping the alerts current as the architecture changes underneath them.

Key Takeaways

• ClickHouse monitoring is built in: system.metrics (gauges), system.events (counters), system.asynchronous_metrics (periodic), plus query_log, part_log, and replication_queue - all exportable via the embedded Prometheus endpoint on port 9363.
• MaxPartCountForPartition is the best leading indicator of ingestion trouble. Alert at 300; defaults throttle inserts at 1,000 parts per partition and reject at 3,000.
• Alert on query p99 from system.query_log, never on averages, and track FailedQuery rate separately.
• Replication health is three numbers: ReplicasMaxAbsoluteDelay (> 300 s), ReplicasMaxQueueSize (> 100), and ReadonlyReplica (> 0). Keeper latency above 50 ms is a write-path problem, not a sidebar.
• The host counts too: LoadAverage15, OSIOWaitTimeNormalized, and OSMemoryAvailable come from the same system.asynchronous_metrics table - no separate node exporter needed.
• On S3-backed tables, derive the filesystem cache hit rate from the CachedReadBufferReadFrom* counters and keep DiskS3* error and throttling rates at zero.
• Every integration adds its own failure table: query_views_log for materialized views, kafka_consumers for Kafka, s3queue_log, distribution_queue, dead_letter_queue. Monitor the ones you actually run.
• Thresholds here are starting points. Baseline your own workload for two weeks, then tighten.

The catch with all of the above is that thresholds are static and workloads aren't - a part count that's normal during a backfill is an incident at 4am on a Tuesday. That's the problem NeverBlink works on: it monitors ClickHouse (and your other databases) continuously, learns each cluster's baseline, and turns these system tables into diagnoses - which table, which setting, which query - instead of another wall of charts. The metrics above are where we'd tell you to start either way.