MySQL Database Clustering Solutions

There are two different cluster solutions available to MySQL users: Galera and MySQL Cluster. Both have some very nice features, but they also have issues.

MySQL Cluster

“MySQL Cluster is a scalable, real-time in-memory, ACID-compliant transactional database, combining 99.999% availability with the low TCO of open source,” says Andrew Morgan, MySQL Principal Product Manager at Oracle. MySQL Cluster is built around a distributed, multiple-master architecture with no single point of failure. MySQL Cluster scales horizontally on standard servers and is accessed with SQL and NoSQL interfaces.

Originally designed as database for telecommunications industry, where availability and real-time performance are essential, MySQL Cluster has been enhanced with new features for web, mobile and enterprise applications, whether on-premise, hybrid, or in the cloud. It is suited to such performance-critical activities as payment processing, financial trading, fraud detection, multi-player online gaming, inventory management, shopping carts, fulfillment tracking, and much more.

Having a multi-master architecture with no single point of failure allows users to structure the cluster with a single data node, one management node, and one API node from which MySQL is run. Typically, the data node would be installed with an API node on each instance.

Users typically have one management node, but that’s risky. That would be your single point of failure. You will need at least two management nodes if you wanted to have no single point of failure.

What hardware does MySQL Cluster require? If you want to have two data nodes and two management nodes, four servers will be needed to implement this. Multiple redundancy would require at least five servers. Each API node will require a server if you want to keep your API nodes separate from data and management nodes.

The first thing to think about with MySQL Cluster is latency. When a transaction is submitted, the cluster needs to have confirmed that all the components within the system “know” the transaction is completed. The lag time associated with this verification and confirmation creates some inherent latency.

At Datavail, we have seen latency issues arise, some of which have resulted in a cascade of increasingly problematic issues throughout the system. In one situation, I was examining a bottleneck and needed to look in the error log. The process of looking at the nodes resulted in a series of events causing corruption in both nodes as well as an unsuccessful repair requiring the restoration of data from backup files.

Galera Cluster

Galera Cluster is a second option. It offers synchronous replication, has an active-active multi-master topology, and reads and writes to any cluster node. Because it has automatic membership control, any failed nodes are automatically dropped from the cluster. It also offers automatic node joining and true parallel replication on the row level. Among its many other attributes, it has a native MySQL look and feel, and you can use Galera Cluster with either the InnoDB or XtraDB storage engines.

Galera “offers real replication,” says to Toby Volpe of ZDnet, giving higher availability than that provided by single instances or traditional failover.” Galera is desired for “its ability to synchronously replicate data across remote data centres,” says John Leonard at Computing UK in a piece on monitoring high availability clusters.

How does Galera Cluster differ from MySQL Cluster? Galera Cluster uses no management node. In a cluster, each of the different nodes communicates with one another. This can result in latency, particularly if you have many nodes in your system and if you are sending a large transaction.

That entire transaction has to be completed on every single node, and each of these nodes must confirm that the transaction has been completed before the data is considered committed. These attributes make Galera Cluster very transactional and ACID-compliant, which is great, save for the inherent latency.

You can also use it, for example, to write to a single node and propagate that data to other nodes as a backup strategy. Or you could do all your writes to one node and do all your reads to another node. You can, in other words, structure the cluster to operate in a way that best meets your organization’s needs.

Other MySQL Database Issues

MySQL Cluster and Galera Cluster are just two of the topics covered in a new whitepaper from Datavail, MySQL Architecture Options. It covers such topics as MySQL database storage solutions, database replication, data redundancy, failover, data restoration and aspects of MySQL database architecture you will find useful. Download a copy today!

The best database architecture for your firm depends on your business requirements are. You need to evaluate your specific needs — the required redundancy, speed, failover, and other parameters — against issues such as costs and currently installed infrastructure. You’ll need to answer questions such as, “Where is MySQL going to be hosted?” “Will I have my own servers?” “Will it be hosted in the cloud or via a hosting service?” “Will I need more hardware?”

There is no single perfect solution for every organization or database. There are several flavors of MySQL out there and they all work very well. Each has its pros and cons, endorsements and caveats associated with it. Ultimately, you will need to select an architecture that will enable your organization to fully access, manage, and update your data resources through MySQL.

If you are uncertain about the best MySQL architecture options for your organization, Datavail can help you evaluate your current database environment against your company’s business priorities and future goals. With more than 400 database administrators worldwide, Datavail is the largest pure-play database services provider in North America. With 24×7 managed database services, including database design, architecture and staffing, Datavail can support your organization as it works with MySQL, regardless of the build you ultimately select.