PHP -> Mysql persistent connection pooling WITHOUT mysql_pconnect - Possible?

Question

I have been trying to figure out a nice way of doing this for a while now. But have had a hard time finding the right pieces to do this. I am guessing this must be possible.

To put it in simple terms here is what I would like to accomplish:

PHP / Other front end -> [SOCKET] ->

Locally hosted 'pooler' -> [Pool of persistent TCP/IP connection(s)]->

Externally hosted MySQLD

Does such a tool / way of doing things exist?

We would basically like to implement persistent mysql connections WITHOUT using mysql_pconnect.

I respectfully ask that we not start to discuss about how persistent connections are not needed etc. They are. We are running out of TIME_WAIT ports, and are having other issues which would be solved if this type of system was implemented.

So yea, to summarize... We would to implement a mysql connection pooler that is socket based on the local end, and persists the connections that are made to a (LAN) externally hosted mysql server.

We do not use transactions or anything else that would be affected from the mysql connections being recycled.

We are running linux on the front end with a master + master percona 5.5 cluster.

Thanks!

Answer 1

After much searching, I have finally found a solution.

I am not much of a writer, so I will do my best to make this as concise as possible.

So as far as I could find, there are 2 possible solutions:

SQL Relay

http://sqlrelay.sourceforge.net/

This does exactly what the question asked for, and a bunch more. I wont go into too much detail on what I was able to find out about this but will mention that it was not a viable solution as it is not transparent. Meaning that the flow is as follows:

PHP -> Queries -> SQL Relay Extension -> SQL Relay -> Externally hosted MySQL

So this would have involved rewriting all of our code from mysql to sql relay. Not an option in our case.

All that being said, if someone is planning a fresh large scale project that requires any of the numerous features that SQL Relay has, it sounds beautiful.

Mysql Proxy

http://forge.mysql.com/wiki/MySQL_Proxy

This is the solution we ended up using.

The key to making this do what we want it to do is the pooling LUA script for mysql proxy.

This LUA extension can be found at:

https://github.com/cwarden/mysql-proxy/blob/315ab806bb95b8223f5afd3d238eff2a40af03d8/lib/ro-pooling.lua

Without going into too much detail, here are some basic stats... Bare in mind, this is tested at LOW usage time:

[root@HOSTNAME etc]# netstat -na | grep ":3306 " | grep TIME_WAIT | wc
   6433   38598  572537

After switching to mysql-proxy, and letting things settle:

[root@HOSTNAME etc]# netstat -na | grep ":3306 " | grep TIME_WAIT | wc
     32     192    2848

As you can clearly see, the TIME_WAIT ports to mysql have dropped to almost none.

The connections are now in fact persistent WITHOUT using mysql_pconnect / mysqli_connect( ... p:hostname ... ).

Worth mentioning there appear to be a few configurable settings near the top of the pooler lua script.

local min_idle_connections

and

local max_idle_connections

These appear to be pretty self explanatory. Except that: It would appear that each username (and password? untested... most likely not tho.) combination creates its own set of persistent connections.

So multiply max_idle_connections by the number of unique mysql users that will be connecting to the database. And that should give you an idea of how many idle connections you will end up having.

So, let me reiterate for so this little blurb hits some keywords for those searching via google:

When using PHP is it possible to have persistent mysql connections WITHOUT mysql_pconnect?

Yes, this can be done via SQL Relay if you dont mind rebuilding most of your code to pipe your queries thru their extension OR transparently using mysql-proxy with the ro-pooling.lua script.

We have been wanting something like this for about a year now.

ENJOY!

Answer 2

1. Persistent connection support was introduced in PHP 5.3 for the mysqli extension. Support was already present in PDO MYSQL and ext/mysql. The idea behind persistent connections is that a connection between a client process and a database can be reused by a client process, rather than being created and destroyed multiple times. This reduces the overhead of creating fresh connections every time one is required, as unused connections are cached and ready to be reused.

2. Unlike the mysql extension, mysqli does not provide a separate function for opening persistent connections. To open a persistent connection you must prepend p: to the hostname when connecting.

3. The problem with persistent connections is that they can be left in unpredictable states by clients. For example, a table lock might be activated before a client terminates unexpectedly. A new client process reusing this persistent connection will get the connection "as is". Any cleanup would need to be done by the new client process before it could make good use of the persistent connection, increasing the burden on the programmer.

The persistent connection of the mysqli extension however provides built-in cleanup handling code. The cleanup carried out by mysqli includes:

Rollback active transactions

Close and drop temporary tables

Unlock tables

Reset session variables

Close prepared statements (always happens with PHP)

Close handler

Release locks acquired with `GET_LOCK()`

This ensures that persistent connections are in a clean state on return from the connection pool, before the client process uses them.

The mysqli extension does this cleanup by automatically calling the C-API function mysql_change_user().

The automatic cleanup feature has advantages and disadvantages though. The advantage is that the programmer no longer needs to worry about adding cleanup code, as it is called automatically. However, the disadvantage is that the code could potentially be a little slower, as the code to perform the cleanup needs to run each time a connection is returned from the connection pool.

It is possible to switch off the automatic cleanup code, by compiling PHP with MYSQLI_NO_CHANGE_USER_ON_PCONNECT defined.

Note:

The mysqli extension supports persistent connections when using either MySQL Native Driver or MySQL Client Library.

Also you can refer these links: http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/