Aurora MySQL Failover Downtime and Purge-Driven Replica Lag

The database was not the problem. Aurora promotion completed inside the normal window; the four minutes were spent by clients holding connections to a host that no longer existed.

Likely causes, in order of probability:

1. The application connects to the instance endpoint (or a hard-coded writer IP) rather than the cluster writer endpoint. The instance endpoint for a former writer still resolves, to a host that is now a reader or is being replaced.
2. DNS caching inside the JVM or the OS resolver. Many Java services cache DNS indefinitely unless networkaddress.cache.ttl is set; the 5-second Route 53 TTL is irrelevant if the client never re-resolves.
3. No connection validation. Idle pooled connections to the dead host are handed out and hang until the TCP timeout expires, which can be tens of seconds per request, multiplied across retries.
4. The client retries without backoff, amplifying load on the newly promoted writer.

Fixes: point everything at the cluster writer endpoint; set a short JVM DNS TTL and verify the OS resolver honour timeouts; enable connection validation in the pool and lower connect/socket timeouts so a dead host fails in under a second; add retry with exponential backoff and jitter around transient connection errors; and use driver-level failover lists where supported.

Infrastructure: put RDS Proxy in front of the cluster. It absorbs the 12,000 connections into a small backend pool, holds client connections across a failover and re-points them at the new writer, which removes most of the outage. Check proxy pinning behaviour first - long-lived sessions or certain prepared-statement patterns pin clients to one backend and reduce the benefit.

Test it. Schedule a controlled failover and measure time-to-first-successful-request from the application, not time-to-DNS-change. That is the only number that matters.

The purge statement is the cause, and it is a write-pattern problem. A single DELETE of 60 million rows is one enormous transaction. Aurora storage is shared, but each reader still has to apply the resulting change stream and observe the commit atomically; the reader sees nothing until commit, then absorbs a very large burst of change records. Meanwhile the writer accumulates undo and an enormous transaction, pins CPU, and blocks purge of old row versions, which inflates history list length and slows reads on the readers too. The lag metric measures the reader falling behind this burst - not a lack of readers.

A third reader does not help. Every reader applies the same change stream, so the new one will lag exactly like the others while adding storage and cost. It also does not reduce writer CPU.

Fix the workload instead:

• Batch the delete: DELETE ... LIMIT 10000, commit, brief sleep, repeat. This keeps the transaction small, keeps CDC and readers current, and keeps undo manageable. Bound the loop by time so the job cannot overrun into business hours.
• Prefer partition-aware purging (drop or exchange old partitions) if session data can be time-partitioned - it is orders of magnitude cheaper than row deletes.
• Consider a scheduled job or pt-archiver-style tool that deletes in chunks with replication-aware throttling.
• Watch AuroraReplicaLag and history list length as the job's guardrails, and alarm on both.

Adding a reader only makes sense as a separate decision - isolating read traffic - not as a remedy for write-induced lag.

Reader endpoint load-balances connections across all readers in the cluster and is the right default for the application tier: it spreads connections and re-routes as readers are added, removed or replaced.

Custom endpoint is the right tool for the ETL team. Build an endpoint (or two, one per ETL job family) containing only the readers you are willing to sacrifice to batch reads, and point the ETL connection strings at it. Trade-offs to state explicitly to the team:

• Custom endpoints do not rebuild themselves the way the reader endpoint does. If a member is replaced, connections to it fail until you modify the endpoint, so design the job to retry, or put two readers in the endpoint and let the job reconnect.
• Membership is manual. Every instance replacement is an operations task, so automate endpoint membership with an EventBridge/ Lambda reaction to instance events, or accept the toil knowingly.
• A custom endpoint gives you isolation, not infinite capacity. Long analytical scans still compete for the readers' buffer cache and CPU.

When to leave Aurora. If the analytics workload is heavy aggregation and scanning, at some point the cheapest answer is to stop running it on OLTP compute: export to S3 and query with Athena, or replicate into Redshift. Define a trigger for that decision rather than waiting for an incident - for example, when ETL reads exceed a defined share of reader CPU, or when an ETL job's p95 exceeds its SLA on a dedicated reader twice in a month. Add capacity on paper first; Aurora read replicas are the expensive way to run a data warehouse.