MySQL – When to Start Using Connection Pooling

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!

This started off as a comment but it's getting a bit long.

Perhaps you could provide a better description of what it is you actually want rather than simply say it's like something else.

If you want multiple clients to use the same connection, then it must be implemented in the DBMS, otherwise you have no session management. Further you need to advise how you intend dealing with issues of state spanning more than a single request/response (i.e. transactions).

It would also be helpful to know why you want it. MySQL has a very different setup cost and per-session overhead cost compared to PostgreSQL - why do you think it needs it, and why you think it would actually yeild any benefit.