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!


Comments:

This is great. We were about to write the same thing. Have you looked into similar changes for the read/write script? Detecting lag and automatically bringing servers back online would be great additions to that script.

Posted by Mark on April 12, 2010 at 04:21 AM EST #

Hi,

how can I get the ro-balance.lua script? I setup a mysql replication but I dont know how to make master write and slave read. Could you help me as I'm a newbie in this.
thanks

Posted by stani on December 06, 2010 at 05:58 AM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Lachlan Mulcahy

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today