Query Latency p95 (ms), ClickHouse

​

At a glance

Query Latency p95 (ms) is the duration below which 95% of queries complete, in milliseconds, over a rolling window. For a platform team this is the honest tail-latency signal: the median (p50) tells you how a typical query feels, but p95 tells you how the slow 1-in-20 feels, and that is the experience that makes a dashboard feel sluggish or a checkout call time out. ClickHouse is built for sub-second analytics, so a p95 above 200ms on a tuned OLTP-style workload is a warning that queries are scanning too much, contending for resources, or queuing behind heavier work.

​

Calculation

SELECT round(quantile(0.95)(query_duration_ms)) AS p95_ms
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 5 MINUTE;

​

Worked example

1. A p95 breach with a healthy p50 means the problem is in the tail, not the typical query. Most queries are still fast; a minority got slow. That pattern almost always means resource contention (a heavy query or merge competing for CPU and disk) or a subset of queries hitting a cold cache. Compare with Query Latency p50 (ms): if p50 had also jumped, the whole instance would be slow (load or a global stall), which is a different diagnosis.
2. The tail is where SLAs break. At p50 of 22ms the dashboard feels instant for most refreshes, but 1 in 20 panel loads now takes 240ms and 1 in 100 takes over a second. Customers notice the slow ones, not the fast ones. p95 is the metric the SLA is written against because it captures the experience of the unlucky requests. Pair with Slow-Query Rate % to see how many queries crossed the 1-second slow threshold.
3. Hunt the tail in system.query_log, ordered by duration. The fix starts with finding the slow queries: SELECT query_duration_ms, normalized_query_hash, read_rows, memory_usage FROM system.query_log WHERE type = 'QueryFinish' AND event_time >= now() - 600 ORDER BY query_duration_ms DESC LIMIT 20. If the top queries all share a normalized_query_hash, one query pattern is the culprit (often a missing filter causing a full scan). If they are varied but read_rows is uniformly huge, the instance is under merge or cache pressure. Cross-check Merges In Progress and UncompressedCache Hit Rate %.

Diagnosing the 11:30 breach:
  - p50 held:        18ms -> 22ms   (typical query fine)
  - p95 jumped:      58ms -> 240ms  (tail degraded)
  - p99 jumped:      140ms -> 1,100ms
  - Pattern:         tail-only -> contention or cold cache, not global load
  - Next step:       order query_log by duration; check merges + cache hit rate

​

Sibling cards platform teams should reference together

​

Reconciling against the source

system.query_log is the authoritative source. Reproduce the headline with: SELECT quantile(0.95)(query_duration_ms) FROM system.query_log WHERE type = 'QueryFinish' AND event_time >= now() - 300. For an exact (slower) figure use quantileExact(0.95)(query_duration_ms). Order by duration to find the offenders: ... ORDER BY query_duration_ms DESC LIMIT 20. system.metrics for live concurrency context (queries running now), which explains queuing-driven latency. ClickHouse Cloud console (managed service): the Metrics tab plots query latency percentiles per service over time.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre