MySQL MHA vs Continuent Tungsten Shootout

Advertisement

Clients often ask us to help evaluate comparable systems to select the technologies most appropriate for their individual situation. To support this, we do ongoing research and document the pros and cons of given technologies to see how they stack up against each other.

Our latest endeavor in this area is a comparison – or “shootout” – of two well-regarded MySQL High Availability solutions – MHA and Continuent Tungsten Enterprise. Both suites deliver MySQL high availability, both of them operate in asynchronous replication mode, and both of them operate in a similar matter by monitoring master databases and transitioning replication roles. But they are very different in the way the monitoring and role transitioning is actually performed. When selecting a MySQL HA solution, it is very important to understand the features and limitations of each solution, so we hope that this MySQL MHA and Continuent Tungsten Shootout can help with that decision process.

MySQL High Availability and synchronous replication
The vast majority of MySQL HA solutions operate in asynchronous replication mode, where the master (or masters) databases replicate the data to the slave databases using log based replication. That is very different from synchronous data replication solutions such as DRBD, MySQL NDB cluster, Schooner and Galera.

One reason that asynchronous replications solutions for MySQL are so widespread and popular is that they require minimal changes to your existing environment – that means no kernel modules to install, as with DRBD and no database storage changes, as with MySQL NDB. MySQL synchronous replication solution can be useful, but for the purpose of our shootout we will focus on the more widespread asynchronous replication MySQL HA options.

About Continuent Tungsten
Tungsten Enterprise (developed by Continuent) started as a CJDBC project and has since developed into a mature MySQL HA asynchronous replication solution that is fully featured and easy to deploy and administer. Tungsten Suite is a commercial offering with license fees, and the Tungsten replicator application was released as open source. In order to enjoy the full benefits of Tungsten HA, additional components are required such as Tungsten Manager, Tungsten Monitor and SQL Router. Together, these comprise 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 with full commercial support options.

Since release 1.5, Tungsten also supports cross data center replication, which allows Tungsten Cluster in one data center to replicate to another mirror image Tungsten Cluster in another data center. In order to accomplish that with “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 – a 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 installation scripts in Ruby.

About MySQL MHA
MySQL MHA (a.k.a. MySQL High Availability) is a collection of perl scripts that were developed and maintained by Yoshinori Matsunobu and can be downloaded here.

Like Tungsten, MySQL MHA is a MySQL role transition solution that has built-in monitoring components to actively monitor the the master database and perform role relocation in the event 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. Unlike Tungsten however, MHA uses native MySQL replication for master/slave monitoring and role transitions.

MySQL MHA is a free and open source tool that can be easily customized to fit your specific needs. Since release .53 MHA supports for master/master configurations, which allows one of the selected slaves to be designated a priority failover destination by using MHA parameters. For example, the following parameters designate two masters for priority failover 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 failover to the designated master. To designate any slave to 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 failover or switchover.

Comparison
Production Readiness
By production readiness, we mean the number of active production deployments and the duration of time the particular tool has been in use. Tungsten clearly wins here, as it has been in development far longer than MHA and has many more production deployments.

MySQL MHA: 0 Continuent Tungsten: 1

Development Tools
Here we refer to the tools and languages that were used to develop the particular solution. Tungsten is developed in Java, and requires Oracle Java runtime environment to function. Additionally, Tungsten requires Ruby since the installation and deployment scripts for Tungsten are written in Ruby. MHA is written in Perl, which can be quirky to configure depending on your operating system release. We therefore score another shot for Tungsten:

MySQL MHA: 0 Continuent Tungsten: 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 – 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 failover 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 monitors master and slaves and performs the automated failover 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. 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. MHA manager does the monitoring and directs the MHA scripts installed on MySQL nodes to parse the relay logs and apply on the slaves, 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: 1 Continuent Tungsten: 0
MHA, 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. If your MHA node is down, the entire monitoring is down and failover has to be performed manually using the MHA scripts.

MHA deployment
Here is a simple MHA configuration with two master databases and two slave databases. The secondary master database is a slave of the primary master database and is set to “read-only” using MySQL parameter. MHA configuration requires a dedicated MHA manager node that monitors the entire configuration (dotted lines) and performs the automated failover by relocating replication roles. One master at a time receives the application writes and the slaves (including the standby master) receive application “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 Failover
When the master outage is detected by MHA manager, MHA will relocate the slave databases to the standby master database. The new master will receive the write traffic and the slaves will receive the read traffic.

MySQL MHA

Continuent Tungsten
Tungsten works conceptually similarly 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, though the Tungsten monitoring and roles transition functions very differently from MHA.

Instead of using standard MySQL replication Tungsten uses 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. In earlier releases (1.3 and below), Tungsten replicator used to store replication data in MySQL data files, but since release 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 built-in consistency checking and recovery procedures. Just as with 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, and each event has its own SEQ#

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

This is a unique solution for MySQL replication. Additionally, because Tungsten replicator is open source, it is also capable of replicating data from other sources, such as MongoDB, into MySQL. Because of this, MySQL Tungsten Replicator scores a shot in our shootout:

MySQL MHA: 0 Continuent Tungsten: 1
But there is a lot more to Tungsten suite then just the replicator. Tungsten manager actively monitors the nodes using group communication protocol, and once the master database outage is detected Tungsten manager will perform failover to the next available node in the cluster. What is unique in the Tungsten solution is that there is no need for a dedicated node to perform the monitoring and failover. In fact the failover or switchover can be performed from any node that is a member of Tungsten cluster. Tungsten has effectively eliminated the SPF (single point of failure) that 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 slave. You can perform the switchover by logging to the slave, which is a unique and useful feature that does not require you 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 a JDBC wrapper – you establish your database connections to Tungsten SQL Router and Tungsten SQL router is in turn connected to the MySQL databases. This allows Tungsten SQL router to be 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 connections to the next-in-line master and slaves, which is very powerful. And with that Tungsten scores yet another shot in our “shootout”:

MySQL MHA: 0 Continuent Tungsten: 1

Tungsten Deployment
Here is a Tungsten cluster with a master database and three slave databases. The application is connected to Tungsten connectors on each box, and the Tungsten connector automatically performs read/write distribution based on database roles.

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

Installation
MHA
MHA installation is fairly simple once you have 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 that are members of MHA cluster. MHA can be downloaded from here.

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 node 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. The MHA manager components rely on a particular Perl distribution, so instead of suffering though and using the documented installation path, we 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 Continuent. You will only need one archive tungsten-enterprise-1.5.0-415.tar.gz to install.

Tungsten, 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.

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:

Using MHA tools is fairly simple once you have configured the MHA manager configuration file. MHA tool set allows you to monitor the MHA manager and the replication, and 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 failover. 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: 0 Continuent Tungsten: 1

Open Source
MHA is fully open source, which allows you to customize it as you wish.

Tungsten, on the other hand, is a commercial software. Continuent released the Tungsten replicator as open source, but only that component.

MHA scores a shot on the open source release:

MySQL MHA: 1 Continuent Tungsten: 0

Conclusion
Here are the results of our “shootout”:

MySQL MHA: 2 Continuent Tungsten: 5

While we felt both leading options for asynchronous MySQL High Availability were strong choices, we recognize that Continuent Tungsten is a more mature and well-established product, and it clearly wins by 3 points.

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.

Have your say!

0 0