skip to content

After a news site raised its PHP-FPM worker count, MySQL began refusing logins with 'Too many connections'; how do PDO persistent connections cause this, and how do you fix it?

level: seniorimportance: should knowfreq 35%

answer

  1. workers, not queries, set the count
  2. servers × pools × workers × keys
  3. idle workers still hold links
  4. plus cron, queues and admin logins
  5. drop persistence or add a proxy

basics

~20 s

Each FPM worker keeps its own persistent connection per DSN and user, even while idle, so connections grow to servers × workers × keys and overran MySQL's limit. Resize workers, disable persistence or add a pooling proxy.

solid answer

~50 s

With `PDO::ATTR_PERSISTENT` every FPM worker that has connected keeps one connection per distinct DSN and user for as long as the worker lives, busy or idle. So the web tier's peak is servers × pools × workers × keys: four servers going from 60 to 150 workers means 240 to 600 connections, before cron jobs, consumers and admin logins. Once that passes the database's connection limit, new logins fail with "Too many connections" — not a leak, just multiplication. I confirm it by counting connections per client host against live workers and looking for long-idle sessions. Fixes: keep total workers times keys below the limit, switch persistence off so only busy requests hold connections, raise the server limit only if it has the memory, or put a pooling proxy in front and point the DSN at it.

code

ini · 7 lines
ini
; news pool, identical on each of the four servers
pm = dynamic
pm.max_children = 150   ; was 60

; With PDO::ATTR_PERSISTENT and one DSN/user:
;   4 servers x 150 workers x 1 key = up to 600 idle-or-busy connections
; plus cron, queue consumers and admin logins must fit under the database limit.

go deeper

for a junior

Recall that every PHP-FPM worker holds its own persistent connection, so more workers means more database connections.

for a middle

Explain the servers × pools × workers × keys formula and why idle workers still count.

for a senior

Diagnose the incident from connection counts per host, then choose between smaller pools, no persistence, a higher limit or a proxy.

for a principal

Make the database connection limit an explicit input to capacity planning for the web tier, alongside CPU and memory.

## The incident A news site runs PHP-FPM on four application servers. Traffic spikes during breaking news, so the team raises each pool's worker limit (`pm.max_children`) from 60 to 150. The next spike brings a new error from MySQL: **"Too many connections"**. The database is not busy; it is simply refusing new logins. Every PDO connection is opened with `PDO::ATTR_PERSISTENT => true`. ## The arithmetic With persistent connections, a worker keeps its connection for as long as the worker lives. The upper bound on connections from the web tier is therefore: ``` servers × pools per server × workers per pool × distinct persistent keys ``` For the news site, with one DSN and one user: | | Before | After | |---|---|---| | Workers per server | 60 | 150 | | Servers | 4 | 4 | | Persistent keys | 1 | 1 | | Web-tier connections at peak | 240 | 600 | Add cron jobs, queue consumers, admin tools, replication and monitoring, all of which also log in. If the server allows, say, 400 connections, the old setup fitted and the new one cannot. The manual's own advice is exactly this check: the database's connection limit must be greater than the maximum number of web workers, plus other usage such as crons and administrative connections. ## Why persistence makes it worse - **Idle workers keep their connections.** During the spike FPM starts many workers; afterwards they sit idle but still hold a connection each until the worker exits or the database drops it for inactivity. - **Without persistence**, a connection exists only while a request that uses the database runs. The peak is bounded by concurrent requests, not by workers that have ever connected — still up to the worker count in the worst case, but it falls back as soon as load drops. - **Distinct keys multiply.** A second DSN (a read replica) or a second user doubles the per-worker count. - **mysqli's `mysqli.max_persistent` is per process.** Its default, `-1`, means no limit, and even a limit only caps each worker; it cannot cap the total across workers and servers. ## Diagnosing it 1. Count the connections the database holds per client host and user, and compare with `workers × keys` per host. 2. Check how many FPM workers are alive right now and the configured maximum, per server. 3. Look for idle connections with long idle times — the signature of persistent links held by idle workers. 4. Check the database's idle timeout: a long one keeps abandoned connections around. ## Fixing it Options, roughly from cheapest to most structural: - **Turn persistence off** (`ATTR_PERSISTENT` false, drop the `p:` prefix). The count falls back to the concurrently busy workers, at the cost of a connection setup per request. - **Size workers against the database, not only against CPU and memory.** The worker maximum across all servers, times keys, plus other users, must stay below the database limit. How to choose `pm.max_children` itself is the FPM pool's topic. - **Raise the database's connection limit** only if the server has memory for it; every connection costs server-side resources. - **Put a pooling proxy between PHP and the database**, so many worker connections share a smaller set of server connections. Its sizing and modes are a database-side topic; for PHP it is a different host in the DSN, and PHP-side persistence is typically switched off. - **Reduce distinct keys** — one application user instead of one per module. ## What to say in the interview The error is not a leak. It is the direct product of a per-worker connection cache multiplied by the number of workers: scaling FPM scaled the connection count with it. The fix is to make the database limit part of the capacity plan, and to decide deliberately whether saving connection setup time is worth holding hundreds of idle connections.

  • Would switching persistence off make the error impossible?
    No, it lowers the typical count but not the worst case. Without persistence a connection exists only while a request uses the database, so the count follows busy requests and drops after a spike. If every worker on every server is busy with a query at once, the total can still reach the worker count, so the worker maximum must still fit the database limit.
  • Can mysqli.max_persistent protect the database here?
    Not by itself. The directive, default `-1` for no limit, caps persistent links per PHP process, so it cannot bound the total across 150 workers on four servers. Once a worker reaches its cap, further persistent connects in that worker fail with a "Too many open persistent links" error rather than queueing. The total has to be controlled by the worker count, persistence settings or a proxy.

saying these in an interview costs you the question

  • Calling the error a connection leak in the PHP code.
  • Believing idle FPM workers give their persistent connections back.
  • Raising the database connection limit without checking server memory.
  • Expecting mysqli.max_persistent to cap connections across all workers.
  • Forgetting cron jobs and consumers in the connection budget.
  • Assuming turning off persistence removes the need to size workers.