Routing Reads and Writes with ProxySQL Query Rules

This guide is part of ProxySQL & MySQL Connection Multiplexing. Read/write splitting is the reason many teams put ProxySQL in front of MySQL, and it is also where the first correctness incident usually comes from. A rule set written as “send SELECT to the readers” is one regular expression away from sending SELECT ... FOR UPDATE to a replica, or from routing the read that follows a write to a replica that has not received it yet:

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
# or, worse, no error at all:
INSERT INTO orders ... ;                      -- writer
SELECT * FROM orders WHERE id = LAST_ID;      -- replica, 40 ms behind: zero rows

The objective is a rule set that routes by intent rather than by prefix, keeps transactions and read-your-writes on the writer, degrades safely when a replica lags, and can be verified from ProxySQL’s own statistics rather than by hope.

Rapid incident diagnosis

Read the rules in effect. Runtime rules are what matter, not the on-disk table:

SELECT rule_id, active, match_digest, match_pattern, destination_hostgroup, apply, flagIN, flagOUT
FROM runtime_mysql_query_rules ORDER BY rule_id;

Rules are evaluated in rule_id order; apply = 1 stops evaluation at the first match. A broad ^SELECT rule with apply = 1 at a low rule_id shadows every more specific rule below it, which is the most common cause of mis-routing.

See where queries actually went. The digest table records the hostgroup each statement was sent to:

SELECT hostgroup, count_star, SUBSTR(digest_text, 1, 70) AS query
FROM stats_mysql_query_digest
ORDER BY count_star DESC LIMIT 20;

Writes appearing against a reader hostgroup, or locking reads against a reader, identify the rule that needs narrowing.

Check the transaction setting. transaction_persistent = 1 on the user keeps every statement of a transaction on the hostgroup where it started, which is what prevents a read inside a write transaction from being routed away:

SELECT username, default_hostgroup, transaction_persistent, max_connections FROM runtime_mysql_users;

Check replica lag handling. A replica beyond max_replication_lag is shunned, so lag shows as traffic concentrating on the remaining readers rather than as stale data — provided monitoring is configured:

SELECT hostgroup, srv_host, status, max_replication_lag FROM runtime_mysql_servers;
SELECT * FROM monitor.mysql_server_replication_lag_log ORDER BY time_start_us DESC LIMIT 10;

Mathematical sizing or parameter formula

Routing changes how load divides, so the pools on each side must be sized from the split rather than from the total.

writer_pool  = ceil( write_tps × mean_write_seconds ÷ 0.7 )
reader_pool  = ceil( read_tps  × mean_read_seconds  ÷ 0.7 ) ÷ live_replicas

A service at 2,400 statements per second with an 85 % read share, writes averaging 9 ms and reads 3 ms:

writes: 360/s × 0.009 = 3.2 busy   → writer default_pool_size = 5
reads: 2,040/s × 0.003 = 6.1 busy  → 9 across readers, so 5 per replica with two replicas

Size the readers for one replica fewer than you have. When a replica is shunned for lag or maintenance, its share moves to the survivors:

reader_pool_per_replica ≥ ceil( read_busy ÷ (live_replicas − 1) ÷ 0.7 )

With two replicas that means each pool must be able to carry the whole read load: ceil(6.1 ÷ 1 ÷ 0.7) = 9.

Lag threshold. max_replication_lag is measured in seconds and should be set from what the application can tolerate, not from what the replicas usually achieve:

max_replication_lag ≈ acceptable_staleness_seconds

A value of 1 or 2 seconds is typical; setting it to 0 disables the check entirely, which is the opposite of what the name suggests.

Weights. backend_weight divides read traffic proportionally. Equal weights suit identical replicas; a replica also serving analytics should get a lower weight so interactive reads are not queued behind long scans.

Exact remediation & configuration

Order rules from specific to general, and keep the set small enough to read:

DELETE FROM mysql_query_rules;

-- 1. Anything that must see its own writes, or takes locks, goes to the writer.
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply, comment)
VALUES
  (100, 1, '^SELECT .* FOR UPDATE$',        10, 1, 'locking read'),
  (101, 1, '^SELECT .* LOCK IN SHARE MODE$',10, 1, 'locking read'),
  (102, 1, '^SELECT +GET_LOCK',             10, 1, 'named lock'),
  (103, 1, '^SELECT .* FROM +outbox',       10, 1, 'read-your-writes table');

-- 2. Driver housekeeping that is safe anywhere and should not pin multiplexing.
INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex, apply, comment)
VALUES
  (150, 1, '^SELECT @@session\\.transaction_read_only', 1, 0, 'driver probe');

-- 3. Everything else that reads goes to the replicas.
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply, comment)
VALUES
  (200, 1, '^SELECT',  20, 1, 'plain reads'),
  (201, 1, '^SHOW ',   20, 1, 'metadata reads');

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

Keep transactions whole. transaction_persistent = 1 means a transaction that began on the writer stays there even when rule 200 would send its reads elsewhere:

UPDATE mysql_users SET transaction_persistent = 1 WHERE username = 'orders_app';
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Set the lag guard and weights on the servers:

UPDATE mysql_servers SET max_replication_lag = 2 WHERE hostgroup_id = 20;
UPDATE mysql_servers SET weight = 500 WHERE hostname = 'mysql-replica-b.internal';  -- also runs analytics
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Give the application an explicit escape hatch. Some reads must be fresh even outside a transaction — a redirect after a write, a confirmation page. Rather than trying to detect them with patterns, let the application say so with a query comment and match on it:

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES (90, 1, '/\\*\\s*route:writer\\s*\\*/', 10, 1, 'explicit writer hint');
LOAD MYSQL QUERY RULES TO RUNTIME;

cur.execute("/* route:writer */ SELECT status FROM orders WHERE id = %s", (order_id,))

Note that this rule matches match_pattern — the raw query text — rather than match_digest, because ProxySQL strips comments when computing the digest.

Use mysql_replication_hostgroups so ProxySQL moves servers between the writer and reader hostgroups automatically based on read_only, instead of a rule set that assumes a fixed topology:

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES (10, 20, 'read_only', 'orders');
LOAD MYSQL SERVERS TO RUNTIME;

Verifying routing before and after release

Routing rules are code, and they deserve the same treatment: a test that proves intent, and a check that catches drift.

Assert the destination for representative statements. ProxySQL can explain its own decision, which makes a rule set testable without sending real traffic:

mysql -h 127.0.0.1 -P 6032 -u admin -p -e "
  SELECT rule_id, destination_hostgroup, match_digest
  FROM runtime_mysql_query_rules
  WHERE active = 1 ORDER BY rule_id;"

For end-to-end proof, run each representative statement through the proxy and read the digest afterwards:

for q in "SELECT id FROM orders WHERE id=1" \
         "SELECT id FROM orders WHERE id=1 FOR UPDATE" \
         "/* route:writer */ SELECT status FROM orders WHERE id=1" \
         "UPDATE orders SET status='x' WHERE id=0"; do
  mysql -h proxysql.internal -P 6033 -u orders_app -p"$PW" orders -e "$q" >/dev/null 2>&1
done

mysql -h 127.0.0.1 -P 6032 -u admin -p -e "
  SELECT hostgroup, SUBSTR(digest_text,1,60) FROM stats_mysql_query_digest
  ORDER BY last_seen DESC LIMIT 10;"

The locking read, the hinted read and the update must all show hostgroup 10; the plain read must show 20.

Prove read-your-writes. Inside a transaction, write and then read back, several hundred times:

BEGIN;
INSERT INTO probe(id, at) VALUES (UUID(), NOW());
SELECT COUNT(*) FROM probe WHERE id = LAST_INSERT_ID();   -- must never be 0
COMMIT;

Prove the lag guard. On a staging replica, stop replication and generate writes:

STOP REPLICA;   -- on the replica

Within a couple of monitor intervals the replica’s status must become SHUNNED in runtime_mysql_servers, and its select_cnt in stats_mysql_connection_pool must stop increasing. START REPLICA restores it.

Catch drift in CI. Export the runtime rules and compare them with the committed definition on every deploy; a rule added by hand during an incident and never written down is the usual source of surprise routing months later.

Frequently Asked Questions

Related

• ProxySQL & MySQL Connection Multiplexing — the parent guide to hostgroups and multiplexing.
• Tuning ProxySQL Multiplexing and Backend Connection Limits — sizing the pools this routing divides.
• Configuring pgpool-II Connection Pooling and Load Balancing — the same problem on the PostgreSQL side.
• Configuring Spring Boot Read Replica DataSource Routing — doing the split in the application instead.