Weekly Replica Read SLO Report from Prometheus

Problem statement: the read tier has dashboards, alerts and an SLO, and the slow degradations still go unnoticed for months because nothing looks at the trend.

Alerts catch fast failures. Dashboards catch what someone happens to look at. Neither catches a replica share drifting from 85% to 60% over six weeks, or a fallback rate that has doubled since a release nobody connected it to. A short recurring report does, provided it is built around decisions rather than around available metrics. This page builds one on top of the SLO defined in tracing and SLO reporting for replica reads.

Symptom Identification

A recurring report is worth building when:

  • A degradation was discovered by accident — during unrelated work, or when a threshold was finally crossed — and had clearly been developing for weeks.
  • The answer to “is the read tier healthy?” requires someone to open four dashboards and interpret them.
  • Capacity decisions are argued from intuition because no one has the trend to hand.
  • A replica was found to be receiving almost no traffic, and nobody knows how long that had been true.
  • SLO burn is only ever discussed during an incident, so the error budget is a post-hoc explanation rather than a planning input.

The last point is the difference between having an SLO and using one. A budget that is only consulted after it has been spent is a metric, not an objective.

Root Cause Analysis

Alerts and dashboards have complementary blind spots, and slow degradation falls between them. An alert is a threshold on a fast window: it must be quiet in normal operation, so it cannot fire on a value that is drifting but still acceptable. A dashboard shows the current state to whoever is looking, which during a quiet week is nobody. A trend that takes six weeks to become a problem is invisible to both for five of them.

A report is only useful if it is built backwards from decisions. The failure mode of most recurring reports is that they enumerate available metrics, grow to eight pages, and stop being read within a quarter. Starting from the decisions the report must support — and refusing to include anything that does not feed one — keeps it to one page and keeps it read.

Three decisions cover the read tier.

Do we need more replica capacity? Answered by error budget consumed per read path, plus the fallback rate attributable to lag.

Is there a routing regression? Answered by the fallback split by cause: a rise in pinned_txn or unclassified is a code change, not a capacity problem.

Is every replica earning its cost? Answered by per-replica share of reads, with a floor check for a node receiving nothing.

Attribution is what makes the report actionable. “Fallbacks rose 40%” prompts a discussion. “Fallbacks rose 40%, entirely in pinned_txn, starting the day of release 4.2.1” prompts a fix.

What a weekly report catches that alerts and dashboards do not Replica share of reads over eight weeks. It sits near ninety percent for the first week, then drifts steadily downward. The alert threshold sits at sixty percent and is not crossed until week six, so no alert fires for five weeks. A dashboard would show the current value to anyone who looked, but nobody did. The weekly report compares each week against the previous one and flags the change in week two, four weeks before the alert. 100 % 60 % replica share of reads week → alert threshold week 2 — report flags a 4-point drop week 6 — the alert finally fires four weeks already paid for Nothing here is a failure of the alert: a threshold cannot fire on a value that is still acceptable. The report's contribution is comparison against last week, which no single-value view can provide.

Step-by-Step Resolution

Step 1 — Write the three questions before choosing metrics

markdown
## Read-tier weekly report — scope
1. Is any read path burning its freshness budget faster than sustainable?
   → owner: the team owning that read path. Decision: invest or renegotiate.
2. Is the primary carrying read work it should not be?
   → owner: application team if pinned_txn; platform if fallback_lag.
3. Is every replica earning its cost?
   → owner: platform. Decision: rebalance, resize, or remove.

Anything that does not answer one of these belongs on a dashboard.

Inline verification: every section of the generated report maps to exactly one question, and no section maps to none.

Step 2 — Budget consumed per read path

promql
# Fraction of the 30-day budget consumed during the last 7 days.
# Above 7/30 ≈ 0.233 means this week burned faster than sustainable.
(
  1 - (
    sum by (db_read_path) (rate(db_read_served_age_ms_count{db_read_within_budget="true"}[7d]))
    / sum by (db_read_path) (rate(db_read_served_age_ms_count[7d]))
  )
) / 0.005

Inline verification: the result is a per-path number; sorting descending gives the report’s first section directly.

Step 3 — Split fallbacks by cause, with a week-over-week delta

promql
# This week's fallback share by cause.
sum by (db_replica_select_reason) (
  rate(db_read_served_age_ms_count{db_replica_role="primary"}[7d]))
/ ignoring(db_replica_select_reason) group_left
  sum (rate(db_read_served_age_ms_count[7d]))

# Change against the previous week — the number that turns data into a finding.
(
  sum by (db_replica_select_reason) (rate(db_read_served_age_ms_count{db_replica_role="primary"}[7d]))
  -
  sum by (db_replica_select_reason) (rate(db_read_served_age_ms_count{db_replica_role="primary"}[7d] offset 7d))
)
/ ignoring(db_replica_select_reason) group_left
  sum (rate(db_read_served_age_ms_count[7d] offset 7d))

The offset 7d comparison is what distinguishes a report from a dashboard. A dashboard shows this week; only the delta identifies a change.

Inline verification: the causes present are drawn from the closed enumeration, and their shares sum to the total primary share.

Step 4 — Per-replica share, with a silent-node floor

promql
# Share of replica-served reads per node.
sum by (db_replica_instance) (
  rate(db_read_served_age_ms_count{db_replica_role="replica"}[7d]))
/ ignoring(db_replica_instance) group_left
  sum (rate(db_read_served_age_ms_count{db_replica_role="replica"}[7d]))

# The floor check: any node under 5 % of an even share is effectively silent.
(
  sum by (db_replica_instance) (rate(db_read_served_age_ms_count{db_replica_role="replica"}[7d]))
  / ignoring(db_replica_instance) group_left
    sum (rate(db_read_served_age_ms_count{db_replica_role="replica"}[7d]))
) < 0.05

A silent replica costs full price, contributes nothing, and — most importantly — is untested: you discover it cannot serve traffic at the moment you finally need it to. The likely causes are in why DNS round robin unbalances read replica traffic.

Inline verification: the shares sum to approximately 1, and the floor check returns no rows in a healthy week.

Step 5 — Generate and deliver it

python
#!/usr/bin/env python3
"""weekly_read_report.py — one page, three questions, run by cron on Monday."""
import requests, datetime

PROM = "http://prometheus:9090/api/v1/query"

def q(expr: str):
    r = requests.get(PROM, params={"query": expr}, timeout=30)
    r.raise_for_status()
    return r.json()["data"]["result"]

def pct(v): return f"{float(v) * 100:.1f} %"

lines = [f"# Read-tier report — week ending {datetime.date.today()}", ""]

lines += ["## 1. Budget consumed per read path", ""]
for s in sorted(q(BUDGET_EXPR), key=lambda s: -float(s["value"][1])):
    used = float(s["value"][1])
    flag = "  ⟵ burning faster than sustainable" if used > 7 / 30 else ""
    lines.append(f"- {s['metric']['db_read_path']}: {pct(used)} of the "
                 f"30-day budget{flag}")

lines += ["", "## 2. Primary reads by cause (Δ vs last week)", ""]
for s in q(FALLBACK_DELTA_EXPR):
    d = float(s["value"][1])
    arrow = "▲" if d > 0 else "▼"
    lines.append(f"- {s['metric']['db_replica_select_reason']}: "
                 f"{arrow} {abs(d) * 100:.1f} pt")

lines += ["", "## 3. Per-replica share", ""]
for s in q(REPLICA_SHARE_EXPR):
    share = float(s["value"][1])
    flag = "  ⟵ effectively silent" if share < 0.05 else ""
    lines.append(f"- {s['metric']['db_replica_instance']}: {pct(share)}{flag}")

print("\n".join(lines))

Inline verification: the script’s output is under about forty lines. If it is longer, something that does not answer one of the three questions has crept in.

Configuration Snippet

yaml
# .github/workflows/read-report.yml — generated on a schedule, delivered to
# both audiences, and archived so the trend is reconstructable later.
name: read-tier-weekly-report
on:
  schedule:
    - cron: "0 8 * * 1"      # Monday 08:00 UTC, before planning
  workflow_dispatch: {}       # and on demand, for an ad-hoc check

jobs:
  report:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: pip install requests
      - name: Generate
        run: python tools/weekly_read_report.py > report.md
      - name: Archive
        uses: actions/upload-artifact@v4
        with:
          name: read-report-${{ github.run_number }}
          path: report.md
          retention-days: 400        # a full year of trend, recoverable
      - name: Deliver to BOTH owning teams
        run: |
          # One message, both audiences: most findings need application and
          # platform together. Splitting the delivery splits the follow-up.
          curl -sX POST "$SLACK_WEBHOOK" \
            -H 'Content-Type: application/json' \
            -d "$(jq -Rs '{text: .}' < report.md)"

The 400-day artifact retention is a small detail that pays for itself the first time someone asks “when did this start”. Without archived reports the answer requires reconstructing the query against whatever retention Prometheus has, which is typically far shorter than the period over which a slow degradation develops. The archived report is a durable record at exactly the resolution the question needs.

Sending to both teams in one message is equally deliberate. A report delivered only to the platform team produces findings whose fixes live in application code; delivered only to the application team, it produces findings nobody can act on. Naming the owner next to each finding, in a channel both teams read, is what turns the report into work.

Three report sections, three owners, three decisions Three rows. The first is budget consumed per read path, owned by the team that owns that path, driving a decision to invest in capacity or renegotiate the bound. The second is primary reads split by cause, owned by the application team when the cause is transaction pinning and by the platform team when it is lag fallback, driving a code fix or a capacity change. The third is per-replica share, owned by the platform team, driving a decision to rebalance, resize or remove a node. section owner decision it drives 1 · budget consumed per path ranked; flagged above 7/30 of the 30-day budget read-path team invest in capacity, or renegotiate the bound 2 · primary reads by cause with a week-over-week delta — the delta is the finding application team if pinned_txn else platform fix the transaction scope, or add replica capacity 3 · per-replica share with a floor check for a node receiving effectively nothing platform team rebalance, resize, or remove the node A finding with no named owner is an observation. The owner column is what makes the report produce work.

Verification and Rollback

Confirm the report’s numbers match the dashboards:

bash
# Generate on demand and compare a value against Grafana for the same window.
python tools/weekly_read_report.py | tee /tmp/report.md

A discrepancy is nearly always a window mismatch — the report uses a rolling [7d] while a dashboard uses the panel’s selected range. Fix by pinning both to the same expression rather than by adjusting one to match the other.

Confirm the report is being read, which is the only measure of whether it is working:

promql
# If the report links to a dashboard, traffic to that dashboard on Mondays
# is a reasonable proxy for engagement.
sum(rate(grafana_http_request_duration_seconds_count{
  handler=~"/d/read-tier.*"}[1d]))

A report with no follow-through does not need better metrics; it needs to be shorter, or its findings need owners.

Rollback. There is nothing to roll back operationally — the report reads Prometheus and writes a message. What does need revisiting is scope creep:

markdown
## Report review (quarterly)
- Which sections produced a decision in the last quarter?
- Which produced none? → remove them.
- Which questions did we ask that the report could not answer? → add one.

Reports grow. A quarterly review that deletes sections which have not driven a decision keeps it to one page, which is what keeps it read. Deleting a section is not a loss of visibility — the metric is still on the dashboard; it is only no longer claiming weekly attention it has not earned.

Edge Cases and Gotchas

Prometheus retention shorter than the comparison window

An offset 7d comparison requires 14 days of data, and a [30d] budget calculation requires 30. If local retention is 15 days, the budget query silently returns a partial result rather than an error, and the number looks plausible while being computed over the wrong window. Verify retention covers your longest expression, and prefer a recording rule that materialises the weekly aggregate so the report reads a small series rather than raw data.

A quiet week is not a good week

A read path with almost no traffic produces a near-perfect ratio, because there were few events to fail. Ranking by budget consumed will therefore place genuinely quiet paths at the top of the healthy list, which is uninformative. Include the event count next to each ratio so a path with 400 reads all week is visibly not the same as one with 40 million.

Deploys need to appear on the timeline

A finding like “fallback rate rose 12 points” is far more actionable next to “release 4.2.1 shipped Tuesday”. If your deployment events are in Prometheus as an annotation or a counter, include the week’s deploys as a short list at the top of the report; if they are not, adding them is usually a few lines in the deploy pipeline and improves every subsequent report.

FAQ

What should a weekly read-tier report actually contain?

Enough to answer three questions and nothing else: is any read path burning its error budget faster than sustainable, is the primary carrying read work it should not be, and is every replica pulling its weight. Each has an owner and a decision attached. Metrics that do not feed one of those questions belong on a dashboard, not in a recurring report.

Why weekly rather than daily or monthly?

Weekly matches the cadence at which the underlying quantities change and at which teams can act. Daily is noise — a single batch window moves the numbers and nobody can respond before the next report. Monthly is too late for a trend that started in week one to be caught before it consumes the budget. Weekly also aligns with most planning cycles, so the conclusions can become work.

Should the report include incidents?

Only as an explanation for a budget number, never as the main content. Incidents already have their own review process. The value of an SLO report is that it captures the slow degradations that never trigger an incident — a replica share drifting down over six weeks, a fallback rate creeping up after a release — which is exactly what incident reviews cannot see.

Who should receive it?

The team that owns the read path and the team that owns the databases, in the same message, because most findings need both. Sending it only to the database team produces recommendations the application team must implement; sending it only to the application team produces findings nobody can act on. One report, both audiences, with the owner named next to each finding.

← Back to Tracing & SLO Reporting for Replica Reads