Replication & Availability Monitoring

A ReplicatedMergeTree deployment looks healthy right up to the moment a replica loses its ClickHouse Keeper session, flips read-only, and starts serving hours-old data without raising a single error. This reference gives data platform and DevOps teams the system.* queries, alert thresholds, and remediation runbook to catch replica drift, read-only flips, and stale reads before dashboards do.

Replication in ClickHouse is coordinated through Keeper (or legacy ZooKeeper): every insert appends an entry to a shared replication log, and each replica advances its own log_pointer as it pulls and applies those entries. When Keeper is healthy the replicas stay within a second of each other. When Keeper is slow, partitioned, or a node’s session expires, the failure is quiet — the affected replica keeps answering SELECTs from whatever parts it already has, so absolute_delay climbs while the query layer sees no exception. Owning this surface means watching four tables together — system.replicas, system.replication_queue, system.replicated_fetches, and system.zookeeper — and knowing which combination of is_readonly, is_session_expired, and absolute_delay means “page someone now” versus “a merge is catching up.” This page is the working reference for that ownership, and it sits under the broader monitoring, observability & operations practice.

How replication health is coordinated

Inserts land on one replica and are replicated to the others through Keeper. The replication log is the source of truth; each replica’s distance behind the log tail is its lag. A replica that cannot reach its Keeper session loses the ability to accept writes and goes read-only — but it does not stop serving reads, which is exactly why the condition is dangerous.

Two properties of this topology set every alert threshold below. First, a read-only flip is downstream of session loss, not an independent event — is_session_expired = 1 is what forces is_readonly = 1, so the session flag is the earlier, more actionable signal. Second, absolute_delay is a derived, whole-cluster-visible quantity: it measures how far the local replica trails the newest committed data, which is precisely the staleness a reader experiences. Everything else — queue_size, inserts_in_queue, fetch counts — explains why the delay is what it is.

Core monitoring reference

You do not create tables to monitor replication; the system.* tables already exist. But a durable monitoring pipeline benefits from a small history table so you can alert on trends rather than instantaneous samples, and so you can correlate a lag spike with a deploy or a Keeper incident after the fact. The queries and the optional history table below are the copy-ready core.

The single most important health query reads one row per replicated table on the local node:

-- Per-replica health snapshot. Run on every node; each node reports only its own replicas.
SELECT
    database,
    table,
    is_readonly,           -- 1 = cannot accept writes (usually Keeper session loss)
    is_session_expired,    -- 1 = lost Keeper session; the root cause of most read-only flips
    absolute_delay,        -- seconds this replica trails the freshest committed data
    queue_size,            -- total unprocessed replication_queue entries
    inserts_in_queue,      -- GET_PART/fetch entries still pending (write backlog)
    merges_in_queue,       -- MERGE_PARTS entries still pending
    log_pointer,           -- last replication-log index this replica has applied
    log_max_index,         -- newest index in the shared log (Keeper)
    total_replicas,
    active_replicas        -- < total_replicas means peers are unreachable
FROM system.replicas
ORDER BY absolute_delay DESC;

A healthy row shows is_readonly = 0, is_session_expired = 0, absolute_delay near zero, and active_replicas = total_replicas. The gap log_max_index - log_pointer is the backlog this replica still has to apply; when it grows monotonically the replica is falling behind rather than catching up. The precise arithmetic and its alerting thresholds are worked through in monitoring replication lag with system.replicas.

To persist samples for trend alerting, land them in a compact MergeTree history table on a monitoring database:

-- Optional history table so alerts fire on sustained lag, not single noisy samples.
CREATE TABLE IF NOT EXISTS monitoring.replica_health_history
(
    sampled_at         DateTime64(3),
    host               LowCardinality(String),
    database           LowCardinality(String),
    table              LowCardinality(String),
    is_readonly        UInt8,
    is_session_expired UInt8,
    absolute_delay     UInt32,
    queue_size         UInt32,
    inserts_in_queue   UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(sampled_at)          -- daily parts, cheap TTL expiry
ORDER BY (database, table, host, sampled_at) -- query by table then time window
TTL toDateTime(sampled_at) + INTERVAL 30 DAY
SETTINGS index_granularity = 8192;

An external sampler (a Prometheus exporter, a cron job, or an orchestrated task) runs the snapshot query every 15–30 seconds and inserts the rows. Keep the sampler pointed at every node individually — system.replicas is node-local, so a single endpoint behind a load balancer will silently miss whichever replica it is not routed to.

Step-by-step implementation

1. Confirm every replica is registered and active

Before trusting any delay number, confirm the ensemble agrees on its own membership. A replica missing from active_replicas is unreachable, which changes how you read every other metric.

SELECT database, table, total_replicas, active_replicas,
       total_replicas - active_replicas AS unreachable
FROM system.replicas
WHERE total_replicas != active_replicas;

An empty result is the healthy state. Any row means at least one peer cannot be reached over the interserver protocol or has lost its Keeper session — investigate that node before tuning anything.

2. Read the replication queue to explain the lag

system.replicas tells you that a replica is behind; system.replication_queue tells you why. Each row is one pending operation with its retry count and last error.

SELECT database, table, type, num_tries, num_postponed,
       postpone_reason, last_exception,
       last_attempt_time
FROM system.replication_queue
WHERE num_tries > 3 OR last_exception != ''
ORDER BY num_tries DESC
LIMIT 50;

A queue with low num_tries and empty last_exception is a healthy backlog draining normally. High num_tries on the same entries, or a recurring last_exception, is a stuck entry — a part that cannot be fetched or merged — which will hold absolute_delay up indefinitely until you intervene.

3. Watch in-flight fetches during catch-up

When a replica recovers or is added, it pulls missing parts from peers. system.replicated_fetches shows those transfers live, so you can tell “actively catching up” from “wedged.”

SELECT database, table, source_replica_hostname,
       round(progress, 3)               AS progress,
       formatReadableSize(total_size_bytes_compressed) AS part_size,
       round(elapsed, 1)                AS elapsed_s
FROM system.replicated_fetches
ORDER BY elapsed DESC;

Rows with progress advancing between samples mean recovery is proceeding. Rows whose progress is stuck at the same value across samples, or an empty result while inserts_in_queue stays high, means fetches are not being scheduled — check network bandwidth throttling and Keeper health next.

4. Verify Keeper itself is healthy

Every symptom above can trace back to Keeper. Read Keeper state directly through the system.zookeeper virtual table, which proxies live requests to the ensemble.

-- Round-trip to Keeper: an error or a hang here is the real incident.
SELECT name, value, numChildren, mtime
FROM system.zookeeper
WHERE path = '/clickhouse'
ORDER BY name;

If this query returns promptly, the local node’s Keeper session is alive. If it errors or hangs, the node has lost coordination — expect read-only flips and climbing delay on that node until the session is re-established. Cross-check the ensemble’s own view with echo mntr | nc keeper-host 9181 at the OS level to confirm quorum, follower count, and outstanding requests; a Keeper leader with a growing zk_outstanding_requests count is the upstream cause of cluster-wide lag.

5. Assemble a whole-cluster view

Because system.replicas is node-local, a single node’s snapshot is blind to peers. Fan the health query across every node with clusterAllReplicas so one query returns the whole cluster’s replication state and you can spot the outlier without scraping each host by hand.

SELECT
    hostName()                       AS host,
    database, table,
    is_readonly, is_session_expired,
    absolute_delay, queue_size, inserts_in_queue
FROM clusterAllReplicas('analytics_cluster', system.replicas)
WHERE is_readonly = 1
   OR absolute_delay > 30
   OR is_session_expired = 1
ORDER BY absolute_delay DESC;

An empty result is the all-clear across the entire cluster. Any row names the specific host and table to investigate, which is exactly the input a health-aware router or an alerting rule needs. Run this from a monitoring node on a 15–30 second interval and persist the rows into the history table so alerts fire on a sustained condition rather than a single sample.

Integration touchpoints

Replication monitoring does not stand alone; it shares failure modes and remediation levers with the tiers on either side of it.

• Part and merge health. A stuck MERGE_PARTS entry in the replication queue shows up first as a merge that never completes, so replication lag and merge lag are frequently the same incident viewed from two tables. Correlate this page’s queries with part & merge health monitoring — a replica whose queue is full of merge entries is bottlenecked on background_pool_size, not on the network.
• Failover and routing. Detection is only half the job; the other half is routing reads away from a read-only or lagging replica. The fallback routing & high availability reference covers the quorum settings, health-aware load balancing, and failover DDL that consume the is_readonly and absolute_delay signals produced here.
• Ingestion pressure. A high sustained inserts_in_queue on the receiving replicas often means the write path is producing parts faster than they replicate. When that is the cause, the fix is upstream — larger insert blocks and buffering — not more merge threads on the replica.
• Memory ceilings during recovery. A replica catching up runs many concurrent fetches and merges, which spikes memory. Watch it against the budget described in tracking memory pressure with system.metrics so a recovery does not trigger an OOM that resets the catch-up to zero.

Tuning parameters

These settings shape how aggressively a replica catches up and how tolerant the deployment is of coordination hiccups. Values assume a 32 vCPU / 128 GB node on a replicated cluster; tune against your own baselines.

The two levers that most often need attention together are max_replicated_fetches_network_bandwidth and background_fetches_pool_size. Uncapped bandwidth lets a recovering replica saturate the network and degrade live queries; too tight a cap makes catch-up take hours, during which the replica serves stale data. Set the bandwidth ceiling to roughly a third of your interserver link and raise the fetch pool only if catch-up is the bottleneck and memory headroom allows.

Troubleshooting

Replica is read-only and refuses writes. is_readonly = 1 with is_session_expired = 1 is Keeper session loss. Confirm the scope and the root cause:

SELECT database, table, is_readonly, is_session_expired, zookeeper_exception
FROM system.replicas
WHERE is_readonly = 1;

Fix: verify Keeper quorum (echo mntr | nc keeper-host 9181) and network reachability from the affected node. Once the session re-establishes, the replica clears read-only automatically; if it does not, SYSTEM RESTORE REPLICA db.table rebuilds its Keeper metadata from the other replicas.

absolute_delay keeps climbing but the node is up. The queue is backlogged. Determine whether it is fetches or merges:

SELECT type, count() AS entries, max(num_tries) AS max_tries
FROM system.replication_queue
GROUP BY type
ORDER BY entries DESC;

Fix: a GET_PART backlog is a fetch/network problem — raise background_fetches_pool_size or the bandwidth cap. A MERGE_PARTS backlog is a compute problem — raise background_pool_size or reduce insert-side part creation.

A single queue entry is stuck and blocks everything behind it. A poison entry with a persistent last_exception and rising num_tries halts the queue for its table.

SELECT database, table, node_name, type, num_tries, last_exception
FROM system.replication_queue
WHERE num_tries > 20
ORDER BY num_tries DESC;

Fix: if the exception names a missing or corrupt part, SYSTEM SYNC REPLICA db.table re-reconciles against peers; for a genuinely unrecoverable entry, detaching the offending part on the source replica lets the queue advance. Remediation for these entries is detailed in monitoring replication lag with system.replicas.

active_replicas < total_replicas on an otherwise idle table. A peer is unreachable even though this node is fine. Confirm which peer:

SELECT database, table, replica_name, active_replicas, total_replicas
FROM system.replicas
WHERE active_replicas < total_replicas;

Fix: the missing replica has either lost its Keeper session or is down. Check that node directly; do not fail reads away from the reachable replicas, which are serving correctly.

Stale reads slip through despite healthy-looking replicas. A replica with absolute_delay = 40 answers SELECTs with 40-second-old data and raises no error. For read-after-write correctness, force consistency at query time:

SELECT count() FROM analytics.events_raw
SETTINGS select_sequential_consistency = 1;

Fix: set select_sequential_consistency = 1 on latency-tolerant correctness-critical reads, and route dashboard traffic away from any replica whose absolute_delay exceeds your freshness budget using the health-aware routing in fallback routing & high availability.

Related

• Monitoring replication lag with system.replicas — the log_pointer arithmetic, alert thresholds, and stuck-entry remediation in depth.
• Tracking memory pressure with system.metrics — the memory budget a recovering replica must respect.
• Part & merge health monitoring — where a stalled MERGE_PARTS queue entry surfaces first.
• Fallback routing & high availability — consuming is_readonly and absolute_delay to route reads and drive failover.
• Monitoring, observability & operations — the broader operational surface this replication view is one facet of.

Up: Monitoring, Observability & Operations