ClickHouse Performance Troubleshooting: 9 Proven Techniques for 26.7

ClickHouse performance troubleshooting is mostly a reading problem, not a tooling problem. A 26.7 server already writes down almost everything you need — every query it ran, every byte it touched, every microsecond each operator spent — and the hard part is knowing which of the ninety-odd tables in system answers the question actually in front of you.

What follows is the ClickHouse performance troubleshooting sequence we run on customer clusters, in the order we run it, with the SQL we paste into clickhouse-client. It was checked against ClickHouse 26.7 (released 22 July 2026). If you are still on a 24.x or 25.x LTS, the workflow is identical — a couple of the system tables are just narrower.

Table of Contents

• Step zero: define “slow”
• 1. Start in system.query_log, not in a dashboard
• 2. Read the ProfileEvents map instead of guessing
• 3. Prove the index works with EXPLAIN indexes = 1
• 4. Find the stuck operator in the pipeline
• 5. Watch one query breathe, second by second
• 6. Turn CPU time into a flamegraph
• 7. Memory ceilings: read the error, then bound the query
• 8. Part explosion and merge backpressure
• 9. Prove the fix with cold caches
• What 26.7 changed
• The one-page runbook
• Where troubleshooting usually ends
• FAQ

Step zero of ClickHouse performance troubleshooting: define “slow”

Half the tickets we get titled “ClickHouse is slow” turn out to be a client that changed its page size, or a BI tool that quietly started issuing SELECT *. So the first move in any ClickHouse performance troubleshooting session is boring: agree on a number, a time window, and a query fingerprint. Without those three you will end up optimising something that was never broken.

Pick the percentile that matters to the people complaining. p50 tells you about the shape of your workload; p99 tells you about the tail that pages someone at 3 a.m. They usually have different root causes, and our p99 latency analysis playbook explains why the tail is rarely just the mean with a longer shadow.

Then decide which stage of execution you are actually accusing. The diagram below is the mental model we hand to every engineer on an engagement — it maps each execution stage to the one table that can prove or disprove your theory about it.

1. Start ClickHouse performance troubleshooting in system.query_log, not in a dashboard

Dashboards aggregate away the thing you need: the individual query. system.query_log keeps the raw rows, and it is the only honest starting point for ClickHouse performance troubleshooting.

One gotcha that costs people an hour: the log is buffered. If the query you are hunting was run seconds ago and is not there yet, run SYSTEM FLUSH LOGS first.

-- Where did the cluster's time go in the last six hours?
SYSTEM FLUSH LOGS;

SELECT
    normalized_query_hash AS fingerprint,
    count() AS runs,
    round(quantile(0.5)(query_duration_ms)) AS p50_ms,
    round(quantile(0.99)(query_duration_ms)) AS p99_ms,
    formatReadableSize(avg(read_bytes)) AS avg_read,
    round(avg(read_rows) / greatest(avg(result_rows), 1))
        AS read_amp,
    formatReadableSize(max(memory_usage)) AS peak_mem,
    any(query) AS sample
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 6 HOUR
  AND is_initial_query
GROUP BY fingerprint
ORDER BY sum(query_duration_ms) DESC
LIMIT 20
FORMAT Vertical;

Sort by total duration, not by the single worst run. A 40-second report that runs twice a day is a curiosity; a 300 ms query that runs eight thousand times an hour is your actual problem, and it will never show up at the top of a “slowest queries” list.

The column that earns its keep is read_amp. When a query reads 400 million rows to return twelve, the index is not doing its job and no amount of hardware will rescue it. We go deeper on the log’s less obvious columns in our guide to slow query troubleshooting with system.query_log.

2. Read the ProfileEvents map instead of guessing

Every row in query_log carries a ProfileEvents map holding a few hundred counters. This is where ClickHouse performance troubleshooting stops being folklore: rather than arguing about whether a query is I/O-bound or CPU-bound, you read it off.

-- The 25 largest counters for one specific query
SELECT e.1 AS event, e.2 AS value
FROM system.query_log
ARRAY JOIN arrayReverseSort(x -> x.2, arrayZip(
    mapKeys(ProfileEvents), mapValues(ProfileEvents))) AS e
WHERE query_id = 'PASTE-QUERY-ID-HERE'
  AND type = 'QueryFinish'
LIMIT 25;

Four ratios do most of the work here. OSCPUVirtualTimeMicroseconds approaching query_duration_ms × 1000 × max_threads means you really are CPU-bound. A large DiskReadElapsedMicroseconds points at storage, not the plan. MarkCacheMisses dwarfing MarkCacheHits means the mark cache is too small for the working set. And SelectedMarks against SelectedRanges tells you whether the granules you read were at least contiguous.

If the storage counters dominate, stop reading SQL and go look at the device. Our notes on ClickHouse IOPS troubleshooting cover the queue-depth and readahead side of that story.

3. Prove the index works with EXPLAIN indexes = 1

Most regressions we are called in for live in one place: granule selection. EXPLAIN indexes = 1 is the cheapest, highest-yield probe in the whole toolkit, because it prints how many parts and granules each index step actually eliminated.

EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE tenant_id = 4711
  AND event_time >= '2026-08-01 00:00:00'
  AND event_time <  '2026-08-08 00:00:00';

Read the output as a funnel, top to bottom:

ReadFromMergeTree (default.events)
Indexes:
  MinMax
    Keys:     event_date
    Parts:    214/1180
    Granules: 61432/402118
  Partition
    Keys:     toYYYYMM(event_date)
    Parts:    214/214
    Granules: 61432/61432
  PrimaryKey
    Keys:     tenant_id, event_time
    Parts:    96/214
    Granules: 812/61432    <-- this index works
  Skip
    Name:        idx_url_bloom
    Description: bloom_filter GRANULARITY 4
    Parts:       96/96
    Granules:    812/812   <-- eliminated nothing

Two readings matter. First, PrimaryKey cutting 61,432 granules down to 812 is a healthy sort key for this predicate. Second, the bloom filter eliminated exactly zero granules — it is pure write-time and merge-time cost, and it should be dropped. We see dead skip indexes on roughly a third of the clusters we audit.

If PrimaryKey shows no reduction at all, your predicate is not reaching the index. The usual culprits are a filter wrapped in a non-monotonic function, a type mismatch between the column and the literal, or a leading key column that nobody filters on. Fixing that is a schema conversation rather than a ClickHouse performance troubleshooting one, and the trade-offs are laid out in our MergeTree optimization guide.

Two companions earn a place in every ClickHouse performance troubleshooting session: EXPLAIN ESTIMATE gives you the part, row and mark estimates as a compact table, and EXPLAIN PLAN actions = 1 shows whether predicate pushdown survived the analyzer. The full grammar lives in the EXPLAIN reference.

4. Find the stuck operator in the pipeline

When the plan is fine and the read ratios are healthy, the time is being burned inside the execution graph. EXPLAIN PIPELINE shows you the shape; system.processors_profile_log shows you where it stalled. Together they are the heart of pipeline-level ClickHouse performance troubleshooting.

SET log_processors_profiles = 1;

-- run the query, keep its query_id, then:
SYSTEM FLUSH LOGS;

SELECT
    name,
    count() AS instances,
    sum(elapsed_us) AS busy_us,
    sum(input_wait_elapsed_us) AS wait_upstream_us,
    sum(output_wait_elapsed_us) AS wait_downstream_us,
    sum(output_rows) AS rows_out
FROM system.processors_profile_log
WHERE query_id = 'PASTE-QUERY-ID-HERE'
GROUP BY name
ORDER BY busy_us DESC
LIMIT 15;

The pattern to look for is asymmetry. Sixteen AggregatingTransform instances where one accounts for 90% of busy_us is a skew problem in the GROUP BY key, not a throughput problem. High wait_upstream_us across the board means the bottleneck is further down, usually in MergeTreeSelect. High wait_downstream_us means something late in the graph — often sorting or the output format — is applying backpressure.

A single-threaded stage in the middle of an otherwise parallel pipeline is the classic silent killer, and FINAL is the usual reason. Our walkthrough of EXPLAIN PIPELINE bottlenecks has the full pattern catalogue, including the shapes that show up most often in ClickHouse performance troubleshooting work.

5. Watch one query breathe, second by second

Aggregate numbers hide shape. A query that averages 6 GB of memory might sit flat at 6 GB for two minutes, or spike to 6 GB for 800 milliseconds — and those two need completely different fixes. system.query_metric_log samples per-query metrics once a second, which makes it the most underused table in ClickHouse performance troubleshooting.

SELECT
    event_time,
    formatReadableSize(memory_usage) AS mem,
    ProfileEvent_SelectedRows AS rows_read,
    ProfileEvent_OSCPUVirtualTimeMicroseconds / 1e6
        AS cpu_seconds
FROM system.query_metric_log
WHERE query_id = 'PASTE-QUERY-ID-HERE'
ORDER BY event_time;

For ClickHouse performance troubleshooting, shape is the signal. A sawtooth in mem means external aggregation is spilling and reloading. A flat line at exactly your limit means the query was clamped, not satisfied. A staircase means a merge join accumulating build-side blocks.

6. Turn CPU time into a flamegraph

Once you have established that a query is CPU-bound, the next question is which function is eating the cycles. The built-in sampling query profiler answers that without attaching perf to a production process, which makes it safe for ClickHouse performance troubleshooting on a live cluster.

SET allow_introspection_functions = 1,
    query_profiler_cpu_time_period_ns  = 1000000, -- 1 ms
    query_profiler_real_time_period_ns = 0;

-- run the query, then:
SYSTEM FLUSH LOGS;

SELECT
    arrayStringConcat(arrayReverse(arrayMap(
        a -> demangle(addressToSymbol(a)), trace)), ';')
        AS stack,
    count() AS samples
FROM system.trace_log
WHERE query_id = 'PASTE-QUERY-ID-HERE'
  AND trace_type = 'CPU'
GROUP BY stack
ORDER BY samples DESC
INTO OUTFILE 'cpu.folded'
FORMAT TabSeparatedRaw;

Feed cpu.folded to flamegraph.pl and you get an SVG in about two seconds. What you are looking for is a wide plateau. Decompression frames wide open means the codec is wrong for the column. String comparison frames wide open usually means a LowCardinality that should exist and does not. Hash table frames wide open means the GROUP BY cardinality is higher than anyone assumed.

Debug symbols must be installed or you will get hex addresses instead of names, which is the single most common false start in flamegraph-based ClickHouse performance troubleshooting. The end-to-end recipe, including the container case, is in our post on diagnosing high CPU usage with flamegraphs.

7. Memory ceilings: read the error, then bound the query

Error code 241 is the most common hard failure in ClickHouse performance troubleshooting, and it is also the most commonly mis-fixed — usually by raising max_memory_usage until the server gets OOM-killed instead.

SELECT
    event_time,
    query_duration_ms,
    formatReadableSize(memory_usage) AS peak,
    left(exception, 140) AS why,
    left(query, 120) AS q
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND exception_code = 241
  AND event_time > now() - INTERVAL 1 DAY
ORDER BY event_time DESC
LIMIT 20;

Read the exception text, not just the code. It names the allocator that failed, which tells you whether the memory went into an aggregate state, a join build side, or a sort buffer. Then bound that specific operator rather than the whole query:

-- spill instead of dying
SET max_bytes_before_external_group_by = 8000000000,
    max_bytes_before_external_sort     = 8000000000,
    join_algorithm                     = 'grace_hash',
    max_threads                        = 16;

Recent versions also accept ratio-based variants such as max_bytes_ratio_before_external_group_by, which scale with the server instead of hard-coding bytes — much friendlier when the same profile is deployed on mixed instance sizes. Lowering max_threads feels counter-intuitive but often helps, because each thread carries its own hash table.

A per-user quota beats a per-query limit for protecting a shared cluster. The full decision tree, including when spilling to disk is the wrong answer, is in our guide to fixing MEMORY_LIMIT_EXCEEDED.

8. Part explosion and merge backpressure

This is the one ClickHouse performance troubleshooting case where the fix almost always belongs in the application rather than the database: it looks like a read problem and it is really a write problem. Small, frequent inserts create one part per insert per partition, merges fall behind, and every SELECT starts paying to open hundreds of files. Then parts_to_delay_insert starts throttling writers and parts_to_throw_insert starts rejecting them with error 252.

-- which partitions are fragmenting?
SELECT
    database,
    table,
    partition_id,
    count() AS active_parts,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    round(avg(rows)) AS avg_rows_per_part
FROM system.parts
WHERE active
GROUP BY database, table, partition_id
ORDER BY active_parts DESC
LIMIT 15;

-- is the merge pool keeping up?
SELECT metric, value
FROM system.metrics
WHERE metric IN (
    'BackgroundMergesAndMutationsPoolTask',
    'DelayedInserts',
    'PartMutation');

-- what is running right now?
SELECT
    table,
    elapsed,
    progress,
    num_parts,
    formatReadableSize(total_size_bytes_compressed) AS sz
FROM system.merges
ORDER BY elapsed DESC;

An avg_rows_per_part in the low thousands is the smoking gun. Batch at the writer before you touch server settings — async_insert = 1 with wait_for_async_insert = 1 gives you server-side batching without giving up delivery guarantees. Raising the merge pool first just moves the wall further away.

Long-running mutations deserve their own look, because ALTER ... UPDATE rewrites whole parts; see our notes on merge and mutation performance and the column reference in the system.parts guide.

9. Prove the fix with cold caches

The last step of any ClickHouse performance troubleshooting cycle is the one people skip, and it is the reason so much tuning folklore survives. A second run of the same query is always faster, so measuring it proves nothing. Drop the caches, then measure both paths.

SYSTEM DROP MARK CACHE;
SYSTEM DROP UNCOMPRESSED CACHE;
SYSTEM DROP QUERY CACHE;
SYSTEM DROP FILESYSTEM CACHE;   -- object storage

# 200 iterations, 4 concurrent, before and after
clickhouse-benchmark -c 4 -i 200 --query "
  SELECT tenant_id, count()
  FROM events
  WHERE event_date >= today() - 7
  GROUP BY tenant_id" --json bench-after.json

Then confirm the improvement in the log rather than in your terminal, because that is where your alerting lives:

SELECT
    toStartOfHour(event_time) AS hour,
    round(quantile(0.99)(query_duration_ms)) AS p99_ms,
    round(avg(read_rows)) AS avg_rows,
    count() AS runs
FROM system.query_log
WHERE type = 'QueryFinish'
  AND normalized_query_hash = 4207834701285403136
  AND event_time > now() - INTERVAL 2 DAY
GROUP BY hour
ORDER BY hour;

One variable per change. We have watched teams apply six settings at once, see a 30% improvement, and never learn which one mattered — which means they cannot reproduce it on the next cluster.

What 26.7 changed for ClickHouse performance troubleshooting

Two changes in the 26.x line belong in your ClickHouse performance troubleshooting notes, because both can look like a query regression.

The older XML approach to workload scheduling has been retired in favour of CREATE RESOURCE and CREATE WORKLOAD statements. If you were isolating a noisy tenant through server config, that isolation quietly stops applying after the upgrade, and the symptom looks exactly like a query regression. Worth checking first.

S3 access initiated from user SQL also no longer falls back to the server’s own cloud credentials by default. That surfaces as an exception rather than a slowdown, but it lands in the same triage queue, so it is useful to recognise on sight.

On the more cheerful side, the query condition cache introduced earlier in the 25.x line keeps paying off: use_query_condition_cache lets repeated filters skip granules they have already rejected, and system.query_condition_cache shows you whether it is being used at all. The analyzer has also been the default for long enough that enable_analyzer = 0 should now be a deliberate, documented decision rather than a leftover. Release notes for each version are published in the ClickHouse changelog on GitHub, and we summarise the operational impact in our ClickHouse upgrade guide.

The one-page ClickHouse performance troubleshooting runbook

Print this, or paste it into the top of your incident channel. It is the compressed form of everything above, and it is what we hand to on-call engineers who need to do ClickHouse performance troubleshooting at 3 a.m. without reading a blog post first.

1. Flush and look. SYSTEM FLUSH LOGS, then rank fingerprints in system.query_log by total duration.
2. Compute read amplification. read_rows / result_rows. Above a few hundred, suspect the index before anything else.
3. Read the counters. Pull the ProfileEvents map for one bad query_id and decide: CPU, disk, network, or memory.
4. Probe the storage layer. EXPLAIN indexes = 1. Confirm each index step actually eliminates granules.
5. Probe the pipeline. system.processors_profile_log sorted by elapsed_us. Look for asymmetry, not totals.
6. Check the write side. Active parts per partition, system.merges, and DelayedInserts.
7. Change one thing. Drop caches, re-benchmark, and verify in query_log rather than in your terminal.

Where ClickHouse performance troubleshooting usually ends

In our experience, the majority of ClickHouse performance troubleshooting engagements end at step 4 — a sort key chosen for how the data arrives rather than how it is queried, or a skip index that has never eliminated a single granule. The second-largest bucket ends at step 6, with tiny inserts and a merge pool that never catches up. Genuine CPU-bound work that needs a code-level fix is rare, and hardware is almost never the answer people expect it to be.

The workflow matters more than any individual setting. Measure, isolate, attribute, fix, re-measure — and resist the urge to change three things because you are in a hurry. If you would like a second pair of eyes on a cluster, ChistaDATA does exactly this work; our query performance tuning and ingestion performance guides are good next reads in the meantime.

ClickHouse performance troubleshooting FAQ

Where should ClickHouse performance troubleshooting start?

In system.query_log, ranked by total duration rather than by the single slowest run. Every other ClickHouse performance troubleshooting step — EXPLAIN, profilers, part counts — is a follow-up probe for a hypothesis that log gave you.

Do I need external tools?

Almost never. The server records what you need, and nine out of ten ClickHouse performance troubleshooting sessions we run use nothing but clickhouse-client. External monitoring is for trend lines and alerting, not for root cause.

How do I tell a CPU problem from an I/O problem?

Compare OSCPUVirtualTimeMicroseconds with DiskReadElapsedMicroseconds in the same ProfileEvents map. Whichever dominates decides which half of this article you read next.

Is EXPLAIN enough on its own?

No. EXPLAIN tells you what the server intends to do; system.processors_profile_log and system.trace_log tell you what it actually did. Plans are cheap to read and easy to over-trust.

What is the most common mistake?

Changing several settings at once. The second most common is raising a memory limit instead of bounding the operator that consumed it. Both make the next round of ClickHouse performance troubleshooting harder, because nobody can say which change helped.

Does this apply to ClickHouse Cloud and managed deployments?

Yes, with one caveat: on object storage the read stage behaves differently, so watch the filesystem cache hit ratio and S3GetObject counts before you conclude that a query plan regressed. The rest of the ClickHouse performance troubleshooting sequence is unchanged.