Understanding PDO Persistent Connection Recycling Limits for MySQL Driver

Answer

The MySQL PDO driver returns a persistent connection to its internal pool when the PDO object is destroyed (or goes out of scope) unless the MySQL server has already closed the socket. The driver itself does not apply an idle timeout or a request-based limit; the connection stays alive for the lifetime of the PHP worker process.

Confirmed facts

• When PDO::ATTR_PERSISTENT => true is set, the driver keeps the underlying TCP socket open after a request ends and stores it in a per-PHP-process pool.
• The pool key is the exact combination of DSN, username, password, and driver options; only connections with an identical key are reused.
• The driver does not track idle time or request count; there is no built-in timeout or max-reuse counter.
• The effective limit on how many persistent connections can exist is determined by:
  • MySQL server variable max_connections (total connections allowed).
  • The number of PHP worker processes/threads (each has its own pool).
  • The number of distinct DSN/credential combinations used by your code.
• If the MySQL server closes an idle socket because wait_timeout or interactive_timeout expires, the next PDO usage may fail with "MySQL server has gone away" and the driver will create a new socket.

Steps to predict exhaustion

1. Identify the PHP SAPI in use (e.g., Apache mod_php, PHP-FPM) to know how many worker processes will exist.
2. Count the distinct DSN/username/password combinations your application uses; each unique combo creates a separate pool entry per worker.
3. Multiply workers × unique combos to get the maximum persistent connections the driver could hold.
4. Compare that number to MySQL's max_connections; if the product approaches or exceeds it, you risk exhausting the server's connection limit.
5. Monitor MySQL's Threads_connected and SHOW PROCESSLIST for idle (Sleep) connections from your PHP hosts to verify reuse.

Verification (safe, scoped)

# 1. Check PHP persistent-connection stats (requires mysqlnd)
php -r "print_r(mysqli_get_client_stats());"
# Look for 'persistent_connections' and 'connection_reused' counters.

# 2. See idle MySQL connections from your app hosts
mysql -e "SHOW PROCESSLIST;" | grep -c 'Sleep'

# 3. Test reuse: run a script that opens/closes PDO many times
# with an identical DSN and watch Threads_connected in MySQL.

Caveats

This recycling behavior is not formally documented and may change between PHP versions, so verify it in your specific SAPI and PHP version before relying on it. If idle connections are being killed by server timeouts, consider disabling persistence or running a lightweight validation query before reuse.