Tracking query and resource trends from system tables
Roll system.query_log up into a small per-minute table, then answer trend questions with SQL: this week against last week, new and unused query shapes, and disk growth from system.parts.
system.query_log answers “what happened?” very well and “is this normal?” poorly. It is verbose, it expires after a retention period you configure, and every question you ask of it scans a lot of rows. If you want trends, such as queries per minute, memory per day or the share of failures compared with last week, keep a compact rollup of your own.
This tutorial builds that rollup in four steps and then shows the queries to read it. All SQL ran on clickhouse local 26.4 against a synthetic stand-in for system.query_log; on a server you replace the stand-in with the real table.
What is worth tracking
A short list covers most operational questions:
| Signal | Source | Why |
|---|---|---|
| Queries per minute, split by finished and failed | system.query_log |
Load and error rate |
| Memory, duration, rows and bytes read, per kind and user | system.query_log |
Where resources go |
| Distinct, new and unused query shapes | system.query_log (normalized_query_hash) |
Change detection |
| Disk size, rows, active parts per table | system.parts |
Growth, merge health |
For each signal you want three views: the last few days, a comparison with the same weekday last week, and a comparison with an average over a longer baseline. Everything else (Grafana panels, alerts, expected ranges) is presentation on top.
Step 0: a stand-in for query_log
Save this as setup.sql. It creates a table with the query_log columns we need and fills it with four weeks of synthetic traffic with a weekday and weekend pattern, slow growth, and a few failures.
CREATE TABLE query_log_demo
(
event_date Date,
event_time DateTime,
type Enum8('QueryStart' = 1, 'QueryFinish' = 2, 'ExceptionBeforeStart' = 3, 'ExceptionWhileProcessing' = 4),
query_kind LowCardinality(String),
initial_user LowCardinality(String),
normalized_query_hash UInt64,
memory_usage UInt64,
query_duration_ms UInt64,
read_rows UInt64,
read_bytes UInt64
) ENGINE = MergeTree ORDER BY event_time;The rollup tables come next (step 1), and the data is inserted after them, so that the materialized view sees it. The complete script is at the end of the page.
Step 1: a per-minute rollup
Create a SummingMergeTree table keyed by the dimensions you want to slice by, and a materialized view that feeds it from the log:
CREATE TABLE query_costs
(
event_date Date,
event_time DateTime,
type Enum8('QueryStart' = 1, 'QueryFinish' = 2, 'ExceptionBeforeStart' = 3, 'ExceptionWhileProcessing' = 4),
query_kind LowCardinality(String),
initial_user LowCardinality(String),
queries UInt64,
memory_usage UInt64,
query_duration_ms UInt64,
read_rows UInt64,
read_bytes UInt64
) ENGINE = SummingMergeTree
ORDER BY (event_date, event_time, type, query_kind, initial_user);
CREATE MATERIALIZED VIEW query_costs_mv TO query_costs AS
SELECT
event_date,
toStartOfMinute(event_time) AS event_time,
type,
query_kind,
initial_user,
count() AS queries,
sum(memory_usage) AS memory_usage,
sum(query_duration_ms) AS query_duration_ms,
sum(read_rows) AS read_rows,
sum(read_bytes) AS read_bytes
FROM query_log_demo
WHERE type != 'QueryStart'
GROUP BY event_date, event_time, type, query_kind, initial_user;Some points that matter:
type != 'QueryStart'drops the start rows so that each query is counted once, as finished or as failed. Keep thetypecolumn: the failure rate isExceptionWhileProcessingover everything.- The
ORDER BYof aSummingMergeTreeis its grouping key. Rows with the same key are summed when parts merge. Merging is eventual, so always aggregate again when you read (sum(queries) ... GROUP BY), never read raw rows and assume one row per key. - Each insert block into the log produces one batch for the view, and the view aggregates within that batch only. This is why the table is a
SummingMergeTreeand not a plainMergeTree. - A materialized view only sees data inserted after it exists. To include history, run the same
SELECTas anINSERT INTO query_costsonce. In the demo below the view exists before the data arrives, so nothing needs backfilling.
On a real server, replace query_log_demo with system.query_log. The log tables are ordinary tables that ClickHouse inserts into in batches, and a view on them fires on each flush. Keep in mind:
system.query_logis local to each node. On a cluster, addhostName() AS host_nameto the view and to the key, and either use a replicated engine for the rollup or create it on every node and read throughclusterAllReplicas.- When an upgrade changes the log’s schema, ClickHouse renames the old table and creates a new one. Check after upgrades that your view still reads from the live table.
- The rollup has no TTL by default. Add one if you do not want to keep minute data forever, for example
TTL event_date + INTERVAL 1 YEAR.
Now insert the data (this is the second half of setup.sql):
INSERT INTO query_log_demo
WITH
toDateTime('2025-02-03 00:00:00') AS t0, -- a Monday
intDiv(number, 3000) AS day,
toDayOfWeek(toDate(t0) + day) AS dow
SELECT
toDate(t0) + day,
t0 + toIntervalDay(day) + toIntervalSecond(cityHash64(number, 1) % 86400),
if(cityHash64(number, 2) % 1000 < 5 + day, 'ExceptionWhileProcessing', 'QueryFinish'),
['Select', 'Insert'][1 + (cityHash64(number, 3) % 5 = 0)],
['dashboard', 'api', 'etl'][1 + cityHash64(number, 4) % 3],
cityHash64('shape', if(cityHash64(number, 5) % 100 < 2, day * 7 + number % 5, number % 40)),
20000000 + cityHash64(number, 6) % 80000000,
50 + cityHash64(number, 7) % 500 + day * 5,
1000 + cityHash64(number, 8) % 1000000,
100000 + cityHash64(number, 9) % 100000000
FROM numbers(3000 * 28)
WHERE cityHash64(number, 11) % 100 < 60 + day * 1.2
AND (dow < 6 OR cityHash64(number, 10) % 3 = 0);Look at the first rows of the rollup:
SELECT event_date, event_time, type, query_kind, initial_user, queries, memory_usage
FROM query_costs ORDER BY event_time LIMIT 3; ┌─event_date─┬──────────event_time─┬─type────────┬─query_kind─┬─initial_user─┬─queries─┬─memory_usage─┐
1. │ 2025-02-03 │ 2025-02-03 00:00:00 │ QueryFinish │ Select │ dashboard │ 1 │ 51245455 │
2. │ 2025-02-03 │ 2025-02-03 00:01:00 │ QueryFinish │ Select │ etl │ 1 │ 90034418 │
3. │ 2025-02-03 │ 2025-02-03 00:02:00 │ QueryFinish │ Select │ dashboard │ 1 │ 69863946 │
└────────────┴─────────────────────┴─────────────┴────────────┴──────────────┴─────────┴──────────────┘(The demo traffic is sparse, so most minutes hold a single query. Real rows hold many.) Because it is minute-grained, you can roll it up to any coarser grain with toStartOfHour, toStartOfDay and so on:
SELECT toStartOfHour(event_time) AS hour, sum(queries) AS queries, formatReadableSize(sum(memory_usage)) AS mem
FROM query_costs WHERE event_date = '2025-02-10' GROUP BY hour ORDER BY hour LIMIT 3; ┌────────────────hour─┬─queries─┬─mem──────┐
1. │ 2025-02-10 00:00:00 │ 79 │ 4.27 GiB │
2. │ 2025-02-10 01:00:00 │ 85 │ 4.86 GiB │
3. │ 2025-02-10 02:00:00 │ 86 │ 5.02 GiB │
└─────────────────────┴─────────┴──────────┘Step 2: read trends
A day, a rolling average, and the failure rate
SELECT
event_date,
sum(queries) AS total,
round(avg(total) OVER (ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)) AS avg_7d,
round(sumIf(queries, type = 'ExceptionWhileProcessing') / total * 100, 2) AS failed_pct
FROM query_costs
GROUP BY event_date
ORDER BY event_date DESC
LIMIT 5; ┌─event_date─┬─total─┬─avg_7d─┬─failed_pct─┐
1. │ 2025-03-02 │ 949 │ 2150 │ 3.16 │
2. │ 2025-03-01 │ 902 │ 2140 │ 2.33 │
3. │ 2025-02-28 │ 2699 │ 2129 │ 2.89 │
4. │ 2025-02-27 │ 2648 │ 2096 │ 2.68 │
5. │ 2025-02-26 │ 2650 │ 2067 │ 2.72 │
└────────────┴───────┴────────┴────────────┘The window function runs after the GROUP BY, so avg(total) OVER (...) is a moving average over the daily totals. The weekend days (the first two rows) are low on purpose: workloads have weekly shape, and a plain “today vs yesterday” comparison will cry wolf every Monday.
The same weekday last week
Comparing with the same weekday removes the weekly pattern. A self-join on event_date - 7 does it:
WITH daily AS
(
SELECT event_date, sum(queries) AS total
FROM query_costs
GROUP BY event_date
)
SELECT
d.event_date,
toDayOfWeek(d.event_date) AS dow,
d.total,
p.total AS total_last_week,
round((d.total - p.total) / p.total * 100, 1) AS change_pct
FROM daily AS d
LEFT JOIN daily AS p ON p.event_date = d.event_date - 7
ORDER BY d.event_date DESC
LIMIT 7; ┌─event_date─┬─dow─┬─total─┬─total_last_week─┬─change_pct─┐
1. │ 2025-03-02 │ 7 │ 949 │ 873 │ 8.7 │
2. │ 2025-03-01 │ 6 │ 902 │ 828 │ 8.9 │
3. │ 2025-02-28 │ 5 │ 2699 │ 2470 │ 9.3 │
4. │ 2025-02-27 │ 4 │ 2648 │ 2446 │ 8.3 │
5. │ 2025-02-26 │ 3 │ 2650 │ 2416 │ 9.7 │
6. │ 2025-02-25 │ 2 │ 2619 │ 2352 │ 11.4 │
7. │ 2025-02-24 │ 1 │ 2586 │ 2260 │ 14.4 │
└────────────┴─────┴───────┴─────────────────┴────────────┘The last week against a longer baseline
For a one-line summary per metric, compare the last seven days with the average of the preceding period. The ARRAY JOIN turns one row of numbers into one row per metric, so adding a metric means adding one entry to each of the arrays:
WITH (SELECT max(event_date) FROM query_costs) AS last_day
SELECT
metric,
round(last_7d, 1) AS last_7d,
round(baseline, 1) AS baseline_per_day,
round((last_7d - baseline) / baseline * 100, 1) AS change_pct
FROM
(
SELECT
sumIf(queries, event_date > last_day - 7) / 7 AS q_last,
sumIf(queries, event_date <= last_day - 7) / 21 AS q_base,
sumIf(memory_usage, event_date > last_day - 7) / 7 / 1e9 AS m_last,
sumIf(memory_usage, event_date <= last_day - 7) / 21 / 1e9 AS m_base
FROM query_costs
)
ARRAY JOIN
['queries/day', 'memory GB/day'] AS metric,
[q_last, m_last] AS last_7d,
[q_base, m_base] AS baseline; ┌─metric────────┬─last_7d─┬─baseline_per_day─┬─change_pct─┐
1. │ queries/day │ 2150.4 │ 1740.3 │ 23.6 │
2. │ memory GB/day │ 128.5 │ 104.2 │ 23.4 │
└───────────────┴─────────┴──────────────────┴────────────┘The divisor (21) assumes the demo’s 28 days of data; on real data use the number of days in your baseline. Queries and memory grow together here, so cost per query is stable. If memory had grown faster than the query count, that would be the interesting row.
Step 3: query shapes, new and unused
Totals can hide change. A new query shape, or a shape that quietly disappeared, is often the real news. For that you need a per-shape history, which a second small rollup provides:
CREATE TABLE query_shapes_daily
(
event_date Date,
normalized_query_hash UInt64,
queries UInt64,
memory_usage UInt64,
query_duration_ms UInt64
) ENGINE = SummingMergeTree
ORDER BY (event_date, normalized_query_hash);
CREATE MATERIALIZED VIEW query_shapes_daily_mv TO query_shapes_daily AS
SELECT event_date, normalized_query_hash, count() AS queries,
sum(memory_usage) AS memory_usage, sum(query_duration_ms) AS query_duration_ms
FROM query_log_demo
WHERE type != 'QueryStart'
GROUP BY event_date, normalized_query_hash;
-- the demo data was loaded before this view existed, so backfill once
INSERT INTO query_shapes_daily
SELECT event_date, normalized_query_hash, count(), sum(memory_usage), sum(query_duration_ms)
FROM query_log_demo WHERE type != 'QueryStart'
GROUP BY event_date, normalized_query_hash;normalized_query_hash is the same for queries that differ only in their constants, so it counts shapes and not individual statements. With this table, “how many shapes ran, how many of them were seen for the first time today, and how much of the day’s memory did the newcomers use?” is a window function:
SELECT
event_date,
uniqExact(normalized_query_hash) AS distinct_queries,
countIf(first_day = event_date) AS new_queries,
round(sumIf(memory_usage, first_day = event_date) / sum(memory_usage) * 100, 1) AS new_queries_mem_pct
FROM
(
SELECT event_date, normalized_query_hash, memory_usage,
min(event_date) OVER (PARTITION BY normalized_query_hash) AS first_day
FROM query_shapes_daily
)
GROUP BY event_date
ORDER BY event_date DESC
LIMIT 4; ┌─event_date─┬─distinct_queries─┬─new_queries─┬─new_queries_mem_pct─┐
1. │ 2025-03-02 │ 45 │ 5 │ 2.6 │
2. │ 2025-03-01 │ 45 │ 5 │ 2.4 │
3. │ 2025-02-28 │ 45 │ 5 │ 2 │
4. │ 2025-02-27 │ 45 │ 5 │ 2 │
└────────────┴──────────────────┴─────────────┴─────────────────────┘Note the caveat: “new” means “not seen since the rollup started”, so ignore the first days after creating it. The demo has a steady five new shapes per day by construction; in real life a spike in this column deserves a look.
Unused shapes are the opposite: seen in the past, absent in the last week.
WITH (SELECT max(event_date) FROM query_shapes_daily) AS last_day
SELECT normalized_query_hash, max(event_date) AS last_seen, sum(queries) AS runs_before
FROM query_shapes_daily
GROUP BY normalized_query_hash
HAVING last_seen <= last_day - 7
ORDER BY last_seen DESC
LIMIT 5; ┌─normalized_query_hash─┬──last_seen─┬─runs_before─┐
1. │ 17936254792276827510 │ 2025-02-23 │ 6 │
2. │ 10999376752357214026 │ 2025-02-23 │ 3 │
3. │ 4372116661852424001 │ 2025-02-23 │ 2 │
4. │ 6297940156680669907 │ 2025-02-23 │ 3 │
5. │ 10255038319684179896 │ 2025-02-22 │ 5 │
└───────────────────────┴────────────┴─────────────┘Shapes that vanish tell you a dashboard was retired or a job stopped, which is exactly the information you want before dropping the index or projection that served them.
Step 4: disk size and row counts
system.parts shows only the present, not the past, so for growth you need snapshots. A refreshable materialized view can take one every hour and append it to a history table:
CREATE TABLE parts_history
(
snapshot_time DateTime,
database LowCardinality(String),
`table` LowCardinality(String),
active_parts UInt64,
`rows` UInt64,
bytes_on_disk UInt64
) ENGINE = MergeTree ORDER BY (snapshot_time, database, `table`);
CREATE MATERIALIZED VIEW parts_history_mv REFRESH EVERY 1 HOUR APPEND TO parts_history AS
SELECT now() AS snapshot_time, database, `table`,
count() AS active_parts, sum(`rows`) AS `rows`, sum(bytes_on_disk) AS bytes_on_disk
FROM system.parts
WHERE active
GROUP BY database, `table`;A refresh runs when the view is created. To wait for it and look at the latest snapshot:
SYSTEM WAIT VIEW parts_history_mv;
SELECT database, `table`, active_parts, `rows`, formatReadableSize(bytes_on_disk) AS size
FROM parts_history
WHERE snapshot_time = (SELECT max(snapshot_time) FROM parts_history)
ORDER BY `table`; ┌─database─┬─table──────────────┬─active_parts─┬──rows─┬─size──────┐
1. │ default │ query_costs │ 1 │ 44363 │ 1.00 MiB │
2. │ default │ query_log_demo │ 1 │ 51599 │ 1.32 MiB │
3. │ default │ query_shapes_daily │ 1 │ 1229 │ 18.53 KiB │
└──────────┴────────────────────┴──────────────┴───────┴───────────┘On a cluster, take the snapshot per node and add hostName() to the key. If your version predates refreshable views, run the same INSERT INTO parts_history SELECT ... from cron.
A snapshot every hour, plus the previous-week comparison from step 2 applied to bytes_on_disk, is enough to see a table that grows faster than it used to, or an active-parts count that climbs because merges cannot keep up.
Step 5: presentation and alerting
With the rollups in place, a dashboard is a handful of panels, each reading one of the tables above:
- Queries per minute and failed queries per minute, with the weekly comparison overlaid.
- Memory, duration, rows read and bytes read, as sum and per query.
- Top users and top query kinds by cost.
- New and unused shapes per day.
- Disk bytes and active parts per table.
For alerting, avoid fixed thresholds on raw counts. Compare each signal with its expected range: the same weekday in previous weeks, or a mean and standard deviation over a quiet baseline. The blog post “Investigating a rising ClickHouse error rate” shows the standard-deviation approach on real symptoms.
The complete demo script
Save every sql block above that creates or fills a table or view, in the order they appear, as setup.sql: step 0, the rollup table and view, the INSERT, the step 3 tables and backfill, the step 4 table and view. Run with clickhouse local --path ./demo --multiquery < setup.sql; the --path keeps the tables so you can run the read queries afterwards with clickhouse local --path ./demo --query "...".
Docs: system.query_log, SummingMergeTree, refreshable materialized views.
Discussion
Comments live in GitHub Discussions. Sign in with GitHub to join in.