Query Latency p99 (ms), ClickHouse

​

At a glance

The 99th-percentile query duration over the recent window: the time below which 99 of every 100 queries complete, expressed in milliseconds. p99 is the tail-latency metric. The median (p50) tells you how the typical query behaves, but p99 tells you how your worst-served queries behave, which is where dashboards time out, ETL jobs stall, and downstream API calls breach their own SLAs. On ClickHouse, a healthy analytical p99 sits well under half a second for point and small-aggregate queries; a p99 above 500ms means the slowest 1% of queries are crossing into user-visible delay. This card flags amber at that threshold so a DBA sees tail regression before users start complaining.

​

Calculation

SELECT
    quantile(0.99)(query_duration_ms) AS p99_ms,
    quantile(0.50)(query_duration_ms) AS p50_ms,
    count()                           AS sampled_queries
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 5 MINUTE

​

Worked example

1. The median is healthy, so this is not a cluster-wide slowdown. If the whole instance were starved (CPU, memory, disk I/O) the p50 would have risen too. It did not, so the cause is a subset of queries, not global pressure.
2. The gap between p95 and p99 is large. p95 at 210ms but p99 at 840ms means the tail is fat: a small number of queries are far slower than the rest. This is the signature of a few expensive queries, not a uniform regression.
3. The new dashboard is the prime suspect. It shipped just before the regression. Tail latency that appears alongside a deploy almost always traces to a new query pattern: a missing index condition, a full-scan filter, or an aggregate over an unpartitioned range.

Triage path from a p99 spike:
  1. Find the slow queries:
     SELECT query_duration_ms, read_rows, read_bytes, query
     FROM system.query_log
     WHERE type = 'QueryFinish'
       AND event_time > now() - INTERVAL 5 MINUTE
       AND query_duration_ms > 500
     ORDER BY query_duration_ms DESC
     LIMIT 20
  2. Look at read_rows: a slow query reading billions of rows is doing a full scan.
  3. Check the WHERE clause against the table's ORDER BY (primary) key:
     a filter that does not align with the sort key cannot use the sparse index.
  4. Fix at the query: add a primary-key-aligned predicate, or add a projection /
     materialised view for the access pattern the dashboard needs.

1. p99 is the tail, and the tail is where users feel pain. A great median hides slow outliers. Always watch p99 for interactive workloads; the median alone will lull you.
2. A wide p50-to-p99 gap means a few bad queries, not global pressure. If the whole distribution rises together, suspect resource saturation. If only the tail rises, hunt for the specific slow query.
3. Tail spikes that coincide with a deploy are almost always a new query pattern. Check system.query_log for the slowest queries in the window and align their filters to the table’s sort key.

​

Sibling cards

​

Reconciling against the source

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

SELECT quantile(0.99)(query_duration_ms)
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 5 MINUTE

Inspect individual slow queries with the full-row form (add read_rows, memory_usage, and query to the select). For currently running queries use SELECT elapsed, query FROM system.processes ORDER BY elapsed DESC. On ClickHouse Cloud, the same system.query_log query works in the SQL console, and the managed service surfaces a query-latency panel in its monitoring view.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre