ClickHouse Performance Observability: 9 Proven Signals We Trust in 26.9

The idea

What ClickHouse performance observability means in 26.9

ClickHouse monitoring tells you that something is wrong: p95 is up, memory is high, a replica is behind. ClickHouse performance observability tells you why. It means you can follow one query from parse to result and point at the stage that cost the time, the memory or the I/O, without re-running it on production.

ClickHouse makes this possible because it records its own work in system tables. Every query, every thread, every processor in the pipeline, every part written or merged, and a sampled stack trace every few milliseconds can be written to MergeTree tables in the system database. You query them with the same SQL you use for your data. Nothing has to be installed, and the history survives the incident.

The trick is knowing which table answers which question, and in what order to open them. Figure 1 is the map we work from. The blue row is only visible while a query runs. The orange row is per-query history, where most incidents are solved. Green covers storage and grey covers the whole server.

Before the incident

Turn the evidence on: the ClickHouse observability config we deploy

The default config.xml already writes query_log and several other logs. What it rarely has is retention. With no TTL, system logs grow until somebody notices the disk. We drop the file below into config.d/ on every server we look after. Each log is partitioned by day so the TTL removes whole parts instead of running a mutation.

<!-- /etc/clickhouse-server/config.d/observability.xml
     ClickHouse performance observability config, tested on ClickHouse 26.9.2.8. Requires a server restart.
     If a log's schema or engine changes, ClickHouse renames the old table
     (for example query_log_0) and creates a new one: history is kept, not lost. -->
<clickhouse>
    <!-- Per-query history: keep 30 days, one partition per day -->
    <query_log>
        <database>system</database>
        <table>query_log</table>
        <partition_by>event_date</partition_by>
        <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_log>

    <!-- Sampled stacks: the biggest log, 7 days covers an incident review -->
    <trace_log>
        <database>system</database>
        <table>trace_log</table>
        <partition_by>event_date</partition_by>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </trace_log>

    <!-- Per-query memory and ProfileEvents over time -->
    <query_metric_log>
        <database>system</database>
        <table>query_metric_log</table>
        <partition_by>event_date</partition_by>
        <ttl>event_date + INTERVAL 14 DAY DELETE</ttl>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_metric_log>

    <!-- Per-processor timings; filled only when log_processors_profiles = 1 -->
    <processors_profile_log>
        <database>system</database>
        <table>processors_profile_log</table>
        <partition_by>event_date</partition_by>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </processors_profile_log>

    <!-- Parts written, merged, mutated and removed -->
    <part_log>
        <database>system</database>
        <table>part_log</table>
        <partition_by>event_date</partition_by>
        <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </part_log>

    <!-- One row per second of system.metrics and system.events -->
    <metric_log>
        <database>system</database>
        <table>metric_log</table>
        <collect_interval_milliseconds>1000</collect_interval_milliseconds>
        <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </metric_log>

    <!-- Prometheus scrape endpoint; 9363 is the documented default port -->
    <prometheus>
        <port>9363</port>
        <endpoint>/metrics</endpoint>
        <metrics>true</metrics>
        <asynchronous_metrics>true</asynchronous_metrics>
        <events>true</events>
        <errors>true</errors>
    </prometheus>
</clickhouse>

After the restart, check that the TTLs were applied before you trust them. On our lab server the six logs above showed their TTL; asynchronous_metric_log, query_thread_log and text_log showed none, which is the reminder to add them too.

-- ClickHouse monitoring: verify retention on every system log that has one
SELECT
    name,
    extract(engine_full, 'TTL [^S]*') AS ttl_clause
FROM system.tables
WHERE database = 'system'
  AND name LIKE '%\_log'
ORDER BY name;

Two logs are controlled by query settings, not server config. processors_profile_log stays empty unless log_processors_profiles is on, and query_thread_log needs log_query_threads. The sampling profiler is controlled by query_profiler_real_time_period_ns and query_profiler_cpu_time_period_ns. The documented default is one sample per second, as described in the sampling query profiler guide.

<!-- users.d/observability.xml: ClickHouse performance observability settings for application users -->
<clickhouse>
    <profiles>
        <default>
            <log_queries>1</log_queries>
            <!-- per-processor timings: cheap, and they separate CPU-bound from I/O-bound -->
            <log_processors_profiles>1</log_processors_profiles>
            <!-- one row per thread per query: switch on per session when needed -->
            <log_query_threads>0</log_query_threads>
            <!-- tag every query with the service that sent it -->
            <log_comment></log_comment>
        </default>
    </profiles>
</clickhouse>

Raise the profiler rate for one query, never for a whole server, when you need detail. That is what we did for the failing query in this guide: 10 ms timers, set inline, with a log_comment so every log row is easy to find later.

-- ClickHouse performance lab: the failing query, profiled at 100 samples per second per thread
SELECT
    country,
    device,
    quantileTDigest(0.99)(latency_ms) AS p99,
    uniqExact(user_id)                AS users
FROM lab.events
WHERE event_date >= '2026-05-01'
GROUP BY country, device
ORDER BY p99 DESC
LIMIT 10
SETTINGS
    query_profiler_cpu_time_period_ns  = 10000000,   -- 10 ms of CPU time
    query_profiler_real_time_period_ns = 10000000,   -- 10 ms of wall time
    log_comment = 'obs:heavy';                       -- searchable in every log

Signal 1 · live

system.processes: ClickHouse performance while the query is still running

When a page arrives for a query that is slow right now, system.processes is the only table with an answer. It holds one row per running query, with elapsed time, rows and bytes read so far, current and peak memory, the thread IDs working on it and a live ProfileEvents map. The columns are listed in the system.processes reference.

-- ClickHouse performance, live: running queries, longest first, with the counters that explain them
SELECT
    query_id,
    user,
    round(elapsed, 2)                                    AS elapsed_s,
    formatReadableQuantity(read_rows)                    AS rows_read,
    formatReadableSize(read_bytes)                       AS bytes_read,
    formatReadableSize(memory_usage)                     AS mem_now,
    formatReadableSize(peak_memory_usage)                AS mem_peak,
    length(thread_ids)                                   AS threads,
    ProfileEvents['SelectedMarks']                       AS marks_selected,  -- granules planned
    round(ProfileEvents['OSCPUVirtualTimeMicroseconds'] / 1e6, 2) AS cpu_s,
    is_cancelled,
    substring(query, 1, 50)                              AS query_head
FROM system.processes
WHERE is_initial_query                       -- skip shard sub-queries on clusters
  AND query NOT LIKE '%system.processes%'    -- skip this query itself
ORDER BY elapsed DESC
FORMAT Vertical;

Four seconds into a full-table GROUP BY on our lab node it printed this:

query_id:       obs_live_3
elapsed_s:      4.05
rows_read:      4.85 million
bytes_read:     37.00 MiB
mem_now:        63.37 MiB
mem_peak:       69.26 MiB
threads:        28
marks_selected: 122071
cpu_s:          0.93

The number we read first is marks_selected. It is fixed once planning ends, and 122,071 is every granule in the table. Index analysis skipped nothing, so this query will read the whole table whatever else happens. Rows read divided by elapsed time gives the scan rate, and from that you can estimate how long the query will run.

If it has to stop, KILL QUERY with SYNC waits until the query has actually stopped and released its memory. Always check the target first, because the WHERE clause can match more than one query.

-- 1. Verify the target: exactly one row expected
SELECT query_id, user, elapsed, substring(query, 1, 80)
FROM system.processes
WHERE query_id = 'obs_live_3';

-- 2. Cancel and wait for the threads to finish
KILL QUERY WHERE query_id = 'obs_live_3' SYNC;
-- returned: kill_status = finished

-- 3. Validate: the row is gone and the cancellation was counted
SELECT count() FROM system.processes WHERE query_id = 'obs_live_3';      -- 0
SELECT value FROM system.errors WHERE name = 'QUERY_WAS_CANCELLED';

Signal 3 · the plan

EXPLAIN ANALYZE: see where ClickHouse performance went, step by step

Since 26.7, EXPLAIN ANALYZE runs the query, throws the result away and prints the plan with what actually happened at each step: rows and bytes in and out, time and its share of the total, and average and maximum parallelism. The EXPLAIN reference describes every variant. Because it really executes, run it on a replica that is not serving production traffic.

We used it on a smaller query: the error rate for one user over one month. We turned off the query condition cache so that a repeat run could not reuse the filter results of an earlier one.

-- ClickHouse performance before: user_id is not in the sort key (tenant_id, event_time)
EXPLAIN ANALYZE
SELECT countIf(http_status >= 500) / count() AS error_rate
FROM lab.events
WHERE user_id = 4218139
  AND event_date >= '2026-06-01'
SETTINGS use_query_condition_cache = 0;   -- measure the scan, not the cache

Query summary:
  Time:        360.77 ms (planning 3.45 ms · execution 357.32 ms)
  Read:        165.03 million rows, 660.27 MB (461.86 million rows/s., 1.85 GB/s.)
  Peak memory: 78.06 KiB
...
         └──ReadFromMergeTree (lab.events)
               Parts: 1 | Granules: 20146
               Prewhere filter column:  event_date >= '2026-06-01' AND user_id = 4218139
               Indexes:
                 Min-Max      Granules: 20146/122071
                 Partition    Granules: 20146/20146
                 PrimaryKey
                   Condition: true
                   Granules: 20146/20146
               I/O: rows 0 → 13 · 0 B → 26 B
                 time 356.32 ms (99.7%) · parallelism 1.98/2

The plan explains the problem completely. The partition and min-max indexes kept one month, then the primary key line reads Condition: true. It had nothing to work with and kept all 20,146 granules. ReadFromMergeTree took 99.7% of the time, with both cores busy. In the application, every user belongs to a tenant, and the tenant is the first column of the sort key.

-- ClickHouse performance after: the leading sort-key column lets the primary index skip
EXPLAIN ANALYZE
SELECT countIf(http_status >= 500) / count() AS error_rate
FROM lab.events
WHERE tenant_id = 42                 -- known to the application
  AND user_id = 4218139
  AND event_date >= '2026-06-01'
SETTINGS use_query_condition_cache = 0;

Query summary:
  Time:        6.13 ms (planning 2.63 ms · execution 3.50 ms)
  Read:        344.06 thousand rows, 1.60 MB (98.24 million rows/s., 455.99 MB/s.)
  Peak memory: 54.38 KiB
...
                 PrimaryKey
                   Keys:
                     tenant_id
                   Condition: (tenant_id in [42, 42])
                   Granules: 42/20146
                   Search Algorithm: binary search

The figure shows our first run, at 386.97 ms against 13.32 ms. The second run, printed above, gave 360.77 ms against 6.13 ms. Timings move with cache state and noise from other processes. Granule counts do not. That is why we compare plans by granules and rows, and treat milliseconds as supporting evidence.

You do not need to execute anything to see the index line. EXPLAIN indexes = 1 prints the same Min-Max, Partition and PrimaryKey sections from planning alone. It is safe on a busy primary and should be the first thing you check when marks_selected in system.processes looks too high.

Signal 5 · internals

system.trace_log: which ClickHouse function used the CPU and the memory

The sampling profiler writes a stack trace to system.trace_log on every timer tick, for every thread of the query. Each row has a trace_type: Real for wall-clock samples, CPU for on-CPU samples, Memory for allocations crossing memory_profiler_step, MemoryPeak for new peaks, and JemallocSample for the jemalloc profiler. The trace_log reference lists all of them.

At 10 ms the failing query produced 55,529 Real, 4,803 CPU, 680 Memory and 678 MemoryPeak samples. The addresses are turned into names by the introspection functions, which need allow_introspection_functions. Grant that to the people who run incident reviews, not to application users.

-- ClickHouse performance observability: share of on-CPU samples by leaf function
SELECT
    substring(demangle(addressToSymbol(trace[1])), 1, 80)  AS leaf_function,  -- trace[1] = innermost frame
    count()                                                AS samples,
    round(100 * samples / sum(samples) OVER (), 1)         AS pct
FROM system.trace_log
WHERE query_id = 'obs_heavy_1'
  AND trace_type = 'CPU'
  AND event_date = today()
GROUP BY leaf_function
ORDER BY samples DESC
LIMIT 8
SETTINGS allow_introspection_functions = 1;

36.4% of CPU samples landed in a single function: the batch insert of AggregateFunctionUniq over AggregateFunctionUniqExactData, which is uniqExact adding user IDs to its hash sets. The t-digest for the p99 came next with radix sort and compress at 8.5% and 6.6%. After that came key serialisation for the two-level hash table that groups by two string columns.

CPU tells you what was busy. For a memory failure, you need to know who allocated. Memory samples record the stack at allocation time, but the top frames always belong to the allocator. This query skips those frames and credits each sample to the first frame from the engine itself:

-- ClickHouse performance observability: credit each Memory sample to the first non-allocator frame
WITH arrayMap(x -> demangle(addressToSymbol(x)), trace) AS frames
SELECT
    substring(arrayFirst(f -> f NOT ILIKE '%MemoryTracker%'
                          AND f NOT ILIKE '%Allocator%'
                          AND f NOT ILIKE '%operator new%'
                          AND f NOT ILIKE '%PODArray%'
                          AND f != '', frames), 1, 70)   AS first_engine_frame,
    count()                                             AS allocations,
    formatReadableSize(sum(size))                       AS bytes
FROM system.trace_log
WHERE query_id = 'obs_heavy_1'
  AND trace_type = 'Memory'
  AND size > 0                    -- allocations only; frees are negative
  AND event_date = today()
GROUP BY first_engine_frame
ORDER BY sum(size) DESC
LIMIT 5
SETTINGS allow_introspection_functions = 1;

first_engine_frame                                   allocations   bytes
DB::IAggregateFunctionHelper<DB::AggregateFunctionUniq<...         490   1.73 GiB
DB::AggregatingTransform::work()                                    188   680.31 MiB

CPU and memory pointed at the same function, so we changed it. uniqExact keeps every distinct value in a hash set per group. uniq keeps an adaptive sample with a bounded size. Its result is approximate, so this is a product decision as well as an engineering one. The dashboard showed user counts rounded to thousands, so approximate was acceptable. With the change, the query finished in 85.1 seconds and peaked at 175.85 MiB instead of failing at 2.67 GiB.

For a visual, fold whole stacks into the collapsed format that flame graph renderers read, one line per unique stack with a sample count:

-- ClickHouse performance flame graph: collapsed stacks, root first, frames joined by ';'
SELECT
    arrayStringConcat(
        arrayReverse(arrayMap(x -> demangle(addressToSymbol(x)), trace)),
        ';')                                   AS stack,
    count()                                    AS samples
FROM system.trace_log
WHERE query_id = 'obs_heavy_1'
  AND trace_type = 'CPU'
  AND event_date = today()
GROUP BY trace
ORDER BY samples DESC
SETTINGS allow_introspection_functions = 1
INTO OUTFILE 'obs_heavy_1.folded' FORMAT TSV;   -- clickhouse-client writes it locally

For the failing query this wrote 899 unique stacks covering all 4,803 CPU samples. Any renderer that reads the collapsed-stack format turns that file into an interactive flame graph. The widest tower in ours was the uniqExact path described above.

Signal 7 · storage

system.part_log: ClickHouse insert performance and merge pressure

Not every ClickHouse performance problem is a SELECT, and not all ClickHouse monitoring is about queries. Small, frequent inserts create many parts, and background merges then rewrite those rows again and again. system.part_log records each event with its type (NewPart, MergePartsStart, MergeParts, MutatePart, MovePart, RemovePart and others), duration, rows, merge reason and peak memory. The event types are documented in the part_log reference.

To show the pattern, we ran 40 inserts of 5,000 rows each into a fresh MergeTree table, then grouped part_log by event:

-- ClickHouse monitoring for inserts: part and merge activity for one table today
SELECT
    event_type,
    merge_reason,
    count()                                      AS events,
    round(avg(duration_ms), 1)                   AS avg_ms,
    max(duration_ms)                             AS max_ms,
    formatReadableQuantity(sum(rows))            AS rows_touched,
    formatReadableSize(max(peak_memory_usage))   AS peak_mem
FROM system.part_log
WHERE database = 'lab'
  AND table = 'ingest_demo'
  AND event_date = today()
GROUP BY event_type, merge_reason
ORDER BY events DESC;

200,000 rows were inserted, and merges rewrote 740,000 rows: 3.7 times the inserted volume, and this is a table with no queries at all. At production insert rates the same ratio becomes merge CPU and disk writes that compete with SELECTs. When the NewPart count per minute climbs, batch on the client side or use asynchronous inserts. Watch MaxPartCountForPartition in system.asynchronous_metrics; it is exported to Prometheus as well.

Signal 9 · alerting

The Prometheus endpoint: ClickHouse performance monitoring outside the server

System tables are for investigation. ClickHouse monitoring alerts belong outside the server, so they still work when the server is unwell. The <prometheus> block in our config exposes metrics, events, asynchronous metrics and error counters on port 9363, as described in the Prometheus metrics documentation. On our lab node one scrape returned 9,488 lines.

# ClickHouse monitoring: one scrape, filtered to the series we alert on
curl -s http://127.0.0.1:9363/metrics | grep -E '^ClickHouse(Metrics_Query|Metrics_MemoryTracking|ErrorMetric_MEMORY_LIMIT_EXCEEDED|AsyncMetrics_MaxPartCountForPartition) '

# ClickHouseMetrics_Query 0
# ClickHouseMetrics_MemoryTracking 513855163
# ClickHouseErrorMetric_MEMORY_LIMIT_EXCEEDED 3
# ClickHouseAsyncMetrics_MaxPartCountForPartition 6

These are the ClickHouse monitoring rules we start every customer with. The thresholds are starting points to tune against your own baseline, not universal numbers.

# prometheus/rules/clickhouse.yml: ClickHouse monitoring alerts (starting thresholds; tune per cluster)
groups:
  - name: clickhouse-performance
    rules:
      # Any query hitting the memory limit is a page: work was thrown away
      - alert: ClickHouseMemoryLimitExceeded
        expr: increase(ClickHouseErrorMetric_MEMORY_LIMIT_EXCEEDED[10m]) > 0
        labels: { severity: page }

      # Tracked memory above 80% of the server limit for 5 minutes
      - alert: ClickHouseMemoryPressure
        expr: ClickHouseMetrics_MemoryTracking / ${CH_MAX_SERVER_MEMORY_BYTES} > 0.8
        for: 5m
        labels: { severity: ticket }

      # Parts piling up in one partition: inserts outrunning merges
      - alert: ClickHouseTooManyParts
        expr: ClickHouseAsyncMetrics_MaxPartCountForPartition > 300
        for: 10m
        labels: { severity: ticket }

      # Replica lag; the same signal /replicas_status turns into HTTP 503
      - alert: ClickHouseReplicaDelay
        expr: ClickHouseAsyncMetrics_ReplicasMaxAbsoluteDelay > 300
        for: 5m
        labels: { severity: ticket }

Error counters reset when the server restarts, so always use increase() or rate(), never the raw value. We saw the reset ourselves: the counter read 3 before a config restart and 0 after it. For load balancers, /ping answers when the server is up, and /replicas_status returns 503 when a replica is too far behind. The built-in /dashboard page plots the main metrics without any extra setup.

Honest notes

What did not work, and what caught us out

Two more lessons from the lab are worth keeping. Changing a system log’s configuration renamed the existing tables to query_log_0, trace_log_0 and so on. Incident queries written against system.query_log silently missed the older rows until we pointed them at the renamed tables. If you run a retention change during an incident, check system.tables for numbered copies.

Also, the fix in this guide, uniq instead of uniqExact, made a different query slower in our ClickHouse Cloud cost measurements. There, each group had few distinct users and the exact hash set was cheap. Here, each country and device group had millions of distinct users. The profile decides, not the rule of thumb. That is the whole case for ClickHouse performance observability.

FAQ

ClickHouse performance observability: common questions

Which system table should I check first for a slow ClickHouse query?

If the query is still running, check system.processes and look at ProfileEvents['SelectedMarks']. If it has finished, group system.query_log by normalized_query_hash and include ExceptionWhileProcessing rows. Then use EXPLAIN indexes = 1 on the top fingerprint.

How do I find out why a ClickHouse query exceeded the memory limit (Code 241)?

Use system.query_metric_log to see how memory grew during the query. Then group system.trace_log Memory samples by the first non-allocator frame. In our lab that pointed straight at uniqExact, which held 1.73 GiB.

Is EXPLAIN ANALYZE safe to run on production ClickHouse?

It executes the query fully and only throws the result away, so it uses the same ClickHouse performance budget as the query itself. Run it on a replica that is not serving traffic. EXPLAIN indexes = 1 only plans the query and is safe anywhere.

Does the ClickHouse query profiler slow the server down?

At the documented default of one sample per second it is light, and it is the cheapest ClickHouse performance observability you can leave on. At 10 ms our failing query wrote 55,529 Real-time samples by itself. Raise the rate per query with a SETTINGS clause, and keep a TTL on trace_log.

What should I alert on for ClickHouse monitoring?

Start with the increase of ClickHouseErrorMetric_MEMORY_LIMIT_EXCEEDED, tracked memory against the server limit, MaxPartCountForPartition and replica delay. Use the Prometheus endpoint on port 9363 for ClickHouse monitoring, and tune thresholds against your own baseline.