Improved read-only load balancing with MySQL Proxy 0.7.x
A customer had come to us this week asking about some of the functionality in the ro-balance.lua script that ships with MySQL Proxy. As I investigated things further, I came to realise that due to some changes in the 0.7.x series, the script was broken.
Being the helpful fellow I am, I updated the ro-balance.lua script so that it works again. I also ended up making some nifty improvements...
For those of you unfamiliar with the ro-balance.lua script, it is designed to do the following:
- proxy new connections to a pool of slaves
- periodically check that slave IO thread is running and slave has not fallen too far behind
- take slaves determined to be offline or otherwise unfit for use (see above) out of the pool
- fall back to the master server if no slaves are available at all
A problem that the script had was that if a slave was found to be offline, the proxy would never try to connect to it again. Also, if a slave fell too far behind or had its IO thread stopped, it would be flagged as bad and never checked again. This meant the only way to retry these slaves again would be to bounce the proxy, which is rather undesirable.
I had a tinker with the script and I think I have managed to address the issue.
I added a new configurable variable to the script called min_recheck_time.
When a new connection is initiated the script will now re-attempt to use (and thus check) connections that are flagged as down, too far behind or without a slave IO thread provided that the bad connection was not last checked any more than min_recheck_time ago.
We don't want the re-checks to occur too often because when we check a connection that is still bad, the proxy will drop the clients connection forcing it to reconnect, so that it will get a good connection next time.
The current default for min_recheck_time is 60 seconds, but that can be configured easily at the top of the script.
I created my own little branch on launchpad called script-updates as I would like to see this make its way into the mainline. I also have a few other ideas for proxy script stuff I would like to implement.
If you would like to get your hands on the code, you can check it out via launchpad. The launchpad branch can be checked out with "bzr branch lp:~lmulcahy/mysql-proxy/script-updates".
I'm interested in knowing if you find it useful or find any problems with it.
Cheers!
