Sleeping PDO handles that filled MySQL

MySQL started refusing connections around lunch. The listing was fine. SHOW PROCESSLIST was almost all Sleep. One hundred forty handles from php-fpm with a query twenty minutes old. Horizon idle workers held eight more. wait_timeout was 28800, the default. Persistent PDO does what it says. The worker keeps the TCP session for its whole life.

pm.max_children was 8. That is eight connections if each worker has one. Recycles with pm.max_requests open another if the old persistent handle is still in the pool from the process. Horizon --max-processes and the schedule container used the same DSN with ATTR_PERSISTENT. Compose scaled php once for a spike and forgot to scale it down. 8 became 32. MySQL default is 151. The leftover Sleep rows were from a previous scale-out that still had workers until max_requests.

The problem was persistent PDO across php-fpm, Horizon, and schedule filling max_connections 151 with Sleep. Editors and shoppers got too many connections at noon. I needed short-lived handles, wait_timeout 60, and the same 151 as a hard cap I do not hide.

Short lives

ATTR_PERSISTENT is off. Each request opens, queries, closes. Eight workers at lunch is eight connections, not one hundred forty. Horizon uses a connection per process and closes when the worker restarts on --memory. wait_timeout is 60. A leftover Sleep dies before it becomes the afternoon outage.

• Memcached and Redis take the listing off MySQL. The remaining queries are cheap. A new connection per request is cheaper than a full processlist.
• max_connections stayed 151. Raising it hides the Sleep rows until the next scale-out.
• The mysql container healthcheck uses a dedicated user. It is not one of the 140.

Threads_connected at noon sits near the number of busy workers. Too many connections left with the persistent flag. The catalog did not need more MySQL. It needed fewer handles that did nothing.

What I took from this

SHOW PROCESSLIST full of Sleep is not a query problem. It is ATTR_PERSISTENT plus a long wait_timeout.

Scale-out without scale-in leaves handles until max_requests. Persistent multiplies that leftover.

Raising max_connections hides the graph. I keep 151 and close the TCP.