Call us Toll-Free:
1-800-218-1525
Live ChatEmail us

 Sponsors

Nginx + PHP mysql_pconnect = Database errors (Too many connections)

Mike Peters, 03-12-2009
If you're using NGinx spawn-cgi or FPM with PHP and calling mysql_pconnect, you are likely going to experience frequent database crashes and "Too many connections" errors.

This took a while to trace, but once you understand the issue, it all makes sense.

mysql_pconnect opens a "persistent" connection to the database. From the documentation: "the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect())."

The issue is that FPM keeps a number of php-cgi processes running in the background to process php scripts. These php-cgi processes never die and so MySQL connections keep open forever...

Sooner or later, you are going to run out of MySQL connections (or worse yet - run out of file descriptors) and that's when all hell breaks loose.

And if that's not enough, after doing some digging into mysql_pconnect I found a few additional reasons NOT to use mysql_pconnect:

1. If you use mysql_pconnect on a machine that has a local database and you are connecting to a remote database, PHP will try to use the same mysql connection for both databases.

2. Temporary tables don't work with persistent connections (they are only visible to the connection that was used to open the table)

3. Setting charset variables on a persistent connection, is going to impact all future queries on that connection as well

4. Calling mysql_pconnect twice (in the same script) with different parameters doesn't work as expected

5. PHP 4.1 on Apache running with MySQL persistent connections, is known to memory leak (not flushing properly).

Bottom line, never ever use mysql_pconnect.

Replace all occurrences of mysql_pconnect with mysql_connect in your code and in your php.ini file, prevent persistent connections:


[MySQL]
;
Allow or prevent persistent links.
mysql.allow_persistent = Off

Avi Saranga, 03-12-2009
Mike, pconnect should be used only when the overhead for creating new connections is huge.
(over the inet with latency && slow ns)
further more; if you are pre-forking php and use pconnect you are going to get this problem as its a *software design flaw* php/nginx does not have a global connection pool my lord. you are spawning DIFFERENT processes how would they communicate!?!?

Yay!!! More page hits to you!!

Mike Peters, 03-19-2009
Thank you Avi

That function should be deprecated. If overhead for creating a new connection is huge (i.e. you're connecting from a web server in China to a MySQL database in Seattle), then redesign your architecture.

MySQL connections are too precious of a resource to jeopardize.

I can't think of a single scenario where using mysql_pconnect would be justified.

Nikhil Mohan, 12-17-2010

Here's a nifty little class which will load balance across multiple replicated MySQL server instances, using persistent connections, automatically removing failed MySQL servers from the pool.



You would ONLY use this for queries, never inserts/updates/deletes, UNLESS you had a multi-master situation where updates to any database serverautomatically replicate to the other servers (I don't know whether that's possible with MySQL).



Using this class, you get a connection to a MySQL server like this:

¬*¬*¬*
$con = MySQLConnectionFactory::create();



Here is the class (you'll need to customize the $SERVERS array for your configuration -- note that you would probably use the same username, password and database for all of the servers, just changing the host name, but you're not forced to use the same ones):



<? php

class¬*MySQLConnectionFactory¬*{

¬*¬*¬*
static¬*$SERVERS¬*= array(

¬*¬*¬*
array(

¬*¬* ¬* ¬*¬*¬*
'host'¬*=>¬*'myHost1',

¬*¬* ¬* ¬*¬*¬*
'username'¬*=>¬*'myUsername1',

¬*¬* ¬* ¬*¬*¬*
'password'¬*=>¬*'myPassword1',

¬*¬* ¬* ¬*¬*¬*
'database'¬*=>¬*'myDatabase1'),

¬*¬*¬*
array(

¬*¬* ¬* ¬*¬*¬*
'host'¬*=>¬*'myHost2',

¬*¬* ¬* ¬*¬*¬*
'username'¬*=>¬*'myUsername1',

¬*¬* ¬* ¬*¬*¬*
'password'¬*=>¬*'myPassword2',

¬*¬* ¬* ¬*¬*¬*
'database'¬*=>¬*'myDatabase2')

¬*¬*¬*
);



¬*¬*¬*
public static function¬*create() {

¬*¬*¬*¬*
// Figure out which connections are open, automatically opening any connections

¬*¬*¬*
// which are failed or not yet opened but can be (re)established.

¬*¬*¬*¬*
$cons¬*= array();

¬*¬*¬*
for ($i¬*=¬*0,¬*$n¬*=¬*count(MySQLConnectionFactory::$ SERVERS);¬*$i¬*<¬*$n;¬*$i++) {

¬*¬* ¬* ¬*¬*¬*
$server¬*=¬*MySQLConnectionFactory::$SERVERS[$i];

¬*¬* ¬* ¬*¬*¬*
$con¬*=¬*mysql_pconnect($server['host'],¬*$server['username'],¬*$server['password']);

¬*¬* ¬* ¬*¬*
if (!($con¬*===¬*false)) {

¬*¬* ¬* ¬*¬*
if (mysql_select_db($server['database'],¬*$con) ===¬*false) {

¬*¬* ¬* ¬* ¬* ¬*¬*
echo('Could not select database: '¬*.¬*mysql_error());

¬*¬* ¬* ¬* ¬* ¬*¬*
continue;

¬*¬* ¬* ¬*¬*
}

¬*¬* ¬* ¬*¬*¬*
$cons[] =¬*$con;

¬*¬* ¬* ¬*¬*
}

¬*¬*¬*
}

¬*¬*¬*¬*
// If no servers are responding, throw an exception.

¬*¬*¬*¬*
if (count($cons) ==¬*0) {

¬*¬* ¬* ¬*¬*
throw new¬*Exception

¬*¬* ¬* ¬*¬*¬*
('Unable to connect to any database servers - last error: '¬*.¬*mysql_error());

¬*¬*¬*
}

¬*¬*¬*¬*
// Pick a random connection from the list of live connections.

¬*¬*¬*¬*
$serverIdx¬*=¬*rand(0,¬*count($cons)-1);

¬*¬*¬*¬*
$con¬*=¬*$cons[$serverIdx];

¬*¬*¬*¬*
// Return the connection.

¬*¬*¬*¬*
return¬*$con;

¬*¬*¬*
}

}

?>

Enjoyed this post?

Subscribe Now to receive new posts via Email as soon as they come out.

 Comments
Post your comments












Note: No link spamming! If your message contains link/s, it will NOT be published on the site before manually approved by one of our moderators.



About Us  |  Contact us  |  Privacy Policy  |  Terms & Conditions