Connection Exhaustion and Replica Lag on RDS for MySQL
ShopFast runs NHN Cloud RDS for MySQL 8.0. The primary is 8 vCPU/32 GB with max_connections=800; one read replica is 4 vCPU/16 GB. The PHP application runs on 20 pods, each configured with 40 persistent connections and no shared pool. At peak, the primary reports 850 active connections and returns Too many connections; CPU is 88%. The replica lags 120 seconds because a reporting query scans a 50-million-row orders table every five minutes. Product listing pages read from the replica, so shoppers see stale inventory and prices. The team wants an immediate fix tonight, then a durable design. Automatic backup is enabled; the maintenance window is 02:00-03:00. There is no HA standby.
1. What immediate changes would you make tonight, in order, and what would you avoid?
Read the worked answer
First, cut the per-pod connection limit from 40 to 10-12 and roll the pods; that brings 20 pods to 200-240 connections, leaving headroom. Second, add a shared pooler such as ProxySQL or PgBouncer if it can be deployed quickly, or at least enforce a hard application-side cap. Third, kill only idle connections, not active ones. Fourth, throttle or pause the five-minute reporting query. Avoid raising max_connections to 2000 on a 32 GB primary; per-connection buffers and sort memory can cause swapping and make things worse. Avoid restarting the database during peak. Verify with Threads_connected, Threads_running, and Aborted_connects before and after each step.
2. Explain why the replica lag causes stale listings and how to isolate reporting without buying a new cluster.
Read the worked answer
The replica is doing two jobs: serving listing reads and running a 50-million-row scan. The scan competes with the replication apply thread and fills the replica buffer pool, so replication falls behind and listings read older data. Isolate reporting by creating a second read replica used only by reporting, or move reporting to a scheduled job on the primary during the 02:00 window if it can tolerate primary load, or to a separate analytics store. For listings, add a short-TTL cache and route reads for inventory changes with read-your-writes logic. Do not simply point all listings at the primary; that moves the connection storm rather than solving it.
3. The business now asks for HA. What changes and failover considerations matter, given there is no standby today?
Read the worked answer
Enable RDS High Availability so a standby is created; today there is no automatic failover. Plan a controlled failover test, confirm the application endpoint follows the new primary, and update runbooks. Read replicas are not a failover target by default; promotion is manual and may lose replication position, so size the standby like the primary. Keep the maintenance window outside peak, verify backups restore to a point in time, and monitor replication lag, connections, and failover events. HA protects availability, not the connection storm; the pool, query, and reporting isolation fixes must stay in place.