Two query rules in the wrong order will send a locking read to a replica, and nothing in the logs will tell you. The statement succeeds. The rows come back. The lock you asked for was never taken, because the node that served the query is not the node that owns the write.
That is the sharp end of a ProxySQL read/write split. Getting reads onto replicas is the easy part, and the Percona XtraDB Cluster and ProxySQL setup guide already covers the working configuration end to end. This one goes after the parts that bite afterwards: how rules are evaluated, what apply changes, which session state silently stops working once statements land on different servers, and how stale reads behave under real write pressure.
If you need the cluster itself first, build it with the pillar guide above, or the Rocky Linux build of the same stack. Everything here was benchmarked in August 2026 against a three-node Percona XtraDB Cluster 8.4.10-10.1 running Galera provider 4.27, fronted by two ProxySQL 3.0.10 nodes on Ubuntu 24.04.
What the four hostgroups actually mean
ProxySQL’s native Galera support does not ask you to write failover logic. You declare four hostgroup numbers and a monitor moves servers between them based on wsrep state. Set the connection details once at the top of your shell so the admin commands stay short:
export PROXY_HOST="192.168.1.224"
export APP_USER="appuser"
padmin() { mysql -u admin -padmin -h 127.0.0.1 -P6032 --protocol=tcp "$@"; }
papp() { mysql -u "${APP_USER}" -p -h "${PROXY_HOST}" -P6033 --protocol=tcp shopdb "$@"; }The two helpers point at different hosts on purpose. Run every padmin command on the ProxySQL node itself, because the built in admin account is hardcoded to loopback. Hand it the node’s own LAN address, even while sitting on that node, and it refuses:
ERROR 1040 (42000): User 'admin' can only connect locallyAdministering the proxy from another machine means adding a second credential pair to admin-admin_credentials, and port 6032 has no business being reachable off the host anyway. Treat the shipped admin:admin as a lab default and change it before anything leaves your bench. papp on 6033 is the application path and works from anywhere the firewall allows.
Now read the Galera hostgroup definition back:
padmin -e "SELECT * FROM runtime_mysql_galera_hostgroups\G"The four numbers and the two policy knobs are the whole contract:
writer_hostgroup: 10
backup_writer_hostgroup: 20
reader_hostgroup: 30
offline_hostgroup: 40
active: 1
max_writers: 1
writer_is_also_reader: 2
max_transactions_behind: 100max_writers: 1 is the setting that keeps this sane. All writes go to one node, which sidesteps the certification conflicts you get when three nodes accept writes on the same rows. The other two sit in hostgroup 20 ready to take over. Checking which server currently holds each role tells you whether the monitor is doing its job:
padmin -e "SELECT hostgroup_id,hostname,status FROM runtime_mysql_servers ORDER BY hostgroup_id,hostname;"Note what hostgroup 10 looks like. Two of the three nodes are listed and marked SHUNNED, which is how the monitor parks a candidate writer without deleting it:
hostgroup_id hostname status
10 192.168.1.221 SHUNNED
10 192.168.1.222 SHUNNED
10 192.168.1.223 ONLINE
20 192.168.1.221 ONLINE
20 192.168.1.222 ONLINE
30 192.168.1.221 ONLINE
30 192.168.1.222 ONLINEwriter_is_also_reader: 2 is why the writer does not appear in hostgroup 30 at all. Value 2 keeps the active writer out of the reader pool, so reads only touch the two backup nodes. Set it to 1 and the writer joins the read rotation as well, which is what you want on a two-node cluster and usually not what you want on three.
Read the digest before you write a single rule
Rules written from imagination route the wrong things. ProxySQL already records every statement it has seen, grouped and normalised, and that table is the only sensible starting point. Reset the counters, run your application for a while, then read them back.
padmin -e "SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;"After a representative workload, sort by total time rather than by count. The statement you run ten times that takes 90 ms each matters more than the one you run a thousand times at 0.2 ms:
padmin -e "SELECT hostgroup AS hg, count_star, ROUND(sum_time/1000.0,1) AS total_ms,
ROUND(sum_time/1000.0/count_star,2) AS avg_ms, digest_text
FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;"The 1000.0 in both expressions is not a typo. sum_time and count_star are both integers, so the obvious sum_time/count_star/1000 divides in integer space and truncates the answer. One float anywhere in the expression fixes it, and the difference is not cosmetic on exactly the rows you care about:
hg count_star avg_float avg_integer
30 120 2.23 2.0
30 6 5.42 5.0
30 40 0.55 0.0A half millisecond read reported as 0.0 is the sort of thing that makes a genuinely hot statement invisible the moment you sort by average. This is a real capture from a mixed workload of 243 statements. The hg column is the payoff, because it shows where each normalised statement actually went:
hg count_star total_ms avg_ms digest_text
30 120 268.0 2.23 SELECT COUNT(*) FROM orders
10 25 239.6 9.58 INSERT INTO orders(item) VALUES(?)
10 10 230.9 23.09 UPDATE orders SET item=? WHERE item=?
30 6 32.5 5.42 SELECT o1.item FROM orders o1 JOIN orders o2 ON o1.id=o2.id WHERE o1.item LIKE ? LIMIT ?
30 40 22.0 0.55 SELECT id,item FROM orders ORDER BY id DESC LIMIT ?
10 1 22.0 21.98 DELETE FROM orders WHERE item LIKE ?
10 1 13.2 13.24 DELETE FROM orders WHERE item LIKE ? OR item=?
10 40 0.0 0.0 select @@version_comment limit ?Writes on 10, reads on 30, which is the split working. The ranking is the useful part. Top by total time is the cheapest statement in the capture, a 2.23 ms count run 120 times, while the most expensive single call is the UPDATE at 23.09 ms on ten executions. Sort by count and you chase the count. Sort by total time and you find the self join, at roughly 5 ms a call, as the only read expensive enough to justify its own rule later. Those decisions come from the table, not from a template.
The digest_text values are also exactly what your rules match against, with literals already replaced by ?. Copying a digest string into a rule is far more reliable than inventing a regex against raw SQL.
Reading that table after every rule change is the fastest way to confirm a change did what you intended, rather than assuming it did.
Order the rules from most specific to least
ProxySQL walks mysql_query_rules in ascending rule_id order and stops at the first matching rule that carries apply=1. That single sentence is the whole trap. A working pair looks like this:
padmin -e "SELECT rule_id,active,match_digest,destination_hostgroup,apply
FROM runtime_mysql_query_rules ORDER BY rule_id;"The narrow rule sits in front of the broad one:
rule_id active match_digest destination_hostgroup apply
100 1 (?i)^SELECT.*FOR UPDATE$ 10 1
200 1 (?i)^SELECT 30 1Renumber the generic rule so it is evaluated first and the split quietly breaks. Nothing errors, so this is worth reproducing once on a lab cluster to see how silent it is:
padmin -e "UPDATE mysql_query_rules SET rule_id=50 WHERE rule_id=200; LOAD MYSQL QUERY RULES TO RUNTIME;"Send a locking read through the proxy, then ask the digest where it landed:
padmin -e "SELECT hostgroup,count_star,digest_text FROM stats_mysql_query_digest
WHERE digest_text LIKE '%FOR UPDATE%';"Hostgroup 30. The FOR UPDATE rule never got a chance to run, so a statement whose entire purpose is to take a row lock on the writer was served by a replica:
hostgroup count_star digest_text
30 1 SELECT id FROM orders WHERE item=? FOR UPDATEPut the specific rule back in front and the same statement routes to 10 again. The practical habit that prevents this: leave wide gaps between rule IDs (100, 200, 300) so a new narrower rule always has room to slot in ahead of a broader one without renumbering anything.
What apply really does
apply=0 does not mean the rule is inactive. It means ProxySQL records what the rule asked for and keeps evaluating later rules, and a subsequent match can overwrite the destination. Evaluation still ends at the first matching rule that carries apply=1, so what apply=0 really buys is the chance to pre-set a destination that a later rule can overrule before the chain stops.
Adding a probe rule ahead of the working pair demonstrates it. Rule 90 matches the same locking read and points at hostgroup 20, but with apply=0:
padmin -e "INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES(90,1,'(?i)^SELECT.*FOR UPDATE\$',20,0); LOAD MYSQL QUERY RULES TO RUNTIME;"Rule 90 matches first and pencils in hostgroup 20. Rule 100 matches next, carries apply=1, and ends the chain before the broad rule 200 is ever considered. The statement lands on hostgroup 10:
hostgroup count_star digest_text
10 1 SELECT id FROM orders WHERE item=? FOR UPDATEThat makes apply=0 useful for rules that set something other than a destination, mirroring or logging or rewriting, without ending evaluation. Used carelessly it produces routing that looks unexplainable until you read the rule chain in order.
Session state does not survive the split
Once consecutive statements from one client can land on different servers, anything the server remembers between statements is at risk. Three cases behave three different ways, and the differences matter more than the general warning.
Error 9006: connection is locked to hostgroup 10 but trying to reach hostgroup 30
Setting a user variable and reading it back in the next statement fails outright. ProxySQL notices the SET, locks the session to the hostgroup that served it, and then refuses to route the following SELECT anywhere else:
SET @cart_id=42;
SELECT @cart_id AS cart_id_readback;The client gets a ProxySQL error rather than a wrong answer, which is the better of the two outcomes:
ERROR 9006 (Y0000) at line 1: ProxySQL Error: connection is locked to hostgroup 10 but trying to reach hostgroup 30The SET went to hostgroup 10 because it does not match the ^SELECT rule and fell through to the user’s default hostgroup. The read matched the rule and wanted hostgroup 30. Loud failure, easy to diagnose.
Error 1146: Table ‘shopdb.t_mux’ doesn’t exist
Temporary tables get no such protection, and this is the asymmetry worth remembering. ProxySQL does disable multiplexing as soon as it sees CREATE TEMPORARY TABLE, and disables it for the remaining life of that connection. What it does not do is lock the session to a hostgroup. Routing rules keep applying, so the create and the insert run on the writer while the read matches ^SELECT and goes to a replica:
CREATE TEMPORARY TABLE t_mux(i INT);
INSERT INTO t_mux VALUES(7);
SELECT i FROM t_mux;The error names a missing table, which sends people hunting for a schema or permissions problem that does not exist:
ERROR 1146 (42S02) at line 1: Table 'shopdb.t_mux' doesn't existThe digest confirms the split: CREATE TEMPORARY TABLE and the INSERT on hostgroup 10, the SELECT on hostgroup 30. Applications that build temporary tables need their own routing rule pinning them to the writer, or a transaction around the whole sequence.
Both failures come from the same cause and report it completely differently, which is why the temporary table case takes longer to diagnose.
What does keep working
LAST_INSERT_ID() is handled correctly with no configuration. ProxySQL recognises it and keeps it on the writer alongside the INSERT that produced it, returning the real value:
hg count_star digest_text
10 1 INSERT INTO orders(item) VALUES(?)
10 1 SELECT LAST_INSERT_ID() AS liiWrapping the sequence in an explicit transaction also fixes every case above, because transaction_persistent=1 pins the whole transaction to one hostgroup. In a test where a user variable, a temporary table and their reads all ran between BEGIN and COMMIT, all eight statements went to hostgroup 10 and every value came back correct. The cost is that the reads inside that transaction no longer reach a replica, so this buys correctness by giving up the split for those statements.
Stale reads under write load, and why the obvious fix misses
Galera is often described as synchronous, which leads people to assume a row written on the writer is immediately readable on a replica. Replication is synchronous. Applying the write set on the replica is not, and under load the gap is easy to measure.
The test inserts a uniquely tagged row through the proxy, then immediately counts it back, 40 times, while 16 concurrent clients hammer the writer with inserts. Idle, this almost never fails. Under load, at defaults:
stale reads [baseline] : 40 / 40Every read missed its own write. The documented fix is wsrep_sync_wait=1, which makes a replica wait until it has applied everything committed before the read started. Setting it globally on both replicas and confirming it took:
mysql -N -e "SET GLOBAL wsrep_sync_wait=1; SELECT @@hostname, @@global.wsrep_sync_wait;"Both nodes report the new value, and the identical test still fails 40 out of 40 times. The reason shows up by asking a proxied connection what it sees rather than asking the server:
papp -e "SELECT @@hostname AS node, @@session.wsrep_sync_wait AS sess, @@global.wsrep_sync_wait AS glob;"Sampling that repeatedly is the tell. Some proxied reads report a session value of 0 while the global reads 1:
node sess glob
pxc02 0 1
pxc02 1 1
pxc02 1 1wsrep_sync_wait is a session variable that inherits the global value at the moment a connection is created. ProxySQL’s backend connections are pooled and long lived, so each one keeps the value it was born with, and the pool turns over on its own schedule. SET GLOBAL reaches connections opened after it ran and never retrofits the ones already in the pool. A read that lands on an older connection gets no causality wait at all, which is why the fix appears to do nothing while the pool still holds pre change connections.
The reliable answer is to apply it per connection, scoped to the reader hostgroup only, using ProxySQL’s own init_connect:
padmin -e "INSERT OR REPLACE INTO mysql_hostgroup_attributes(hostgroup_id,init_connect)
VALUES(30,'SET SESSION wsrep_sync_wait=1');
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"With the global reset to 0 on every node so nothing else can be credited for the result, ten consecutive proxied reads all carry the session value while the global stays off, and the same 40 trials under the same 16 client write load come back clean:
node sess glob
pxc02 1 0
pxc02 1 0
pxc02 1 0
pxc02 1 0
pxc02 1 0
pxc01 1 0
RESULT both : 0 / 40 staleThat run is labelled both because it was captured with the per hostgroup timeout from the next section already in place, proving the two settings coexist. Removing init_connect and changing nothing else puts the failure straight back, which is the control that makes the result mean something:
RESULT timeout-only : 40 / 40 staleThe before and after, run back to back against the same 16-client write load:
Scoping it to hostgroup 30 matters. Writes never pay the causality wait, and the read cost is smaller than expected: on an idle cluster, 500 sequential reads down one persistent session took 696 ms with the wait off and 721 ms with it on, roughly 1 ms per read either way. Measure it idle and you are measuring the floor, since a causality wait costs most when there is a queue to drain. Even so, 25 ms across 500 reads is a rounding error against serving a customer their own order as missing 40 times out of 40.
Pull a lagging reader out of rotation
max_transactions_behind is the safety net for replicas that fall behind, and the Galera monitor also reacts to state changes on its own. Desyncing a node demonstrates the second behaviour immediately:
# on one of the REPLICAS, never the active writer
mysql -e "SET GLOBAL wsrep_desync=ON;"That one statement is enough on its own. Between the three second and six second samples the node leaves hostgroup 30 and appears in the offline hostgroup, with no operator action and nothing else touched:
t=3s
30 192.168.1.221 ONLINE
30 192.168.1.222 ONLINE
pxc2 state=Donor/Desynced recv_queue=151
t=6s
30 192.168.1.221 ONLINE
40 192.168.1.222 ONLINE
pxc2 state=Donor/Desynced recv_queue=0Turning desync off brings it back inside the first five second sample, reporting Synced by the time it reappears in hostgroup 30, which lines up with the mysql-monitor_galera_healthcheck_interval default of 5000 ms. Run this on a lab node only. A desynced node stops participating in flow control, so the rest of the cluster stops throttling for it and its receive queue is free to grow without bound.
The threshold itself deserves less trust than it looks. This run had max_transactions_behind lowered to 10 to make it observable, and the surviving replica’s wsrep_local_recv_queue still spiked to 184 during the burst without ever being pulled from hostgroup 30. The monitor samples on an interval rather than watching continuously, so a spike that starts and drains between two samples is invisible to it. That makes the setting a guard against a replica that is persistently behind, not a fast reflex against bursts. Watch the real queue depth under your own write pattern before picking a number, which is what the Prometheus and Grafana monitoring setup is for.
Cap slow reads per hostgroup
ProxySQL 3.0.10 landed a default_query_timeout that applies per hostgroup, set through the hostgroup_settings JSON column. Before it, query timeouts were per rule or global, so capping analytics reads without also capping writes meant duplicating the limit across every read rule.
Without a timeout, a deliberately slow read runs to completion in a little over five seconds. Adding a two second cap to the reader hostgroup only:
padmin -e "INSERT OR REPLACE INTO mysql_hostgroup_attributes(hostgroup_id,init_connect,hostgroup_settings)
VALUES(30,'SET SESSION wsrep_sync_wait=1','{\"default_query_timeout\":2000}');
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"Both columns are named in that statement even though only one of them is new, and leaving the other out is the mistake worth understanding. hostgroup_id is the primary key of that table, and SQLite implements REPLACE as a delete followed by an insert, so every column you do not name reverts to its schema default. Setting hostgroup_settings on its own silently blanks the init_connect from the previous section:
-- after INSERT OR REPLACE with hostgroup_settings only:
30 {"default_query_timeout":2000}
-- after UPDATE ... WHERE hostgroup_id=30:
30 SET SESSION wsrep_sync_wait=1 {"default_query_timeout":2000}No error, no warning, and the 40 out of 40 stale reads come straight back. An UPDATE ... WHERE hostgroup_id=30 also preserves the other columns, but it reports 0 rows affected and changes nothing if the hostgroup has no attributes row yet, which is easy to miss when you land on this section first. Naming every column in one INSERT OR REPLACE works either way. The same trap applies to any other attribute on that table.
The same statement is now killed at the boundary, and SLEEP() returning 1 rather than 0 is the server confirming it was interrupted:
slept
1
real 0m2.057sA four second sleep wrapped in a transaction, and therefore pinned to hostgroup 10, ran its full duration untouched at 4043 ms. The cap belongs to the reader hostgroup and nothing else inherits it. Precedence runs per rule first, then per hostgroup, then global.
Two more things about 3.0.10 are worth knowing before you upgrade. The release fixes an information disclosure bug in compressed packet handling, tracked as GHSA-fvch-fpgq-pwfx, which makes it a security upgrade rather than an optional one. It also confirms the tier split: 3.0.x is Stable, 3.1.x is Innovative, and 4.0.x carries the AI and MCP features. Pass-through authentication is gated to the two higher tiers and a Stable build rejects it even when configured, so check which tier you are on before planning around a feature.
The upgrade itself was uneventful on Ubuntu 24.04. The package appeared in the ProxySQL apt repository the same day the release was published, and the full runtime configuration, servers, rules, users and Galera hostgroups all survived the restart, because ProxySQL keeps them in its own SQLite database rather than a flat config file.
The rule set this lab ended up with
Four pieces of configuration cover everything above. First the two routing rules, specific before generic, with a gap left for future insertions:
rule_id match_digest destination_hostgroup apply
100 (?i)^SELECT.*FOR UPDATE$ 10 1
200 (?i)^SELECT 30 1Persist them once you are happy. Every rule change in this article used LOAD MYSQL QUERY RULES TO RUNTIME, which is deliberate while you are experimenting, because a bad rule disappears on restart. It also means a good rule disappears on restart:
padmin -e "SAVE MYSQL QUERY RULES TO DISK;"Second, max_writers=1 and writer_is_also_reader=2 on the Galera hostgroups, so one node takes every write and never serves a read. Third, init_connect on hostgroup 30 carrying SET SESSION wsrep_sync_wait=1, which is the only one of these that is not obvious from the documentation and the only one that fixed a measurable correctness bug. Fourth, transaction_persistent=1 on the application user, which is already the default and should stay that way.
What that configuration does not solve is application behaviour. A code path that sets a user variable and reads it back, or builds a temporary table and queries it, has to be found and either wrapped in a transaction or given its own rule pinning it to the writer. The digest table is how you find them, since those statements show up split across two hostgroups. Run it against production traffic for a day before you assume the split is clean.
The same reasoning transfers to MariaDB, where the equivalent stack is documented in the MariaDB Galera high availability guide and, with this same proxy, in MariaDB Galera Cluster with ProxySQL. For a single node before any of this becomes relevant, start with Percona Server for MySQL on Ubuntu. And if the workload is Postgres rather than MySQL, the routing problems look very different, as the Patroni and HAProxy setup shows.
