Friday, September 19, 2014

A Comparison of MySQL HA Architectures

I was recently asked to design a new MySQL HA architecture for an internal project which currently runs on a Master-Slave. The acceptable criteria were pretty much defined and agreed:

  • Provide High Availability (no need to be automatic failover)
  • Easy Failover (everyone should be able to do it without being a DBA)
  • Seamless Failover (the application should not be modified on a failover)
  • Scale Reads (for reports and DWH applications)
  • The performance should be reasonable good or at least not worse than the current setup
  • The application is not robust enough to handle crash failures in an async master-master setup (ie distributing writes is out of the question)

With this in mind, we were discussing several setups:

Setup #1 MHA:

This is a very popular setup in the MySQL community, and if setup well it provides you with zero downtime if one node crashes. 

In this architecture we have 2 elements: MHA manager (x1) and MHA nodes (x3 in our case). The MHA Manager can run on a separate server and can manage multiple master-slave clusters. It polls the nodes of the clusters and, on finding a non-functioning master it promotes the most up-to-date slave to be the new master and then redirects all the other slaves to it. 

The failover is transparent to the application. An MHA node runs on each MySQL server and facilitates failover with scripts that monitor the parsing & purging of logs. The architecture being proposed here is shown in figure 1.
Figure 1 - MHA with 3 nodes


The problem with this setup is that it is very difficult to maintain if something goes wrong. Also I hate Perl.


Setup #2 Manual Managed Replication (Master-Slave-Delayed Slave):

In this architecture we make use of traditional self managed master-slave replication with an additional delayed slave. The application always points to the master and should the master goes down, we have to manually promote the slave to master and point the application to it. 

Doing a failover entails DBA knowledge - the downtime, in comparison to architecture 2, will be a bit longer. The benefit with this architecture is its simplicity and lack of Perl scripts. This architecture is shown in figure 2. 

Figure 2 - Simple MySQL replica with a delayed Slave

A delayed slave is useful if a MySQL user accidentally drops a table or deletes a row. The problem with this setup is that on a failover the application needs to be changed to point to the new master.

Setup #3 Percona XtraDB Cluster:

I will talk more about this setup in detail in a future blog. I personally installed my first Percona XtraDB/Galera cluster last April 2014 on a Rackspace infrastructure. Writes and reads scaled beautifully and the failover was seamless. But I was experiencing random node failures, network partitioning and corruption. Was it a bug in Percona XtraDB or Galera? Was is due to Rackspace infrastructure? I filed bug reports and I never had the time to investigate further so I ditched this setup completely. I feel like this product needs to mature a bit more before being production ready.

Setup #4 MySQL Clustered by HAProxy:

When designing this architecture, I kept in mind all the pitfalls of the previous setups. In this architecture we are making use of HAProxy to handle all the failover between two clusters of master-slave nodes. The application will write to just one cluster at any point in time, but the master-master replication between both clusters will make sure that they are always in sync. To failover, we point haproxy to the other cluster, as depicted in Figure 3. 

Note that during the failover there is no downtime on the application. Therefore this can be used to do real time and downtime-less changes on the application-database stack.

Figure 3 - Failover using HAProxy and master-master replication between the clusters

This is personally my favorite setup due to my positive experience with HAProxy. Additionally, this setup ticks all the boxes for our requirements of then new architecture. As an extra bonus we can setup one of the slaves as a delayed. While writes will not be scaled (to satisfy our acceptable criteria), the reads can be scaled if we wanted to.

How would I make this setup fool-proof that even non-DBAs can failover? Simple - we can use Jenkins or Bamboo tasks to switch the HAProxy cluster as per diagram.

In the next blog post I will show in detail how to setup an HAProxy load balanced MySQL cluster.
Post a Comment