High Availability with mysqlnd_ms on Percona XtraDB Cluster_MySQL

This is the second part of my series on High Availability with mysqlnd_ms. In my first post, “Simple MySQL Master HA with mysqlnd_ms,” I showed a simple HA solution using asynchronous MySQL replication. This time we will see how to leverage an all-primary cluster where you can write to all nodes. In this post I usedPercona XtraDB Cluster, but you should also be able to do the same with MySQL NDB Cluster or Tungsten Replicator.

To start with, here is the mysqlnd_ms configuration I used:mysqlnd_ms_mm.ini.  All of these files are available from myGithub repository. Below, I have three Percona XtraDB Cluster nodes, all defined as masters and no slaves. I’ve configured a roundrobin filter where all connections will happen on the first node, in this case192.168.56.44 . In case the first node fails, the second node will be used and so forth until no more nodes are available. Another interesting configuration option here is the loop_before_master strategy whereby if connection or a statement to the current server fails, it will be retried silently on the remaining nodes before returning an error to the user, more on this below.

JavaScript

{ "primary": { "master": { "master_1": { "host": "192.168.56.44", "port": "3306" }, "master_2": { "host": "192.168.56.43", "port": "3306" }, "master_3": { "host": "192.168.56.42", "port": "3306" } }, "slave": { }, "filters": { "roundrobin": [ ] }, "failover": { "strategy": "loop_before_master", "remember_failed": true } } }

{

  "primary":{

    "master":{

      "master_1":{

        "host":"192.168.56.44",

        "port":"3306"

      },

      "master_2":{

        "host":"192.168.56.43",

        "port":"3306"

      },

      "master_3":{

        "host":"192.168.56.42",

        "port":"3306"

      }

    },

    "slave":{},

    "filters":{"roundrobin":[]},

    "failover":{"strategy":"loop_before_master","remember_failed":true}

  }

}

Similar to my previous post, I also used a custom INI file for PHP to use, this time aptly namedmaster-master.ini :

mysqlnd_ms.enable = 1mysqlnd_ms.disable_rw_split = 1mysqlnd_ms.multi_master = 1mysqlnd_ms.force_config_usage = 1mysqlnd_ms.config_file = /home/revin/git/demo-me/phpugph201407/mysqlnd_ms_mm.ini

mysqlnd_ms.enable=1

mysqlnd_ms.disable_rw_split=1

mysqlnd_ms.multi_master=1

mysqlnd_ms.force_config_usage=1

mysqlnd_ms.config_file=/home/revin/git/demo-me/phpugph201407/mysqlnd_ms_mm.ini

A new addition to this configuration ismysqlnd_ms.multi_master , when enabled it would allow you to use all nodes or just one and treat the others as passive. The PHP script I used this time is calledmaster-master.php , it is largely similar tomaster-slave-ng.phpwith a few differences:

1. There is no need for /tmp/PRIMARY_HAS_FAILED  sentinel as all nodes were writable.
2. There is no need for /*ms=master*/  SQL hint when validating a connection from connect_mysql function since all nodes acts as master.

So here is a quick test, first with roundrobin filter, after 4 INSERTs, I shutdown192.168.56.44  which sends my connection to the next server in the configuration,192.168.56.43 . When I started back 192.168.56.44  again, the script resumed connections there. Pretty cool right?

[revin@forge phpugph201407]$ php -c master-master.ini master-master.phpLast value 3564 from host 192.168.56.44 via TCP/IP and thread id 19Last value 3565 from host 192.168.56.44 via TCP/IP and thread id 20Last value 3566 from host 192.168.56.44 via TCP/IP and thread id 21Last value 3567 from host 192.168.56.44 via TCP/IP and thread id 22Warning: connect_mysql(): MySQL server has gone away in /home/revin/git/demo-me/phpugph201407/master-master.php on line 63Warning: connect_mysql(): Error while reading greeting packet. PID=23464 in /home/revin/git/demo-me/phpugph201407/master-master.php on line 63ERRROR: 192.168.56.43 via TCP/IP [2006] MySQL server has gone away on line 30Last value 0 from hostand thread id 0Last value 3568 from host 192.168.56.43 via TCP/IP and thread id 1552Last value 3569 from host 192.168.56.43 via TCP/IP and thread id 1553[...]Last value 3584 from host 192.168.56.43 via TCP/IP and thread id 1568Last value 3585 from host 192.168.56.44 via TCP/IP and thread id 18

[revin@forgephpugph201407]$php-cmaster-master.inimaster-master.php

Lastvalue3564fromhost192.168.56.44viaTCP/IPandthreadid19

Lastvalue3565fromhost192.168.56.44viaTCP/IPandthreadid20

Lastvalue3566fromhost192.168.56.44viaTCP/IPandthreadid21

Lastvalue3567fromhost192.168.56.44viaTCP/IPandthreadid22

Warning:connect_mysql():MySQLserverhasgoneawayin/home/revin/git/demo-me/phpugph201407/master-master.phponline63

Warning:connect_mysql():Errorwhilereadinggreetingpacket.PID=23464in/home/revin/git/demo-me/phpugph201407/master-master.phponline63

ERRROR:192.168.56.43viaTCP/IP[2006]MySQLserverhasgoneawayonline30

Lastvalue0fromhost  andthreadid0

Lastvalue3568fromhost192.168.56.43viaTCP/IPandthreadid1552

Lastvalue3569fromhost192.168.56.43viaTCP/IPandthreadid1553

[...]

Lastvalue3584fromhost192.168.56.43viaTCP/IPandthreadid1568

Lastvalue3585fromhost192.168.56.44viaTCP/IPandthreadid18

Here’s another test using the random filter which allows you to write to all nodes, on my mysqlnd_ms_mm.ini above, I just changedroundrobin  torandom . As you can see, all three nodes were being used, of course in random, at the same time you will also see when I shutdown 192.168.56.44  around where the connect_mysql  errors and then the server was used again near the bottom after a started it back up. Still pretty cool right?

[revin@forge phpugph201407]$ php -c master-master.ini master-master.phpLast value 3590 from host 192.168.56.42 via TCP/IP and thread id 2060Last value 3591 from host 192.168.56.43 via TCP/IP and thread id 1569Last value 3592 from host 192.168.56.43 via TCP/IP and thread id 1570Warning: connect_mysql(): MySQL server has gone away in /home/revin/git/demo-me/phpugph201407/master-master.php on line 63Warning: connect_mysql(): Error while reading greeting packet. PID=23919 in /home/revin/git/demo-me/phpugph201407/master-master.php on line 63ERRROR: 192.168.56.43 via TCP/IP [2006] MySQL server has gone away on line 30Last value 0 from hostand thread id 0Last value 3593 from host 192.168.56.42 via TCP/IP and thread id 2061Last value 3594 from host 192.168.56.42 via TCP/IP and thread id 2062Last value 3595 from host 192.168.56.42 via TCP/IP and thread id 2063Last value 3596 from host 192.168.56.42 via TCP/IP and thread id 2064Last value 3597 from host 192.168.56.43 via TCP/IP and thread id 1576Last value 3598 from host 192.168.56.43 via TCP/IP and thread id 1577Last value 3599 from host 192.168.56.43 via TCP/IP and thread id 1578Last value 3600 from host 192.168.56.43 via TCP/IP and thread id 1579Last value 3601 from host 192.168.56.42 via TCP/IP and thread id 2065Last value 3602 from host 192.168.56.43 via TCP/IP and thread id 1581Last value 3603 from host 192.168.56.43 via TCP/IP and thread id 1582Last value 3604 from host 192.168.56.42 via TCP/IP and thread id 2066Last value 3605 from host 192.168.56.44 via TCP/IP and thread id 19Last value 3606 from host 192.168.56.43 via TCP/IP and thread id 1583Last value 3607 from host 192.168.56.44 via TCP/IP and thread id 21

[revin@forgephpugph201407]$php-cmaster-master.inimaster-master.php

Lastvalue3590fromhost192.168.56.42viaTCP/IPandthreadid2060

Lastvalue3591fromhost192.168.56.43viaTCP/IPandthreadid1569

Lastvalue3592fromhost192.168.56.43viaTCP/IPandthreadid1570

Warning:connect_mysql():MySQLserverhasgoneawayin/home/revin/git/demo-me/phpugph201407/master-master.phponline63

Warning:connect_mysql():Errorwhilereadinggreetingpacket.PID=23919in/home/revin/git/demo-me/phpugph201407/master-master.phponline63

ERRROR:192.168.56.43viaTCP/IP[2006]MySQLserverhasgoneawayonline30

Lastvalue0fromhost  andthreadid0

Lastvalue3593fromhost192.168.56.42viaTCP/IPandthreadid2061

Lastvalue3594fromhost192.168.56.42viaTCP/IPandthreadid2062

Lastvalue3595fromhost192.168.56.42viaTCP/IPandthreadid2063

Lastvalue3596fromhost192.168.56.42viaTCP/IPandthreadid2064

Lastvalue3597fromhost192.168.56.43viaTCP/IPandthreadid1576

Lastvalue3598fromhost192.168.56.43viaTCP/IPandthreadid1577

Lastvalue3599fromhost192.168.56.43viaTCP/IPandthreadid1578

Lastvalue3600fromhost192.168.56.43viaTCP/IPandthreadid1579

Lastvalue3601fromhost192.168.56.42viaTCP/IPandthreadid2065

Lastvalue3602fromhost192.168.56.43viaTCP/IPandthreadid1581

Lastvalue3603fromhost192.168.56.43viaTCP/IPandthreadid1582

Lastvalue3604fromhost192.168.56.42viaTCP/IPandthreadid2066

Lastvalue3605fromhost192.168.56.44viaTCP/IPandthreadid19

Lastvalue3606fromhost192.168.56.43viaTCP/IPandthreadid1583

Lastvalue3607fromhost192.168.56.44viaTCP/IPandthreadid21

So here are some issues I’ve observed during these tests:

1. remember_failed  during failover does not work as advertised. Supposedly, a failed node should not be used again for every connection request but in my test, this is not the case. See more fromthis bug. This means that if you have 2 out of 3 failed nodes in this scenario the overhead would be too big when testing both connections. Perhaps some sort of in memory shared TTL can be used to overcome this? I’m not sure.
2. If you look closely around line 7 on my last output above the error displayed is kind of misleading. In particular it saysERRROR:192.168.56.43viaTCP/IP , whereby it was not 192.168.56.43  that failed, it was192.168.56.43 . This is because under the hood, immediately after failure the next node will be cycled to, this is especially true since we have loop_before_master configured. I sure do have a bug on the script that should capture the host_info  properly, but this is something to always keep in mind so you don’t keep scratching your head.

So we’ve seen these two forms of possibilities and they definitely have use cases and advantages. On the other hand because of the issues we have found so far(I’ve reported 4 bugs on the PHP bugs database during the course of these tests including onecrashing), I recommend to make sure you test seriously before putting this on production.