Mysql – PHP -> Mysql persistent connection pooling WITHOUT thesql_pconnect – Possible

MySQL

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!

Best Answer

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!

Related Question