Re: active/passive stick-table not sticky

Mon, 13 Oct 2014 09:10:37 -0700 

On 13.10.2014 16:54, Baptiste wrote:

On Sun, Oct 12, 2014 at 6:47 PM, Benjamin Vetter <[email protected]> wrote:

Hi,

i'm using the example from
http://blog.haproxy.com/2014/01/17/emulating-activepassing-application-clustering-with-haproxy/
with haproxy 1.5.4 for a 3 node mysql+galera setup to implement
active/passive'ness.

global
   log 127.0.0.1 local0
   log 127.0.0.1 local1 notice
   maxconn 8192
   uid 99
   gid 99
   debug
   stats socket    /tmp/haproxy

defaults
   log global
   mode http
   option tcplog
   option dontlognull
   retries 3
   maxconn 8192
   timeout connect 5000
   timeout client 300000
   timeout server 300000

listen mysql-active-passive 0.0.0.0:3309
   stick-table type ip size 1
   stick on dst
   mode tcp
   balance roundrobin
   option httpchk
   server db01 192.168.0.11:3306 check port 9200 inter 12000 rise 3 fall 3
on-marked-down shutdown-sessions
   server db02 192.168.0.12:3306 check port 9200 inter 12000 rise 3 fall 3
on-marked-down shutdown-sessions backup
   server db03 192.168.0.13:3306 check port 9200 inter 12000 rise 3 fall 3
on-marked-down shutdown-sessions backup

I tested the stickyness via this tiny ruby script, which simply connects and
asks the node for its stored ip address:

require "mysql2"

loop do
   begin
     mysql2 = Mysql2::Client.new(:port => 3309, :host => "192.168.0.10",
:username => "username")
     puts mysql2.query("show variables like '%wsrep_sst_rec%'").to_a
     mysql2.close
   rescue
     # Nothing
   end
end

First, everything's fine. On first run, stick-table gets updated:

# table: mysql-active-passive, type: ip, size:1, used:1
0x1c90224: key=192.168.0.10 use=0 exp=0 server_id=1

Then i shutdown 192.168.0.11. Again, everything's fine, as the stick table
gets updated to:

# table: mysql-active-passive, type: ip, size:1, used:1
0x1c90224: key=192.168.0.10 use=0 exp=0 server_id=2

and all connections now go to db02.

Then i restart/repair 192.168.0.11, the stick table stays as is (fine), such
that all connections should still go to db02.
However, the output of my script now starts to say:

...
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.12"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.12"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.11"}
{"Variable_name"=>"wsrep_sst_receive_address", "Value"=>"192.168.0.12"}
...

such that sometimes the connection goes to db01 and sometimes to db02.
Do you know what the problem is?

Thanks
   Benjamin





Hi Benjamin,

Could you remove the 'backup' keyword from your server lines and run
the same test?

Baptiste
... same effect and even worse, as soon as i start the script two times (in two independent terminals) the output of both terminals begin to regularly switch between the hosts when used in combination with "balance roundrobin". When i change to "balance first", i get the same behaviour as with "backup", such that the output begins to switch between the hosts when the old primary comes back UP while the script runs within two terminals concurrently. Moreover, i now noticed that the output of
watch -n 0,1 'echo "show table mysql-active-passive" | socat unix-connect:/tmp/haproxy stdio'
sometimes is empty but after next refresh (0,1s) again shows the correct sticky entry from before. Does this mean that the stick-table for some reason gets flushed regularly? I already tried to set "expire 20h", but doesn't help either. 

Thanks for your help.

Reply via email to