Read replicas, and the queries that must not use them

The primary was at 80% CPU on weekday afternoons and almost all of it was reads. Adding a replica and pointing the application at it is a two-line configuration change, and it took about forty minutes to produce a customer support ticket saying the cart had emptied itself.

The symptom

11:42:18 cart.item_added   {"request_id":"a3f1","cart_id":9912,"sku":"FR-100"}
11:42:18 cart.rendered     {"request_id":"a3f1","cart_id":9912,"items":0}

11:42:19 cart.rendered     {"request_id":"b0c4","cart_id":9912,"items":1}

The write succeeded, the redirect happened, and the read a few milliseconds later found nothing. A second later the same read found the item. Nothing was broken and everything was working exactly as configured.

Why it happens

MySQL replication is asynchronous by default. The primary commits, acknowledges the client and writes the binary log; the replica reads that log and applies it on its own schedule. The gap is usually single-digit milliseconds and it is not zero, and a redirect after a POST arrives well inside it.

Every read-after-write is exposed to this, and read-after-write is most of what an application does immediately after a form submission — which is exactly the interaction a user is watching.

The fix

Sticky connections

Laravel 5.4 has this built in and it is off by default. With sticky enabled, any connection that has performed a write in the current request uses the primary for the rest of it.

// config/database.php
'mysql' => [
    'read'   => ['host' => ['db-replica-01', 'db-replica-02']],
    'write'  => ['host' => ['db-primary']],
    'sticky' => true,
    // ...
],

That fixes the redirect case entirely, because the write and the read are in different requests only when they are not — the add-to-cart POST reads the cart back before redirecting, and that read is now on the primary.

What it does not fix is a read in a subsequent request, which is the case the log above actually shows: the POST redirected, and the GET that followed was a new request with no memory of the write. Sticky is per request by design, because holding it across requests would mean session affinity to the primary and defeat the exercise.

Deciding per query, not per connection

The general answer is that some reads are allowed to be stale and some are not, and that is a property of the query rather than of the request. Anything a user is about to act on — a cart, a balance, an order they just placed — reads from the primary.

// stale is fine: a catalogue page, a report, a search result
$products = Product::where('active', true)->paginate(24);

// stale is not fine: the thing the customer just changed
$cart = Cart::onWriteConnection()->find($cartId);

// and the rule made explicit at the repository boundary
public function forCheckout(int $id): Cart
{
    return Cart::onWriteConnection()->with('items')->findOrFail($id);
}

Putting the decision in a repository method rather than at each call site is what makes it survivable. A rule that lives in the head of whoever writes the query is a rule that lasts until the next person, and the failure is invisible in development where there is no replica at all.

Measuring the lag rather than trusting it

Seconds_Behind_Master is the obvious metric and it reports zero when the replica has stopped, because nothing is behind if nothing is being applied. A heartbeat row written by the primary and read from the replica measures the thing you actually care about.

-- on the primary, every second
REPLACE INTO heartbeat (id, ts) VALUES (1, NOW(6));

-- on the replica
SELECT TIMESTAMPDIFF(MICROSECOND, ts, NOW(6)) / 1000 AS lag_ms FROM heartbeat WHERE id = 1;

replica-01  lag_ms  4.2    io_running yes  sql_running yes   ok
replica-02  lag_ms  8100   io_running yes  sql_running yes   WARN
replica-02  seconds_behind_master 0                          ← lying

Alert on all three: the heartbeat lag, and both threads. The second replica in that output was applying a long-running ALTER and was eight seconds behind while reporting zero, which is the exact situation where reads should have been routed away from it.

Verifying it worked

# primary CPU, weekday afternoon
# before  78-84%
# after   31-38%

# and the correctness check: a delayed replica on staging
$ mysql -e 'CHANGE MASTER TO MASTER_DELAY = 5;' -h staging-replica
$ vendor/bin/phpunit --group checkout
OK (34 tests, 96 assertions)

The deliberately delayed replica is the test that means something. With five seconds of lag, any read-after-write that has not been routed to the primary fails visibly rather than intermittently — which turned a class of bug that only appeared under load into one the suite catches.

The jobs that must not read a replica at all

Queue workers are the case that does not fit the per-query rule, because a job frequently starts by loading the record whose change triggered it — and the event that enqueued it may have committed milliseconds ago.

class SendOrderConfirmation implements ShouldQueue
{
    public function handle()
    {
        // the order was created moments ago; the replica may not have it
        $order = Order::onWriteConnection()->find($this->orderId);

        if ($order === null) {
            $this->release(5);   // and if it still is not there, wait
            return;
        }

        Mail::to($order->email)->send(new OrderShipped($order));
    }
}

The release() is the belt to the primary-read braces: a job dispatched inside a transaction that has not committed yet will not find the row on any connection, and retrying in five seconds is cheaper than reasoning about transaction boundaries in every dispatcher. Dispatching after commit is the real fix and is not always in your hands.

Reporting jobs are the opposite and should be pinned to a replica deliberately — a nightly aggregate has no freshness requirement and every reason to keep its load off the primary. Making that explicit in the job rather than inheriting the default is what stops it drifting back.

What this costs

Every new query now needs a decision that used to be automatic, and the default is the dangerous one. That is a permanent tax on writing code, paid by everyone, and the only mitigation is that the decision is made once per repository method rather than once per call.

There is also a new failure mode: a replica that is up, accepting connections and serving data from an hour ago. That is worse than a replica that is down, because nothing routes away from it automatically. The heartbeat check has to be wired into whatever removes a host from the pool, or the monitoring is telling a story nobody acts on.