$ guides / clickhouse ▌

CLICKHOUSE · OPERATIONS PLAYBOOK

Running ClickHouse in production, without the too-many-parts pages

A columnar OLAP engine where every insert writes a part, background merges race to consolidate them, and a coordination service keeps replicas in step. The mental model of how ClickHouse actually behaves under load, where it tends to break, what to monitor as your operation matures, and the runbooks for the incidents you'll see.

> Start with the monitoring checklist → # Jump to the full guide list

ClickHouse is famously fast to query and famously unforgiving once the insert rate, the part count, or the replica count grows past what the defaults assumed.

The defaults work. Until small inserts outrun the merge pool, a partition crosses parts_to_throw_insert, and every write fails with DB::Exception: Too many parts. Until a single GROUP BY over a high-cardinality column allocates past the server limit and the query dies with Memory limit (total) exceeded. Until a forked merge needs free space to write its result, the data disk crosses 90%, and you hit No space left on device with no graceful degradation. Until a Keeper session drops and system.replicas shows is_readonly = 1 while SELECT queries keep answering against stale data. Until a replica silently loses parts and only a SELECT count() on each node reveals the divergence.

These guides are written for engineers who already run ClickHouse, not for people learning what a column store is. The goal is to give you the mental model of how the server actually behaves under load, the failure patterns that keep recurring, the monitoring story that catches problems before they page anyone, and the runbooks you wish someone had handed you before your last incident.

How ClickHouse actually runs in production

ClickHouse is not just a fast SELECT engine. It is a write path that turns every insert into immutable parts, a background merge pool that fights to consolidate them, a coordination layer that keeps replicas in sync, and a disk that needs free space just to stay healthy. Most production failures live between these layers, not inside any one of them.

clients / connections

Applications connect over the native protocol (port 9000) and HTTP (port 8123). Concurrency is gated by <code>max_concurrent_queries</code> — exceed it and queries queue or fail with <code>Too many simultaneous queries</code>. ClickHouse degrades non-linearly under concurrency; it is built for fewer, heavier queries.

CLIENT

query execution

Each query fans out across <code>max_threads</code> for parallel reads and aggregation. <code>GROUP BY</code> and <code>JOIN</code> build hash tables in memory; a high-cardinality aggregation or a Cartesian-product join can allocate past the limit and die with <code>MEMORY_LIMIT_EXCEEDED</code> (code 241).

EXEC

INSERT → parts

Every <code>INSERT</code> writes one or more immutable data parts on disk — a directory of column files, index files, and checksums. Many small inserts create many small parts; this is the single most common road to a merge crisis.

INSERT

MergeTree storage

Parts belong to a table and a partition. The sparse primary index and granules let queries skip parts; partition pruning skips whole partitions. Part limits (<code>parts_to_delay_insert</code>, <code>parts_to_throw_insert</code>) are counted per partition, so over-partitioning multiplies the risk.

STORAGE

background merges + mutations

Background threads continuously merge small parts into larger ones — the workhorse that keeps part counts down. <code>ALTER UPDATE/DELETE</code> mutations rewrite whole parts and share the same pool, so heavy mutations silently starve merges and let parts pile up.

MERGE

ReplicatedMergeTree

Replicated tables stream a per-table replication log; each replica applies entries to fetch and merge parts. <code>system.replicas</code> exposes <code>queue_size</code>, <code>absolute_delay</code>, <code>is_readonly</code>, and <code>is_session_expired</code> — the operational truth of replication health.

REPLICA

ZooKeeper / Keeper

The coordination service holds replication and DDL state. A lost session turns a replica <code>is_readonly = 1</code>; a saturated ensemble freezes writes cluster-wide. ZK request P99 above ~10ms is the danger zone before sessions start expiring.

KEEPER

disk (parts + merge headroom)

Local NVMe, EBS, or object-store tiering under the data path. ClickHouse needs free space to write a merged part before deleting its sources — at 85–90% full, merges stall and the part count spirals. It does not handle <code>No space left on device</code> gracefully.

DISK

Why this matters: 'ClickHouse is slow' or 'inserts are failing' can come from a part-count wall, a starved merge pool, a stalled mutation, a memory-limited query, a Keeper session loss, a disk too full to merge, or one slow shard in a distributed query. The symptom rhymes, but each layer has a different signal and a different fix.

The failures you'll actually see

Most ClickHouse incidents fall into a small set of recurring patterns. Recognise the shape, and triage gets dramatically faster.

CRITICAL

The merge death spiral

Inserts arrive faster than background merges can consolidate parts. As parts accumulate, each merge has more candidates to evaluate and more files to open, so merges fall further behind. A partition crosses parts_to_delay_insert (1000) and inserts are throttled, then parts_to_throw_insert (3000) and writes are rejected with DB::Exception: Too many parts. If the disk fills during this, recovery needs manual intervention.

MaxPartCountForPartition climbing toward 3000
DelayedInserts then RejectedInserts incrementing in system.events
merge pool saturated but part count still growing
many small INSERTs in system.query_log with low rows-per-insert

Investigate →

CRITICAL

The disk space collapse

A merge reads its source parts and writes the merged result before deleting the sources, so it needs roughly twice the largest part as free space. Once the data disk is too full for a merge to complete, merges stop, small parts pile up, metadata grows, and disk usage accelerates into a self-reinforcing freeze. ClickHouse does not handle a full disk gracefully — it often crashes when logs consume the last of the space.

system.disks free_space below 15% and falling
No space left on device in the server log
merge activity stopped while part count grows
unreserved_space approaching zero

Investigate →

ACTIVE

The memory pressure cascade

A workload surge pushes MemoryTracking toward max_server_memory_usage. Either one runaway query — a Cartesian-product join or a high-cardinality GROUP BY — grabs bulk memory, or many concurrent queries accumulate near the limit. Queries then die with Memory limit (total) exceeded or spill to disk, retries compound the load, and a cgroup OOM kill can take the whole process down.

query failures with exception code 241 (MEMORY_LIMIT_EXCEEDED)
MemoryTracking approaching max_server_memory_usage
large peak_memory_usage in system.processes
spill-to-disk activity in /var/lib/clickhouse/tmp/

Investigate →

IMMINENT

The Keeper saturation choke

The coordination layer saturates — too many replicated tables, a DDL storm, or a ZooKeeper disk bottleneck. Request latency spikes, replicas lose their sessions, and system.replicas shows is_readonly = 1. The replica cannot accept writes even though its local storage and SELECT queries still work, and the failure spreads to every replicated table at once.

is_session_expired = 1 flapping then sticking on replicas
is_readonly = 1 sustained beyond a leader election
replication queue growing on multiple tables at once
distributed DDL hanging in system.distributed_ddl_queue

Investigate →

WATCHFUL

The silent replication divergence

A replica silently loses parts — a disk failure, filesystem corruption, or an accidental DROP PARTITION on one node. It reports perfectly healthy: the replication queue is empty, Keeper shows all replicas active, and queries answer fast. The divergence is invisible to every standard metric and surfaces only when a SELECT count() on each replica returns different numbers.

row counts differ across replicas for the same table
ReplicatedDataLoss counter above zero
empty replication queue but mismatched per-partition sums
queries returning different results from different replicas

Investigate →

ACTIVE

The query concurrency storm

A retry loop, a dashboard 'refresh all', or a runaway batch job floods the server with concurrent queries. Because ClickHouse is built for fewer, heavier queries, performance degrades non-linearly: queries compete for CPU, memory, and I/O, latency climbs, and new queries hit max_concurrent_queries and fail with Too many simultaneous queries while memory pressure builds underneath.

Too many simultaneous queries errors in the log
system.metrics Query count near max_concurrent_queries
concurrent queries above 2x typical peak
query latency P99 climbing with concurrency

Investigate →

The Netdata solution

ClickHouse monitoring with Netdata

Netdata monitors ClickHouse with per-second metrics and ML anomaly detection. Track merge debt, memory usage, replication lag, Keeper/ZooKeeper saturation, and disk headroom against the host signals that drive them.

See ClickHouse monitoring → Start monitoring free

ClickHouse monitoring maturity levels

ClickHouse observability works in four practical levels. Each is a complete operation, not a stepping stone. Pick the level that matches how much your ClickHouse matters. Most production clusters should land at the second level.

Level 1: Survival

Know that something is wrong

Survival monitoring is the floor. With these signals you can answer one question: is ClickHouse still accepting reads and writes? You will not learn what broke, but you will learn that something broke before users do. Survival is enough for dev environments and low-stakes analytics.

Ping / SELECT 1 Does /ping return Ok. and does a trivial query succeed?
Process running Is clickhouse-server alive, or did it crash or fail to start?
RejectedInserts counter Any non-zero rate means inserts are being dropped — too many parts.
Disk space on the data volume Is the data disk under 90%, or about to freeze merges?
Server memory vs limit Is MemoryTracking far enough below max_server_memory_usage?
is_session_expired on replicas Has any replicated table lost its Keeper session?

Level 2: Operational

Diagnose most incidents on your own

Operational monitoring is what most production clusters should target. Survival tells you something is wrong; operational tells you what. With this coverage your team can usually diagnose an incident on its own: part accumulation, merge stalls, memory pressure, replication lag, insert throttling.

MaxPartCountForPartition The #1 leading indicator — how close is the worst partition to parts_to_throw_insert?
DelayedInserts rate Are inserts being throttled before they get rejected?
Active merges (system.merges) Are merges actually running while part count is high?
MemoryTracking / MemoryResident Tracked memory and RSS against the server limit.
FailedQuery / Query ratio Is the error rate above 1%, and which exception codes?
Query latency P99 Is tail latency drifting above its baseline?
absolute_delay on replicas How many wall-clock seconds is each replica behind?
is_readonly on replicas Can each replicated table still accept writes?
Disk utilization (system.disks) Free and unreserved space against the 80–85% target.

Level 3: Mature

Catch problems before they become incidents

Mature monitoring catches problems before they wake anyone up. A mutation quietly starving merges, a replication entry stuck on retries, the merge pool saturating, file descriptors climbing with part count, distributed DDL drifting. None of these page you on day one. They become page-out incidents on day thirty.

Mutation queue (system.mutations) Is parts_to_do decreasing, or is a mutation stalled and blocking merges?
Replication queue detail Any entry with num_tries > 5 and a non-empty last_exception?
Background pool utilization Is the merge/mutation pool saturated while parts grow?
File descriptors vs nofile limit Is FD usage climbing with part count toward the limit?
Per-query memory (system.processes) Which live query is consuming the most memory right now?
Keeper connection health Is the ZK/Keeper session stable and is request latency low?
Distributed DDL queue Are ON CLUSTER statements finishing on every host?
Detached parts Are parts being detached for fetch or checksum trouble?
Insert latency P99 Is write latency rising ahead of a merge backlog?

Level 4: Expert

Reactive instrumentation after real incidents

Expert signals enter your stack the day after a specific incident proved you needed them. Per-partition part skew, merge duration trends, cache hit rates, spill-to-disk volume, per-shard latency, jemalloc divergence. Most teams never need every signal here. Add the ones your incident history says you do.

Per-partition part skew Is one partition accumulating parts while the table average looks fine?
Merge duration trend (P99) Are merges taking hours instead of minutes — the 1–3 day warning?
Mark / uncompressed cache hit rate Is the working set outgrowing the caches?
Spill-to-disk in /tmp How often are large aggregations falling back to disk?
Per-shard distributed latency Is one slow shard dragging every distributed query?
MemoryTracking vs RSS divergence Is jemalloc fragmentation pushing RSS toward the OOM line?
ReplicatedDataLoss / checks failed Has any part been lost or failed a checksum?
session_log login failures Brute-force attempts or credential drift after rotation?

Operating mistakes worth avoiding

The traps ClickHouse teams keep falling into. Each has a clear, well-known fix. Most teams only learn it after an incident.

Inserting one row at a time

Every <code>INSERT</code> creates at least one part, and the merge pool has to consolidate every one of them. A stream of tiny inserts is the single most common road to <code>Too many parts</code>. Batch to 1000+ rows per insert, or enable <code>async_insert</code> so the server buffers small inserts into fewer, larger parts.

Only watching latency and CPU, not part count

Most ClickHouse incidents start as storage-structure debt — accumulating parts — not classic database load. By the time latency degrades, the merge crisis is already advanced. <code>MaxPartCountForPartition</code> per partition is the leading indicator that predicts the emergency hours ahead.

Over-partitioning the table

Part limits are counted per partition, so partitioning by day or by a high-cardinality column multiplies your part count and your merge load. Most tables want a coarse partition key — monthly, not hourly — with the time and primary dimensions in the <code>ORDER BY</code> key instead.

Treating ZooKeeper as fire-and-forget

For replicated setups, Keeper is the most critical dependency, and teams assume it 'just works'. Not monitoring session expiry and request latency leads to cluster freezes where everything looks connected but nothing accepts writes. Keep ZK request P99 under ~10ms and alert on <code>is_session_expired</code>.

Using ALTER UPDATE/DELETE like row updates

Mutations rewrite entire parts and share the background pool with merges, so heavy <code>ALTER UPDATE/DELETE</code> usage silently starves merges and lets part counts climb. Watch <code>system.mutations</code> alongside <code>system.parts</code>, and model updates with <code>ReplacingMergeTree</code> or lightweight <code>DELETE</code> instead.

Treating disk space as a capacity metric

ClickHouse needs free space to write a merged part before deleting its sources. Disk at 85% isn't 'running low' — it's potentially blocking merges, and the cliff from 'low disk' to 'dead system' is sudden. Keep at least 2x the largest active part free, and stay under 80–85%.

Trusting liveness checks for health

<code>SELECT 1</code> and <code>/ping</code> succeed even when the server is severely degraded. A healthy ping can coexist with a merge crisis, a broken replication control plane, and a stale replica serving outdated data fast. Liveness gives false confidence — monitor the internal signals too.

Letting system log tables grow unbounded

<code>system.query_log</code>, <code>part_log</code>, and friends are MergeTree tables that grow forever unless you give them a TTL. On a high-QPS server they can consume serious disk and even trigger <code>Too many parts</code> on themselves. Set a retention TTL and a sensible partition on the system tables.

ClickHouse runbooks in this section

Each guide is a focused runbook for one symptom or topic. Pick one when you have an incident, or use the categories to learn the area.

▸

Start here

▸

Parts, merges, and the too-many-parts wall

▸

Inserts, batching, and async inserts

▸

Memory limits and OOM

▸

Mutations and ALTER UPDATE/DELETE

▸

ReplicatedMergeTree and replication lag

▸

ZooKeeper / ClickHouse Keeper coordination

▸

Disk space, parts, and storage

▸

Slow queries and query performance

▸

Concurrency, connections, and saturation

▸

Data integrity, corruption, and security

WHERE TO GO NEXT

Setting up ClickHouse monitoring, or putting out a fire?

If you're starting from scratch, the monitoring checklist is the path of least regret. If you're mid-incident, jump straight to the symptom that matches what you're seeing.

> Start with the checklist > Back to Operations Guides