Re: Architecture problem, rw-splitter does not work

[Daniel Wharton]

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).