MySQL Replication Failoverv – Maxscale vs MHA: Part Three

In our previous two posts we described in depth how MySQL Master-HA (MHA) and MaxScale + MariaDB Replication Manager (MRM) both perform their so called slave promotions (also known as master failover). MHA has been created by Yoshinori Matsunobu when he still worked for DeNA and the product has been available for over five years. MaxScale is a MySQL proxy created by MariaDB and the combination with the MariaDB Replication Manager has only been added recently.

When we compare the two procedures, it is apparent they both have good sides and bad sides for automated failover. Both support adding and removing virtual ips and both check the entire topology during failover. It is difficult to say which one is better and which one is not so we will highlight the differences.

Comparison

Flapping

MHA protects itself by design from flapping by its one shot failover. If you ever have used MMM before, you are well aware what damage flapping can cause. On the other hand: if your master failed due to a capacity problem, it is most likely that the new master will fail before an engineer can act upon the failover, fix the master and restart MHA. MaxScale is able to promote another slave (or the old master) to a master if the new master is dead as well so it will continue to perform failover. However it is not protected against flapping as we configured it to act upon a the master_down event and once we have promoted a slave to master, it could trigger the event as well.

Lost transactions

MaxScale will not be able to have the slaves catch up until the last transactions of the binlog of the old master and not be able to have them catch up before attaching them to the new master. Due to the MariaDB GTID, the most advanced master will have the most transactions of the old master and the slaves can apply the remaining transactions and catch up. MHA copies the binlogs from the candidate master to ensure the MHA manager is up to date and MHA also works without GTID when applying the delta. This does mean that if the most advanced slave was not able to copy the last few transactions from the master you will lose them and when the master comes back online it may give conflicting transactions.

There is one plus using MaxScale: if you wish to have similar functionality as offered within MHA you could utilize MaxScale as a binlog server (and intermediate-master) to ensure that if the master is down you always have one single slave/intermediate-master that is the most advanced one. However the binlog server is beyond the scope of this article.

Network partitioning

If the master would “fail” due to a networking issue between MaxScale and the master, the failover procedure gets started regardless if the master really is failing. Especially with an unstable network, this could lead to potential flapping behaviour. Suppose the master comes back up again and goes down again shortly after due to the network issues, the “master down” event will be triggered a second time in MaxScale.

For MHA this has been taken care of by adding a second MHA Manager node, preferably in another section of your network. This gives MHA a better view on the situation and topology, it might not failover as it is unnecessary. For MaxScale this could be overcome by adding an extra check in the script that is executed by MaxScale, connect to another host in the network and check if the master is down from that host as well.

Roles

Within MHA you are able to apply roles to a specific host, so for instance ‘candidate_master’ and ‘no_master’ will help you determine which hosts are preferred to become master. A good example could be the data center topology: spread the candidate master nodes over multiple racks to ensure HA. Or perhaps you have a delayed slave that may never become the new master even if it is the last node remaining.

This last scenario is likely to happen with MariaDB Replication Manager as it can’t see the other nodes anymore and thus can’t determine that this node is actually, for instance, 24 hours behind. MariaDB does not support the Delayed Slave command but you could use pt-slave-delay instead. There is a way to set the maximum slave delay allowed for MRM, however MRM reads the Seconds_Behind_Master from the slave status output. Since MRM is executed after the master is dead, this value will obviously be null.

Conclusion

MHA is still more advanced, more intelligent and more versatile than MaxScale with the MariaDB Replication Manager. It can handle all replication methods with or without GTID and ensure all hosts are equal. The product is relatively mature after five years of existence but at the same time new replication topologies arrive, like Galera masters with asynchronous slaves, and the question is if these will be supported.

MaxScale is really good at figuring out the topology of the hosts and detecting dead hosts while it delegates the failover to the MariaDB Replication Manager. The MariaDB Replication Manager has to detect the topology again and it works great in various topologies but limits you to MariaDB GTID replication. MaxScale has a lot of potential and the decoupling between detection and failover make it quite flexible. At the same time this is a huge pitfall: relying on external scripts invoking external binaries can be error prone. For instance the example on MariaDB’s website is no longer functioning with the existing MRM code in Github and thus no failover will happen. Extending the script with extra checks can also lead to more false positives or negatives.

MaxScale would be more robust and fail safe if multiple MaxScale instances could communicate with each other, make decisions based on multiple angles and have a routing plugin perform the failover. This way there is only one product involved and the failover logic is not duplicated over multiple products.