PHP/MySQL Persistent Connections

Summary: Do not use persistent connections in your PHP application when connecting to MySQl.

Important reasons NOT to use persistent connections:

  • Most high volume site that deploy php and mysql do not enable persistent connections. Follow the example of the leaders.
  • MySQL has a thread cache which makes non-persistent connections very fast. Make sure your mysql thread cache is set just high enough deal with your load level.
  • When you lock a table, normally it is unlocked when the connection closes, but since persistent connections do not close, any tables you accidentally leave locked will remain locked, and the only way to unlock them is to wait for the connection to timeout or kill the process. The same locking problem occurs with transactions.
  • Temporary tables are dropped when the connection closes, but since persistent connections do not close, temporary tables aren't so temporary. If you do not explicitly drop temporary tables when you are done, that table will already exist for a new client reusing the same connection. The same problem occurs with setting session variables.
  • If PHP and MySQL are on the same server or local network, the connection time may be negligible, in which case there is no advantage to persistent connections.
  • Web servers do not work well with persistent connections. When it receives a request from a new client, instead of using one of the available children which already has a persistent connection open, it tends to spawn a new child, which must then open a new database connection. This causes excess processes which are just sleeping, wasting resources, and causing errors when you reach your maximum connections, plus it defeats any benefit of persistent connections.

By avoiding persistent connections, you can actually increase performance with your web server as well as eliminate any other issues you may be having such as 503 errors and connection timeouts.

  • Admin
  • Last modified: 2015/07/28 19:01
  • by Michael Alegre