MySQL Read Replicas: Load Balancing in Applications

1. Why read replicas are not a plug-and-play solution

Read replicas are read-only copies of a MySQL database kept up to date through asynchronous or semi-synchronous replication, primarily meant to keep read traffic off the primary instance. The basic idea is simple: as read load grows, add more replicas instead of scaling up primary hardware. In practice, implementing this is more complex than just configuring an application with multiple database connections.

The core tension with read replicas is the time delay of replication. Between a write on the primary and its visibility on the replica, some amount of time always passes, and that amount is not constant. Applications that ignore this delay and carelessly distribute reads across replicas produce situations where a user does not see a change they just saved, because the replica has not yet applied it. Such bugs are particularly unpleasant because they are rarely reproducible and only occur under load.

2. Read/write splitting: patterns in the application

Read/write splitting refers to the deliberate separation of write operations, which always go to the primary, and read operations, which can optionally be distributed across replicas. The simplest implementation happens at the connection level: the application maintains two connection pools, one for writes to the primary and one for reads to a pool of read replicas, often using round-robin or weighted load balancing between replicas.

This separation should be handled consistently and centrally, for example in a database abstraction layer or repository layer, instead of scattering manual connection assignments throughout application code. A central place for read/write splitting makes it easier to handle exceptions cleanly: certain reads, for example immediately after a write within the same request, must still go to the primary, even though they are content-wise pure read operations.

-- Application-level connection configuration (conceptual)
-- Write pool: always the primary
WRITE_DSN = "mysql://app:pw@db-primary.internal:3306/shop"

-- Read pool: a set of read replicas behind a load balancer or driver-level list
READ_DSNS = [
  "mysql://app:pw@db-replica-1.internal:3306/shop",
  "mysql://app:pw@db-replica-2.internal:3306/shop",
  "mysql://app:pw@db-replica-3.internal:3306/shop"
]

-- Check replica health and lag before adding it to the read pool
SELECT
  CASE WHEN @@read_only = 1 THEN 'replica' ELSE 'primary' END AS role,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Seconds_Behind_Source') AS lag_seconds;

3. Consistency problems from replication lag

Replication lag is not a theoretical edge case with read replicas, it is a factor that must be actively considered in every read design decision. A typical scenario: a user updates their profile, the application confirms the write, and the same user is immediately redirected to the profile page, which sends a read request to a replica. If that replica's lag is even a few hundred milliseconds, the user does not see their own change and wrongly assumes the application is broken.

Such inconsistencies do not just affect individual users, they also affect internal consistency across multiple tables. If an order is written to table A and a related log entry to table B, a read that pulls both tables from replicas lagging by different amounts can see inconsistent intermediate states, even though both writes ran in the same transaction on the primary. That is why reads touching multiple related tables should always use the same replica within a single request, never mixed across multiple replicas.

For read replicas that fall far behind the primary, actively excluding them from the read pool until lag drops back below a defined threshold is recommended. This health check logic prevents users from systematically landing on an overloaded, heavily lagging replica while other replicas are nearly current.

4. Read-after-write and session consistency

The read-after-write problem is the most common practical pitfall when using read replicas. The most robust solution is so-called sticky routing within a session: after every write by a user, all subsequent reads in that same session are forced to the primary for a defined window, for example a few seconds, instead of a replica. Only after that window expires does routing fall back to the replica pool.

A more precise but more elaborate alternative uses GTID-based waiting: after a write, the application remembers the transaction's GTID and, before the next read on a replica, executes SELECT WAIT_FOR_EXECUTED_GTID_SET(gtid, timeout). This command blocks until the replica has applied the relevant transaction, or returns an error after the timeout, which the application can then treat as a fallback to the primary. This method guarantees consistency but increases latency for the affected read by the remaining replication delay.

-- After a write on the primary, capture the resulting GTID
SELECT @last_gtid := @@session.last_gtid;

-- Before the next read on a replica, wait until it applied that GTID
-- Returns 0 once caught up, 1 on timeout (fall back to primary in that case)
SELECT WAIT_FOR_EXECUTED_GTID_SET(@last_gtid, 2) AS caught_up;

-- Application pseudocode for the fallback decision
-- if caught_up == 1: route this read to the primary instead of the replica
-- if caught_up == 0: safe to read from the replica connection

5. Routing strategies: when reads belong on the replica

Not every read operation is a good fit for read replicas. As a rule of thumb: reads used for reporting, analytics, search features or list views without an immediate relationship to a prior write by the current user are good candidates for replicas. Reads happening right after a write within the same request or session, particularly ones where the user should immediately see the just-saved change, should stay on the primary.

Another category is multi-statement transactions: once a database transaction begins with START TRANSACTION, all reads and writes contained in it should consistently stay on the same connection to the primary, to avoid violating transaction semantics by splitting across multiple servers. Read/write splitting only works reliably at the level of individual, independent requests outside explicit transactions.

6. Transparent routing with ProxySQL

ProxySQL is a specialized database proxy that handles read/write splitting based on rule-based query analysis, without the application itself having to distinguish between multiple connections. The application connects to a single ProxySQL endpoint, and ProxySQL decides based on configurable rules whether a query is forwarded to the primary or to a replica from the configured host group.

-- ProxySQL: define host groups for primary and replicas
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES
  (10, 'db-primary.internal', 3306, 1000),
  (20, 'db-replica-1.internal', 3306, 900),
  (20, 'db-replica-2.internal', 3306, 900);

-- Route SELECT statements to the replica hostgroup, everything else to primary
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
  (1, 1, '^SELECT.*FOR UPDATE$', 10, 1),   -- locking reads must hit the primary
  (2, 1, '^SELECT', 20, 1);                -- plain reads go to replicas

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

The advantage of this approach: application code stays free of routing logic, and ProxySQL automatically detects SELECT ... FOR UPDATE or statements within an active transaction and correctly forwards them to the primary. The downside: ProxySQL does not know the business context of a request and cannot solve read-after-write problems, which must be handled explicitly at the application level, for example through sticky sessions or GTID waiting.

7. Read/write splitting at the ORM and framework level

Many modern ORMs and frameworks provide native support for read replicas, often through configuration defining a list of replica connections alongside the primary connection. The ORM then automatically decides which connection to use based on the statement type, SELECT versus INSERT, UPDATE, DELETE, similar to ProxySQL, but within the application instead of at the infrastructure layer.

The advantage of the ORM level lies in the ability to explicitly flag business exceptions: a developer can exempt a specific read within a read-after-write scenario from automatic replica assignment using a hint like forcePrimary() or a corresponding annotation. This explicitness makes consistency decisions visible and traceable in code, instead of hiding them implicitly in an infrastructure component the developer does not see while writing the query.

; database.ini (conceptual ORM-level read/write split configuration)
[database.write]
host = db-primary.internal
port = 3306

[database.read]
; multiple entries form the round-robin read pool
hosts[] = db-replica-1.internal
hosts[] = db-replica-2.internal
hosts[] = db-replica-3.internal
strategy = round_robin
sticky_after_write_seconds = 3

; Usage in application code (pseudocode)
; $products = DB::read()->select('SELECT * FROM products');
; $order = DB::write()->insert('INSERT INTO orders ...');
; $order = DB::forcePrimary()->select('SELECT * FROM orders WHERE id = ?', $id);

8. Limits of read scaling with replicas

Read replicas do not scale read capacity indefinitely. Every additional replica increases network and IO load on the primary, because the binlog has to be streamed to each replica separately. Beyond a certain number of replicas, in practice often somewhere between five and ten, the primary itself becomes noticeably burdened by replication overhead, independent of the application's actual write load.

For workloads outgrowing the capacity of individual replicas, horizontal sharding strategies or caching layers like Redis in front of the database are often the more sustainable path, rather than continuously increasing the number of read replicas. Replicas solve the problem of read distribution, but not the problem of a fundamentally too-large data volume or overly complex queries, which are better addressed through indexing, denormalization, or caching.

#!/usr/bin/env bash
# health-check-replica-pool.sh: remove lagging replicas from the active pool
set -euo pipefail

MAX_LAG_SECONDS=5
REPLICAS=("db-replica-1.internal" "db-replica-2.internal" "db-replica-3.internal")

for host in "${REPLICAS[@]}"; do
  lag=$(mysql -h "$host" -N -e \
    "SHOW STATUS LIKE 'Seconds_Behind_Source'" | awk '{print $2}')

  if [[ -z "$lag" || "$lag" -gt "$MAX_LAG_SECONDS" ]]; then
    echo "[WARN] $host lag=${lag:-unknown}s, removing from read pool"
    # call your load balancer or service discovery API here
  else
    echo "[OK] $host lag=${lag}s"
  fi
done

9. Routing strategies compared

The choice of routing strategy for read replicas directly affects implementation effort, consistency guarantees, and operational complexity. The table below compares the three most common approaches.

In practice, many setups combine multiple layers: ProxySQL handles coarse distribution, while the application uses forcePrimary() or GTID waiting for known read-after-write scenarios. This combination reduces implementation effort compared to pure application-level routing, without fully giving up consistency control.

10. Summary

Read replicas are an effective means of load balancing, but not an automatic one. Without deliberate read/write splitting, without accounting for replication lag, and without a clear strategy for read-after-write scenarios, hard-to-trace inconsistencies emerge that users perceive as bugs. Central routing logic, whether in the application, the ORM, or via a proxy like ProxySQL, is the prerequisite for read replicas to work reliably.

The right strategy depends on the consistency needs of the specific read operation: reporting and search queries tolerate replicas without issue, while reads immediately following a user's own write either need to stay on the primary or be explicitly synchronized via GTID waiting. Anyone who cleanly reflects this distinction in code gains real read capacity without jeopardizing the application's data consistency.

11. FAQ: MySQL Read Replicas