Page 1 of 1

mysql_pconnect issues and how to solve some of them

Posted: Fri Feb 04, 2005 10:11 am
by tores
Hi all.

I've been doing some research around mysql_pconnect, and thought I might share what I discovered. (I do not guarantee that all of my following statements are 100% accurate:))

In the php-manual it is stated that mysql_pconnect should only be used when the setuptime for a new connection to the sql-server is long.
However, as the ordinary mysql_connect is closed when the script who created it terminates, it is not suitable for database-transactions or locks who's supposed to persist across several scripts.
The problem is how to tear down a persistent mysql-connection, and especially if it holds on to a table lock that for some reason haven't been released. (The client may f.x. have crashed before releasing the lock).
At first I tried using a combination of "show processlist" and "kill [thread id]" to kill of persistent connections. This doesn't work because killing a thread is dependent on certain events (listed in the mysql-manual) to actually detect that it's been killed and exit accordingly. Such an event will never occur unless some client uses that same connection and releases the lock.
So what I ended up with was setting a timeout on the persistent connection after setting it up. With the query "set session wait_timeout=[number of seconds]" this is possible. The "session" option tells mysql to set the spesified timout only for the current thread. By using the "global" option one may set the timeout for ALL subsequent connections.
Now the connection and it's corresponding thread will exit after the given period of time and release every lock it holds at that time, avoiding many of the caveats mentioned in the php-manual.

For mysql_pconnect to actually set up a persistent connection you must run php as an apache module or as a plug-in on an other multithreaded web server.

[The following is a qualified guess. I've not verfied any of it]
Apache runs each client-request in a separate process (Unix) or thread (Windows), so even though your using the same database-connection across several scripts the apache server may run several processes/threads which is (persistently) connected to the same port on the database-server. (Each process/thread will not end when the script terminates, as it normally does, because of the pconnect made in that same script. So these processes/threads won't terminate until the pconnection terminates).
You should consider this when setting the maximum number of apache children and the timeout-value for wait_timeout.

Note: You might use interactive_timeout instead of wait_timeout but I couldn't get the results I wanted with it. Didn't quite get the difference between the two.

Here is the description of the two as it is stated in the mysql manual:

# interactive_timeout

The number of seconds the server waits for activity on an interactive connection before closing it.
An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect().
See also wait_timeout.

--------------------------------------------------------------------------------

# wait_timeout

The number of seconds the server waits for activity on a non-interactive connection before closing it.

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or
from the global interactive_timeout value, depending on the type of client
(as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

regards tores