Slow Queries Anatomy

A Grafana panel times out. The on-call engineer reruns the same panel against /api/v1/query from curl and the server returns error: query timed out. A second engineer, on a different dashboard, sees panels loading slowly. Someone mutters “Prometheus is broken again” and pages the team that owns the platform.

In most production incidents of this shape, Prometheus is not broken. A single query is asking the database to do work that takes longer than its configured timeout, and the platform is behaving exactly as designed. The work for the operator is to identify which query and which class of cost it belongs to.

This lesson introduces the four families of slow query that account for the overwhelming majority of incidents in production: large range, high cardinality, expensive regex, and poor aggregation. It then gives the order to check, the metric to read at each step, and the most common single cause.

What a slow query is

A slow query is a query whose evaluation cost exceeds the budget you have given it. The budget is set on three independent axes:

    User-visible budget        Platform budget           Backend budget
    -------------------        --------------            --------------
    Grafana timeout            Prometheus                Prometheus / Loki
    (panel refresh)            query.max-concurrency      query.timeout
                               --query.max-concurrency    --query_timeout
                               (Prometheus)              (Loki)
    Default: 30 s              Default: 20               Default: 60 s

When any of these is exceeded, the query is killed. The user sees query timed out. The platform sees a freed slot. The backend sees a cancelled context. None of them tells you which family caused the failure. That is your job.

The four families, in roughly descending order of frequency in production:

1. Large range. A query that scans too many samples or too much log line for the time window it covers.
2. High cardinality. A query whose result set touches many more series than the platform can scan in its budget.
3. Expensive regex. A query whose label or log filter is unanchored, ungreedy, or matches a large fraction of candidates.
4. Poor aggregation. A query that aggregates at the panel rather than at the source, repeating work the recording layer should already have done.

Storage I/O sits underneath all four and is treated separately in the final lesson of this module.

Why a sysadmin cares

A slow query is not just an inconvenience for the user. It consumes a slot in the query scheduler. With --query.max-concurrency=20 and one rogue query holding ten slots for thirty seconds, every other query in the platform queues behind it. Alerts miss their evaluation windows. Rules miss their tick. Grafana dashboards blank out across the whole tenant.

The user-visible failure mode is therefore a panel error on one dashboard and a platform-wide slowdown on every other dashboard. The two have the same root cause, and the cost of mistaking one for the other is minutes of misdirected investigation.

How it works: the query path

A Prometheus query, an instant or range query against /api/v1/query, walks the following path:

  Grafana
    |  HTTP POST /api/v1/query_range
    |  (expr, start, end, step)
    v
  Prometheus
    |  1. parse + analyse expression
    |  2. look up matching series in the head + TSDB index
    |  3. for each step, scan samples on disk (mmap blocks)
    |  4. apply operator / function pipeline
    |  5. materialise result
    v
  Result

Cost is paid in three places: index lookup (proportional to the number of matching series), on-disk scan (proportional to the number of samples touched), and pipeline evaluation (proportional to the number of points produced). Each of the four families maps cleanly onto one of these:

Loki and Tempo follow the same shape with different labels. Loki cost lives in the iterator pipeline (loki_request_duration_seconds) and in chunk selection (loki_index_request_duration_seconds). Tempo cost lives in the block-level search and trace-level fan-out (tempo_query_request_duration_seconds).

How to read the slow-query signal

The first response to a slow-query report is never to change the query. The first response is to confirm the platform is behaving as configured, find the offending query, and classify it.

Prometheus exposes every query’s cost on its own metrics. The two that matter for triage:

# Top ten queries by evaluation cost over the last five minutes.
# READ-ONLY.
promql='topk(10, sum by (query) (rate(prometheus_engine_query_duration_seconds_sum[5m])))'
curl -s --data-urlencode "query=${promql}" http://prometheus:9090/api/v1/query \
  | jq '.data.result[] | {query: .metric.query, rate: .value[1]}'

{
  "query": "sum(rate(http_requests_total[1m]))",
  "rate": "0.42"
}

That is the five-minute running rate of total evaluation time for every query text the engine has seen. The single largest contributor is almost always a query that has been running recently and is large enough to dominate.

The histogram version lets you cut by latency bucket:

# Count of queries that ran longer than thirty seconds in the last five
# minutes. READ-ONLY.
promql='sum(rate(prometheus_engine_query_duration_seconds_bucket{le="+Inf"}[5m])) -
       sum(rate(prometheus_engine_query_duration_seconds_bucket{le="30"}[5m]))'
curl -s --data-urlencode "query=${promql}" http://prometheus:9090/api/v1/query

{
  "status": "success",
  "data": {
    "resultType": "vector",
    "result": [
      { "metric": {}, "value": [1735000000.000, "3"] }
    ]
  }
}

Three queries in five minutes overran thirty seconds. That is the budget breach. The classification step identifies which family caused it.

The diagnostic order

Apply these steps in order. Each step is read-only until the final one.

1. Confirm the platform budget. Read the command-line flags and confirm —query.max-concurrency and —query.timeout are what you expect. A regression introduced by a recent restart will show up here.

2. Find the offending query. Use the topk query above. Note its text and the time window it covers.

3. Check series count. Run prometheus_tsdb_head_series against the metric the query touches. A metric that looks innocent in a small test can carry millions of series in production.

4. Check the time range. Compute the ratio of samples touched to wall time. A range scan that touches ten million samples per second of wall time is a large-range problem even at low series count.

5. Reproduce outside the panel. Run the same query with curl and the same start, end, step. The panel’s instant evaluation mode hides range-scan cost.

6. Fix at the lowest cost layer. Recording rule before query rewrite before label change before platform resize. Each of the four families has a lowest-cost fix described in the next lessons.

How it can fail

Six failure shapes cover the overwhelming majority of slow-query incidents in production.

1. Range scan over months of data. A dashboard was set to “last 6 months” against a metric with one-second scrape interval. The TSDB is healthy. The query is the problem.
2. Cardinality explosion. A new label was added to a metric by an exporter in a previous release. The series count doubles overnight. Old queries now return more series than the index can serve in budget.
3. Unanchored regex. A path=~"/api/.*" match is run on every label value in the index for every step of the query.
4. Per-series aggregation in the panel. A user has a high-cardinality metric in a panel with no sum by (...) and the panel evaluates the raw series set every refresh.
5. Recording-rule gap. The recording rule that should pre-aggregate the metric was last evaluated six months ago and was disabled in a reload that no one noticed.
6. Backend storage slow path. A TSDB whose working set exceeds RAM falls back to disk for the index. The same query that returned in 200 ms now takes 12 s. The query has not changed.

How to troubleshoot it

The fix path is determined by the family. The next four lessons cover each in detail. In summary:

• Large range. Reduce the time window. Add a recording rule that pre-aggregates over a longer window and serve the dashboard from the rule.
• High cardinality. Drop the high-cardinality label from the metric at the exporter or at relabel-config. Aggregate upstream of the query.
• Expensive regex. Anchor the regex. Switch to equality where possible. Move the matcher into label-drop at relabel-config.
• Poor aggregation. Aggregate at the recording rule, not at the panel. Verify the rule is current.
• Storage slow path. Confirm the head block fits in RAM (node_memory_MemAvailable_bytes). Check the --storage.tsdb.wal-compression setting. Move the TSDB to NVMe.

Security implications

The query path exposes two attack surfaces. The HTTP endpoint of every Prometheus, Loki and Tempo accepts queries from any caller allowed to reach it. A slow query is also a denial of service: a single authenticated user with the ability to send arbitrary expressions can saturate the scheduler.

Mitigations to put in place:

• Reverse-proxy with basic auth or mTLS. Prometheus supports neither natively; Loki and Tempo do.
• Per-tenant concurrency limits in Loki (querier.max-concurrent) and Tempo (multitenancy_enabled: true).
• Query-time logging. Every Prometheus, Loki and Tempo logs the query text on stderr. Ship those logs to Loki and alert on any single expression whose running five-minute rate exceeds a threshold.

Performance implications

Performance implications come from five axes:

• Cardinality. Each unique combination of label values is one series. The series count is the cost denominator for every range scan and for every regex match.
• Scrape / push interval. A 1 s interval gives sixty samples per minute per series. A 15 s interval gives four. The TSDB size scales linearly.
• Rule size. A recording rule that returns millions of series is itself expensive to evaluate and to store.
• Retention. Storage grows with cardinality and with sample rate. Long retention multiplies the cost of any range scan that crosses it.
• Query cost. A single expensive query can occupy the scheduler for its full timeout.

Production guidance

• Read prometheus_engine_query_duration_seconds first, not the user’s dashboard.
• Reproduce the query with curl before changing anything.
• Add a recording rule for every query whose panel is opened by more than three users or evaluated as an alert.
• Alert on rate(prometheus_engine_query_duration_seconds_count[5m]) rising and on any single query text whose five-minute rate exceeds a fixed threshold.
• Keep dashboards to windows that match the resolution the data was collected at. Thirty days of one-second samples is sixty times larger than thirty days of fifteen-second samples.

Verification

You should now be able to answer:

• What four families account for the majority of slow-query incidents in production?
• In what order should you read the platform’s self-observability metrics when a slow query is reported?
• Why is “kill the query and resize the platform” a poor first response?
• What is the most common single cause of a slow query in production?

Quiz