Practical Read/Write Splitting with MySQL Replicas

Read/write splitting is one of those changes that looks like a configuration flag and behaves like a distributed-systems decision. Point reads at a replica, keep writes on the primary, get more capacity. What you also get is a window during which a replica does not yet know about a write that already succeeded — and every user-visible bug this feature produces lives inside that window.

This post is about closing it deliberately rather than hoping it is small.

Replication lag is the whole problem

MySQL asynchronous replication does not make the primary wait for replicas. A transaction commits, the client gets its acknowledgement, and the replica applies the change some time later — usually milliseconds, occasionally much longer.

“Occasionally much longer” is the part that matters. Lag spikes on a large ALTER TABLE, a long-running transaction, a batch delete, or simply when the replica’s single-threaded apply path cannot keep up with a write burst.

The classic bug: a user updates their profile, the app redirects to the profile page, that read goes to a lagging replica, and the user sees their old data. They update it again. Now you have a support ticket and, if the second write raced the first, possibly bad data.

So the real question is not “can this query use a replica” but “is this query allowed to be stale, and for how long”.

Step 1: classify your reads before you route anything

Before touching any configuration, sort queries into three buckets. This is a code and product exercise, not an infrastructure one.

Most applications are dominated by the third bucket in cost and by the first in risk. The middle bucket is where judgement is needed: a shared team dashboard refreshing every 30 seconds does not care about a two-second lag; a stock counter on a checkout page might.

Write the classification down somewhere a reviewer will see it. An undocumented rule becomes a bug the first time somebody adds a query.

Step 2: make replicas observable before you depend on them

You cannot route around lag you cannot see.

SHOW REPLICA STATUS\G

The field to watch is Seconds_Behind_Source. Two caveats about it:

• It measures the apply delay, not the network delay. A replica that has received everything but is slowly applying still reports a growing number — which is what you want.
• It reports NULL when replication is stopped. NULL is not zero. Alerting on Seconds_Behind_Source > 5 silently ignores a completely broken replica; alert on NULL as a separate, higher-severity condition.

For anything beyond a single pair, pt-heartbeat from Percona Toolkit gives you a more honest measurement: it writes a timestamp on the primary at a fixed interval and computes lag from the difference on the replica, which survives idle periods and clock-skew weirdness better than the built-in counter.

Export both to Prometheus and put the threshold on a dashboard next to your error rate. You will want them on the same screen during the first incident.

Step 3: choose where the routing decision lives

There are three honest options.

In the application

A second connection pool, and explicit routing at the call site or in the ORM.

# Illustrative — the pattern, not a library
def get_user(user_id, *, must_be_fresh=False):
    pool = primary_pool if must_be_fresh else replica_pool
    with pool.connection() as conn:
        return conn.execute(SELECT_USER, user_id).fetchone()

Good: the decision sits next to the business rule that justifies it. A reviewer can see that must_be_fresh=True is correct for a post-write read.

Bad: every service needs the logic, and “default to replica” makes new code unsafe by default. Prefer defaulting to the primary and opting into replicas explicitly — slower to adopt, much harder to get wrong.

In a proxy

ProxySQL, MaxScale, or a similar layer parses the query and routes it. The application sees one endpoint.

A minimal ProxySQL rule set:

-- hostgroup 10 = primary (writes), hostgroup 20 = replicas (reads)
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES
  (100, 1, '^SELECT.*FOR UPDATE',  10, 1),   -- locking reads: primary
  (200, 1, '^SELECT',              20, 1),   -- everything else read-only: replicas
  (300, 1, '.*',                   10, 1);   -- default: primary

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

Rule order is everything. SELECT ... FOR UPDATE acquires locks and must go to the primary; if the generic ^SELECT rule is evaluated first, you get lock errors that look like application bugs.

ProxySQL also handles the lag guard for you:

UPDATE mysql_servers
   SET max_replication_lag = 5
 WHERE hostgroup_id = 20;

LOAD MYSQL SERVERS TO RUNTIME;

A replica exceeding max_replication_lag is temporarily shunned and traffic shifts to the remaining healthy ones.

Good: one place to change, works for every client language, gives you connection multiplexing and per-query metrics as a bonus.

Bad: it is another stateful hop to run, monitor and upgrade, and regex-based routing gets subtle with prepared statements, multi-statement queries and stored procedures.

In the driver

Some drivers and cluster-aware clients route by themselves. Convenient when it fits; check carefully whether the driver understands transactions the way you assume, because several route the first statement of a transaction independently of the rest.

My default for a team running more than one service is the proxy, with an application-level override for the small set of reads that must hit the primary.

Step 4: solve read-your-own-writes explicitly

The lag guard prevents catastrophic staleness. It does not prevent the one-second staleness that produces the profile-page bug. That needs a separate mechanism.

Session pinning is the pragmatic answer: after a write, pin that session to the primary for a bounded window.

PIN_WINDOW_SECONDS = 5

def after_write(session):
    session["primary_until"] = time.time() + PIN_WINDOW_SECONDS

def pick_pool(session):
    if session.get("primary_until", 0) > time.time():
        return primary_pool
    return replica_pool

Simple, cheap, and correct as long as the window comfortably exceeds normal lag. It costs you some replica offload right after writes — which, in a read-heavy application, is a small fraction of total reads.

GTID-based waiting is the precise answer. Capture the GTID after the write and have the replica wait for it before serving the read:

-- On the primary, immediately after committing
SELECT @@gtid_executed;

-- On the replica, before the dependent read (timeout in seconds)
SELECT WAIT_FOR_EXECUTED_GTID_SET('<gtid-set-from-primary>', 1);

A return of 0 means the replica has caught up to that point. A timeout means it has not, and you fall back to the primary.

This is exactly correct and more plumbing: you have to carry the GTID from the write path to the read path. Worth it for a small number of critical flows; overkill as a global policy.

Step 5: keep transactions on the primary, entirely

An easy way to reintroduce every bug you just fixed is to let a transaction split across hosts. Once a connection has issued BEGIN, every statement until COMMIT or ROLLBACK belongs to the same server.

Proxies handle this with a transaction-persistence setting; make sure it is on:

UPDATE mysql_users SET transaction_persistent = 1 WHERE username = 'app';
LOAD MYSQL USERS TO RUNTIME;

In application-level routing, take the pool once at the start of the unit of work and pass the connection down — never re-resolve inside.

Also watch for autocommit-off drivers. A driver that implicitly opens a transaction for every statement makes every query a write-path query as far as the proxy is concerned, and your replicas quietly receive no traffic at all.

Step 6: verify the split is real

After deploying, confirm the traffic actually moved. Two checks:

-- On each server: where are queries landing?
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_select';

-- In ProxySQL: per-hostgroup query counts
SELECT hostgroup, sum(count_star), sum(sum_time)
FROM stats_mysql_query_digest
GROUP BY hostgroup;

If your replicas show near-zero Com_select, something upstream is defeating the routing — usually autocommit behaviour, a transaction wrapper, or a rule that never matches. In an example setup with a conventional read-heavy workload I would expect something in the region of 60–80% of reads to move; the exact figure depends entirely on your query mix.

The trade-offs, stated plainly

What you gain: primary CPU and I/O headroom, the ability to run expensive reporting queries without touching production latency, and a replica that is already warm if you ever need to promote it.

What you pay: a correctness burden that now lives in your application’s rules rather than in the database’s guarantees. Every new query is a small decision about staleness, and decisions that are not written down get made wrong.

What it does not fix: write capacity. Read/write splitting adds read capacity only. If the primary is saturated by writes, replicas make it marginally worse, because they add replication work. That is a different problem with different answers — sharding, batching, or moving the write path somewhere else entirely.

Split your reads when reads are the bottleneck, classify them before you route them, and make the read-your-own-writes case explicit rather than hoping lag stays small. The rest is configuration.