Advanced ClickHouse Troubleshooting on 26.8 LTS: 9 Proven Techniques for Slow Queries, Stuck Merges and Memory Errors

The surface

What 26.8 LTS and 26.9 added to the surface for advanced ClickHouse troubleshooting

The core tables for advanced ClickHouse troubleshooting have been stable for years. What the two latest releases changed is who can read them and how much a session leaves behind, and both changes matter in practice because the slowest part of advanced ClickHouse troubleshooting is often getting the right person access to the right row.

system.user_query_log (26.8). Until now, letting an application team see their own slow queries meant granting SELECT on system.query_log, which shows everyone’s queries, settings and, with the wrong option, query text with literals. The per-user log gives each user their own history and nothing else. We now hand it to product teams on day one of an engagement so they can answer “what did my dashboard run at 09:14” without a ticket to us.

Background queries (26.8). run_query_in_background = 1 lets a long INSERT SELECT, backfill or OPTIMIZE survive the client disconnecting. Operationally this removes a whole class of “the migration died when my laptop slept” tickets; diagnostically it means the query keeps a query_id you can watch in system.processes from another session.

system.statements and system.session_query_ids (26.9). The first documents every statement type the server understands, which is a small thing until you are checking whether a customer’s tooling emits syntax the LTS build accepts. The second records the query ids a session ran, so you no longer need the client to set query_id by hand to correlate an application request with its rows in the log. On 26.9 we also read min, max and count straight from column statistics (the release call shows 8 to 24 ms dropping to 2 ms), which changes what “this simple query is slow” can mean on a fresh table.

What did not change. enable_parallel_replicas = 0 is still the open-source default on 26.8.11.7; the query condition cache and lazy materialization are on by default; compile_expressions = 1 is the default and, as we measured in the billion-row article, not always a win on small boxes. Read these from the binary you are running, not from memory:

-- Advanced ClickHouse troubleshooting, first minute on any server: what am I running and what is on?
SELECT version();
SELECT name, value, changed
FROM system.settings
WHERE name IN ('max_threads', 'max_memory_usage', 'max_bytes_ratio_before_external_group_by',
               'join_algorithm', 'use_query_condition_cache', 'query_plan_optimize_lazy_materialization',
               'enable_parallel_replicas', 'compile_expressions', 'enable_adaptive_aggregator',
               'materialize_statistics_on_insert', 'use_statistics_for_part_pruning');
SELECT name, value FROM system.merge_tree_settings
WHERE name IN ('old_parts_lifetime', 'parts_to_throw_insert', 'parts_to_delay_insert',
               'max_bytes_to_merge_at_max_space_in_pool', 'auto_statistics_types');

Technique 2

Advanced ClickHouse troubleshooting with the plan: EXPLAIN indexes and EXPLAIN PIPELINE

Once a fingerprint is chosen, advanced ClickHouse troubleshooting moves to the plan, which tells you which stage of the read funnel failed to prune. This is real output from 26.8.11.7 on the lab table; note the Statistics line, which is where 26.8’s automatic column statistics show up, and which was absent on older builds.

EXPLAIN indexes = 1
SELECT count() FROM lab.events
WHERE tenant_id = 42 AND event_time >= '2026-03-01 00:00:00' AND event_time < '2026-03-02 00:00:00';

ReadFromMergeTree (lab.events)
  Indexes:
    MinMax       Condition: true      Parts: 6/6   Granules: 122071/122071
    Partition    Condition: true      Parts: 6/6   Granules: 122071/122071
    Statistics   Keys: event_time, tenant_id       Parts: 1/6   Granules: 20925/122071
    PrimaryKey   Keys: tenant_id, event_time       Parts: 1/1   Granules: 2/20925
                 Search Algorithm: binary search

Three things to check on every plan in advanced ClickHouse troubleshooting. First, whether Partition pruned anything: here it did not, because the partition key is event_date and the predicate is on event_time, and statistics had to do the work instead; on a build without automatic statistics this query would have touched all six parts. Second, whether PrimaryKey shows binary search or generic exclusion search; the latter means the predicate skips the first key column and the index is being used weakly. Third, the final granule count against the total; anything above a few percent of the table for a selective predicate is a schema conversation, not a tuning one.

EXPLAIN PIPELINE answers a different question: how many streams the query runs on and where they merge. If a GROUP BY shows a single stream into MergingAggregated on a 32-core node, you are looking at a query that was forced serial, usually by a subquery, an ORDER BY without LIMIT or a setting like max_threads = 1 inherited from a profile. Check the query’s Settings map in system.query_log before you blame the engine.

-- Which settings did that slow query actually run with? (profiles and users override defaults silently)
SELECT query_id, Settings['max_threads'] AS threads, Settings['max_memory_usage'] AS mem,
       Settings['join_algorithm'] AS join_alg, Settings['optimize_move_to_prewhere'] AS prewhere
FROM system.query_log
WHERE type = 'QueryFinish' AND normalizedQueryHash(query) = {fingerprint:UInt64}
ORDER BY event_time DESC LIMIT 5;

Technique 4

Advanced ClickHouse troubleshooting on the write side: too many parts, stuck merges and the disk-full incident we caused ourselves

Parts are the unit of everything in MergeTree, so most of advanced ClickHouse troubleshooting on the write side is counting them. parts_to_delay_insert and parts_to_throw_insert (defaults 1,000 and 3,000 per partition on current builds; check your system.merge_tree_settings) are the guard rails, and Code: 252. DB::Exception: Too many parts means inserts arrived faster than merges could fold them. The query to run is not “how many parts” but “how many parts per partition, and are merges making progress”:

SELECT database, table, partition, count() AS parts, sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS size, max(modification_time) AS newest_part
FROM system.parts WHERE active
GROUP BY database, table, partition
HAVING parts > 100 ORDER BY parts DESC LIMIT 20;

SELECT database, table, elapsed, round(progress, 2) AS progress, num_parts,
       formatReadableSize(total_size_bytes_compressed) AS size, is_mutation
FROM system.merges ORDER BY elapsed DESC;

Here is the incident, included because articles on advanced ClickHouse troubleshooting rarely show their authors’ own mistakes. Loading the 1,000,000,000-row lab table on 26.8.11.7, the fourteenth of twenty 50-million-row batches failed with Code: 243. DB::Exception: Cannot reserve 26.52 MiB, not enough space. (NOT_ENOUGH_SPACE). The active parts held 10.4 GiB; the disk showed 39 GB used. The gap was outdated parts: merges had already produced larger parts, but the source parts they replaced were still on disk waiting out old_parts_lifetime, which defaults to 480 seconds, and on a box with 30 GB free that was enough to run out.

-- The signal: outdated parts still holding disk
SELECT active, count() AS parts, formatReadableSize(sum(bytes_on_disk)) AS on_disk, sum(rows) AS rows
FROM system.parts WHERE database = 'lab' AND table = 'events'
GROUP BY active;
-- 0 (outdated)  517 parts  15.21 GiB
-- 1 (active)     38 parts  10.41 GiB

-- The change, table-scoped, reversible, no restart
ALTER TABLE lab.events MODIFY SETTING old_parts_lifetime = 30;
-- rollback: ALTER TABLE lab.events RESET SETTING old_parts_lifetime;

Ninety seconds later the outdated parts were gone and the load resumed. The second lesson cost us more: the failed batch had inserted 6.67 million rows before the error, so a naive re-run produced 1,006,671,718 rows. We found it by comparing per-partition row counts against what the generator should produce, dropped the one partition that was over, reloaded exactly its number range and verified the count before running a single measurement. The rule we took from it: after any failed load, reconcile per partition before you resume, because INSERT into MergeTree is atomic per block, not per statement.

Two more things from the same afternoon. OPTIMIZE TABLE ... FINAL returned in 40 seconds and silently left three partitions with ten parts each because there was not enough disk to merge them; the only evidence was in system.parts, not in the client. And merging a partition needs roughly its own size in free space, so on a tight disk merge one partition at a time with OPTIMIZE TABLE ... PARTITION and wait for cleanup between them.

Technique 6

Advanced ClickHouse troubleshooting for mutations that never finish, and the lightweight alternatives on 26.8

Mutations are the part of advanced ClickHouse troubleshooting where patience is usually the wrong instinct. ALTER TABLE ... UPDATE and DELETE rewrite every affected part, and on a large table a mutation that waits behind merges can sit for hours with nothing wrong except the queue. system.mutations tells you which it is: parts_to_do decreasing slowly is a capacity problem, latest_fail_reason set is a correctness problem, and a mutation with a failing reason blocks every mutation behind it on that table.

SELECT database, table, mutation_id, command, create_time, parts_to_do, is_done,
       latest_failed_part, latest_fail_time, latest_fail_reason
FROM system.mutations WHERE NOT is_done ORDER BY create_time;

-- After reading the reason, and only then:
KILL MUTATION WHERE database = 'lab' AND table = 'events' AND mutation_id = '{id}';

On 26.8 the alternatives are worth knowing before the mutation is written, because advanced ClickHouse troubleshooting of mutations is easiest when there is no mutation. Lightweight DELETE FROM ... WHERE marks rows instead of rewriting parts and took 312 ms on a 2-million-row slice in our lab.

Lightweight UPDATE ... SET uses patch parts, and 26.8 moves those to a v2 on-disk format; it is not available on every table, and the error is explicit about why. When we tried it on a table created with defaults the server returned Code: 48 ... Lightweight updates are not supported. Lightweight updates are supported only for ... and named the table settings it needs. Read the full message on your own build rather than a summary, because it is the documentation.

For bulk restatements, ALTER TABLE ... REPLACE PARTITION from a rebuilt table remains the cleanest tool of all: no mutation, no patch parts, one atomic swap and a validation query on either side of it.

Technique 8

Advanced ClickHouse troubleshooting: benchmarks that lie: the query condition cache and lazy materialization

Two defaults on 26.x make careless measurements wrong in opposite directions in advanced ClickHouse troubleshooting. The query condition cache, on by default since 25.3, remembers which granules failed a predicate; the second run of a filter that scanned 1,000,000,000 rows in 1,577 ms on our lab table took 12 ms. Anyone who runs a query three times and reports the best run is reporting the cache, not the engine. Disable it per query when measuring, and remember that 26.8 extends it to ORDER BY ... LIMIT as well.

-- Cold measurement discipline in advanced ClickHouse troubleshooting on 26.x
SYSTEM DROP MARK CACHE;
SYSTEM DROP UNCOMPRESSED CACHE;
SYSTEM DROP QUERY CONDITION CACHE;
SELECT count(), avg(latency_ms) FROM lab.events WHERE user_id = 4200042
SETTINGS use_query_condition_cache = 0, log_queries = 1, query_id = 'cold-run-1';

Lazy materialization cuts the other way in advanced ClickHouse troubleshooting: a SELECT * ... ORDER BY ... LIMIT 10 that takes 3.2 s with the default on takes 11.5 s with it off, so a customer who has the setting disabled in a legacy profile will report a slowness that a fresh install cannot reproduce. When a complaint does not reproduce, diff the two sessions’ Settings maps from system.query_log before anything else. It is the cheapest step in advanced ClickHouse troubleshooting there is, and it closes more tickets than any other single query in this article.

Checklist

The checklist for advanced ClickHouse troubleshooting we leave with every customer

Every query above runs on 26.8.11.7 LTS as written. Names of merge-tree settings and their defaults vary by build; read yours from system.merge_tree_settings before relying on a number in this article.