Planning for a MySQL Cluster Setup

Recently, I’ve had the opportunity to setup a MySQL Cluster infrastructure for a major educational institution. Theoretically, the deployment is a straightforward process. Of course like most IT projects, the actual roll-out process is only a minor concern.

The process of gluing together the components and making sure they stick solidly is where the challenge is more extreme.

In this article, I’m not going to discuss how to setup a MySQL Cluster. There are tons of documentations regarding this procedure already available. What I’ll be touching on is presented more as a deployment strategy, preparation and maintenance guide.

By the way, if your organization is interested in deploying a MySQL Cluster infrastructure, please note that HTNet Solutions provides this service and would be more than happy to get yours up and running.

Introduction

MySQL Cluster is a HA implementation of MySQL built from the ground up as a shared nothing setup by having redundant system components (ie. having more than one of each component). Through this approach, the annual availability of a properly designed MySQL Cluster is more likely to reach 99.999% (which is roughly 5 minutes and 16 seconds downtime per year).

Contrary to many laymen opinion, getting this amount of high availability is not simply a case of just deploying MySQL Cluster and forgetting about it. Like I said, the actual deployment process is probably the least of your concern.

Server Preparation

In MySQL Cluster, the servers form are your engines. Without properly working engines, you vehicle will crash. In this case, your database system is the vehicle.

So what are the minimal server requirements for a MySQL Cluster? To be called a proper MySQL Cluster, you need the following servers:

  1. Data Node (to store the actual data)
  2. SQL API Node (to provide an SQL interface to access your data)
  3. Management Node (to manage the MySQL Cluster)

The absolute minimum number of physical servers you’ll need for a HA MySQL Cluster setup would be as follows:

  • Two servers configured as data nodes
  • Two servers configured to run both SQL API and Management nodes

This setup provides the most minimum redundancy capability as you will have a standby server for any given MySQL Cluster function.

For cluster data nodes, you’ll need the following minimum amount of RAM:

(Database Size * Replicas * 1.1) / Number of Data Nodes

So for a two data node cluster that will be used to store databases sized 80GB and having two replicas, you’d need the following amount of RAM per data node:

(80GB * 2 * 1.1) / 2 = 88GB RAM minimum per data node

It is worth noting however that MySQL Cluster in MySQL 5.1 or later supports disk based storage for non-indexed data. Nevertheless, I’m personally reluctant to use this feature unless absolutely unavoidable.

A good practise when deploying production servers is to just run services that are necessary for them to function. This holds true for member servers in a MySQL Cluster as well. For example, the data nodes shouldn’t be running Apache. Even if the cluster data is meant for web applications, you should consider running the Apache service on the API nodes instead since the resource requirements for them aren’t as great as the data nodes.

Last but not least, you should remember that the main point of deploying MySQL Cluster is to have high availability of data. Even with the most advanced servers being used in your cluster, it will all be useless if they are unprepared for power outages. Therefore it is critical that your cluster members are hooked up to decent UPS units.

Network Preparations

This is another critical aspect of your MySQL Cluster infrastructure. The power of your cluster’s member server can be rendered useless by using dodgy network equipment to transport the data around.

Therefore it is recommended to use the best network equipment possible for your MySQL Cluster. It’s highly recommended that member servers are installed with gigabit NICs which are hooked up to a dedicated gigabit switch. When all that’s done, do not skimp on the network cables… use the best CAT6 gigabit cables for your infrastructure to ensure data transport integrity.

Of course as with the servers, hook up your switch to a UPS unit as well. Power failure doesn’t only affect servers but also the virtual highway linking your member servers.

Preparing for Outages

A fact of life is that when things can go wrong, at some point they will! The reason that you’re deploying MySQL Cluster is to mitigate the effects of such an occurrence.

MySQL Cluster has its own built in protection mechanism where the cluster as a whole will continue to function as long as at least one data node and one API node is still active within the cluster.

Nevertheless, your front end application might not know that one of the API node it interfaces with is down. This should be a relatively simple fix where you can program the application to try accessing other members in the API node pool.

Alternatively, you can hook up your API nodes to a load balancer that could redirect network traffic to a server that is known to be available to process the request.

Remember the UPS units that I keep recommending that you hook your servers to earlier? Most decent UPS units have an IEC connector port that you can link to your servers. This interface is put there to notify the server when power is lost and they should prepare to shut down gracefully before the backup power in the UPS is drained.

Using some simple programming, you could write an application (or even a shell script) to shutdown the MySQL Cluster member server when a power outage occurs. This is especially important for data nodes as it provides a window for them to dump whatever data that’s in memory to disk and resume from that last stable state.

Reducing Post-Deployment Woes

Regular backups are part and parcel of maintaining a data storage infrastructure. MySQL Cluster is no different.

You should perform regular backups of the clustered data; if possible on a dedicated storage unit (eg. a SAN unit) and make sure that the storage device is protected from abrupt power outages as well; yeah, hook that thing up to a UPS unit as well!

It also helps that the assigned admin for the MySQL Cluster read up on how to perform rolling restarts of member servers. This is a definite requirement because at some point, there are bound to be OS or software patches that will require a member server to be rebooted.

That concludes my write-up on this topic. Yes, I realize it’s lengthy and at times pretty dry (hope it isn’t that frequent though) but believe me that going through the things I highlighted in here will help you on at least identifying the potential gotchas rather that colliding head on with them unprepared… and that, my friend, is not something nice at all.