Why ClickHouse monitoring breaks under growing load
While the cluster is small, basic host sensors are enough, and ClickHouse monitoring seems like a solved problem: CPU is not maxed out, memory is free, disk responds.
The problem is that cluster bottlenecks do not live at the hardware level, but inside the DBMS itself — in the query queue, in background part merges, in the replication queue. External sensors do not show them until the overload has already happened.
The first to give in is the flow of heavy analytical queries. An unlimited report raises hundreds of threads, evicts the working cache, and forces the system to re-read data from disk.
Slow queries stop being isolated: what responded in fractions of a second starts responding in seconds, and the queue only accumulates. Host-level load monitoring at this moment cheerfully reports normal.
Replica lag develops more quietly. Writes go to one node, are processed through a queue, and when the insert stream exceeds the speed of its processing, some replicas start serving stale data.
The reading service does not get errors — the query is successful, the answer is just incorrect, and this is not reproduced on all nodes.
The result is predictable: the team learns that slow queries have degraded or a replica has fallen behind from users. The on-call engineer sees a live process and a responding port, that is, according to his dashboard everything is fine.
The gap is exactly here — there is a lot of data, but there are no state indicators by which decisions are made.
We analyze how this is closed: which metrics to collect inside the server, where to set thresholds, and how to build signals that trigger before complaints.
What changes after implementing an alerting and dashboard system
Before implementing alerts, ClickHouse metrics are usually already being collected, but no one looks at them during load growth: the insert queue grows for hours, replica lag increases gradually, and we learn about it from users, not from the system. After setting up thresholds and dashboards, the very mode of working with the cluster changes.
Unified cluster view
A single panel brings together the state of all nodes: merge queue, replica lag, memory consumption, slow query duration, active connections, read errors.
The engineer answers the question "where is the bottleneck" in a minute instead of half an hour of walking through nodes and logs.
Metric-based alerts instead of after-the-fact reaction
Thresholds are set not "by eye", but according to the historical load profile — separately for reporting peaks, separately for nightly transfers.
The signal comes with a sustained deviation, so false alarms do not wake the on-call engineer, and real growth of the insert queue is visible before queries start failing by timeout.
Degradation is visible before failure
Incident response time is reduced from hours to minutes, and most problems are caught at the stage of metric growth, not after complaints about slow reports.
The class of failures due to unnoticed queue overflow or replica lag disappears entirely: the system does not let you miss the moment when resource headroom begins to melt away.
Options for collecting metrics: agent, exporter, system tables
By default, ClickHouse metrics live in the server's own system tables: they are visible only from the host and only in the moment.
To build graphs and analyze incidents after the fact, a separate collection loop is needed — and here three schemes really work, and they are chosen between.
| Scheme | When it fits | Limitations |
|---|---|---|
| Collection agent on each host | Databases on dedicated servers, a cluster of several nodes, collection from each node is needed | Requires access to the file system and permissions to read logs; when the host image is updated, the agent has to be rebuilt |
| External exporter next to the server | Container deployments where nothing can be installed on the host; a single point of connection to the database | Holds a constant connection to the server, sensitive to its restarts, the entire stream is collected on one exporter |
| Polling system tables on a schedule | Quick start, one-time surveys, debugging non-standard metrics | Low time resolution, no history, and each query is additional load on the production database |
The polling frequency determines what you actually see: a rare interval averages out spikes and hides short spikes in memory and failures.
History storage is moved out of the production database into a separate time-series storage: raw points are kept for a limited time, then minute and hour aggregates remain — so depth of months does not turn into increased load on the server itself.
In practice, the schemes are combined: an agent or exporter provides the stream, and system tables remain a tool for pinpoint diagnostics. We analyze how to assemble a stable loop from this.
Implementation stages: from schema audit to handover of regulations
Work proceeds sequentially: first we capture the actual load picture and which metrics are already available, and only then we build the collection scheme.
Each stage ends with an artifact — a configuration, rule, or regulation, so the result can be verified rather than taken on faith.
- Schema and load audit. We analyze top queries, queues, distribution across replicas: where the bottleneck is — disk, memory, or the queries themselves. Output: a list of metrics that really should be polled, and a polling interval for the current pace.
- Collection scheme design. We decide which nodes are polled directly and which through an intermediate collector, how history is stored and for how long it is sufficient. We agree on the scheme before anything is installed.
- Polling setup. We bring up exporters on nodes, consolidate metrics to unified names, remove duplicates, and check that the collection itself does not create noticeable load on the database.
- Alert rules and panels. We set thresholds not "by default" but from observed values: separately for degradation, separately for failure, with different urgency. We assemble panels for real questions — what is slowing down now and what changed in a day.
- Testing on a stand. We artificially kill a replica, fill the disk, stop the collector: we check whether the rule fired, whether the notification reached the on-call engineer, whether there are false positives. A load test shows that metric collection does not take resources from the database itself.
- Handover of regulations. We provide documentation: what each signal means, what the on-call engineer does, where to escalate. We train the shift on a live stand.
What remains with the customer: dashboards, alerts, documentation
Everything we set up remains with you in the form of ordinary files and settings inside your environment — not a "black box" that depends on the contractor.
Below is the full list of artifacts that we hand over along with accesses, so the system can be developed by your own forces or given to another team.
- Grafana panels — dashboard descriptions in a version control system: cluster overview, breakdown by shards and replicas, insert and merge delays, write queue.
- Alert rules — thresholds and trigger windows: replica unavailability, replication lag, growth of deferred inserts, disk exhaustion, spike of errors in logs.
- Metric collection configurations — collection targets, intervals, breakdowns by nodes, retention policy: what we keep for months and what we roll up into summary series.
- Accesses and roles — separation of view and edit rights, service accounts for exporters, integration with your single sign-on and groups.
- On-call regulations — who receives the notification, escalation procedure, silence windows during planned maintenance, recording the analysis of each trigger.
- Documentation — metric map with explanations, justification of thresholds, procedure for connecting a new node, signal path diagram from collector to notification.
- Instructions for typical failures — step-by-step scenarios: exporter not responding, part of history lost, false trigger.
- Acceptance checklist — test alert trigger, role check, history completeness, restoring a panel from its description.
All of the above is agreed upon at the launch stage so that acceptance is formal and quick, rather than "on word". Describe the task — we will return with an estimate and a handover plan.
Case study: incident response time reduced from hours to minutes
Before implementing monitoring, the team learned about problems on the ClickHouse cluster from users: reports returned errors before the on-call engineer saw the first signal.
Initial diagnostics took about forty minutes — logs were raised manually, event times were checked against files, the picture was assembled piece by piece, and all this time the downtime continued.
What we changed
We collected metrics from each node and assembled a dashboard by tables: insert and read speed, replica lag, number of unfinished merges, memory consumption and disk space, share of queries hitting limits.
Alert thresholds were set on the rate of change of the metric, not on the absolute value — the signal comes as a warning, 10-15 minutes before the user notices degradation.
What we got
Detection time was reduced to 3-5 minutes, incident analysis — from several hours to 15-20 minutes: the needed interval is already on the graphs, and you can see which metric grew first.
The class of failures that previously reached the client (disk overflow for temporary data, replica lag after peak load) is now closed at the warning stage, without downtime.
The same symptoms are no longer investigated anew: the rule and threshold are written once, and the next similar case is closed automatically — before anyone outside learns about it.
How ClickHouse monitoring from inside and outside differs
Short answer: these are not alternatives, but two layers that answer different questions.
The external loop shows whether the service is alive and what state the node is in, the internal one — what happens inside the engine: how merges proceed, whether replicas catch up, whether queues grow.
From inside, collection comes from system tables — system.metrics, system.events, system.query_log and asynchronous metrics.
From there you can see what cannot be guessed from outside: replication lag, number of merges and their duration, cache hits, distribution of time across execution stages.
The price is overhead: each poll is itself a query, gets into the log, and at a frequent interval begins to distort the picture.
From outside, the state of the process and node is collected: availability and response time, disk fill, memory, open connections. This loop continues to respond when the engine is already silent — it is the one that catches failure, not degradation.
| Approach | What it shows | Limitations |
|---|---|---|
| From inside the engine | Merges, replication, queues, caches, query stage times | Creates load itself; when the engine fails, there is no data |
| External loop | Availability, response time, disk, memory, connections | Does not see internal queues and replica lag |
In practice, the loops are brought together: frequent polling from outside and rare from inside — different intervals for different classes of metrics, so that completeness is not bought with extra load.
Alerts are separated by meaning: the external layer is responsible for "service unavailable", the internal one — for "service works but degrades".
Common technical doubts before starting work
Metric collection itself becomes a load — a justified concern, but only with an incorrect scheme. The exporter reads system counters already calculated by the server and does not scan table data. Below are the questions most often raised at the first meeting.
Will polling become extra load on the cluster?
The sample is taken from system counters from node memory, not from table data: the cost is network and writing to metric storage. Hot metrics are collected every 15–30 seconds, capacity and sizes — once a minute. Heavy queries to history do not go into polling.
How much history to store so that storage does not grow indefinitely?
Raw points live in a short window — it is enough for incident analysis. Then aggregation rules work: minute and hour rollups live for months, so graphs for a quarter remain, and the volume grows predictably.
Will notifications turn into a stream of false positives?
We build rules on derivatives: replication lag, merge queue, failure rate — not on "space below threshold". Each has a stability interval, the signal goes out only if the state persists for several cycles. The on-call engineer sees what exactly degraded and on which node.
We already have monitoring deployed — do we have to redo everything from scratch?
First, inventory: what is collected, which panels are alive, where rules duplicate each other. We integrate into the existing loop — supplement sources and rules, preserve metric history and connect it to new panels, leave the old ones working.
Let's discuss the task and come back with an estimate and work plan
To come back with a work plan rather than general words, we need input data on your environment. Topology is enough: how many nodes and shards, how replication is arranged, where inserts come from, and which consumers read data.
If failures have already occurred, describe how they manifested — replication delay, merge failures, growth of the insert queue, or failure of one node.
We think through accesses in advance so as not to touch anything in the production environment: a separate user only for reading system tables, metric collection from ClickHouse nodes and from the collector, a separate network path for panels with graphs. None of these changes require write permissions to data.
On our side, we come back not with a single number, but with an analysis by which a decision can be made:
- Input data on the environment: number of nodes and shards, replication scheme, insert sources, data volume.
- Accesses: user only for reading system tables, network rules for nodes and panels.
- Failure history: how they manifested, how many minutes they were noticed, how much time went into diagnostics.
- Thresholds and notifications: which metrics are important specifically to you and who receives the on-call signal.
- Scope of work and boundaries: what we do, what remains on your side — network, permissions, configuration changes.
- Work plan by stages with timelines and points where you see an intermediate result.
- Project estimate: scope of work, dependencies and risks, not a single number without explanations.
Describe the task in free form — write what already hurts and which metrics you want to see on dashboards. We will come back with an estimate, scope of work, and timelines.