Replication lag, stale reads, and a messy failover on Cloud DB for MySQL
A community platform serves about 2,000 read requests per second from a Cloud DB for MySQL primary plus two read replicas. All read traffic goes to the replicas and writes go to the primary through a single connection string. At 02:00 a nightly job deletes and re-inserts roughly 4 million rows in one transaction on the primary. During that window one replica's replication delay climbs to 240 seconds and stays elevated for 40 minutes; the other replica, which has a larger spec, peaks at 25 seconds. Users complain that a comment they just posted is missing from the list until they refresh several times. In the same week, a planned maintenance failover produced about 90 seconds of application errors even though the database itself completed failover in roughly 20 seconds. The application uses a connection pool of 200 connections and has no retry logic.
1. Explain the replication lag using the evidence in the setup, and lay out the remediation you would apply first.
Read the worked answer
Read the two replicas as a controlled comparison. Same primary, same binlog stream, different hardware: 240 s versus 25 s. That is the strongest signal in the incident - the problem is not one oversized transaction alone, it is that one replica cannot apply the write burst fast enough. The lag starts when the batch starts and clears when it ends, so the trigger is clearly the 02:00 job.
Mechanism. A single transaction touching 4 million rows produces one enormous binlog event. The replica's SQL applier must apply it to completion before it can move on, and the applier is single-threaded per schema unless parallel replication is enabled and the workload has independent transactions to parallelize. A single monster transaction gives it nothing to parallelize. Worse, the rollback and undo work, plus lock contention on the replica, means a smaller or throttled replica falls further behind while the primary keeps generating binlog. Large deletes also leave gaps and force heavy index maintenance.
Remediation, in order.
- Fix the job: delete and re-insert in chunks (for example 10k rows per statement with a commit between chunks), never one 4-million-row transaction. This alone removes the spike.
- Move the batch off the general-purpose replica: either run it during a lower-traffic window or give reporting its own replica that is explicitly allowed to lag.
- Right-size the slow replica's spec and storage IOPS to match its sibling; asymmetric replicas should be a deliberate decision, not an accident.
- Verify parallel replication settings and monitor replication delay and replica IOPS in Cloud Insight so you have an alert before users notice, not after.
Do not "fix" this by routing read traffic back to the slow replica while it is 240 s behind - that converts a performance incident into a data-correctness incident.
2. Users cannot see their own freshly written comments. Explain the consistency failure and recommend a pattern that keeps read scale without breaking read-your-writes.
Read the worked answer
The failure is architectural, not a bug in the list query. All reads go to replicas; replicas are asynchronous by design. A user writes a comment to the primary, the page immediately re-reads from a replica that is N seconds behind (240 s during the batch), and the comment does not exist yet. Then it appears "randomly" on refresh - which is exactly what lag looks like to a user.
Patterns that work, cheapest first.
- Session pinning. After a successful write, mark that user's session to read from the primary for a short window (a few seconds, or until the replica has caught up past the write's position). This costs little because only users who just wrote go to the primary, and it directly encodes read-your-writes.
- Version/position check. If the write returns a GTID or binlog position, the read can wait until the target replica reports it has applied past that point, with a short timeout that falls back to the primary.
- Read-back from the write path. Return the created object in the write response and render it locally, so the immediate read is not a database read at all. This removes the most common case cheaply.
- Split endpoints explicitly. Write endpoint and read endpoint should be distinct configuration, not one string, so you can route a specific query to the primary without a code change during an incident. Today the single connection string makes that impossible.
What not to do. Do not add a blanket cache in front of the list endpoint to mask the symptom; it will cache the stale list and can outlive the lag. Reserve the replicas for genuinely lag-tolerant traffic: feeds with time decay, analytics, and search indexing.
3. The database failed over in 20 seconds but the application was broken for 90. Explain the gap and the changes that close it.
Read the worked answer
The 70-second difference is connection-state time, not database time. During failover the database endpoint's resolution changes to the new primary and existing sessions are severed. The application holds 200 pooled connections with no retry and no maximum connection lifetime, so it keeps handing out connections to an address that no longer serves, and each request blocks until a TCP timeout or an error surfaces. Nothing in the app is watching for a dead connection, so the outage lasts as long as the pool takes to notice.
Changes that close the gap.
- Idle/lifetime limits. Set maximum connection lifetime and validation-on-borrow shorter than the failover detection window, so the pool discards dead connections quickly instead of serving them.
- Retry with backoff, and make writes idempotent. A short connect timeout, two or three retries with jittered backoff, and an idempotency key on inserts so a retried comment does not become a duplicate. Retrying without idempotency converts an outage into data corruption.
- Fail fast on the read path. During failover, degrade reads (serve cached feeds) rather than queueing 200 threads against a dead endpoint.
- Health signalling. Have the app check a lightweight database probe and expose it to the load balancer so unhealthy instances are removed during the window.
- Rehearse. Schedule a failover drill in a maintenance window on the real topology and measure time-to-recovery end to end. If the maintenance-driven failover was not announced to the app team, fix that process too - you cannot test what you do not schedule.
Expect recovery to be bounded by (failover detection) + (pool lifetime) + (one retry budget), and target that number explicitly in the next drill.