🔀 Designing ProxySQL Read/Write Splitting With AI

I’ve put ProxySQL in front of more MySQL clusters than I can count, and the appeal never wears off: a single endpoint your application talks to, with reads fanned out to replicas and writes pinned to the primary, all without the application knowing a replica exists. The catch is that the routing logic lives in a small set of mysql_query_rules and mysql_servers rows, and a single misordered rule can quietly send a write to a read-only replica or, worse, send a read inside a transaction to a replica that hasn’t applied the write yet. Those bugs don’t crash anything — they just hand you stale or inconsistent data that’s miserable to reproduce.

So I design the hostgroups and rules by hand, but I lean on AI to draft the first cut of the routing rules and, more importantly, to review them. It’s good at spotting the classic mistakes — rules in the wrong order, a regex that’s too greedy, a transaction-routing gap. I apply nothing without testing it on a runtime that isn’t serving real traffic first, because ProxySQL applies config changes live and globally the moment you LOAD ... TO RUNTIME.

Hostgroups: The Mental Model

ProxySQL routes to hostgroups, not servers. You assign servers to numbered groups, then write rules that send queries to a group. The standard read/write layout uses two:

• A writer hostgroup (say 10) containing the primary.
• A reader hostgroup (say 20) containing the replicas.

-- Run inside the ProxySQL admin interface (port 6032 by default)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
  (10, '10.0.0.11', 3306, 1000),   -- primary  -> writer group
  (20, '10.0.0.12', 3306, 1000),   -- replica  -> reader group
  (20, '10.0.0.13', 3306, 1000);   -- replica  -> reader group

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

The weight column controls how reads are balanced across the readers. The piece that makes this safe in practice is ProxySQL’s monitor, which reads each server’s read_only flag and automatically shuffles servers between writer and reader groups when a failover flips the flag. Configure that and a primary failover doesn’t require you to touch ProxySQL at all.

Query Rules for the Split

Rules are evaluated in rule_id order, top to bottom, and the first match wins unless you set apply=0 to keep falling through. The minimal sane split is three rules: pin anything inside a transaction to the writer, send obvious reads to the reader group, and let everything else default to the writer.

-- 1. Anything in an open transaction MUST go to the primary
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, '.*', 10, 0);   -- placeholder; real transaction handling below

-- The transaction-aware rule: keep all statements in a write txn on the writer
UPDATE mysql_query_rules SET match_digest=NULL, transaction_persistent=1 WHERE rule_id=1;

-- 2. Route SELECTs (not SELECT ... FOR UPDATE) to the reader group
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (10, 1, '^SELECT.*FOR UPDATE$', 10, 1);   -- locking reads -> writer

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (20, 1, '^SELECT', 20, 1);                -- plain reads -> readers

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

Two things bite people here. First, SELECT ... FOR UPDATE is a write intent — it takes locks — so it must go to the writer, and its rule has to sit above the generic ^SELECT rule or the greedy one swallows it. Second, transactions: a SELECT issued after BEGIN to read a row you just inserted must stay on the primary, or it’ll hit a replica that may not have the row yet. The transaction_persistent flag handles this by keeping every statement in a started transaction on whichever hostgroup the transaction opened on.

Connection Multiplexing

ProxySQL’s quietest performance win is multiplexing: many frontend client connections share a smaller pool of backend connections, so your thousands of app connections become hundreds against MySQL. But multiplexing turns off automatically when a connection has session state that can’t be shared — user-defined variables, SET statements, an open transaction, temporary tables. If you see your backend connection count creeping toward your frontend count, something in the traffic is sticking sessions to connections.

-- Check whether multiplexing is being disabled and why
SELECT * FROM stats_mysql_connection_pool ORDER BY hostgroup;

A common culprit is an ORM that sets a session variable on every connection. The fix is usually to write a query rule with multiplex=1 to force re-enable it for those harmless statements — but I never guess which ones; I check the digest stats first.

Monitoring What’s Actually Happening

Before I trust any rule, I read the stats tables. stats_mysql_query_digest tells me which digests are hitting which hostgroup, which is how you prove the split is working rather than assuming it.

SELECT hostgroup, digest_text, count_star
FROM stats_mysql_query_digest
ORDER BY count_star DESC
LIMIT 15;

If a SELECT digest shows up under the writer hostgroup, my rule ordering is wrong. If an UPDATE shows up under the reader group, I have a genuine emergency. This table is the single best feedback loop ProxySQL gives you.

Where AI Helps

Drafting rules from scratch is fiddly regex work, and reviewing someone else’s rule set for ordering bugs is exactly the kind of careful, tedious reading AI does well. I paste in the full mysql_query_rules dump and ask for a review. My prompt:

You are a senior DBA reviewing a ProxySQL mysql_query_rules configuration. Writer hostgroup is 10, reader hostgroup is 20. Find: rules in an order that causes reads-in-transactions or SELECT ... FOR UPDATE to reach a replica; overly greedy regexes; and any write digest that could match a reader rule. List issues by rule_id with severity. Do not rewrite the rules, just flag the problems.

A representative response:

rule_id 20 (^SELECT -> hostgroup 20): MEDIUM. This will route SELECT ... FOR UPDATE to the reader group because rule_id 10 only matches when the statement ends in FOR UPDATE; statements with a trailing semicolon or whitespace will bypass it and fall through to rule 20. Tighten rule 10 to FOR UPDATE anywhere in the digest. rule_id 1: GOOD — transaction_persistent=1 correctly pins in-transaction reads to the writer. No write digests match a reader rule.

That FOR UPDATE$ anchor bug is real and easy to ship. The AI caught it; I then confirmed it by firing SELECT id FROM orders WHERE id=1 FOR UPDATE; through a test ProxySQL instance and reading stats_mysql_query_digest to see which hostgroup it landed on. AI to find the suspect, the digest table to convict it.

The Orchestrator Angle

ProxySQL routes; it doesn’t decide who the primary is. For automated failover I pair it with Orchestrator, which detects a dead primary, promotes a replica, and flips read_only. ProxySQL’s monitor sees the changed flags and moves servers between hostgroups on its own — so a failover reroutes writes without a config push. The clean separation matters: Orchestrator owns topology decisions, ProxySQL owns traffic. I keep them decoupled so a bug in one can’t make the other do something catastrophic.

Closing

ProxySQL gives you a precise, observable way to split read and write traffic, and the failure modes are subtle rather than loud — which is exactly why I let AI draft and audit the rules while keeping the digest stats as my source of truth. Design by hand, draft with AI, review with AI, then prove it on a runtime that isn’t serving customers before it ever sees production load.

For more MySQL operational guides, see the MySQL category, and pull the ProxySQL routing-review prompts from the prompt library to standardize your config audits.