Building Query Log Retention and Dashboards
Raw system.query_log is a firehose: millions of rows a day, expired aggressively by TTL, and far too heavy to scan on every dashboard refresh. To turn it into a durable performance dashboard you need two things it does not give you out of the box — retention long enough to compare this week against last month, and a compact pre-aggregated table that a visualization tool can read in milliseconds. This guide builds both: a partitioned, TTL-managed query log, a materialized view that rolls each flush into a small metrics table, and the Grafana panels and Prometheus signals that read from it.
Prerequisites
The retention and rollup pipeline
The raw log is transient by design. A materialized view attached to system.query_log fires on every flush, aggregating that batch into a small metrics table that outlives the raw rows and is cheap to query. Grafana reads the compact table directly, or a Prometheus exporter scrapes it.
Step 1 — Partition and TTL the raw log
Retention starts at the source. Shape system.query_log in config so it partitions by day and drops old parts automatically, keeping the on-node footprint bounded.
<!-- /etc/clickhouse-server/config.d/query_log_retention.xml -->
<clickhouse>
<query_log>
<database>system</database>
<table>query_log</table>
<partition_by>toYYYYMMDD(event_date)</partition_by>
<order_by>(event_date, event_time)</order_by>
<!-- Raw rows are only needed until the rollup has consumed them. -->
<ttl>event_date + INTERVAL 14 DAY DELETE</ttl>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>
</clickhouse>
Apply an equivalent TTL to an already-running table without a restart, then reclaim space:
ALTER TABLE system.query_log
MODIFY TTL event_date + INTERVAL 14 DAY DELETE;
-- Materialize the new TTL on existing parts (run off-peak).
ALTER TABLE system.query_log MATERIALIZE TTL;
Expected: system.parts for query_log stops growing past ~14 daily partitions. The rollup below is what preserves history beyond that window.
Step 2 — Create the compact metrics table
The target is an AggregatingMergeTree holding pre-aggregated five-minute buckets. Storing quantiles as AggregateFunction state (not a finished number) lets ClickHouse merge partial states across flushes correctly — a p99 of merged states is accurate, whereas averaging pre-computed p99s is not.
CREATE DATABASE IF NOT EXISTS monitoring;
CREATE TABLE monitoring.query_metrics_5m
(
bucket DateTime,
query_kind LowCardinality(String),
user LowCardinality(String),
runs AggregateFunction(count),
duration_p50 AggregateFunction(quantile(0.50), UInt64),
duration_p99 AggregateFunction(quantile(0.99), UInt64),
read_rows_sum AggregateFunction(sum, UInt64),
mem_peak_max AggregateFunction(max, UInt64),
errors AggregateFunction(countIf, UInt8)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(bucket)
ORDER BY (bucket, query_kind, user)
TTL bucket + INTERVAL 180 DAY DELETE;
The monthly PARTITION BY toYYYYMM(bucket) keeps part counts low over a long retention horizon, and the 180-day TTL gives you a half-year of history in a table thousands of times smaller than the raw log it summarizes.
Step 3 — Attach the materialized view
The materialized view reads FROM system.query_log and fires on each flush, writing -State aggregate functions into the metrics table. Because the view runs on the insert (flush) path, it only ever sees each batch of rows once.
CREATE MATERIALIZED VIEW monitoring.query_metrics_5m_mv
TO monitoring.query_metrics_5m
AS
SELECT
toStartOfFiveMinute(event_time) AS bucket,
query_kind,
user,
countState() AS runs,
quantileState(0.50)(query_duration_ms) AS duration_p50,
quantileState(0.99)(query_duration_ms) AS duration_p99,
sumState(read_rows) AS read_rows_sum,
maxState(memory_usage) AS mem_peak_max,
countIfState(type != 'QueryFinish') AS errors
FROM system.query_log
WHERE type IN ('QueryFinish', 'ExceptionWhileProcessing', 'ExceptionBeforeStart')
GROUP BY bucket, query_kind, user;
Keeping both successes and exceptions (and counting the latter separately with countIfState) means one table serves both the latency SLA and the error-rate signal. Only new flushes are captured; to seed history from rows already in the log, run a one-off backfill:
INSERT INTO monitoring.query_metrics_5m
SELECT
toStartOfFiveMinute(event_time) AS bucket, query_kind, user,
countState(), quantileState(0.50)(query_duration_ms),
quantileState(0.99)(query_duration_ms), sumState(read_rows),
maxState(memory_usage), countIfState(type != 'QueryFinish')
FROM system.query_log
WHERE type IN ('QueryFinish', 'ExceptionWhileProcessing', 'ExceptionBeforeStart')
AND event_time < now() - INTERVAL 5 MINUTE -- avoid the bucket the MV is filling
GROUP BY bucket, query_kind, user;
Step 4 — Read the rollup with -Merge
Because the table stores aggregate state, queries must finalize it with the matching -Merge combinator. This is the query shape every dashboard panel uses.
SELECT
bucket,
countMerge(runs) AS queries,
quantileMerge(0.50)(duration_p50) AS p50_ms,
quantileMerge(0.99)(duration_p99) AS p99_ms,
sumMerge(read_rows_sum) AS rows_read,
maxMerge(mem_peak_max) AS peak_mem,
countIfMerge(errors) AS error_count
FROM monitoring.query_metrics_5m
WHERE query_kind = 'Select'
AND bucket > now() - INTERVAL 24 HOUR
GROUP BY bucket
ORDER BY bucket;
Expected: one row per five-minute bucket, returned in milliseconds even across months of history, because the engine reads a tiny pre-aggregated table instead of the raw log.
Step 5 — Wire Grafana panels
With the official ClickHouse datasource, each panel is a SELECT against the compact table using Grafana’s $__timeFilter macro on bucket. A latency time-series panel:
-- Grafana panel: p50 / p99 SELECT latency
SELECT
bucket AS t,
quantileMerge(0.50)(duration_p50) AS p50_ms,
quantileMerge(0.99)(duration_p99) AS p99_ms
FROM monitoring.query_metrics_5m
WHERE query_kind = 'Select' AND $__timeFilter(bucket)
GROUP BY bucket
ORDER BY bucket;
A top-N heavy-user table panel and an error-rate stat panel round out a minimal dashboard:
-- Panel: top users by total rows read in the window
SELECT user, sumMerge(read_rows_sum) AS rows_read, countMerge(runs) AS queries
FROM monitoring.query_metrics_5m
WHERE $__timeFilter(bucket)
GROUP BY user ORDER BY rows_read DESC LIMIT 10;
-- Panel: error rate as a single stat
SELECT countIfMerge(errors) / countMerge(runs) AS error_ratio
FROM monitoring.query_metrics_5m
WHERE $__timeFilter(bucket);
Step 6 — Expose to Prometheus for alerting
If you standardize on Prometheus, scrape the rollup rather than the raw log. Point the clickhouse-exporter at a query that returns current values, or define a Grafana alert rule directly on the panel query. A scrape-friendly query returning one gauge row per query kind for the last complete bucket:
SELECT
query_kind,
quantileMerge(0.99)(duration_p99) AS p99_ms,
countIfMerge(errors) AS errors_5m
FROM monitoring.query_metrics_5m
WHERE bucket = toStartOfFiveMinute(now() - INTERVAL 5 MINUTE)
GROUP BY query_kind;
An alert rule then fires when p99_ms for Select crosses your SLA, or when errors_5m jumps — both computed against the compact table, so the alert path never touches the raw log. Tie the memory alert to the ceiling defined in threshold tuning and performance limits so the threshold moves with the setting.
Verification
Confirm the view is populating and the rollup matches the raw log for an overlapping window:
SYSTEM FLUSH LOGS;
-- Rollup p99 vs. raw-log p99 for the last hour — should match within rounding.
SELECT
(SELECT round(quantileMerge(0.99)(duration_p99))
FROM monitoring.query_metrics_5m
WHERE query_kind = 'Select' AND bucket > now() - INTERVAL 1 HOUR) AS rollup_p99,
(SELECT round(quantile(0.99)(query_duration_ms))
FROM system.query_log
WHERE type = 'QueryFinish' AND query_kind = 'Select'
AND event_time > now() - INTERVAL 1 HOUR) AS raw_p99;
A close match confirms the state math and the -Merge reads are correct. Also check the compact table stays small:
SELECT formatReadableSize(sum(bytes_on_disk)) AS size, count() AS parts
FROM system.parts
WHERE database = 'monitoring' AND table = 'query_metrics_5m' AND active;
Gotchas & edge cases
- Reading state columns without
-Mergereturns garbage. A plainSELECT duration_p99 FROM query_metrics_5mreturns an opaque binary state, not a number. Every read must use the matching combinator (quantileMerge,sumMerge,countMerge), and the quantile level in the read must equal the level in the state. - The materialized view only captures future flushes. Creating the view does not backfill history — rows already flushed before the view existed are never aggregated. Run the Step 3 backfill
INSERT, and exclude the in-progress bucket so you do not double-count rows the view will also process. - A too-short raw TTL can outrun the rollup. If
query_log’s TTL drops parts before a flush has fired the view, those rows never reach the metrics table. Keep the raw TTL comfortably longer than the flush interval, and verify the view is healthy before shortening retention. - Merging is asynchronous, so recent buckets hold multiple partial states.
AggregatingMergeTreecollapses same-key states only on background merge, so a freshly written bucket may exist as several partial-state rows. The-Mergecombinator in your read reconciles them correctly at query time — never assume one physical row per bucket. - Grafana’s
$__timeFiltermust target the bucket column. Applying the macro toevent_time(which does not exist on the rollup) or forgettingGROUP BY bucketproduces either an error or a single collapsed point. Always filter and group onbucket.
Related
- Query performance monitoring — the parent reference for query_log schema, percentiles, and alert design.
- Detecting slow queries with system.query_log — the raw-log drill-down these dashboards make routine.
- Threshold tuning & performance limits — the memory and execution ceilings your alert thresholds should track.