Canary Rollout Plan for a New Read/Write Split

Problem statement: the new routing rules pass every test in CI, and you have to put them in front of real traffic — where the statement shapes you did not think of live.

A routing change is unusually well suited to canarying, because its failure modes are fast, measurable and reversible. It is also unusually easy to canary badly, by watching only the error rate and declaring success on a change that quietly stopped using the replicas. This page defines the slice, the criteria, and the automation. It completes the verification sequence begun in testing read/write splitting correctness.

Symptom Identification

You need a structured canary — rather than a deploy and a watchful eye — when:

  • The change alters rule order, which can shadow an existing rule and silently move a whole statement class.
  • The change adds a broad pattern such as ^WITH or ^SELECT, which can absorb statements an existing narrow rule was handling.
  • The rule set is being replaced wholesale, for example when migrating between proxies.
  • A previous routing deploy caused an incident, and the current process is “deploy and watch Grafana”.
  • Nobody can state what the current replica share of reads is, which means a regression in it would not be noticed.

The last one is a precondition rather than a symptom: without a baseline replica share, the most important canary criterion cannot be evaluated at all.

Root Cause Analysis

A routing change has two independent ways to be wrong, and they pull in opposite directions. Too permissive and writes reach replicas — loud, immediate, unambiguous. Too restrictive and reads pile onto the primary — silent, gradual, and indistinguishable from correct behaviour unless you are measuring share.

A canary watching only errors is therefore biased: it accepts any change that moves toward the restrictive end, including the degenerate change that routes everything to the primary. Every criterion set needs a floor as well as a ceiling.

Rare shapes are rare in time, not just in volume. The statements that break classification — a monthly reconciliation query, an admin path, a data-modifying CTE in a rarely-hit endpoint — do not appear proportionally in a random 5% sample over thirty minutes. They appear once a day, or once a week, entirely. A canary that samples by volume and runs briefly will systematically miss them, and then the full rollout hits them at 100%.

This makes shape coverage a better selection criterion than volume. A low-traffic internal service that issues thirty distinct statement shapes tests the rule set far more thoroughly than 5% of a high-volume endpoint issuing three. Rank candidate slices by distinct digest count, not by requests per second.

Reverting must be cheap, or it will not happen. If reverting means a deploy, people will hesitate and reason about whether the anomaly is real. If reverting is a single runtime command wired to a threshold, it happens in seconds and the discussion occurs afterwards, with the system already safe.

Canary slice selection: volume versus statement-shape coverage Two candidate canary slices side by side. The first is five percent of the busiest endpoint: very high request rate but only three distinct statement shapes, all of them simple selects. The second is one low-traffic internal service: a small fraction of the request rate but thirty distinct shapes including locking selects, data-modifying CTEs and procedure calls. A note observes that the second slice tests the rule set far more thoroughly despite carrying far less traffic. slice A — 5 % of the busiest endpoint ≈ 4 000 requests/s distinct statement shapes exercised 3 — all plain SELECT passes trivially; proves almost nothing slice B — one internal service ≈ 40 r/s distinct statement shapes exercised 30 shapes thick = the awkward ones exercises locking selects, CTE writes, procedure calls Slice B carries one percent of slice A's traffic and tests ten times as much of the rule set. Choose the widest coverage you can afford to break, then widen by volume afterwards. Rank candidates with a single query: distinct digests per service over the last week.

Step-by-Step Resolution

Step 1 — Rank candidate slices by shape coverage

sql
-- ProxySQL: distinct statement shapes per client user or schema over the week.
-- Whichever dimension identifies your services is the one to group by.
SELECT username,
       count(DISTINCT digest)      AS distinct_shapes,
       sum(count_star)             AS calls
  FROM stats_mysql_query_digest
 GROUP BY username
 ORDER BY distinct_shapes DESC;
sql
-- PostgreSQL equivalent, if routing is per application_name.
SELECT application_name, count(DISTINCT queryid) AS distinct_shapes
  FROM pg_stat_statements s
  JOIN pg_stat_activity a ON true
 GROUP BY application_name ORDER BY 2 DESC;

Inline verification: you have a ranked list, and the top entry by shape count is not the top entry by call count — if it is, either dimension will do.

Step 2 — Write the criteria down before starting

yaml
# rollout/routing-canary.yml — decided in advance, not during the window.
canary:
  slice: internal-reporting-service      # widest shape coverage
  share: 1.0                             # this whole service, not a fraction
  duration: 24h                          # a full cycle including batch

promote_if:
  # SAFETY — a ceiling. Any breach is a defect, not a degradation.
  read_only_txn_errors: 0                # SQLSTATE 25006, absolute zero

  # COMPLETENESS — a FLOOR. Without this, routing everything to the
  # primary would satisfy every other criterion perfectly.
  replica_read_share_delta_pct: ">= -2"

  # PERFORMANCE — a ceiling, generous, because it is the least important.
  p99_read_latency_delta_pct: "< 10"

  # COVERAGE — did the canary actually exercise anything?
  distinct_shapes_seen: ">= 25"

revert:
  automatic_on: [read_only_txn_errors]   # unambiguous; revert without asking
  human_review_on: [replica_read_share_delta_pct, p99_read_latency_delta_pct]

Inline verification: every criterion has a number, and each is labelled as a ceiling or a floor. A criterion without a direction cannot be evaluated.

Step 3 — Route the slice through the new rules

sql
-- ProxySQL: route by user. Give the canary service its own user pointing at
-- a distinct rule set, so no other traffic is affected.
INSERT INTO mysql_users (username, password, default_hostgroup,
                         transaction_persistent, active)
VALUES ('app_canary', 'secret', 10, 1, 1);

-- Rules that apply only to the canary user (rule_id range reserved for it).
INSERT INTO mysql_query_rules
  (rule_id, active, username, match_digest, destination_hostgroup, apply)
VALUES
  (1010, 1, 'app_canary', '^SELECT.*FOR (UPDATE|SHARE)',              10, 1),
  (1020, 1, 'app_canary', '^WITH .*\\b(INSERT|UPDATE|DELETE)\\b',     10, 1),
  (1040, 1, 'app_canary', '^(WITH|SELECT|SHOW|EXPLAIN)',              20, 1),
  (1099, 1, 'app_canary', '.*',                                       10, 1);

LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

Scoping the new rules to a dedicated user is what makes the blast radius exactly the canary slice, and it makes the revert trivial: deactivate the user’s rules and it falls back to the existing set.

Inline verification: SELECT username, count(*) FROM stats_mysql_query_digest GROUP BY username shows the canary user appearing, and only the canary user’s digests hitting the new rule ids.

Step 4 — Automate the revert on the safety criterion

yaml
# The safety revert is a Prometheus alert wired to a webhook, not a runbook
# step. Every second of write leakage is user-visible errors.
groups:
  - name: routing-canary
    rules:
      - alert: CanaryWriteLeakage
        expr: sum(rate(app_db_errors_total{sqlstate="25006", slice="canary"}[1m])) > 0
        for: 0m
        labels: {severity: critical, action: auto_revert}
        annotations:
          webhook: "http://rollout-controller/revert?rollout=routing-canary"
bash
#!/usr/bin/env bash
# The revert itself: one command, no deploy, seconds to take effect.
mysql -h127.0.0.1 -P6032 -uadmin -padmin -e "
  UPDATE mysql_query_rules SET active = 0 WHERE rule_id BETWEEN 1000 AND 1099;
  LOAD MYSQL QUERY RULES TO RUNTIME;
  SAVE MYSQL QUERY RULES TO DISK;"

Inline verification: trigger the revert manually once, before the canary begins, and confirm the canary user’s traffic returns to the old routing within seconds.

Step 5 — Widen in stages, and keep the audit

yaml
stages:
  - {slice: internal-reporting-service, duration: 24h}   # widest shapes
  - {slice: api-tier, share: 0.05, duration: 12h}        # volume, low share
  - {slice: api-tier, share: 0.50, duration: 12h}
  - {slice: all, share: 1.0}
post_rollout:
  keep_running: [digest_audit_weekly, replica_share_dashboard]

The digest audit does not stop when the rollout closes. Every release adds statement shapes, so a rule set that was complete at rollout is a rule set that was complete once — the standing audit is described in auditing ProxySQL query digests for misrouted statements.

Inline verification: after the final stage, the weekly audit still runs and its first post-rollout report shows no new safety defects.

Configuration Snippet

promql
# The four canary criteria as queries. Graph all four on one dashboard so the
# promote/revert decision is a glance rather than an investigation.

# 1. SAFETY — write leakage on the canary slice. Must be flat zero.
sum(rate(app_db_errors_total{sqlstate="25006", slice="canary"}[5m]))

# 2. COMPLETENESS — replica share of reads, canary vs baseline.
(
  sum(rate(db_reads_total{role="replica", slice="canary"}[5m]))
    / sum(rate(db_reads_total{slice="canary"}[5m]))
)
-
(
  sum(rate(db_reads_total{role="replica", slice="baseline"}[5m]))
    / sum(rate(db_reads_total{slice="baseline"}[5m]))
)

# 3. PERFORMANCE — p99 read latency delta.
histogram_quantile(0.99, sum by (le) (rate(db_query_duration_ms_bucket{slice="canary"}[5m])))
  / histogram_quantile(0.99, sum by (le) (rate(db_query_duration_ms_bucket{slice="baseline"}[5m])))

# 4. COVERAGE — did the canary see enough shapes to mean anything?
count(count by (digest) (rate(db_query_duration_ms_count{slice="canary"}[24h]) > 0))

Criterion 4 is the one that gets omitted and the one that decides whether the other three mean anything. A canary that ran for a day and saw four statement shapes has not tested the rule set; promoting on the strength of it is promoting on a sample size of four. Treat a low shape count as a failed canary — not because anything broke, but because nothing was exercised.

Why the canary needs a floor as well as ceilings Four criteria shown as gauges. A hypothetical change that routes every statement to the primary is evaluated against each. It passes the safety ceiling with zero errors, passes the latency ceiling comfortably, and passes coverage. It fails the completeness floor badly, because replica share collapses. The note observes that without the completeness floor this degenerate change would have been promoted. evaluating a change that routes everything to the primary safety (ceiling) write leakage = 0 PASS completeness (floor) replica share ≥ baseline − 2 pt FAIL — far below the floor latency (ceiling) p99 delta < 10 % PASS coverage (floor) ≥ 25 distinct shapes PASS Three of four criteria pass. The change is still wrong. A canary built only from ceilings promotes any change that becomes more conservative — including abandoning the replicas entirely. The completeness floor is the criterion that makes the gate meaningful, and it is the one most often missing. Pair every ceiling with the floor that stops the trivial way of satisfying it.

Verification and Rollback

Confirm the canary slice is genuinely isolated before trusting any of its numbers:

sql
-- Only the canary user should be hitting the new rule ids.
SELECT r.rule_id, r.username, s.hits
  FROM runtime_mysql_query_rules r
  JOIN stats_mysql_query_rules s USING (rule_id)
 WHERE r.rule_id BETWEEN 1000 AND 1099
 ORDER BY r.rule_id;

-- And no non-canary traffic should have changed its destination.
SELECT username, hostgroup, sum(count_star)
  FROM stats_mysql_query_digest
 GROUP BY username, hostgroup ORDER BY username;

Confirm the coverage criterion was actually met:

sql
SELECT count(DISTINCT digest) AS shapes_seen
  FROM stats_mysql_query_digest WHERE username = 'app_canary';

If this is below the threshold, do not promote — extend the window instead. A canary that ran the full duration but saw few shapes has produced no evidence, and promoting on it is the same as promoting with no canary at all.

Rollback. Two levels, both fast:

sql
-- Level 1: disable the canary rules. Traffic reverts to the existing rule set.
UPDATE mysql_query_rules SET active = 0 WHERE rule_id BETWEEN 1000 AND 1099;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- Level 2: if the canary user itself is implicated, remove it and let the
-- service fall back to the standard user.
UPDATE mysql_users SET active = 0 WHERE username = 'app_canary';
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Level 1 takes effect for new statements immediately; existing transactions complete under the old routing, which is correct. Note that connections already established by the canary user keep their host-group assignment until they are recycled, so pair the revert with a short connection lifetime if you need the reversal to be instantaneous rather than merely prompt.

Edge Cases and Gotchas

The canary slice may not represent the shapes you are changing

If the rule change targets data-modifying CTEs and the canary slice never issues one, the canary is silent about the thing you changed. Before starting, check that the slice actually produces statements matching the new or reordered rules — stats_mysql_query_digest filtered to the canary user, matched against the new patterns. A canary that cannot exercise the change is theatre.

Baseline drift over a 24-hour window

Comparing canary metrics against a baseline measured at a different time confuses diurnal variation with the effect of the change. Measure the baseline concurrently — the same metrics from the non-canary traffic during the same window — rather than against yesterday’s numbers. Every criterion above is expressed as a canary-versus-concurrent-baseline delta for this reason.

Reverting does not undo the primary’s accumulated load

If the change caused a completeness regression, the primary has been carrying extra read load for the duration of the canary. Reverting stops the accumulation but the primary may still be recovering — elevated cache pressure, a longer autovacuum backlog. Watch the primary for a while after reverting rather than assuming the state returns immediately, particularly if the canary ran through a peak.

FAQ

Why is error rate not enough as a canary criterion?

Because a routing change can be entirely safe and entirely useless at the same time. Sending every statement to the primary produces zero routing errors and a perfect latency profile while destroying the replica tier’s value. The canary must assert a replica-share floor alongside the error ceiling, so a change that buys safety by abandoning completeness fails the gate.

How should the canary slice be selected?

By statement-shape coverage rather than by volume where you can. Routing 5% of the busiest endpoint may exercise three statement shapes; routing one low-traffic internal service may exercise thirty, including the awkward ones. Rank candidate slices by how many distinct digests they produce and choose the widest coverage you can afford to break.

How long should a routing canary run?

At least one full traffic cycle, which for most services means a business day including the overnight batch window. Rare statement shapes are rare in time as well as in volume — a monthly report, a nightly reconciliation, an admin path used twice a week. A thirty-minute canary systematically misses exactly the shapes that break routing.

Should the revert be automatic?

Yes for the safety criterion, because write leakage is unambiguous and every second of it produces user-visible errors. Completeness and latency regressions can be human-reviewed, since they are degradations rather than failures and a hasty revert may lose useful signal. Encode which criteria are automatic in the rollout config so the distinction is decided in advance rather than during the event.

← Back to Testing Read/Write Splitting Correctness