Alerting on Scatter-Query Ratio

The most dangerous latency regression in a sharded keyspace is the one that never trips a latency alert: a query that used to hit one shard silently starts scattering to all of them, and the mean latency barely moves while the tail — and the load on every shard — quietly climbs.

Context

This is the alerting counterpart to monitoring VTGate query latency, and it sits within observability and operations for sharded Vitess. The scatter ratio is a leading indicator: it moves before absolute latency does, because a small increase in the scatter fraction adds expensive queries without yet dragging the aggregate percentile past its threshold. Alerting on the ratio catches a routing regression — a dropped predicate, a VSchema change, a new query shape — in the window between “the plan changed” and “customers are paging you.” Acting on the alert ultimately means reducing fan-out, which is the substance of reducing scatter-gather query fanout.

Why the ratio, not the latency

A scatter-gather query’s latency is bounded by the slowest of every shard it touches, so each scattered query is intrinsically more expensive and more variable than a targeted one. When the scatter fraction rises from 2% to 8% of read QPS, three things happen well before a latency SLO breaches: every shard absorbs more fan-out load, the p99 tail widens because more queries are exposed to the slowest-shard bound, and the blast radius of any single slow shard grows. The aggregate p50 can stay flat through all of it. That is why the ratio is the alert signal and latency is the confirmation.

The ratio is derived from the plan-type counters VTGate exposes. vtgate_queries_processed counts queries by plan, so scatter plans (SelectScatter, and its variants) over all select plans is a direct fan-out fraction:

# Scatter fraction of read QPS, per keyspace, over a 5-minute window.
sum by (keyspace) (rate(vtgate_queries_processed{plan=~"Select.*Scatter"}[5m]))
/
clamp_min(sum by (keyspace) (rate(vtgate_queries_processed{plan=~"Select.*"}[5m])), 1)

The clamp_min(..., 1) guards against a divide-by-zero producing NaN on a keyspace with no read traffic in the window — an NaN will neither fire nor clear cleanly and creates a flapping alert. An alternative denominator is the routed/processed ratio (vtgate_queries_routed counts shard destinations touched), which measures average fan-out directly; use it when a query can partially scatter to a subset of shards rather than all of them.

The alerting rule

A production alert needs three things beyond the expression: a for clause so a brief spike does not page, labels so Alertmanager routes it, and an annotation carrying the current value into the notification. Pair a fast-burn and a slow-burn threshold so a sudden regression and a gradual drift both surface.

groups:
  - name: vtgate_scatter
    interval: 30s
    rules:
      # Reusable derived series so the alert and the dashboard read the same value.
      - record: vtgate:scatter_ratio:5m
        expr: |
          sum by (keyspace) (rate(vtgate_queries_processed{plan=~"Select.*Scatter"}[5m]))
          /
          clamp_min(sum by (keyspace) (rate(vtgate_queries_processed{plan=~"Select.*"}[5m])), 1)

      - alert: VTGateScatterRatioHigh
        expr: vtgate:scatter_ratio:5m > 0.10
        for: 10m
        labels:
          severity: warning
          team: db-platform
        annotations:
          summary: "Scatter-query ratio high on {{ $labels.keyspace }}"
          description: >
            {{ $value | humanizePercentage }} of reads on {{ $labels.keyspace }}
            are scattering to all shards (threshold 10% for 10m). A query likely
            lost its sharding-key predicate or a VSchema change dropped a vindex.

      - alert: VTGateScatterRatioSpike
        expr: vtgate:scatter_ratio:5m > 0.25
        for: 2m
        labels:
          severity: critical
          team: db-platform
        annotations:
          summary: "Scatter-query ratio spiking on {{ $labels.keyspace }}"
          description: >
            {{ $value | humanizePercentage }} of reads scattered on
            {{ $labels.keyspace }} — investigate the most recent deploy or
            VSchema change immediately.

The for: 10m on the warning absorbs the normal churn of an analytics job that legitimately scatters for a few minutes; the for: 2m critical trades a little noise-tolerance for speed when the ratio is severe. Both read the same vtgate:scatter_ratio:5m recording rule the dashboard uses, so the alert and the graph can never disagree.

Acting on the alert

When the page fires, the goal is to find the query shape that started scattering. VTGate per-plan counters tell you that scatter rose; the query logs or the plan cache tell you which query. The triage path:

# Which plan types are driving the scatter counter right now?
curl -s http://vtgate-0.internal:15000/metrics \
  | grep 'vtgate_queries_processed{' | grep -i scatter

# Inspect a suspect query's plan offline against the current VSchema.
vtexplain --vschema-file vschema.json --schema-file schema.sql --shards 4 \
  --sql "SELECT * FROM orders WHERE status = 'shipped'"

If vtexplain shows a Scatter plan for a query that should be targeted, the fix is upstream: restore the sharding-key predicate in the application, or add a lookup vindex so the secondary-column lookup resolves to one shard. The durable remedies — predicate hygiene, vindex coverage, and query rewriting — are worked through in reducing scatter-gather query fanout. A common immediate mitigation is a temporary routing rule to block or reshape the offending pattern while the real fix ships.

Edge cases and gotchas

  • Legitimate scatter baselines. Analytics keyspaces and reporting queries scatter by design. A single global threshold pages constantly on them. Set the threshold per keyspace (Alertmanager label routing, or a per-keyspace threshold via a recording rule join), and exclude known-analytical keyspaces from the OLTP alert.
  • Low-traffic denominator noise. On a keyspace with a handful of reads per minute, one scattered query is 50% of traffic. Gate the alert on a minimum QPS so the ratio only fires when there is enough traffic to be meaningful: and sum by (keyspace)(rate(vtgate_queries_processed{plan=~"Select.*"}[5m])) > 5.
  • Ratio flat but absolute scatter rising. A doubling of total read QPS with a constant ratio doubles absolute scatter load without moving the ratio. Complement the ratio alert with an absolute-rate alert on SelectScatter QPS for keyspaces where fan-out count is the capacity constraint.
  • NaN flapping. Without clamp_min, a window with zero reads yields NaN; the alert neither fires nor resolves cleanly and Alertmanager shows it flapping. Always guard the denominator.
  • Deploy-correlated spikes are expected but must still be seen. A deploy that adds a legitimately-scattering feature will trip the alert. Do not silence the rule; instead annotate deploys in Grafana so an on-call can correlate a ratio step with a release in one glance and decide fast/expected versus fast/regression.
  • Plan label names differ by version. The exact plan strings (SelectScatter vs a newer taxonomy) vary across Vitess releases. Verify the actual label values on your fleet with curl .../metrics | grep queries_processed before trusting the regex, or the alert silently matches nothing.

Verification

Prove the alert path end to end before relying on it. Inject a sustained scatter and confirm the recording rule crosses the threshold and the alert transitions to firing:

# Sustain a scatter above the warning threshold for longer than the `for` window.
for i in $(seq 1 400); do
  mysql -h vtgate.internal -P 15306 commerce \
    -e "SELECT COUNT(*) FROM orders WHERE status='shipped';" >/dev/null
  sleep 1
done

While that runs, check the derived series and the alert state:

# The recording rule should exceed 0.10 for the keyspace.
curl -s 'http://prometheus:9090/api/v1/query?query=vtgate:scatter_ratio:5m'

# The alert should move pending -> firing after its `for` window elapses.
curl -s 'http://prometheus:9090/api/v1/alerts' | jq '.data.alerts[] | select(.labels.alertname=="VTGateScatterRatioHigh")'

The alert should read pending for the first 10 minutes and then firing. If it never leaves pending, the injected ratio did not clear the threshold — lower the injection cadence of targeted reads or confirm the plan regex matches your version’s label values.

← Back to Monitoring VTGate Query Latency