Slow-Query Rate %, ClickHouse

​

At a glance

The share of queries in the recent window that took longer than one second, expressed as a percentage of all queries. Where the latency-percentile cards (p50, p95, p99) describe the shape of the duration distribution, this card answers a blunt operational question: what fraction of my workload is slow? A query crossing one second on an analytical store is the line where interactive feels sluggish and ETL starts to back up. A healthy interactive workload keeps this under a couple of percent. A slow-query rate above 5% means more than one query in twenty is dragging, which is enough to make dashboards feel broken and to put scheduled jobs at risk of overrunning their windows.

​

Calculation

SELECT
    countIf(query_duration_ms > 1000) AS slow_queries,
    count()                           AS total_queries,
    round(countIf(query_duration_ms > 1000) / count() * 100, 2) AS slow_pct
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 15 MINUTE

​

Worked example

1. One query in twelve is over a second. With analysts actively using the dashboards, that means most users are hitting at least one slow panel per session. The complaint (“the dashboards are crawling this morning”) is real and quantified.
2. The median is fine, so this is not global saturation. p50 at 44ms says the typical query is fast; the cluster is not out of CPU or memory across the board. The slowness is concentrated in a slice of queries, consistent with the high p99 (2,300ms).
3. The slow slice is large enough to investigate by pattern, not by individual query. 512 slow queries is too many to read one by one. The move is to group them: which tables, which query shapes, which users are responsible for the slow set.

Finding the pattern behind a high slow rate:
  SELECT
      normalizeQuery(query) AS pattern,
      count()              AS n,
      avg(query_duration_ms) AS avg_ms,
      avg(read_rows)       AS avg_rows
  FROM system.query_log
  WHERE type = 'QueryFinish'
    AND event_time > now() - INTERVAL 15 MINUTE
    AND query_duration_ms > 1000
  GROUP BY pattern
  ORDER BY n DESC
  LIMIT 10;
  -- normalizeQuery collapses literals so the same shape groups together.
  -- High n + high avg_rows on one pattern = a full-scan query repeated often.

1. Rate, not count, is the operational truth. Ten slow queries are fine at peak and a crisis when quiet. The percentage normalises for traffic so the signal means the same thing at any hour.
2. A high slow rate with a healthy median means a concentrated cause. Group the slow queries with normalizeQuery to find the repeated pattern; do not chase individual queries.
3. The cure is almost always schema or query shape. Align filters to the sort key, add a projection or materialised view for the hot pattern, or batch the offending workload. Hardware rarely fixes a repeated full scan.

​

Sibling cards

​

Reconciling against the source

Run the same ratio against system.query_log from clickhouse-client:

SELECT countIf(query_duration_ms > 1000) / count() * 100
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 15 MINUTE

Group the slow set by shape with normalizeQuery(query) to find the repeated pattern, and add read_rows to spot full scans. Inspect currently running long queries with SELECT elapsed, query FROM system.processes WHERE elapsed > 1 ORDER BY elapsed DESC. On ClickHouse Cloud, the same system.query_log query works in the SQL console, and the managed service surfaces query-performance panels in its monitoring view.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre