A read replica, and the six queries that could not use it

The primary database sat at eighty per cent CPU through the working day, and the shape of the load was obvious: reporting queries that scanned months of orders, running on the same machine as every checkout. A read replica is the standard answer, the setup takes an afternoon, and everything difficult about it happens afterwards.

The symptom

$ mysql -e "SELECT * FROM sys.session WHERE command='Query'
            ORDER BY time DESC LIMIT 3G"
         thd_id: 41208
           user: [email protected]
        command: Query
           time: 41
  current_statement: SELECT DATE(o.created_at), SUM(ol.total_cents)
                     FROM orders o JOIN order_lines ol ...

$ mysql -e "SHOW ENGINE INNODB STATUSG" | grep -A2 'History list'
History list length 4128104

# a 41-second query holding a read view open, and four
# million rows of undo the purge thread cannot clear.

The history list length is the number that turns this from slow-reports into a database problem. A long-running read holds a consistent snapshot, which means every version of every row changed since it started has to be kept — and the write path pays for that in undo log growth and purge lag.

Why it happens

Analytical and transactional workloads want opposite things from the same engine. One wants to scan a lot of rows once; the other wants to touch a few rows constantly with low latency. Sharing a machine means the scan’s snapshot and the transactions’ writes interfere in a way neither can see.

The fix

The replica, which is the easy part

-- on the primary
CREATE USER 'repl'@'10.0.1.%' IDENTIFIED BY '...';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.1.%';

-- on the replica, from a consistent dump
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.1.2', SOURCE_USER='repl',
  SOURCE_AUTO_POSITION=1;
START REPLICA;

-- and the setting that matters most
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

super_read_only rather than read_only is the line to get right: without it, a connection with SUPER privileges can write to the replica, and the application’s user very often has more privileges than anybody intended. A write that succeeds on a replica is a divergence that will be discovered months later.

The connection split, and thinner framework support than it looks

'mysql' => [
    'read'  => ['host' => ['10.0.1.3']],
    'write' => ['host' => ['10.0.1.2']],
    'sticky' => true,
    // ...
],

// what 'sticky' actually does: after a write on this
// connection, subsequent reads IN THE SAME REQUEST go to
// the primary. it does not survive a redirect, a queued
// job, or a second request.

The sticky flag covers read-your-own-write within a request and nothing beyond it, which is the source of most of the surprise. It also routes by statement type — a SELECT goes to the replica — which is a heuristic that breaks on SELECT ... FOR UPDATE and on stored procedures.

Measuring lag, and what normal looks like

two numbers, and only one is useful:

  Seconds_Behind_Source     0 most of the time, and
                            wrong during a stall — it
                            reports 0 when the IO thread
                            is stuck

  a heartbeat table         written by the primary every
                            second; the replica reads it
                            and compares to now()

normal, measured over a fortnight:
  p50    40ms
  p95   180ms
  p99   410ms
  max    11m   ← the batched migration, one Tuesday

The heartbeat is the only measurement that is honest during the failure that matters, because the built-in counter derives from the last event the replica processed and reports zero when it has processed nothing. Forty milliseconds at the median is what makes most of the routing decisions easy; the p99 at four hundred is what makes six of them hard.

The six queries that could not use it

found by asking, per read: is a 400ms-stale answer wrong?

  1  after checkout, the order confirmation page
     → a missing order. the worst one.
  2  after saving a setting, the settings page
     → the old value, and the user saves again
  3  the idempotency check on a retried POST
     → a duplicate charge. correctness, not display.
  4  a queued job reading a row its dispatcher wrote
     → the job runs before replication catches up
  5  the stock reservation check
     → overselling
  6  an admin approving something they just created
     → a 404

three of six are correctness. three are annoyance.

Enumerating them by asking one question per read is a day of work and it is the whole exercise. Three of these are bugs that produce money problems and three are irritations, and the difference matters because the fixes have different costs.

Routing by intent rather than by statement type

// not: is this a SELECT?
// but: does this read require the latest state?

final class OrderRepository
{
    public function find(OrderId $id): ?Order
    {
        return $this->query()->find($id);          // replica
    }

    public function findForUpdate(OrderId $id): ?Order
    {
        return $this->onWriteConnection()->find($id);   // primary
    }
}

Naming the two reads differently makes the decision explicit at the call site rather than implicit in a driver heuristic. It also makes the primary-reading paths countable — there are eleven, they are in one class each, and a reviewer can see when a twelfth is added.

The queued job, which the request-scoped fix cannot reach

// the job runs in another process, milliseconds later
final class SendConfirmation implements ShouldQueue
{
    public function __construct(
        public readonly int $orderId,
        public readonly string $writePosition,   // GTID at dispatch
    ) {}

    public function handle(ReplicaGate $gate): void
    {
        // wait for the replica to reach the position that
        // existed when this job was created, or fall back
        $connection = $gate->hasReached($this->writePosition)
            ? 'replica'
            : 'primary';
        // ...
    }
}

Carrying the write position and waiting for it is the correct general answer and it is more machinery than most jobs deserve. We applied it to two jobs where the read is expensive and used the primary unconditionally for the rest, on the grounds that a job doing one indexed lookup on the primary costs nothing.

The transaction that read from the replica

// the bug, and it is subtle
DB::transaction(function () use ($id) {
    $order = Order::find($id);        // ← replica: outside
                                       //   the transaction
    $order->status = 'paid';
    $order->save();                    // ← primary
});

// the read is not part of the transaction at all, so the
// row could change on the primary between the read and
// the write. a lost update, and no error anywhere.

A transaction that spans two connections is not a transaction. The framework opens the transaction on the write connection and the read goes elsewhere, so the isolation guarantee covers the write half only — which is the kind of bug that produces one wrong row a month and is nearly impossible to reproduce.

Failover, written before it was needed

runbook: primary unavailable

  1  confirm: is it the primary or the network?
     mysqladmin ping from two hosts
  2  stop the application (maintenance page)
  3  on the replica: confirm it has caught up
     SELECT * FROM heartbeat  → within 2 seconds
  4  STOP REPLICA; RESET REPLICA ALL;
     SET GLOBAL super_read_only = OFF;
  5  repoint the application's write host
  6  start the application
  7  the old primary does NOT come back as a replica
     without a rebuild. do not skip this.

step 7 is the one people get wrong under pressure.

Step seven is where a manual failover turns into split-brain: an old primary that is brought back and started will have transactions the new primary does not, and reattaching it as a replica silently diverges. Rebuilding it from a dump is slower and is the only safe answer without automated fencing.

Verifying it worked

# primary CPU, working hours, before and after
  before   78-84%
  after    31-38%

# history list length
  before   4,128,104 peak
  after       88,204 peak

# replica lag, one week
  p50 40ms   p95 180ms   p99 410ms   max 2.1s

# and the deliberate test: a checkout, then an immediate
# confirmation page load, 200 times
$ ./bin/read-after-write-drill --iterations=200
  missing order: 0
  served from primary: 200

# the failover drill, on a Saturday
  time to serve traffic from the replica: 6m 40s

The read-after-write drill is the assertion that the six queries are actually routed to the primary, and running it after every deploy for a month is what caught the seventh — an admin page added in April that read an order it had just created.

What this costs

A consistency model that the whole team now has to hold. Every new read is a decision about whether four hundred milliseconds of staleness is acceptable, and the default in the framework is the wrong answer for the cases that matter. That decision will be got wrong, and the failure is silent.

It is also a second machine to back up, monitor, patch and fail over, and a failover procedure that is manual and therefore only as good as the last time it was rehearsed. Six minutes and forty seconds on a Saturday with everybody present is not the same as six minutes at three in the morning.

The alternative that was not taken is worth naming: moving the reports to a nightly job writing to a summary table would have removed the load without any of this, and would have made the reports stale by a day rather than by four hundred milliseconds. That was rejected because the reports are used during the day to make decisions — which is a business constraint, and the right place for this decision to have been made.