MySQL Connection Failed: Too many connections?

You cannot close a persistent connection in PHP. Closing a persistent link in a script defeats the purpose of having a persistent link. If you want to close it, just open the connection with mysql_connect() so that you can close it.

One persistent connection is established for each Apache child process the first time that the database connect is called in the lifetime of that process. For more info about persistent connections see:

http://www.php.net/manual/features.persistent-connections.php3

In Apache the child process only serves a certain number of requests before it is killed and a new child started. When the process is killed the connection to the database that it held will be closed. Hence, your database connections do have a finite lifetime.

Here is an excerpt from:

http://www.apache.org/docs/mod/core.html#maxrequestsperchild

The MaxRequestsPerChild directive sets the limit on the number of requests that an individual child server process will handle. After MaxRequestsPerChild requests, the child process will die. If MaxRequestsPerChild is 0, then the process will never expire.

Setting MaxRequestsPerChild to a non-zero limit has two beneficial effects:

* it limits the amount of memory that process can consume by (accidental) memory leakage;

* by giving processes a finite lifetime, it helps reduce the number of processes when the server load reduces.

If you run an extremely high traffic site which does a lot of database requests then you may need to be careful. Although Apache/PHP has lost the connection to the database and no longer uses it, MySQL does not necessary close the connection. Your timeout settings for httpd processes and MySQL persistent connections need to be configured properly.

The time the connection stays open relies on mysql settings. If you do:

mysqladmin variables

you will see something like this:

+----------------------------+---------------------------------------+
| Variable_name | Value |
+----------------------------+---------------------------------------+
| back_log | 5 |
| connect_timeout | 5 |
.
.
| max_allowed_packet | 1048576 |
| max_connections | 100 |
| max_connect_errors | 10 |
.
.
| wait_timeout | 28800 |
+----------------------------+---------------------------------------+

wait_timeout is the time in seconds that a persistent connection will wait on a new connection. For some reason this is default to 28800 (8 hours).

If a httpd process dies and had a persistent connection, that connection sits there for 8 hours after its last use and is eating up your max_connections. I have seen this render a server with heavy mysql use inoperable. The httpd was set to 300 seconds and the connection timeout to 28800. Apache processes died and did not take pconnects with them. Basically, no new connects could be made and all the old ones did not
belong to an httpd process.

The main thing is to have your wait_timeout set lower and closer to the timeout of your Apache Processes.

  Latest articles

How to read raw web log server files?