ClickHouse Shard Contention Troubleshooting: A Complete Guide

ClickHouse shard contention is one of the most frustrating performance problems a distributed database team can face. When a cluster scales out to dozens or hundreds of nodes, the promise of linear scalability quietly breaks down the moment one or more shards begin to fight over shared resources. Queries that used to return in milliseconds start crawling, replication lag creeps upward, and dashboards fill with spikes that nobody can immediately explain. This guide walks through the causes of ClickHouse shard contention, the diagnostic workflow that reliably surfaces the root cause, and the concrete remediation steps that restore predictable latency across your cluster.

Throughout this article we focus on practical, reproducible troubleshooting. Every diagnostic query is something you can paste directly into clickhouse-client, and every remediation is grounded in how ClickHouse actually schedules work internally. Whether you run a small three-shard analytics cluster or a large multi-tenant deployment, the same principles for ClickHouse shard contention troubleshooting apply. If you are new to distributed tables, the official ClickHouse Distributed engine documentation is a useful companion to this guide.

What Is ClickHouse Shard Contention?

A ClickHouse cluster distributes data across shards, and each shard typically holds one or more replicas. Contention occurs when concurrent operations compete for a finite resource on a single shard: CPU cores, disk I/O bandwidth, the background merge pool, memory, or the number of simultaneous queries permitted. Because a distributed query fans out to every shard and then waits for the slowest one to respond, contention on a single shard degrades the latency of the entire cluster. This is the classic “straggler” problem, and it is the heart of most ClickHouse shard contention issues.

Contention is different from simple overload. An overloaded cluster is uniformly busy, and the fix is usually more hardware. Shard contention is asymmetric: one shard is saturated while others sit idle. That asymmetry is both the symptom and the clue. When you see uneven resource usage across shards, you are almost certainly looking at a contention or data-skew problem rather than a raw capacity problem. For a deeper look at how data is placed, see our guide on ClickHouse sharding strategies.

Common Causes of Shard Contention in ClickHouse

Before diving into diagnostics, it helps to understand the recurring culprits behind ClickHouse shard contention. Data skew is the most frequent cause: a poorly chosen sharding key sends a disproportionate share of rows to one shard. Hot partitions are a close second, where recent time-based partitions receive all the write and read traffic while older partitions stay cold. Merge storms happen when the background merge scheduler on one shard falls behind and then tries to catch up all at once, starving foreground queries of CPU and disk. Finally, unbounded concurrency, where too many heavy queries hit the same shard simultaneously, produces contention on the query and memory limits.

Each of these causes leaves a distinct fingerprint in ClickHouse’s system tables. The troubleshooting workflow below is designed to read those fingerprints in order, from the cheapest checks to the most involved.

Step 1: Confirm the Contention Is Real and Localized

The first step in any ClickHouse shard contention troubleshooting session is to confirm that ClickHouse shard contention is localized to specific shards rather than spread evenly. Run the following query against each node, or use a distributed query to compare current activity. The system.processes table shows every query currently executing on the node it is queried from.

SELECT
    hostName() AS host,
    count() AS running_queries,
    sum(memory_usage) AS total_memory,
    max(elapsed) AS longest_query_sec
FROM clusterAllReplicas('default', system.processes)
WHERE query NOT LIKE '%system.processes%'
GROUP BY host
ORDER BY running_queries DESC;

If one host reports far more running queries, higher memory usage, or a much larger longest_query_sec than its peers, you have confirmed localized contention. A healthy cluster shows roughly even numbers across hosts. A contended cluster shows one or two outliers standing well above the rest.

Step 2: Check for Data Skew Across Shards

Data skew is the root cause behind a large fraction of ClickHouse shard contention problems, so measuring distribution is central to ClickHouse shard contention troubleshooting. If your sharding key concentrates rows on one shard, that shard will always do more work. Measure the row and byte distribution across shards with a query against system.parts, which reports the size of every active data part.

SELECT
    hostName() AS host,
    database,
    table,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk,
    count() AS part_count
FROM clusterAllReplicas('default', system.parts)
WHERE active AND database = 'analytics'
GROUP BY host, database, table
ORDER BY total_rows DESC;

Look for one host holding significantly more rows or bytes than the others. A variance of ten to twenty percent is normal, but a shard holding double or triple the data of its peers signals a skewed sharding key. Common mistakes include sharding on a low-cardinality column, sharding on a monotonically increasing ID that clusters recent data, or using a hash that does not distribute your particular key space evenly.

Step 3: Inspect Background Merge Activity

A frequent driver of ClickHouse shard contention is background merging, since ClickHouse continuously merges small data parts into larger ones in the background. When merges on a single shard fall behind, they eventually trigger a merge storm that consumes CPU and disk bandwidth, directly contending with foreground queries. The system.merges table exposes in-flight merges, and system.metrics reports the size of the background pool. The official system.merges reference documents every column in detail.

SELECT
    hostName() AS host,
    count() AS active_merges,
    round(avg(progress), 3) AS avg_progress,
    formatReadableSize(sum(memory_usage)) AS merge_memory,
    max(elapsed) AS longest_merge_sec
FROM clusterAllReplicas('default', system.merges)
GROUP BY host
ORDER BY active_merges DESC;

A shard with many active merges, low average progress, and a large longest_merge_sec is caught in a merge storm. You can corroborate this by checking the number of pending parts. A high and rising part count on one shard means the merge scheduler cannot keep up with the write rate, which is a leading indicator of imminent contention.

SELECT
    hostName() AS host,
    table,
    count() AS parts,
    max(level) AS max_merge_level
FROM clusterAllReplicas('default', system.parts)
WHERE active AND database = 'analytics'
GROUP BY host, table
HAVING parts > 300
ORDER BY parts DESC;

Step 4: Identify Hot Partitions and Expensive Queries

Even with balanced data, ClickHouse shard contention is often the victim of a handful of expensive queries hammering a hot partition. The system.query_log table records every executed query with its resource consumption. This query surfaces the heaviest recent queries per host, which almost always reveals the workload driving contention. Our post on ClickHouse query performance tuning covers optimization techniques in depth.

SELECT
    hostName() AS host,
    normalizeQuery(query) AS query_pattern,
    count() AS executions,
    round(avg(query_duration_ms)) AS avg_ms,
    formatReadableSize(avg(memory_usage)) AS avg_memory,
    formatReadableSize(sum(read_bytes)) AS total_read
FROM clusterAllReplicas('default', system.query_log)
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 HOUR
GROUP BY host, query_pattern
ORDER BY sum(read_bytes) DESC
LIMIT 20;

When you find a query pattern that reads enormous amounts of data on the contended shard, you have your workload culprit. The fix may be adding a projection, refining the primary key, or introducing partition pruning so the query touches fewer parts. Often a single missing WHERE clause on the partition column is responsible for scanning an entire shard.

Step 5: Examine Concurrency and Resource Limits

When diagnosing ClickHouse shard contention, remember that ClickHouse enforces several limits that, when hit, cause queries to queue or fail on a shard. The setting max_concurrent_queries caps simultaneous execution, and when it is exceeded new queries wait. Check the current server settings and metrics on each node to see whether you are bumping against a ceiling.

SELECT
    hostName() AS host,
    metric,
    value
FROM clusterAllReplicas('default', system.metrics)
WHERE metric IN ('Query', 'Merge', 'BackgroundMergesAndMutationsPoolTask', 'MemoryTracking')
ORDER BY host, metric;

If the Query metric on one shard sits near your configured max_concurrent_queries, the shard is serializing work and contention is the direct result. In that case you can either raise the limit if the hardware has headroom, or, more sustainably, reduce the concurrency of the client application so it does not overwhelm a single shard.

Remediation: Rebalancing a Skewed Cluster

Once diagnostics point to data skew, the durable fix is a better sharding strategy. For new tables, choose a sharding key with high cardinality and even distribution. A common pattern is to shard on a hash of a natural key rather than the key itself, which spreads related rows evenly. When creating a distributed table, the sharding expression is defined in the engine parameters.

CREATE TABLE analytics.events_distributed AS analytics.events_local
ENGINE = Distributed(
    'default',
    'analytics',
    'events_local',
    cityHash64(user_id)
);

The cityHash64(user_id) expression ensures that rows are distributed across shards based on a well-mixed hash rather than a raw sequential value. For an existing skewed table, you generally cannot reshard in place. The practical approach is to create a new correctly sharded table and re-insert data through the distributed table, allowing ClickHouse to route rows to the appropriate shard.

INSERT INTO analytics.events_distributed
SELECT * FROM analytics.events_old
SETTINGS max_insert_threads = 4,
         max_insert_block_size = 1048576;

Remediation: Taming Merge Storms

If merges are the source of ClickHouse shard contention, the goal is to smooth out background merge activity so it never competes aggressively with foreground queries. ClickHouse exposes several merge-tuning settings at the server and table level. Lowering the number of background pool threads reduces the peak CPU that merges can consume, while adjusting the parts-to-throw thresholds controls how aggressively the scheduler reacts to part accumulation.

ALTER TABLE analytics.events_local
MODIFY SETTING
    max_bytes_to_merge_at_max_space_in_pool = 53687091200,
    parts_to_delay_insert = 300,
    parts_to_throw_insert = 600;

Raising parts_to_delay_insert gives the merge scheduler more room before it begins throttling inserts, while a moderate parts_to_throw_insert still protects the shard from runaway part counts. If your ingestion pattern produces many tiny inserts, the single most effective change is to batch writes on the client side so ClickHouse creates fewer, larger parts and therefore needs fewer merges. Aim for insert batches of at least a few tens of thousands of rows.

Remediation: Controlling Query Concurrency

When concurrency is the bottleneck, the sustainable fix is to shape the workload rather than simply raising limits. ClickHouse workload settings let you cap the resources any single query may consume, which prevents one runaway query from monopolizing a shard. Setting sensible per-query memory and thread ceilings keeps the shard responsive under bursty load.

SET max_memory_usage = 10000000000;
SET max_threads = 8;
SET max_execution_time = 60;
SET max_concurrent_queries_for_user = 20;

Applying these as user-level or profile-level settings gives you predictable behavior across the whole application tier. For multi-tenant clusters, defining separate settings profiles per tenant prevents one heavy tenant from starving others on a shared shard, which is a frequent source of perceived ClickHouse shard contention in SaaS deployments.

Monitoring to Prevent Future Contention

Troubleshooting is reactive; the real goal of ClickHouse shard contention troubleshooting is to catch ClickHouse shard contention before users notice. Build lightweight monitoring on top of the same system tables used in diagnosis. A scheduled job that records per-shard row counts, part counts, active merges, and running query counts gives you the historical baseline needed to spot drift early. When any shard deviates meaningfully from the cluster median, alert on it.

SELECT
    hostName() AS host,
    (SELECT count() FROM system.processes) AS running_queries,
    (SELECT count() FROM system.merges) AS active_merges,
    (SELECT sum(rows) FROM system.parts WHERE active) AS total_rows,
    (SELECT value FROM system.metrics WHERE metric = 'MemoryTracking') AS memory_used
FROM system.one
FORMAT JSON;

Scraping this JSON output on a short interval and pushing it into your metrics stack gives you dashboards that make asymmetry obvious at a glance. The moment one shard’s line diverges from the pack, you know where to look before latency degrades.

Operating-System Level Checks

Not all contention lives inside ClickHouse. Disk saturation, CPU steal on virtualized hosts, and network throttling can all masquerade as shard contention. When the ClickHouse system tables look balanced but one shard is still slow, drop to the operating system and inspect I/O and CPU directly.

iostat -x 2 5
mpstat -P ALL 2 5
clickhouse-client --query "SELECT * FROM system.asynchronous_metrics WHERE metric LIKE '%Disk%' FORMAT Pretty"

High %util and rising await times in iostat point to a storage bottleneck on that specific shard, which may mean the underlying disk is failing, undersized, or shared with a noisy neighbor in a cloud environment. Correlating OS metrics with ClickHouse metrics is the final step that distinguishes a database-level problem from an infrastructure-level one.

Putting the Workflow Together

Effective ClickHouse shard contention troubleshooting follows a disciplined order, and every step targets a specific source of ClickHouse shard contention. First confirm that the slowness is localized to specific shards rather than uniform. Next check data distribution to rule out or confirm skew. Then inspect background merges, hot partitions, and expensive queries in turn, using the query log to pin down the exact workload. Finally verify concurrency limits and, when necessary, drop to the operating system to rule out infrastructure causes. Working from cheapest to most expensive checks means you usually find the root cause within the first two or three steps.

The remediations for ClickHouse shard contention follow naturally from the diagnosis. Skew is solved with a better sharding key and a re-insert. Merge storms are tamed with batched writes and tuned merge settings. Concurrency contention is solved by shaping the workload with per-query and per-user limits. And infrastructure problems are solved by fixing or isolating the affected hardware. In every case the fix is targeted at the specific shard and specific resource that diagnosis identified.

Conclusion

ClickHouse shard contention is rarely mysterious once you approach it methodically. The database exposes everything you need in its system tables, and a small set of queries against system.processes, system.parts, system.merges, and system.query_log will surface the root cause of almost any contention scenario. By confirming that contention is localized, measuring data skew, watching merge activity, profiling expensive queries, and checking concurrency limits, you convert a vague “the cluster is slow” complaint into a specific, actionable finding.

The long-term answer to ClickHouse shard contention is prevention through good design and continuous monitoring. Choose sharding keys that distribute evenly, batch your inserts, tune merges for your ingestion pattern, and shape query concurrency so no single shard is ever overwhelmed. Pair that discipline with dashboards built on the same system tables you use for troubleshooting, and shard contention becomes a problem you catch early rather than one that catches you. With this workflow in hand, your ClickHouse cluster can deliver the predictable, low-latency performance that made you choose it in the first place. For expert help, explore ChistaDATA ClickHouse consulting services.