Slow PHP scripts cause MySQL server to sleep (using pconnect())

I'm having a problem with MySQL going to sleep because a PHP script takes too long.. then the mysql server is unresponsive until its done with that sleep process. It then takes a minute to do any type of database request (faster if you remove the SLEEP process).

I us开发者_JS百科e pconnect() to connect to the server, and I was told this might be causing the problem. Is that right, and if so, why would a persistent connection just stop working like this?

Persistent connections can leave 'garbage' behind if the previous user of the connection aborted abnormally. Consider the case where you're doing a transaction with a series of individual queries. Half-way through, let's assume the remote user killed the connection, which causes the script to abort. Because the mysql connection is persistent, it will not be closed when the script exits, and leaves the transaction open.

MySQL has no way of knowing it should do a roll-back, so it sits there with the transaction in play, waiting for another query to come in.

When this connection gets re-used by another script, that script also has no way of knowing that a transaction is in play, and will simply start issuing its own queries.

This can quite easily cause deadlocks, because of all these 'stale' transactions being left partially copmleted. If your scripts change any server-side settings for some reason, those settings will also be persisted on a per-connection basis, etc...

In other words, unless you're EXTREMELY careful with your code, persistent connections will cause a long series of strange behaviors that are very difficult to diagnose, because each connection will be in a different state for every script that uses them.

The problem is that the persistent connections you're opening with mysql_pconnect are timing out on the MySQL side before they're timing out on the PHP side, causing PHP to try to use zombie connections. You need to make sure that the timeout is longer on the MySQL side than it is on the PHP side to prevent this from happening.

This page also claims you can use mysql_ping() to work around this issue:

http://www.php.net/manual/en/function.preg-match.php

You can also just switch to mysql_connect if the performance is fine.

The persistent connection is held open while doing nothing. If it is set with a short timeout, it will go into sleep state and need to be woken again to be used.

It's very rare to actually require persistent connections, and the time saved by not reopening a connection to the MySQL server in your script seems to be wasted on the sleep/wake process. I would advise you switch to plain old mysql_connect(). If necessary, you can close your database connection and reestablish it later in the script.

If your web server and database server are on the same machine there is virtually no savings with a persistent connection. And according to comments in PHP docs (I know they're not the best resource) Apache misbehaves with mysql_pconnect()

• Apache does 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. (See comments below on 03-Feb-2004, and the footnote at http://devzone.zend.com/node/view/id/686#fn1)

See here for more info.