Auditing ProxySQL Query Digests for Misrouted Statements

Problem statement: the routing rules pass every test you have written, and you want to know which statements are actually being misrouted in production right now — including the shapes nobody thought to test.

stats_mysql_query_digest records every statement shape ProxySQL has seen, how often, and where it went. That makes it the one place a completeness defect is visible, and it is chronically underused because the counters are cumulative and therefore look unactionable at first glance. This page turns the table into a standing audit. It implements the completeness half of testing read/write splitting correctness.

Symptom Identification

Run the audit when you see any of these, and on a schedule regardless:

  • The write host group’s query count is a large fraction of the total despite an overwhelmingly read-heavy workload.
  • A rule was added during an incident and nobody is certain whether it was saved to disk.
  • A release changed the ORM or added a new query path, and no one has checked what shapes it produces.
  • Read-only errors appear intermittently on a statement nobody can identify from the application logs.
  • The rule set has grown past a dozen rules and their interaction is no longer obvious to anyone.

The last case is the strongest signal. Rule sets accumulate, and every added rule can shadow an existing one. Beyond about ten rules the ordering is no longer reasoned about correctly by anybody, and the audit becomes the only way to know what the set actually does.

Root Cause Analysis

ProxySQL routes by first match on an ordered rule list. Each incoming statement is normalised into a digest — literals replaced by placeholders — and tested against runtime_mysql_query_rules in ascending rule_id. The first rule whose match_digest or match_pattern matches, and which has apply=1, decides the destination host group. Everything after it is skipped.

Two failure modes follow directly.

Shadowing. A broad rule placed above a narrow one absorbs the narrow one’s statements. The narrow rule then reports zero hits and its intent is silently unimplemented. The classic instance is a general ^SELECT rule above a SELECT ... FOR UPDATE rule: locking reads go to a replica and fail, while the rule that would have prevented it sits below, never firing.

Cumulative counters hide time. The columns in stats_mysql_query_digest accumulate since the last reset. A statement misrouted for ten minutes during a deploy three weeks ago is indistinguishable, in a raw query, from one being misrouted continuously right now. Differencing two snapshots is what converts a lifetime total into a rate.

A third failure mode is operational rather than logical. ProxySQL has three configuration layers: the runtime set that governs behaviour, the in-memory table you edit, and the on-disk table that survives a restart. A change applied with LOAD MYSQL QUERY RULES TO RUNTIME but never SAVEd works perfectly until the process restarts, then vanishes — with no deploy, no change record, and a routing regression that appears from nowhere.

Runtime, memory and disk: where an unsaved rule change is lost Three stacked layers: runtime governs behaviour, memory is the editable table, and disk survives restart. An edit flows into memory, then LOAD to runtime makes it take effect. Without a SAVE to disk, the disk layer still holds the previous rule set, so on restart the runtime is repopulated from disk and the change disappears with no deployment or change record to explain it. RUNTIME governs every routing decision MEMORY the table you edit DISK survives a restart LOAD … TO RUNTIME SAVE … TO DISK — the step that is skipped what happens at restart runtime is repopulated from DISK, not from memory the unsaved rule disappears no deploy, no change record The audit's last check compares runtime against disk, because this failure is invisible until the process restarts — often weeks later, during an unrelated incident, when nobody connects the two events. Treat an unsaved runtime change as an open incident, not as a housekeeping item.

Step-by-Step Resolution

Step 1 — Snapshot the digest table

sql
-- Run against the ProxySQL admin interface (port 6032).
-- The _reset variant returns the current values AND zeroes the counters,
-- which turns each snapshot into a clean per-interval measurement.
CREATE TABLE IF NOT EXISTS digest_history (
  captured_at   INT,
  hostgroup     INT,
  digest        VARCHAR,
  digest_text   VARCHAR,
  count_star    INT,
  sum_time      INT
);

INSERT INTO digest_history
SELECT UNIX_TIMESTAMP(), hostgroup, digest, digest_text, count_star, sum_time
  FROM stats_mysql_query_digest_reset;

Schedule this every fifteen minutes. Using the _reset view means each row already describes one interval, so no differencing arithmetic is needed later.

Inline verification: SELECT count(*), max(captured_at) FROM digest_history grows on each run and the timestamp advances.

Step 2 — Find writes on the read host group

sql
-- Safety defects first. Anything here is producing errors in production.
SELECT digest_text, hostgroup, sum(count_star) AS calls
  FROM digest_history
 WHERE captured_at > UNIX_TIMESTAMP() - 86400
   AND hostgroup = 20                                     -- read host group
   AND (digest_text LIKE 'INSERT%' OR digest_text LIKE 'UPDATE%'
        OR digest_text LIKE 'DELETE%' OR digest_text LIKE 'REPLACE%'
        OR digest_text LIKE 'WITH%DELETE%' OR digest_text LIKE 'WITH%UPDATE%'
        OR digest_text LIKE '%FOR UPDATE%' OR digest_text LIKE '%FOR SHARE%')
 GROUP BY digest_text, hostgroup
 ORDER BY calls DESC;

Inline verification: an empty result is the expected steady state. Any row is a safety defect and takes priority over everything else in this audit.

Step 3 — Find reads stuck on the write host group

sql
-- Completeness defects, ranked by time rather than by call count so that
-- health checks and probes do not dominate the list.
SELECT digest_text,
       sum(count_star)                             AS calls,
       sum(sum_time) / 1000                        AS total_ms,
       round(100.0 * sum(sum_time) /
             (SELECT sum(sum_time) FROM digest_history
               WHERE hostgroup = 10
                 AND captured_at > UNIX_TIMESTAMP() - 86400), 1) AS pct_primary_time
  FROM digest_history
 WHERE captured_at > UNIX_TIMESTAMP() - 86400
   AND hostgroup = 10                                     -- write host group
   AND digest_text LIKE 'SELECT%'
   AND digest_text NOT LIKE '%FOR UPDATE%'
   AND digest_text NOT LIKE '%FOR SHARE%'
   AND digest_text NOT LIKE 'SELECT ?'                    -- health checks
 GROUP BY digest_text
 ORDER BY total_ms DESC
 LIMIT 25;

Inline verification: the output is a ranked list; the top few entries should account for most of pct_primary_time, giving you a short work queue rather than a long one.

Step 4 — Find rules that never fire

sql
-- ProxySQL counts rule hits in runtime_mysql_query_rules.hits.
SELECT rule_id, active, match_digest, match_pattern,
       destination_hostgroup, apply, hits
  FROM stats_mysql_query_rules s
  JOIN runtime_mysql_query_rules r USING (rule_id)
 ORDER BY rule_id;

For every rule with hits = 0, test whether an earlier rule is shadowing it. Take a statement the rule was written for, and check which rule matches it first:

sql
-- Does anything above rule 30 also match this shape?
SELECT rule_id, match_digest
  FROM runtime_mysql_query_rules
 WHERE rule_id < 30 AND active = 1
 ORDER BY rule_id;
-- Then evaluate each pattern against the statement by hand or in a script.

Inline verification: every zero-hit rule is classified as either shadowed (a defect — fix the order) or genuinely unused (safe to remove, with a note explaining why).

Step 5 — Verify runtime matches disk

sql
-- Any difference here is a change that will revert at the next restart.
SELECT 'runtime_only' AS side, rule_id, match_digest, destination_hostgroup
  FROM runtime_mysql_query_rules
 WHERE rule_id NOT IN (SELECT rule_id FROM disk.mysql_query_rules)
UNION ALL
SELECT 'disk_only', rule_id, match_digest, destination_hostgroup
  FROM disk.mysql_query_rules
 WHERE rule_id NOT IN (SELECT rule_id FROM runtime_mysql_query_rules);

Inline verification: an empty result. If not, run SAVE MYSQL QUERY RULES TO DISK after confirming the runtime set is the one you want — and find out who changed it and why.

Configuration Snippet

bash
#!/usr/bin/env bash
# /usr/local/bin/proxysql-routing-audit.sh — the whole audit as one job.
set -euo pipefail
PSQLADMIN=(mysql -h127.0.0.1 -P6032 -uadmin -padmin --batch --raw -N)

echo "=== 1. writes on the read host group (safety defects) ==="
"${PSQLADMIN[@]}" -e "
  SELECT digest_text, sum(count_star)
    FROM digest_history
   WHERE captured_at > UNIX_TIMESTAMP() - 86400 AND hostgroup = 20
     AND (digest_text LIKE 'INSERT%' OR digest_text LIKE 'UPDATE%'
          OR digest_text LIKE 'DELETE%' OR digest_text LIKE '%FOR UPDATE%')
   GROUP BY digest_text ORDER BY 2 DESC;" | tee /tmp/audit-safety.txt

# A safety defect is a paging condition, not a report line.
if [[ -s /tmp/audit-safety.txt ]]; then
  echo "SAFETY DEFECT: write-shaped digests reached the read host group" >&2
  exit 2
fi

echo "=== 2. reads on the write host group (completeness defects) ==="
"${PSQLADMIN[@]}" -e "
  SELECT digest_text, sum(count_star), sum(sum_time)/1000
    FROM digest_history
   WHERE captured_at > UNIX_TIMESTAMP() - 86400 AND hostgroup = 10
     AND digest_text LIKE 'SELECT%' AND digest_text NOT LIKE '%FOR UPDATE%'
   GROUP BY digest_text ORDER BY 3 DESC LIMIT 25;"

echo "=== 3. rules that never fired ==="
"${PSQLADMIN[@]}" -e "
  SELECT rule_id, match_digest, hits FROM stats_mysql_query_rules
    JOIN runtime_mysql_query_rules USING (rule_id)
   WHERE hits = 0 ORDER BY rule_id;"

echo "=== 4. runtime vs disk drift ==="
"${PSQLADMIN[@]}" -e "
  SELECT count(*) FROM runtime_mysql_query_rules
   WHERE rule_id NOT IN (SELECT rule_id FROM disk.mysql_query_rules);"

The exit code is doing real work here. Section 1 exits non-zero on any finding, so the job can be wired to a pager or a CI gate; sections 2 to 4 are reported rather than enforced, because they are work queues rather than incidents. Splitting the two severities in the tool itself is what stops the whole audit being ignored when the completeness list is long.

Rule shadowing, seen through the hit counters A rule list with hit counts beside each entry. A general select rule with a very high hit count sits above a locking-select rule with zero hits. Statements matching FOR UPDATE are absorbed by the general rule and routed to the read host group, where they fail. The zero hit count is the observable signature, and moving the locking rule above the general one restores the intended routing. runtime_mysql_query_rules, in evaluation order hits rule 10 · ^WITH .*(INSERT|UPDATE|DELETE) → hostgroup 10 4 102 rule 20 · ^(SELECT|SHOW|EXPLAIN) → hostgroup 20 18 447 219 matches first rule 30 · ^SELECT .*FOR (UPDATE|SHARE) → hostgroup 10 0 never reached SELECT … FOR UPDATE matches rule 20 first and is sent to a replica, where it fails with a read-only error. Rule 30 was written to prevent exactly that, and its zero hit count is the only visible evidence that it does not. Fix: move the narrow rule above the broad one. Always audit hits after adding a rule, not only after an incident.

Verification and Rollback

Confirm a rule reorder had the intended effect:

sql
-- Reset the counters, let traffic run for a few minutes, then re-check.
SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;   -- resets
-- ... wait 5 minutes under representative traffic ...
SELECT rule_id, match_digest, hits
  FROM stats_mysql_query_rules JOIN runtime_mysql_query_rules USING (rule_id)
 ORDER BY rule_id;

The previously-shadowed rule should now show a non-zero hit count, and the broad rule’s count should have fallen by approximately the same amount. If the narrow rule is still at zero, something above it is also matching — check every rule with a lower id, not only the one you moved.

Confirm the change is durable:

sql
SAVE MYSQL QUERY RULES TO DISK;
-- Then prove it: the audit's drift check must return zero.
SELECT count(*) FROM runtime_mysql_query_rules
 WHERE rule_id NOT IN (SELECT rule_id FROM disk.mysql_query_rules);

Rollback. ProxySQL keeps the previous configuration on disk until you overwrite it, so the safe reversal is to reload from disk before saving:

sql
-- Discard an unsaved runtime change and return to the last saved state.
LOAD MYSQL QUERY RULES FROM DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;

If the change was already saved, roll back from your configuration repository — which is the argument for exporting the ordered runtime rule set as a versioned artefact rather than editing rules in place. Applying a rule set by replaying an exported ordered list makes rollback a matter of replaying the previous export, and makes the diff between two versions reviewable.

Edge Cases and Gotchas

match_digest and match_pattern are not interchangeable

match_digest tests the normalised digest, where literals have become placeholders; match_pattern tests the raw statement text. A pattern written for one and applied to the other frequently matches nothing — for example a match_digest looking for a specific literal value can never fire, because that literal was replaced by ? before matching. A zero-hit rule with an obviously correct-looking pattern is usually this mistake.

Digest normalisation merges statements you may want to separate

Two statements that differ only in a literal share a digest, so a query with a literal LIMIT 10 and one with LIMIT 10000 are one row in the audit. If routing should differ between them — a small lookup to a replica, a huge export to a dedicated node — digest-level rules cannot express that, and the distinction has to be made with a routing hint or a separate user.

Resetting counters loses history unless you snapshot first

stats_mysql_query_digest_reset returns the values and zeroes them in one operation, which is exactly what you want for periodic snapshots and exactly wrong if someone runs it ad hoc during an investigation. Restrict its use to the scheduled job, and use the plain stats_mysql_query_digest view for interactive queries so an investigation does not destroy the interval another engineer is measuring.

FAQ

Why snapshot the digest table instead of querying it directly?

Because the counters are cumulative since the last reset, so a query against them describes the whole lifetime rather than the last hour. A digest that was misrouted for ten minutes three weeks ago looks identical to one being misrouted right now. Snapshotting into a history table lets you difference two points in time, which is what makes the audit actionable.

What does a rule with zero hits mean?

One of three things: it is shadowed by an earlier rule that matches the same statements, its pattern no longer matches anything your application issues, or the shape it protects genuinely never occurs. The first is a defect, because it usually means the earlier rule is routing something to the wrong place. Always check for shadowing before concluding a rule is merely obsolete.

How do I find which rule matched a given digest?

ProxySQL does not record the matching rule id in the digest table. The reliable method is to evaluate the rules against the digest text in order yourself, replicating the first-match-wins order from runtime_mysql_query_rules. For an individual statement you can also read the hostgroup column in stats_mysql_query_digest to see where it went, then work backwards through the ordered rules.

Should the audit run against production or a replica of the stats?

Against production, because that is the only place the real statement mix exists. The queries are cheap: they read ProxySQL’s in-memory admin tables rather than touching the databases at all. Schedule the snapshot at a fixed interval and run the analysis against the history table, so the production admin interface sees one small query per interval.

← Back to Testing Read/Write Splitting Correctness