MySQL MHA vs Continuent Tungsten Shootout

www.accelerationdb.com

 Table of Contents

Background 3

MySQL High Availability and synchronous replication 3

About Continuent Tungsten 3

About MySQL MHA 4

Production Readiness 4

Development Tools 5

Architecture 5

MHA 5

MHA deployment diagram 6

MHA failover diagram 7

Continuent Tungsten 8

Tungsten Deployment Diagram 11

Tungsten Failover Diagram 12

Installation 13

MHA 13

Tungsten 13

Management 13

MHA 13

Tungsten 14

Open Source 14

Conclusion

Background

We selected two MySQL High Availability solutions - MHA from http://code.google.com/p/mysql-master-ha/ and Continuent Tungsten Enterprise from www.continuent.com. Both suites of applications MHA and Tungsten deliver MySQL high availability, both of them operate in asynchronous replication mode and both of them operate in the similar matter by monitoring master databases and transitioning the replication roles. But they are very different in the way the monitoring and role transitioning is actually performed. When selecting MySQL HA solution is is very important to understand the features and limitations of each solution, and here it is – the MySQL MHA and Continuent Tungsten Shootout!

MySQL High Availability and synchronous replication

The vast majority of MySQL HA solutions are operating in asynchronous replication mode, where the master (or masters) databases replicate the data to the slave databases using the log based replication. That is very different from synchronous data replication solutions such as DRBD, MySQL NDB cluster, Schooner and of course Galera. We do not know the actual statistics in the field but are willing to bet that the 90% (even larger) of all MySQL HA solutions are based on asynchronous replication, so we picked the leaders in the field of HA using asynchronous replication.
The reason is that asynchronous replications solutions for MySQL are so widespread and popular is that they do not require practically any changes to your existing environment – that means no kernel modules to install as in case with DRBD and no database storage changes as in case with MySQL NDB. MySQL synchronous replication solution are surely awesome but for the purpose of our “shootout” we will focus on asynchronous replication MySQL HA options.

About Continuent Tungsten

Tungsten Enterprise developed by Continuent www.continuent.com started as CJDBC project and ever since developed into mature MySQL HA asynchronous replication solution that is fully featured and easy to deploy and administer. Tungsten suite is a commercial offering, that means you have to pay license fees to use the product. Tungsten replicator was released into the open source, but that is replicator only I.E. the application that replicates the data between the nodes. In order to enjoy all the of benefits of Tungsten HA additional components are required such as Tungsten Manager, Tungsten Monitor and SQL Router. Put together all of those components and you get Tungsten Enterprise – the full featured suite of MySQL HA and replication.
Over the years Tungsten has proven itself in the field with numerous production deployments. Additionally Tungsten has a seasoned team of developers behind it – so you do not get stranded when you are in the pinch. Community support is certainly valuable, but when your databases are mangled into digital tapioca at 3 A.M. it definitely helps to have a “commercial” team of developers watching your back.
There is more to Tungsten – since release 1.5 it also supports cross data center replication. Simply put this allows Tungsten Cluster in one data center to replicate to another mirror image Tungsten Cluster in another data center.
That is very cool capability of Tungsten Enterprise since in order to accomplish that with the “traditional” MySQL replication one needs to use the combination of DRBD synchronous replication locally and MySQL replication to replicate the data over to another data center, all and all mix of synchronous and asynchronous solutions to get where Tungsten gets you far easier.
This unique capability of Tungsten is possible since the Tungsten replicator does not rely on MySQL replication to propagate the data from master to the slaves, instead Tungsten is its own log based replication for MySQL.
Tungsten is developed in Java with the installation scripts in Ruby.

About MySQL MHA

MySQL MHA or MySQL High Availability is a collection of Perl scripts that were developed and maintained by Yoshinori Matsunobu and can be downloaded from http://code.google.com/p/mysql-master-ha. Somewhere in the past Yoshinori got frustrated with MySQL MMM (haven't all of us), but instead of putting up with the frustrations of MMM and keep adding scrips like most of us did to make MySQL MMM usable Yoshinori – the savvy dude that he is developed his own solution and MySQL MHA was born.
Like Tungsten MySQL MHA is a MySQL role transition solution that has build-in monitoring components that actively monitor the the master database and perform role relocation in case of master database outage. MHA guarantees the consistency of the slaves by locating the the relay log events from the most current slave and applying them to the other slaves. Just like in the Tungsten configuration any slave can become a master. Very unlike Tungsten however MHA is using native MySQL replication for master/slave monitoring and role transitions.
MySQL MHA is a free and open source tool that can be easily (well that is assuming you know Perl) customized to fit your specific needs. Since the release .53 MHA added support for master/master configurations, that means simply that one of the selected slaves can be designated a priority fail over destination by using MHA parameters for example the following parameters designate two masters for priority fail over or switchover:
[server1]
hostname=atlas
master_binlog_dir=/var/lib/mysql
candidate_master=1
check_repl_delay=0
The above parameters candidate_master=1 and check_repl_delay=0 instruct MHA to perform the fail over to the designated master and if you would like to designate any slave not be failed on or switched on you simply do so by specifying the following in the configuration file:
[server3]
hostname=zeus
no_master=1
no_master=1 instructs MHA manager not to use server zeus for fail over or switchover.

Production readiness.

Unlike Tungsten MHA has seen limited production deployments, it is in use at DeNA where it manages 150+ masters and slaves. Database administrators including truly yours tend to be a conservative bunch, by conservative I do not mean your political orientations here ...simply stating most of the “seasoned” dba's learned to wait until someone else wrecks their own production databases prior to jumping on any new solution, because the last thing you want to do - is to shut down your production environment and desperately Google the way out of your tricky situation, all and all it pays to be a “skeptical” dba. With that we jump to our first shoot in the shootout - “Production Readiness”

Production Readiness

By production readiness we mean of course the number of the active production deployment and the duration of time the particular tool has been in use. Tungsten clearly wins here, it has been in development far longer then MHA and has a lot longer list of production deployments.

MySQL MHA	Continuent Tungsten
0	1

Development Tools

By development tools I of course mean the tools and languages that were used to develop particular solution. Tungsten is developed in Java, hence is requires Oracle Java run time environment to function, additionally Tungsten requires Ruby since the installation and deployment scripts for Tungsten are written in Ruby. MHA solution is written in Perl – while a great choice, Perl can be quirky to configure depending on your operating system release. As you'll see down in the “Installation” section there was some struggle to configure the environment for MHA to function, cpan of course comes to the rescue. I am very aware that many of you would like to dispute the Tungsten scoring an extra point here for choosing Java, and there could be a an entire books written on the subject, but Perl being the scripting language and Java “true” development language we probably can agree on that. By choice of the development environment Tungsten scored yet another shot:

MySQL MHA	Continuent Tungsten
0	1

Architecture

MHA

MHA works simply by monitoring the MySQL replication roles and transitioning those roles upon master's failure. In a single MHA cluster – by cluster of course I mean a single MySQL master database and one or more MySQL slave databases. MHA does support master/master configuration, but it is important to grasp that this capability is simply accomplished by setting the role's priorities. There can be two MySQL master databases configured but only one master can receive the application's “write” traffic, the secondary master (also a slave of primary master) is simply set in the configuration files as a priority fail over destination. Master/master configuration means that there can only be one master within a particular group of replicated databases. Every MHA cluster configuration requires a dedicated node – MHA manager node, that is separate from the databases. MHA manager node is monitoring master and slaves and performs the automated fail over from primary master to the secondary master by relocating the VIP (virtual ip), technically you do not have to have a designated secondary master database, assuming all of your slave databases are the same capacity MHA manager can automatically perform the relocation of the roles to the most current (in the replication terms) slave and relocate other slaves to subscribe to the new master.
MHA manager is essentially a collection of monitoring scripts and on each MySQL node there is a collection of MHA scripts that parse MySQL binary and relay logs, locating the position in the relay logs from which the relay logs should be applied to other slaves. In summary MHA manager does the monitoring and directs the MHA scripts installed on MySQL nodes to parse the relay logs and apply on the slave's thus transitioning the replication roles. MHA solution is replication aware and works in conjunction with MySQL replication.
For its MySQL replication awareness MHA scores a shot in MySQL high availability shootout, the solution is very robust and is using standard MySQL replication:

MySQL MHA	Continuent Tungsten
1	0

MHA solution however falls short of scoring two shots since it depends on a single MHA manager node to perform the cluster monitoring. While providing the monitoring for MySQL databases MHA manager node is itself a SPF – single point of failure or simply put if your MHA node is down the entire monitoring is down and the fail over has to be performed manually using the MHA scripts.
 

MHA deployment diagram

Here is the simple MHA configuration with two master databases and two slave databases. The secondary master database is a slave of primary master database and is set to “read-only” using MySQL parameter. MHA configuration requires a dedicated node – MHA manager node that is monitoring the entire configuration (dotted lines) and performs the automated fail over by relocating replication roles. One master at a time is receiving the applications writes and the slaves including the standby master are receiving applications “reads”. The read/write distribution must be performed by the application itself, i.e. you need to point your application's writes to a master database and your application's reads to the slave databases. Technically your application's writes are actually pointed to the MySQL master database VIP (virtual ip) that is relocated to the the standby master database in case of primary master's outage. Very simple and elegant.

MHA fail over diagram

When the master outage is detected by MHA manager, MHA will relocate the slave databases to the standby master database. New master will receive the write traffic and the slaves will receive the read traffic. 

 

Continuent Tungsten

Tungsten works conceptually similar to MHA but that is where the similarities end. Just like MHA Tungsten actively monitors MySQL instances and transitions the roles in case of primary master database outage, Tungsten actual monitoring and roles transition is very different from MHA.
Instead of using standard MySQL replication Tungsten using its own replication. Tungsten suite consists of several components:

• Tungsten replicator
  
• Tungsten monitoring
  
• Tungsten manager
  
• Tungsten SQL router/connector
  

Tungsten replicator is the component that replicates the data from MySQL master database to one or more MySQL slave databases. Tungsten replicator in earlier releases 1.3 and below used to store the replication data in the MySQL data files, but since the releases 1.5 it stores the replication data in its own log files. In fact in order to use Tungsten Replicator you must have MySQL replication turned off. Tungsten replicator has build in consistency checking and recovery procedures. Just like like using MySQL replication you can monitor the current position and skip the replication events if needed, below is the output from “thl list” command. Tungsten replicator “list” command will display the event within tungsten replicator each event has its own SEQ#

Unlike using traditional MySQL replication the interaction with tungsten replicator is performed using Tungsten replicator tools such as thl. Tungsten replicator is storing the replication data in its own logs usually located in /opt/continuent/thl:

Very unique solution for MySQL replication indeed, but it gets even better Tungsten replicator is released in open source and is capable to replicate data from other sources into MySQL for example MongoDB.
For its unique and recently open source solution and in addition the capability to replicate the database from other sources into MySQL Tungsten Replicator is scoring a shot in our shootout:

MySQL MHA	Continuent Tungsten
0	1

But there is a lot more to Tungsten suite then just the replicator. Tungsten manager is actively monitoring the nodes using group communication protocol and once the master database outage is detected tungsten manager will perform fail over to the next available node in the cluster. What is unique in Tungsten solution is that there is no need for a dedicated node to perform the monitoring and fail over In fact the fail over or switchover can be performed from any node that is member of Tungsten cluster. Tungsten has effectively eliminated the SPF (single point of failure) that is MHA needs in order to function.

Here is the view on the Tungsten console or cctrl:

The interesting aspect of the Tungsten implementation is that this management console “cctrl” can be accessed from any node member of the Tungsten cluster, master or a slave or simply put you can perform the switchover by logging to the slave. Very unique and useful feature, no need to go to a dedicated “management” node.

Tungsten SQL Router/Connector.

Tungsten SQL Router/Connector – is a unique component of Tungsten architecture and deserves special attention. Essentially Tungsten SQL router is JDBC wrapper, you establish your database connections to Tungsten SQL Router and Tungsten SQL router is in turn is connected to MySQL databases. Why bother you ask? Tungsten SQL router being a member of Tungsten suite is aware which database is a master and which are the slaves. Tungsten SQL router automatically sends write requests to the master database and read requests to the slave databases, thus eliminating the need to hard code your application for read/write splitting, just configure the Tungsten SQL router, get connected and it will take care of the rest. In addition when your primary database fails Tungsten SQL router will re route all the connection to the next in line master and slaves. Very powerful feature indeed. And with that Tungsten scores yet another shot in our “shootout”:

MySQL MHA	Continuent Tungsten
0	1

Tungsten Deployment Diagram

Here is depicted Tungsten cluster with master database and three slave databases. Application is connected to Tungsten connectors on each box. Tungsten connector automatically does read/write distribution based on databases roles.

Tungsten Failover Diagram

In this diagram MySQL master database failed and Tungsten manager promoted the next slave to become a master database. Tungsten connector re routed the database connections to to the new master and slaves and repositioned Tungsten replicator.

Installation

MHA

MHA installation is fairly simple once you worked out Perl dependencies. There are two components to install in order to have MHA running. You must have ssh keys configured on all boxes – members of MHA cluster. MHA can be downloaded from http://code.google.com/p/mysql-master-ha/downloads/list.
On each MySQL node install
mha4mysql-node-0.53-0.el6.noarch.rpm
On MHA manager node install
mha4mysql-manager-0.53-0.el6.noarch.rpm
mha4mysql-node-0.53-0.el6.noarch.rpm

MHA manager node is the dedicated node that manages all of the nodes members of MHA cluster. As you probably noticed on MHA node both manager and the node components are installed.
The actual installation using rpms was moderately painful – since the the MHA manager components rely on a particular Perl distribution, so instead of suffering though and using the documented installation path I used cpan to install Perl dependencies. After that the deployment was a breeze. You still have to manually craft the MHA cluster configuration files.

Tungsten

Tungsten installation is simple but does require Ruby and Oracle Java install prior to the installation. After that download Tungsten from www.continuent.com. You will only need one archive tungsten-enterprise-1.5.0-415.tar.gz to install.
Tungsten just like MHA requires ssh keys configured in all the nodes, prior to the installation. Once configured place Tungsten archive tungsten-enterprise-1.5.0-415.tar.gz on your master box and run the installation script . Tungsten install process will push all the components to the nodes members of the Tungsten Cluster and configure the entire cluster for you.
Frankly I missed the simplicity of the old ./configure command that was used in older 1.3 releases. New installation does not give you much control and more work is needs to be done to adapt it to the Chef or Puppet deployments.

 Version:1.0 StartHTML:0000000167 EndHTML:0000001180 StartFragment:0000000939 EndFragment:0000001164

Management

MHA

MHA provides a collection of tools (perl scripts) to manage your MHA cluster. The tools are located on the MHA management node in /usr/bin directory:

Version:1.0 StartHTML:0000000167 EndHTML:0000004664 StartFragment:0000000969 EndFragment:0000004648
Using MHA tools is fairly simple once you configured the MHA manager configuration file. MHA tool set allows you to monitor the MHA manager and the replication, you can also use MHA tools to perform a scheduled switchover i.e. switch from your primary master database to the standby master database or a slave.
The interesting aspect when working with MHA is that after initiating the switchover from primary master to the secondary you must then re establish the replication from the primary master to the secondary and restart MHA manager. That is done automatically when using Tungsten.

Tungsten

Tungsten suite provides a full featured character based console for Tungsten cluster management – cctrl. Using cctrl you can easily perform a scheduled switchover or fail over Tungsten will automatically reposition the replication roles and keep all cluster members in sync. In addition Tungsten console provides tools to recover the replication slaves if needed.
Tungsten management console is a well designed and production tested tool – and here Tungsten earns yet another shot:

MySQL MHA	Continuent Tungsten
0	1

Open Source

MHA is fully open source – feel free to use and customize.
Tungsten on the other hand is a commercial software. Continuent did release the Tungsten replicator into the open source, but that is replicator component only.
MHA scores a shot on the open source release:

MySQL MHA	Continuent Tungsten
1	0

Conclusion

Here are the results of our “shootout”:

MySQL MHA	Continuent Tungsten
2	5

Continuent Tungsten being a more “mature” and established product clearly wins by 3 points. Whichever one you choose those two are the leading options for asynchronous MySQL High Availability.

We expect MySQL MHA to grow and be used in many MySQL HA deployments. Its open source option will surely help MHA to proliferate in MySQL sphere.

Looking for MySQL experts? Visit our site www.accelerationdb.com