Hello everyone.
We have a 2 node load-balanced MySQL cluster running in active-active mode. Linux-HA is running on each with LVS balancing connections on the nodes themselves. This works great for other production clusters but the MySQL nodes require connection jailing, i.e. a single client that connects to the cluster IP must always only connect to a single node and not balance requests to the other MySQL node due to replication race conditions. (Foreign Key issues etc.) So for this we use source hashing investigating after looking through the archives of this list to see that as long as you give the weighting a fairly high number it should be able to balance connections when one node is unavailable and the weighting is set to 0. Unfortunately this doesn't seem to happen. These are my current rules: OGG-MYSQL-01:~ # ipvsadm -Ln IP Virtual Server version 1.2.1 (size=4096) Prot LocalAddress:Port Scheduler Flags -> RemoteAddress:Port Forward Weight ActiveConn InActConn TCP 10.167.20.100:6446 sh -> 10.167.20.10:6446 Local 100 3 1 -> 10.167.20.20:6446 Route 110 0 0 TCP 10.167.20.100:3306 sh -> 10.167.20.10:3306 Local 100 32 0 -> 10.167.20.20:3306 Route 110 0 0 When ldirectord detects that MySQL has died it changes the weighting where connections should then go to the other node, but they don't seem to: IP Virtual Server version 1.2.1 (size=4096) Prot LocalAddress:Port Scheduler Flags -> RemoteAddress:Port Forward Weight ActiveConn InActConn TCP OGG-MYSQL-NLB:6446 sh -> OGG-MYSQL-01:6446 Local 0 12 0 -> OGG-MYSQL-02:6446 Route 110 0 0 TCP OGG-MYSQL-NLB:mysql sh -> OGG-MYSQL-01:mysql Local 0 74 0 -> OGG-MYSQL-02:mysql Route 110 0 0 And new connections can't access the VIP: CLIENT-01:~ # mysql -pxxxxxx -h10.167.20.100 ERROR 2003 (HY000): Can't connect to MySQL server on '10.167.20.100' (111) But if I then use WLC it's fine to balance: -> RemoteAddress:Port Forward Weight ActiveConn InActConn TCP 10.167.20.100:6446 wlc -> 10.167.20.10:6446 Local 0 1 0 -> 10.167.20.20:6446 Route 110 1 0 TCP 10.167.20.100:3306 wlc -> 10.167.20.10:3306 Local 0 1 0 -> 10.167.20.20:3306 Route 110 0 1 CLIENT-01:~ # mysql -pxxxxxx -h10.167.20.100 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 545052 Server version: 5.1.38-enterprise-gpl-advanced-log MySQL Enterprise Server - Advanced Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> I could use WLC with persistence but then you could have 1 client node with a connection to each MySQL node and it's no better off. I believe only source hashing can ensure the source IP gets jailed to one node therefore I have to try and make that work unless anyone can suggest a better way? Further info can be supplied upon request. Thanks in advance for any help. Darren _______________________________________________ Please read the documentation before posting - it's available at: http://www.linuxvirtualserver.org/ LinuxVirtualServer.org mailing list - lvs-users@LinuxVirtualServer.org Send requests to lvs-users-requ...@linuxvirtualserver.org or go to http://lists.graemef.net/mailman/listinfo/lvs-users
