MySQL Forums
Forum List  »  Router & Proxy

Re: Architecture problem, rw-splitter does not work
Posted by: Daniel Wharton
Date: May 09, 2008 04:14PM

Yeah, I hear you. Its not a perfect balance by any means. I hope that I (or someone else) gets the time to work out the connection pooling a little better.

I run a lot of mysql-proxy instances. Actually I went the route of running one on each web-app server (it works great). I'll let you in on my dirty little secret. Its a hack I'm relatively ashamed of: : )

#!/bin/bash

export LUA_PATH="/usr/local/share/mysql-proxy/?.lua"

lastoctet=$(/sbin/ifconfig | grep -A 1 eth0 | tail -n 1 | awk '{ print $2; }' | awk -F '.' '{ print $4; }')

# create /var/log/xxx if nonexistent
mkdir -p /var/log/xxx

if [ $[$lastoctet % 5] -eq "0" ]; then

/usr/local/sbin/mysql-proxy \
--proxy-address=:3306 \
--proxy-backend-addresses=xx.xx.xx.xx:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.41:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.42:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.43:3306 \
--proxy-lua-script=/etc/mysql-proxy/rw-splitting.lua >> /var/log/xxx/mysql-proxy.log 2>&1

elif [ $[$lastoctet % 5] -eq "1" ]; then

/usr/local/sbin/mysql-proxy \
--proxy-address=:3306 \
--proxy-backend-addresses=xx.xx.xx.140:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.42:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.43:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.44:3306 \
--proxy-lua-script=/etc/mysql-proxy/rw-splitting.lua >> /var/log/xxx/mysql-proxy.log 2>&1

elif [ $[$lastoctet % 5] -eq "2" ]; then

/usr/local/sbin/mysql-proxy \
--proxy-address=:3306 \
--proxy-backend-addresses=xx.xx.xx.140:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.43:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.44:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.46:3306 \
--proxy-lua-script=/etc/mysql-proxy/rw-splitting.lua >> /var/log/xxx/mysql-proxy.log 2>&1

elif [ $[$lastoctet % 5] -eq "3" ]; then

/usr/local/sbin/mysql-proxy \
--proxy-address=:3306 \
--proxy-backend-addresses=xx.xx.xx.140:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.44:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.46:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.41:3306 \
--proxy-lua-script=/etc/mysql-proxy/rw-splitting.lua >> /var/log/xxx/mysql-proxy.log 2>&1

else

/usr/local/sbin/mysql-proxy \
--proxy-address=:3306 \
--proxy-backend-addresses=xx.xx.xx.140:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.46:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.41:3306 \
--proxy-read-only-backend-addresses=xx.xx.xx.42:3306 \
--proxy-lua-script=/etc/mysql-proxy/rw-splitting.lua >> /var/log/xxx/mysql-proxy.log 2>&1

This takes the last octet of the ip address for the machine mysql-proxy is running on and does a mod to determine how to order the backend addresses. I have a total of 5 production DB servers.

Yeah, I know at this point it should be a case statement. : ) I also know this will be unruly soon. I have a pretty heavily modified rw-splitting.lua for doing special stuff with sessions and returning a static response for SHOW FIELDS FROM on some tables (to work around a wacky ruby on rails behavior surrounding merge tables). This is buying me time until I can work out a cleaner solution.

I found when you have a bunch of instances like this, it is VERY helpful to run mysql-proxy inside of daemontools (reason why you don't see --daemon).

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Architecture problem, rw-splitter does not work
4022
May 09, 2008 04:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.