clickhouse.guru
Runbooks

Runbook: queries, errors and resource use

On-call queries over query_log and system.errors: costly and failing query shapes, regressions after a change, error codes, CPU history, client timeouts, EXPLAIN indexes and low-memory JOINs.

This is the page to open when the server is slow, an error rate alert fires, or someone asks “did that change make things worse?”. Almost everything is a GROUP BY over system.query_log, the table that records every query with its duration, rows read, memory and exception code. The recurring trick is normalized_query_hash: queries that differ only in literal values share one hash, so one hash is one “kind” of query, and you can rank kinds instead of individual runs.

What runs where. system.query_log, system.asynchronous_metric_log and system.part_log exist only on a real server; they do not exist in clickhouse local. The queries on them below were run against stand-in tables with the same column names and types, to check that the SQL is valid and the logic is right, so no real output is shown for them. The error-code, system.errors, EXPLAIN, timeout-setting and JOIN sections ran in clickhouse local 26.4 and show real output. Query logs are per node: wrap system.query_log in clusterAllReplicas('my_cluster', ...) for a cluster and add hostName() to tell nodes apart. If a log is empty, check that log_queries is not disabled for your users and that the server config has a query_log section; retention is bounded by its TTL, so old entries disappear.

Related reading: tracking query and resource trends, EXPLAIN in ClickHouse, JOINs: memory and algorithms.

Contents

Which queries use the most resources?

One heavy query is easy to spot. A cheap query that runs a million times a day is not, yet it can cost more in total. Grouping by normalized_query_hash ranks by total cost. Use it when the server is busy and you want to know which kind of query to optimise first, or which clients deserve a quota or a settings profile.

SELECT
    normalized_query_hash,
    count() AS runs,
    sum(query_duration_ms) / 1000 AS total_seconds,
    round(avg(query_duration_ms)) AS avg_ms,
    sum(read_rows) AS total_rows_read,
    formatReadableSize(sum(read_bytes)) AS total_read,
    formatReadableSize(max(memory_usage)) AS peak_memory,
    any(substring(query, 1, 150)) AS example
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today() - 1
  AND query_kind = 'Select'
GROUP BY normalized_query_hash
ORDER BY total_seconds DESC
LIMIT 20;

Change the ORDER BY to rank by something else: total_rows_read for I/O, max(memory_usage) for memory pressure, runs for frequency.

Reading it:

  • total_seconds is cumulative wall time summed over concurrent queries. It ranks cost, not latency.
  • High total_rows_read with a low avg_ms is a well-behaved query that is simply asked a lot. High avg_ms with few runs is a heavy query that needs an index (primary key, skip index) or a projection.
  • memory_usage is per query, so max is the right aggregate; summing it across queries is meaningless.
  • example is one representative text; filter on normalized_query_hash to fetch other variants.
  • type = 'QueryFinish' counts successful queries only; failures are in the failing-queries section.

Which tables are queried the most?

The tables column of system.query_log is an array of database.table names. ARRAY JOIN turns it into one row per query and table, so a plain GROUP BY ranks usage. Use it to decide which tables deserve tuning, replication or caching, and to find tables nobody reads before you archive them (see tables nobody uses).

SELECT
    tbl AS table_name,
    count() AS queries,
    uniqExact(user) AS distinct_users,
    sum(read_rows) AS rows_read,
    formatReadableSize(sum(read_bytes)) AS bytes_read
FROM system.query_log
ARRAY JOIN tables AS tbl
WHERE type = 'QueryFinish'
  AND query_kind = 'Select'
  AND event_date >= today() - 7
GROUP BY tbl
ORDER BY queries DESC
LIMIT 20;

Reading it:

  • queries ranks popularity; rows_read and bytes_read rank cost. They often disagree: a dimension table is read by everything but cheaply, a big fact table by few but expensively.
  • A query that joins three tables counts once for each, so the sum of queries exceeds the number of queries.
  • read_rows for a joined query belongs to the query as a whole, not to each table, and repeats on every table’s row. Treat it as an upper bound per table.
  • Change query_kind to 'Insert' to rank by write traffic.

How do I find the queries that touched a table?

Before you change a table (new primary key, new partitioning, dropping it) you want to know who uses it and how. The tables array makes this a one-line filter with has().

SELECT count()
FROM system.query_log
WHERE has(tables, 'analytics.events')
  AND type = 'QueryFinish';

Per day, split by kind (Select, Insert, Create, Drop, System, …):

SELECT
    event_date,
    query_kind,
    count() AS queries
FROM system.query_log
WHERE has(tables, 'analytics.events')
  AND type = 'QueryFinish'
GROUP BY event_date, query_kind
ORDER BY event_date, query_kind;

The most frequent query shapes, with one example text each:

SELECT
    normalized_query_hash,
    count() AS runs,
    any(query) AS example
FROM system.query_log
WHERE has(tables, 'analytics.events')
  AND type = 'QueryFinish'
  AND query_kind = 'Select'
  AND event_date >= today() - 5
GROUP BY normalized_query_hash
ORDER BY runs DESC
LIMIT 10;

Query volume over time. Change toStartOfHour to toStartOfMinute or toStartOfDay, or use toStartOfInterval(event_time, INTERVAL 2 HOUR):

SELECT
    toStartOfHour(event_time) AS hour,
    count() AS queries
FROM system.query_log
WHERE has(tables, 'analytics.events')
  AND type = 'QueryFinish'
  AND query_kind = 'Select'
  AND event_date >= today() - 10
GROUP BY hour
ORDER BY hour;

Notes:

  • Always add type = 'QueryFinish', otherwise every query appears twice (QueryStart and QueryFinish).
  • Queries that fail early, such as a syntax error, may have an empty tables array.
  • Queries through a view also list the underlying tables, and System statements such as SYSTEM RELOAD DICTIONARY record tables too.

Did my queries get slower after a change?

You changed a partition key, added a projection, or upgraded the server. The query log keeps duration, rows read and memory for every run, so you can compare two periods query shape by query shape. Set change_time to the moment of the change; only shapes that ran in both periods are compared.

WITH toDateTime('2026-10-01 12:00:00') AS change_time
SELECT
    normalized_query_hash,
    countIf(event_time < change_time) AS runs_before,
    countIf(event_time >= change_time) AS runs_after,
    round(avgIf(query_duration_ms, event_time < change_time)) AS avg_ms_before,
    round(avgIf(query_duration_ms, event_time >= change_time)) AS avg_ms_after,
    round(avgIf(read_rows, event_time < change_time)) AS avg_rows_before,
    round(avgIf(read_rows, event_time >= change_time)) AS avg_rows_after,
    formatReadableSize(avgIf(memory_usage, event_time < change_time)) AS avg_mem_before,
    formatReadableSize(avgIf(memory_usage, event_time >= change_time)) AS avg_mem_after,
    any(substring(query, 1, 100)) AS example
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_kind = 'Select'
  AND has(tables, 'analytics.events')
  AND event_time >= change_time - INTERVAL 7 DAY
GROUP BY normalized_query_hash
HAVING runs_before > 0 AND runs_after > 0
ORDER BY avg_ms_after - avg_ms_before DESC
LIMIT 20;

The same idea with a rolling window answers “what got slower recently?”:

SELECT
    normalized_query_hash,
    countIf(event_time >= now() - INTERVAL 7 DAY) AS runs_this_week,
    countIf(event_time < now() - INTERVAL 7 DAY) AS runs_last_week,
    round(avgIf(query_duration_ms, event_time >= now() - INTERVAL 7 DAY)) AS avg_ms_this_week,
    round(avgIf(query_duration_ms, event_time < now() - INTERVAL 7 DAY)) AS avg_ms_last_week,
    any(substring(query, 1, 100)) AS example
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_kind = 'Select'
  AND event_time >= now() - INTERVAL 14 DAY
GROUP BY normalized_query_hash
HAVING runs_this_week > 5 AND runs_last_week > 5
ORDER BY avg_ms_this_week - avg_ms_last_week DESC
LIMIT 20;

Swap query_duration_ms for read_rows or memory_usage to rank by rows read or memory growth.

Caveats:

  • Averages are noisy. A cold file cache after a restart or a busier cluster slows everything. Check the run counts, and prefer quantile(0.5)(query_duration_ms) over the mean if you have outliers.
  • normalized_query_hash ignores literals, so queries with different time ranges are lumped together. If the range changed between periods, compare read_rows per result row or filter on a stable query text.
  • The log keeps only what its TTL allows, and each node has its own.

Which queries fail most often?

system.query_log records every failed query with an exception_code and message. Grouping by error and by query shape tells you whether you have one broken client or a platform-wide problem; it is also how you see who hits max_memory_usage or max_execution_time. Errors in a time window:

SELECT
    exception_code,
    errorCodeToName(exception_code) AS error_name,
    count() AS failures
FROM system.query_log
WHERE exception_code != 0
  AND event_time >= now() - INTERVAL 1 DAY
GROUP BY exception_code
ORDER BY failures DESC;

Which query shapes produce them:

SELECT
    normalized_query_hash,
    errorCodeToName(exception_code) AS error_name,
    count() AS failures,
    any(substring(query, 1, 120)) AS example
FROM system.query_log
WHERE exception_code != 0
  AND event_time >= now() - INTERVAL 1 DAY
GROUP BY normalized_query_hash, exception_code
ORDER BY failures DESC
LIMIT 20;

Error count over time:

SELECT
    toStartOfHour(event_time) AS hour,
    count() AS failures
FROM system.query_log
WHERE exception_code != 0
  AND event_time >= now() - INTERVAL 3 DAY
GROUP BY hour
ORDER BY hour;

Failed queries appear as type = 'ExceptionBeforeStart' (parse or analysis errors) or 'ExceptionWhileProcessing' (failed while running); filtering on exception_code != 0 catches both. Translate a code with the error-code section below.

Where in the source code did an error come from?

system.errors keeps a counter per error name, the message and the raw stack trace of the most recent occurrence. With introspection functions you can resolve that trace into function names, which is what you attach to a bug report, or what you read when an error shows up in monitoring and the message alone does not say which code path raised it.

SELECT
    hostName() AS host,
    name,
    value AS occurrences,
    last_error_message,
    arrayMap(addr -> demangle(addressToSymbol(addr)), last_error_trace) AS symbols,
    arrayMap(addr -> addressToLine(addr), last_error_trace) AS source_lines
FROM system.errors
WHERE name = 'ILLEGAL_TYPE_OF_ARGUMENT'
SETTINGS allow_introspection_functions = 1;

Replace the name with the one from your logs (the next section shows how to look a code up). To ask every node, use clusterAllReplicas('my_cluster', system.errors). In clickhouse local, after provoking the error with SELECT 1 + 'a', the row read:

name:               ILLEGAL_TYPE_OF_ARGUMENT
occurrences:        1
last_error_message: Illegal types UInt8 and String of arguments of function plus: In scope SELECT 1 + 'a'
frames:             32

Reading it:

  • occurrences counts how often the error was raised since the server started.
  • symbols is the call stack, innermost frame first. The first frames are the exception constructors; the interesting ones come after.
  • source_lines needs debug info in the binary. The official binary on the test machine returned only the binary’s path for each frame instead of file:line, so symbols is the part you can rely on.
  • allow_introspection_functions is off by default because these functions are slow and expose internals, and the user also needs the INTROSPECTION privilege.
  • Only the last trace per error name is kept, so run this soon after the error happens.

How do I list every error code and name?

Error messages carry a numeric code (Code: 241) and a name (MEMORY_LIMIT_EXCEEDED). Logs, system.errors and client libraries mix the two, so you often need to translate. errorCodeToName returns an empty string for unassigned numbers, so filter those out. On 26.4 there are 647 assigned codes below 2000.

SELECT
    number AS error_code,
    errorCodeToName(number) AS error_name
FROM numbers(2000)
WHERE error_name != ''
ORDER BY error_code;
0  OK
1  UNSUPPORTED_METHOD
2  UNSUPPORTED_PARAMETER
3  UNEXPECTED_END_OF_FILE
4  EXPECTED_END_OF_FILE

(first five rows.) The list is the same set the server uses for the name column of system.errors, so you can join the two. Look up one code:

SELECT errorCodeToName(241) AS error_name;   -- MEMORY_LIMIT_EXCEEDED

To test how your application or alerting reacts to a specific code, throwIf accepts a custom error code when allow_custom_error_code_in_throwif is on:

SELECT throwIf(number = 3, 'custom failure', 999::Int16)
FROM numbers(10)
SETTINGS allow_custom_error_code_in_throwif = 1;

The query fails with Code: 999. Two surprises seen on 26.4: the exception text is the generic Value passed to 'throwIf' function is non-zero (your message appears only inside the trailing while executing 'FUNCTION throwIf(...)' context), and the name printed is KEEPER_EXCEPTION, because 999 is a real built-in code. Pick a code you know is unassigned if you need to tell test failures from real ones. See also the system.errors reference.

What was the average CPU usage over a period?

ClickHouse samples operating-system counters every second and, if asynchronous_metric_log is enabled, stores them. The “normalized” CPU metrics are already divided by the core count, so each is a fraction between 0 and 1 of the whole machine and their sum is overall utilisation. Use it for capacity planning, or to check whether a slow period lined up with a CPU spike without a monitoring stack.

All the metrics that count as busy are summed per sample; idle time is left out. Every sampling instant writes all metrics with the same event_time, so grouping by it adds one sample’s components together, and the outer query averages the samples.

SELECT
    round(avg(cpu) * 100, 2) AS avg_cpu_pct,
    round(max(cpu) * 100, 2) AS peak_cpu_pct
FROM (
    SELECT
        event_time,
        sum(value) AS cpu
    FROM system.asynchronous_metric_log
    WHERE event_date BETWEEN today() - 30 AND today()
      AND metric IN (
          'OSUserTimeNormalized',
          'OSNiceTimeNormalized',
          'OSSystemTimeNormalized',
          'OSIOWaitTimeNormalized',
          'OSIrqTimeNormalized',
          'OSSoftIrqTimeNormalized',
          'OSStealTimeNormalized',
          'OSGuestTimeNormalized',
          'OSGuestNiceTimeNormalized')
    GROUP BY event_time
);

Per hour of day, to see the daily shape:

SELECT
    toHour(event_time) AS hour_of_day,
    round(avg(cpu) * 100, 2) AS avg_cpu_pct
FROM (
    SELECT event_time, sum(value) AS cpu
    FROM system.asynchronous_metric_log
    WHERE event_date >= today() - 7
      AND metric IN ('OSUserTimeNormalized', 'OSSystemTimeNormalized', 'OSIOWaitTimeNormalized',
                     'OSNiceTimeNormalized', 'OSIrqTimeNormalized', 'OSSoftIrqTimeNormalized',
                     'OSStealTimeNormalized', 'OSGuestTimeNormalized', 'OSGuestNiceTimeNormalized')
    GROUP BY event_time
)
GROUP BY hour_of_day
ORDER BY hour_of_day;

For every node of a cluster:

SELECT host, round(avg(cpu) * 100, 2) AS avg_cpu_pct
FROM (
    SELECT hostName() AS host, event_time, sum(value) AS cpu
    FROM clusterAllReplicas('my_cluster', system.asynchronous_metric_log)
    WHERE event_date >= today() - 7
      AND metric LIKE 'OS%TimeNormalized'
      AND metric != 'OSIdleTimeNormalized'
    GROUP BY host, event_time
)
GROUP BY host
ORDER BY host;

Notes:

  • OSIOWaitTimeNormalized is included because I/O wait is time a core could not do other work; drop it if you want compute load only.
  • The set of OS*TimeNormalized names depends on the OS. List what your server records with SELECT DISTINCT metric FROM system.asynchronous_metric_log WHERE metric LIKE 'OS%Normalized'.
  • Inside containers the counters may describe the host rather than the container’s CPU limit.
  • The log is on in the stock server config but is a system table with a TTL, so history is limited.

What should I do about timeout errors in clickhouse-client?

A long OPTIMIZE, ALTER or heavy INSERT ... SELECT ends on the client with something like Code: 209. DB::NetException: Timeout exceeded while reading from socket, while the server may still be working. The client gave up waiting.

The client keeps a connection open and waits for the server to send something. If nothing arrives within receive_timeout (300 seconds by default), it closes the connection. Statements that produce no output for longer, such as OPTIMIZE TABLE ... FINAL on a big partition, hit it. send_timeout (also 300) and connect_timeout (10) cover the other directions. Code 209 is SOCKET_TIMEOUT:

SELECT errorCodeToName(209);   -- SOCKET_TIMEOUT

Pass larger values as client options. They are ordinary settings, so they can also go in the client config file, a profile, or a SETTINGS clause.

clickhouse-client --receive_timeout 900 --send_timeout 900 --queries-file optimize.sql

Confirm a value is in effect in the same session:

SELECT name, value FROM system.settings WHERE name IN ('receive_timeout', 'send_timeout', 'connect_timeout');
connect_timeout  10
receive_timeout  300
send_timeout     300

Notes:

  • A client timeout does not necessarily stop the statement on the server. Check system.processes before running it again, and use KILL QUERY WHERE query_id = '...' to stop it deliberately.
  • It is distinct from the server-side max_execution_time, which kills a query that runs longer than allowed.
  • For work that takes hours, do not rely on a long timeout. Run the statements from a persistent session (tmux, nohup), or use the asynchronous variants where they exist (mutations_sync = 0). Running SQL files in batch covers this.
  • Official docs: clickhouse-client.

How do I see which indexes a query uses?

When a query is slower than it should be, check how much data it selects before reading it:

EXPLAIN indexes = 1
SELECT sum(value) FROM metrics
WHERE metric = 'co2' AND ts >= '2026-02-01' AND ts < '2026-03-01';

Abridged output from the 12-part demo metrics table:

Partition Min-Max   Parts: 2/11   Granules: 2/11
Partition           Parts: 2/2    Granules: 2/2
PrimaryKey          Parts: 2/2    Granules: 2/2

Reading it:

  • Each block is a pruning stage, in order: partition min-max, partition key, primary key, then any Skip blocks for data-skipping indexes (with the index Name).
  • Parts: x/y and Granules: x/y read as “selected / total before this stage”. A granule is a block of 8192 rows by default.
  • Condition: true means the stage found nothing in your WHERE it could use.
  • Granules close to the total after the last stage means the query scans almost everything: the filter does not match the sort key, the partition key or any index.
  • For projections, the ReadFromMergeTree (name) line names the projection that was chosen.

Test with sum(col) rather than count(): a bare count() can be answered from metadata and hide the effect. Details: ORDER BY, PARTITION BY and how queries skip data, data-skipping indexes.

How do I run a JOIN with less memory?

A default hash join builds the whole right table in memory. When it does not fit, the query fails with MEMORY_LIMIT_EXCEEDED (“While executing FillingRightJoinSide”). Pick a merge-based algorithm for that query:

SELECT count()
FROM events AS e
INNER JOIN users AS u ON e.user_id = u.user_id
SETTINGS join_algorithm = 'partial_merge';   -- or 'full_sorting_merge'

Merge joins sort and merge instead of hashing, so they are slower but need much less RAM. Test on two 3-million-row tables with max_memory_usage set to 150 MB: hash and parallel_hash failed with MEMORY_LIMIT_EXCEEDED (hash while executing FillingRightJoinSide), while partial_merge and full_sorting_merge returned the correct count of 3000000.

Other levers:

-- start as hash, switch automatically when the join exceeds these caps
SET join_algorithm = 'auto', max_bytes_in_join = 5000000000;

-- or put the small table on the right and trim it first
SELECT ...
FROM big AS l
INNER JOIN (SELECT key, one_column FROM small WHERE ...) AS r ON l.key = r.key;

The old partial_merge_join boolean setting is gone; use join_algorithm. Full discussion: JOINs in ClickHouse: kinds, table order and memory.

runbookqueries

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.