Entry

Can I close a persistent connection?

Are persistent connections ever closed?

What causes the error 'MySQL Connection Failed: Too many connections' ?

Jul 2nd, 1999 18:57
Nathan Wallace,

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.