Flash-Sale Autoscaling Causing Database Connection Exhaustion
An e-commerce platform runs 24 application VMs behind a load balancer. The autoscaling group is CPU-target based: minimum 12, maximum 60, target 55%. Each VM runs a Java service with a local connection pool of 100 to a managed MySQL instance whose max_connections is 2000. A 16 GB Redis cache serves product pages. During a flash sale, CPU reached 85%, and the group scaled from 24 to 60 VMs in six minutes. Two minutes later, 38% of requests failed with 'too many connections'; Redis CPU hit 100% and hit ratio fell from 92% to 61%. After traffic dropped and the group scaled in, errors continued for ten minutes. The database CPU stayed below 40%. The load balancer health check is a 5-second TCP check.
1. Diagnose the failure chain and calculate the connection overshoot.
Read the worked answer
The connection budget was already breached before the sale. At 24 VMs, 24 x 100 = 2,400 potential connections against a 2,000 limit. At 12 VMs it is 1,200, which is safe, but normal operation at 24 is not. At 60 VMs it is 6,000, three times the database limit.
The chain is: traffic rises, CPU crosses 55%, the group adds 36 VMs in six minutes, each new JVM opens a pool of up to 100 connections, and the database refuses connections. Applications retry, which amplifies the connection storm. The database CPU stays low because it is rejecting work, not because it is healthy.
At the same time, new application instances have empty local caches. They all request the same hot product keys, so Redis receives a stampede, reaches 100% CPU, and hit ratio falls from 92% to 61%. More requests fall through to MySQL, adding load and latency.
On scale-in, VMs are terminated without draining. Open database sessions are killed abruptly; surviving instances reconnect and retry, so errors persist for about ten minutes even after capacity returns. The 5-second TCP health check marks an instance healthy before its connection pool and cache are ready.
2. Redesign scaling and connection management so the same sale does not repeat. Include pre-scale and cache measures.
Read the worked answer
Connections: introduce an external pooler such as ProxySQL or PgBouncer between the application and MySQL, or reduce each local pool to a hard budget. Set the total budget from the database limit: 2,000 x 0.8 = 1,600 connections. At a maximum of 30 application instances, that is about 50 connections per instance. Enforce it in configuration, not by convention. Add pool wait timeouts, circuit breakers and bounded retries with jitter. Raise max_connections only if the instance class has memory and CPU headroom; do not treat it as the primary fix.
Scaling: replace CPU-only target tracking with a policy based on requests per instance and p99 latency, plus CPU as a guardrail. Cap the group at the connection budget. Add step scaling and a cooldown so the group does not add 36 instances in one burst. Pre-scale to the forecast peak before the sale, and keep a warm pool of pre-initialized instances. Use HTTP readiness checks that verify pool and cache warm-up, not a TCP connect.
Cache: run Redis in cluster mode with enough shards, add request coalescing or singleflight for hot keys, jitter TTLs, add short-lived local caches, and pre-warm the cache from the catalog before the sale. Add rate limiting and load shedding at the edge so a retry storm does not become a database event.
3. What post-incident metrics would you add to detect this earlier?
Read the worked answer
Add: active and waiting connections per application instance; total database connections versus max_connections; pool wait time and timeout count; database threads_running and aborted_connects; retry rate and circuit-breaker opens; Redis CPU, latency, evictions and hit ratio by key class; cache stampede indicators such as identical-key request rate; autoscaling events with instance start-to-ready time; p99 latency by endpoint; load-balancer healthy/unhealthy counts; and error-budget burn rate. Alert on connection utilization above 70% and on scale-out events that would push projected connections above 80% of the database limit. Also record scale-in drain duration and post-scale-in error rate so the next incident is not diagnosed from customer complaints.