MySQL Too Many Connections — Pool and Prune Sleepers

ERROR 1040 (HY000): Too many connections. It hits at the worst moment — a deploy finishes, traffic shifts, and the app can't open a single database connection. Dashboards show the database CPU relaxed and queries fast, yet every new connection dies instantly. The server isn't overloaded; it's fully booked.

The usual culprit isn't traffic, it's arithmetic. max_connections caps total slots (151 by default), while every app instance holds a pool of idle-ready connections. Multiply one generous pool setting by forty pods and you oversubscribe the server before serving a single real query. Sleep-state threads — open, authenticated, doing nothing — pile up until the last slot goes. The same math bites cron jobs, replicas, and consoles sharing the server, so count every client in the budget.

This guide covers the emergency drill and the durable fix: confirming saturation, reading SHOW PROCESSLIST like a roster, getting in through the SUPER back door, pruning sleepers, tuning wait_timeout, and sizing pools with explicit math so deploys stop triggering 1040s for good.

Error 1040 Is Arithmetic, Not Traffic

Treat 1040 as a capacity equation with three terms: demand (sum of all pool maximums plus humans, crons, and replicas), supply (max_connections), and headroom for the unexpected. When demand exceeds supply, MySQL refuses new connections while existing ones run fine — which is why CPU looks calm and slow logs look clean during a total outage. The three status queries below separate a full lot from a slow server in seconds.

Threads_connected is the current occupancy; Max_used_connections is the historical peak since restart; max_connections is the ceiling. A Threads_connected pinned at the ceiling with low CPU is the 1040 fingerprint. Compare Max_used_connections against the ceiling too: if the peak just started touching the limit after a deploy, the deploy changed demand — count pods and pools, not queries.

Memory is why you can't just pave a bigger lot. Each connection carries thread stack plus sort, join, and read buffers — gilbally small per thread, significant at 800 threads. Raising max_connections without raising RAM converts refused connections into swapping and OOM kills, which take down existing connections too. Size the ceiling for memory, then fit demand inside it. Monitor the ratio, not just the errors — occupancy creeping from 40% to 65% across releases warns you weeks before the first 1040.

-- Supply vs demand in three numbers
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Occupancy ratio (alert above 0.75 in monitoring)
SELECT VARIABLE_VALUE AS threads_connected
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Threads_connected';

SHOW PROCESSLIST: Sleepers Are the Usual Suspects

SHOW FULL PROCESSLIST is the roster of every slot holder: id, user, source host, db, Command, Time, and State. During a 1040, sort mentally by Command. Sleep with a large Time means an authenticated connection doing nothing — a pool reservation, a leaked handle, or a console someone left open. Query means real work; a handful of long Queries among hundreds of Sleepers proves the server is idle-rich and slot-poor.

Group to find the owner fast. The aggregation query below collapses hundreds of rows into per-source counts: one app host with 300 Sleep rows is the misconfigured pool; fifty cron hosts with 2 each is a leak pattern. The db column adds the schema, and Time shows how long each sleeper has camped — Time values in the thousands next to an 8-hour wait_timeout mean threads that will never leave on their own.

Save the grouped output before killing anything. It justifies the pool change in the postmortem ('service X held 312 of 400 slots in Sleep') and tells you exactly which deploy to fix first. Kill from evidence, not from vibes. Rerun the grouped query after every prune batch — the shrinking top row confirms you're draining the right pool. If two sources tie, fix the one with the oldest idle seconds first: ancient sleepers signal leaks, fresh ones signal oversized minimums.

-- Full roster (id, user, source, db, command, idle seconds)
SHOW FULL PROCESSLIST;

-- Grouped: who holds the slots?
SELECT user, SUBSTRING_INDEX(host, ':', 1) AS src,
  command, COUNT(*) AS n,
  MAX(time) AS max_idle_s
FROM information_schema.processlist
GROUP BY user, src, command
ORDER BY n DESC;

-- Kill candidates: idle Sleepers over 10 minutes
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 600;

Getting In When the Door Is Shut: SUPER and the Admin Port

MySQL always keeps one extra connection slot for SUPER-privileged accounts (CONNECTION_ADMIN in 8.0) beyond max_connections. That +1 is your fire escape: a break-glass admin credential connects while the app gets 1040s, letting you diagnose and prune from inside. It only works if the credential exists, has the privilege, and someone tested it — an untested fire escape is decoration.

MySQL 8 adds a second door: the admin port (admin_port, commonly 33062), a separate listener with its own thread pool and TLS, configured via admin_address. Connections there bypass the main listener's saturation entirely, which matters when even the reserved slot is contended. Configure it on every production node and probe it from monitoring so a dead admin port pages before the incident.

On managed services the mechanics differ but the principle holds. RDS has no SUPER; instead use the master user (which gets the reserved slot via its own privilege) and the rdsadmin kill procedures. Know your platform's equivalent before the outage: the docs page you read at 10 AM beats the one you skim at 2 AM. After entry, run the occupancy queries before any KILL — knowing Threads_connected versus the ceiling tells you how many sleepers must die, so you prune once instead of in panicked rounds.

# Fire escape 1: reserved slot for privileged accounts (+1 over max)
mysql -u breakglass -p -h db-primary -e 'SELECT 1;'

# Fire escape 2: dedicated admin port (MySQL 8, if configured)
mysql -u admin -p -h db-primary --port=33062 -e 'SHOW FULL PROCESSLIST;'

# From inside: confirm saturation, then prune oldest sleepers
# mysql> SHOW STATUS LIKE 'Threads_connected';
# mysql> KILL <id>;  (Sleep rows only — never blind active queries)

# RDS equivalent (no SUPER): master user + rds kill procedures
# mysql> CALL mysql.rds_kill(thread_id);

wait_timeout: Stop Feeding Abandoned Threads

wait_timeout is how long the server tolerates an idle (Sleep) connection before closing it: 28,800 seconds — 8 hours — by default. That default assumes diligent clients; with leaky ones it means every abandoned handle camps until end of business day. Dropping it to 300 seconds bounds any leak to 5 minutes of slot occupancy, which turns most 1040s from outages into blips the pool absorbs.

Set both wait_timeout (TCP clients) and interactive_timeout (console clients) together, or consoles keep the old behavior while you wonder why nothing changed. Apply with SET GLOBAL for immediate effect plus the my.cnf entry for survival across restarts — GLOBAL-only changes evaporate on failover, and failovers love happening the week after you forget. Verify from a fresh session, since existing connections keep the value they negotiated at login.

Treat the timeout as a seatbelt, not a fix. It bounds damage while you repair the leak: unclosed handles in code, minimumIdle set to maximum, health checks opening connections they never close. The timeout keeps you alive; the code review keeps you healthy. Document the standard timeout in the base my.cnf template so new replicas inherit it — a failover that resurrects 8-hour timeouts reopens the leak the week after you closed it.

-- Current idle tolerance (28800 = 8h default)
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

-- Bound abandoned threads to 5 minutes (immediate + persistent)
SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 300;
-- Also add to my.cnf: wait_timeout=300 / interactive_timeout=300

-- Fresh sessions pick it up; verify:
SELECT @@GLOBAL.wait_timeout, @@SESSION.wait_timeout;

Pool Math: Size Hikari Below max_connections

HikariCP rewards explicit budgets. maximumPoolSize caps per-instance connections; minimumIdle sets how many stay open when idle; setting minimumIdle equal to maximum (a common copy-paste) means every pod holds the max forever — the 40×20=800 disaster. Sensible production values are maximumPoolSize 8–10 and minimumIdle 2 for typical services: pools burst under load and shrink when quiet, which is the entire point of pooling.

Do the fleet math in the deploy pipeline, not in your head. Sum maximumPoolSize across every service times its pod count, add 20 for admins, backups, exporters, and replicas, and require the total under 70% of max_connections. The 30% headroom absorbs failovers (replicas promote with their own pools), cron spikes, and the odd console. The script below computes the budget from live pod counts and writes the corrected properties.

Watch connectionAcquisitionTimeout too: when the pool (not the server) is the bottleneck, threads wait for a pool slot instead of failing fast. A 30-second acquisition timeout with pool-exhaustion metrics tells you to grow the pool slightly or add read replicas — before users feel it as latency. Recheck the budget every quarter: pod autoscaling moves the left side silently, and yesterday's comfortable 50% is tomorrow's 85%.

#!/bin/bash
# Fleet pool budget: sum(maximumPoolSize x pods) + 20 headroom < 70% of max
set -euo pipefail
MAX_CONN=$(mysql -u monitor -h db-primary -N -e "SELECT @@GLOBAL.max_connections;")
BUDGET=$(python3 -c "print(int($MAX_CONN * 0.7 - 20))")
echo "max_connections=$MAX_CONN -> pool budget for apps: $BUDGET"
# Example: 40 pods x maximumPoolSize 8 = 320 (fits a 400 budget)
cat > application.properties << 'EOF'
spring.datasource.hikari.maximum-pool-size=8
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.max-lifetime=1800000
EOF
echo "wrote application.properties (max 8, idle 2)"

Prevention: Alerts, Reviews, and Load Tests

Page on occupancy, not on failure. Alert when Threads_connected crosses 75% of max_connections with a 5-minute sustain — that fires while you still have slots to investigate, unlike the 1040 itself. Track Max_used_connections across deploys: a step-change in the peak the morning after a release names the guilty deploy before users do. Export both to your dashboards next to pool-active-threads so app-side and server-side views agree.

Review connection behavior in code like any resource: every opened handle closed in finally blocks, no per-request connections when a pool exists, health checks reusing the pool instead of opening fresh sockets. Load tests should assert on Threads_connected growth per RPS — a service whose slots scale with traffic instead of staying flat has a leak that production will find at the worst hour.

Rehearse the drill quarterly. A game day that fills the lot in staging (lower max_connections, oversized test pools) and walks the team through SUPER entry, sleeper pruning, and pool cuts turns the 3-minute recovery from luck into procedure. The lottery only pays teams that bought a ticket in advance. Record the drill's timings — entry, prune, pool cut — so the next incident runs a proven playbook with real numbers.

#!/bin/bash
# Cron every 2 min: warn at 75% occupancy, page with top holders
set -euo pipefail
read -r MAX_CONN CONN < <(mysql -u monitor -h db-primary -N -e \
  "SELECT @@GLOBAL.max_connections;" -e "SHOW STATUS LIKE 'Threads_connected';" \
  2>/dev/null | awk '{print $2}' | tr '\n' ' ')
PCT=$(python3 -c "print(int(100*$CONN/$MAX_CONN))")
if [ "$PCT" -ge 75 ]; then
  mysql -u monitor -h db-primary -e "
  SELECT user, SUBSTRING_INDEX(host,':',1) AS src, command, COUNT(*)
  FROM information_schema.processlist GROUP BY user, src, command;"
  echo "ALERT: connection occupancy ${PCT}% ($CONN/$MAX_CONN)"
  exit 1
fi
echo "OK: occupancy ${PCT}% ($CONN/$MAX_CONN)"