|
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@xxxxxxxxxxxxxxxxxxxxxx
Send requests to lvs-users-request@xxxxxxxxxxxxxxxxxxxxxx
or go to http://lists.graemef.net/mailman/listinfo/lvs-users
|
