Production floods with SQLSTATE[HY000] [1040] Too many connections at peak load
At peak, PHP starts throwing SQLSTATE[HY000] [1040] Too many connections and the site returns 500s. Nothing was deployed and the traffic is only ~20% above last week's peak. Four PHP-FPM servers sit behind the load balancer; MySQL is a single primary.
Constraints: you may not restart MySQL, and you must explain the number rather than simply raise it.
$ mysql -e "SHOW VARIABLES LIKE 'max_connections'"
max_connections 151
$ mysql -e "SHOW STATUS LIKE 'Threads_connected'"
Threads_connected 151
$ mysql -e "SHOW PROCESSLIST" | awk '{print $5}' | sort | uniq -c
148 Sleep
3 Query
# 4 app servers, each with pm.max_children = 50
# the PDO DSN is built with PDO::ATTR_PERSISTENT => trueDiagnose the cause.
Count the ceiling: four servers times pm.max_children 50 is 200 possible workers, while max_connections is 151 — PHP can demand more connections than MySQL will grant. ATTR_PERSISTENT makes it worse: each worker keeps its socket open between requests, which is why 148 of the 151 are Sleep and only 3 are running a query. Bound the FPM pools, or pool through pgbouncer/ProxySQL — do not just raise max_connections.
- ✗Raising
max_connectionswithout checking servers timespm.max_childrenagainst it - ✗Reading
Sleepthreads as idle capacity rather than workers holding persistent sockets - ✗Calling
ATTR_PERSISTENTa connection pool — it binds one socket to one worker
- →What arithmetic ties
pm.max_childrentomax_connectionsacross several servers? - →What does a transaction-mode pooler give you that
ATTR_PERSISTENTcannot?
What you count is not connections but the ceiling PHP is able to demand.
4 servers × pm.max_children 50 = 200 workers
max_connections = 151200 > 151 — with enough load PHP is guaranteed to hit the wall. Nothing was deployed: more than 151 workers were simply awake at once for the first time.
ATTR_PERSISTENT turns that from a peak problem into a permanent one: the socket stays bound to the worker between requests, so a worker that served a request an hour ago is still holding a connection. Hence 148 Sleep against 3 Query.
<?php
// ❌ every worker permanently occupies a slot in max_connections
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_PERSISTENT => true,
]);
// ✅ the connection lives for exactly one request
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);The order of work:
max_connections with headroom for replication, migrations and admin sessions.
and they are the only things that change demand.
workers: it multiplexes hundreds of short client connections onto a dozen server ones. It is the only way to have many workers and few connections.
memory the database spends on each extra thread.
- Do the arithmetic.
servers × pm.max_childrenmust be below - Drop
ATTR_PERSISTENTor lowerpm.max_children— those are the two levers, - Put a pooler in front (
pgbouncer, ProxySQL) if you genuinely need many - Raising
max_connectionscomes last, and only once you have worked out how much
