From: "Raoul Scarazzini" <rasca(a)redhat.com&gt; To: rdo-list(a)redhat.com Sent: Wednesday, October 7, 2015 12:45:32 PM Subject: [Rdo-list] How to know which galera server haproxy is pointing in an overcloud HA env Hi all, I'm operating in an overcloud HA environment, using galera as db backend. In haproxy I've got this configuration: listen mysql bind 172.16.20.11:3306 option tcpka option httpchk stick on dst stick-table type ip size 1000 timeout client 0 timeout server 0 server overcloud-controller-0 172.16.20.13:3306 backup check fall 5 inter 2000 on-marked-down shutdown-sessions port 9200 rise 2 server overcloud-controller-1 172.16.20.14:3306 backup check fall 5 inter 2000 on-marked-down shutdown-sessions port 9200 rise 2 server overcloud-controller-2 172.16.20.15:3306 backup check fall 5 inter 2000 on-marked-down shutdown-sessions port 9200 rise 2 Now, for what I've understood, the "stick on dst" directive determines that just one of the three galera servers is contacted by the proxy (even if the galera configuration is an active/active/active setup), and this is fine for a couple of reasons that involves locking and things like this. So using stick on dst makes our balancer operating as active-standby-standby without automatic failback. And this is also fine. What I am looking for at this point is to understand which server haproxy is pointing. I've enabled this option in haproxy conf: stats socket /var/run/haproxy mode 600 level admin so I am able to see, on the host which carries the VIP associated to haproxy bind address, the stick table: [root@overcloud-controller-1 ~]# echo "show table mysql" | socat /var/run/haproxy stdio # table: mysql, type: ip, size:1000, used:1 0x7f9babb4d7d4: key=172.16.20.11 use=0 exp=0 server_id=1 But of course this does not help me to understand which host is haproxy contacting. I was expecting to see on the output something useful, but the key is the value I already know. Can you help me to find out a way? Thanks a lot, -- Raoul Scarazzini rasca(a)redhat.com _______________________________________________ Rdo-list mailing list Rdo-list(a)redhat.com https://www.redhat.com/mailman/listinfo/rdo-list To unsubscribe: rdo-list-unsubscribe(a)redhat.com

From: "Raoul Scarazzini" <rasca(a)redhat.com&gt; To: "Marius Cornea" <mcornea(a)redhat.com&gt; Cc: rdo-list(a)redhat.com Sent: Wednesday, October 7, 2015 1:18:40 PM Subject: Re: [Rdo-list] How to know which galera server haproxy is pointing in an overcloud HA env Il giorno 7/10/2015 13:07:30, Marius Cornea ha scritto: > You can check the haproxy stats dashboard and determine which of the backend nodes takes the sessions. To access it look for the ip address haproxy.stats binds to in haproxy.cfg and reach it via http on port 1993. Hi Marius, thank you for the answer. This is not so clear to me. What do you mean with "takes the sessions"? If I understand correctly I need to store somewhere a delta, because without it how can I know which server is increasing?

Look at this: [root@overcloud-controller-1 ~]# echo "show stat" | socat /var/run/haproxy stdio | grep mysql mysql,FRONTEND,,,65,87,2000,6290,12411640,28349332,0,0,0,,,,,OPEN,,,,,,,,,1,13,0,,,,0,1,0,13,,,,,,,,,,,0,0,0,,,0,0,0,0,,,,,,,, mysql,overcloud-controller-0,0,0,65,87,,6290,12411640,28349332,,0,,0,0,0,0,UP,1,0,1,0,0,10271,0,,1,13,1,,1,,2,1,,13,L7OK,200,54,,,,,,,0,,,,0,0,,,,,1,OK,,0,1,0,14961, mysql,overcloud-controller-1,0,0,0,0,,0,0,0,,0,,0,0,0,0,DOWN,1,0,1,8,2,4651,4676,,1,13,2,,0,,2,0,,0,L7STS,503,65,,,,,,,0,,,,0,0,,,,,-1,Service Unavailable,,0,0,0,0, mysql,overcloud-controller-2,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,0,1,6,1,6042,94,,1,13,3,,0,,2,0,,0,L7OK,200,55,,,,,,,0,,,,0,0,,,,,-1,OK,,0,0,0,0, mysql,BACKEND,0,0,65,87,200,6290,12411640,28349332,0,0,,0,0,0,0,UP,1,0,2,,0,10271,0,,1,13,0,,1,,1,1,,13,,,,,,,,,,,,,,0,0,0,0,0,0,1,,,0,1,0,14961, As you can see the mysql,overcloud-controller-1 is DOWN, so the choice is between the other two. From what I can suppose, is the first one the suspected one, but just because it has a value that increases. Your suggestion is to double take the value to each server and see which one increases? What if in that moment there are no connections and the values remains the same? Thanks, -- Raoul Scarazzini rasca(a)redhat.com

From: "Raoul Scarazzini" <rasca(a)redhat.com&gt; To: "Marius Cornea" <mcornea(a)redhat.com&gt; Cc: rdo-list(a)redhat.com Sent: Thursday, October 8, 2015 4:15:59 PM Subject: Re: [Rdo-list] How to know which galera server haproxy is pointing in an overcloud HA env Il giorno 7/10/2015 17:21:11, Marius Cornea ha scritto: > I was just suggesting a way to see to which of the backend nodes haproxy is directing the traffic. Please see the attachment. Thanks again Marius, from your point of view, does this script make sense? #!/bin/bash # haproxy bind address VIP=$1 # Associative array for controller -> bytes list declare -A controllers function get_stats { # 2nd field -> controller name | 10th field -> bytes in | 11th field -> bytes out stats=$(echo "show stat" | socat /var/run/haproxy stdio | grep mysql,overcloud | cut -f2,10 -d,) } get_stats # Put the first byte values in the array for line in $stats do controller=$(echo $line | cut -f1 -d,) controllers[$controller]=$(echo $line|cut -f2 -d,) done # Do something (nothing) on the VIP's db mysql -u nonexistant -h $VIP &> /dev/null get_stats # Compare the stats the one different is the master for controller in ${!controllers[@]} do value2=$(echo "$stats"|grep $controller|cut -f2 -d,) [ ${controllers[$controller]} -ne $value2 ] && echo "$controller is MASTER" || echo "$controller is slave" done I know it's ugly, but since we don't have any other method to get those informations I don't see any other solution. Of course it can be adapted to get values from http instead of the socket (that by default is not enabled). What do you think?

Thanks a lot, -- Raoul Scarazzini rasca(a)redhat.com