Drupal uses pconnect() when connecting to the database for a slight performance improvement if PHP is configured to allow pconnect.

But there are times when PHP should be configured to disallow persistent connections to the database.

Each apache process can potentially obtain a persistent connection to each database the server is hosting. If there are lots of apache processes and lots of databases it is common to see the database throwing "too many connections" errors maybe like this:

PDOException: SQLSTATE[42000] [1203] User foo already has more than 'max_user_connections' active connections in lock_may_be_available()

If this occurs there are a couple of options:

  • Increase the number of available connections the database will allow. If most connections are idle most of the time, this is a viable solution.
  • Disable pconnect via the XXsql.allow_persistent = Off attribute in your php.ini. The performance hit from this setting is very slight.

Comments

j.m.’s picture

j.m. commented

I did a little search in Drupal 5 and found in includes/database.mysql.inc the following comment for the function db_connect:

 * Note that you can change the mysql_connect() call to mysql_pconnect() if you
 * want to use persistent connections.

So I changed the following line
$connection = @mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, 2);
for
$connection = @mysql_pconnect($url['host'], $url['user'], $url['pass'], TRUE,2);

It did work because I had mysql.allow_persistent = On in php.ini.

For other DBMS, it must be the same with *_connect in includes/database.*.inc where * is your DBMS.

I have also a little trick in mysql to find out if persistents connections are working. Just do a show processlist; and check if Drupal still show up after a benchmark or just normal access of the site. If pconnect is used, you will see some rows with the Drupal user with column Command at 'sleep' and Time incrementing.

And now, the performance improvement of pconnect? I see nothing stunning yet, even if it's cleary working. Optimisation is more of a trial and error method than exact science, so I will try some more stuff to see if pconnect really adds something.

Please comment if you know more!

Mojah’s picture

Mojah commented

wait_timeout setting when using pconnect suggestion/question

We use vps accounts (588M RAM) to run our Drupal sites. While playing with my.conf settings recently, I noticed several performance changes with settings. One thing I can suggest is to check the wait_timeout setting in my.conf. By default it is something like 8hrs (28800) but if your my.conf has been tweaked it may be a different setting.

My understanding is that wait_timeout should be around be between 300-1800 if you are using pconnect (depending on what types of queries are run). But remember that this will keep a mysql process running for 300-1800secs and if there are more mysql requests than max_connections, the server will not respond.

My vps accounts crashed recently when I reached the numfile limit which is related to the max number of open files. My wait_timeout was set to 100. I believe that an open table is an open file so it may have been that certain queries were taking longer than 100sec and were not terminating thereby leaving the file open and taking the vps to its max open file limit (indicated by numfile).

*WARNING* - I am a total amateur with server management and optimisation and the above info is what works for me. An experienced mysql server admin will be able to provide more clarity on this