Has your company saturated the database pipeline to the point where other subsystems are being slowed down because the database subsystem is unable to keep up with demand? It is time to consider replacing that bottleneck and clustering some database servers to the point where another subsystem becomes the bottleneck, or better yet, there are no bottlenecks. That’s where MySQL Cluster comes in.
MySQL Cluster CGE (Carrier Grade Edition) provides a high-availability approach to handling database bottlenecks. Instead of relying on a single database server, you can have MySQL Cluster split apart your system before putting it back together in the form of a multimastered, autosharded, scalable system, capable of dynamically growing as your company exceeds your wildest expectations.
Is a Multiserver MySQL Cluster for You?
Replication has been around for some time. With databases, it started with setting up multiple systems in a master-slave environment. All changes went through the master, to maintain atomicity, consistency, isolation, and durability (ACID) integrity, and then updates were propagated out to the slave servers. Read requests might come in to the slave servers, keeping the master prepared to handle the higher-demand database operations.
Master-slave replication works great for certain environments, but if you’re looking to create a fault-tolerant, geographically disbursed system, where each server is considered a master, that’s where clustering comes into play — specifically, MySQL Cluster. Many clustering systems offer multiple database front ends to a common backing store. With MySQL Cluster, nothing is shared. This means that if one system in the cluster goes down, another can live on without relying on the dead machine. As a whole, the outside sees the cluster as a single unit. Internally, it certainly isn’t.
Requirements for a MySQL Cluster Setup
The MySQL Cluster CGE software works by connecting existing MySQL server environments together. It relies on something called autosharding to partition the live data out across the different servers, thus keeping some data local for performance but still maintaining ACID integrity thanks to the NDBCluster Storage Engine.
People typically consider acquisition costs when looking at database servers such as Oracle or DB2, as well as MySQL. There is more to deciding on a system than price alone, though. You must also consider training and administration costs, as well as support availability.
From a pure hardware point of view, you’ll need an Intel/AMD x86 or UltraSPARC processor in each computer system. Give yourself a gigabyte of memory, 3 GB of drive space and you’ll need a network connection. How many computer systems do you need? Minimally, you’ll need two for data nodes, another two for SQL (or NoSQL) application nodes, and two more for management nodes, although these last two can be combined, resulting in just four nodes needing setup.
MySQL Cluster Solutions
What is the typical environment where someone needs to use MySQL Cluster? Here are some cold, hard facts. First and foremost, someone wanting to use MySQL Cluster must be comfortable using open-source software under the GNU general public license. If not, the rest doesn’t matter. Some key reasons for needing to cluster are the need for high availability and redundancy. Geographic reasons are also common, whether because you have people in multiple offices, widely spread throughout the country, or you need to maintain a disaster-recovery setup. If one machine in the system goes down, the whole environment shouldn’t struggle in a cluster.
What type of application makes MySQL Cluster shine? Consider using Cluster for massive, multiplayer online gaming sessions, high-volume online transaction processing (OLTP), and micropayment processing. These applications are just an example of where MySQL Cluster system would shine.
How Does MySQL Cluster Work?
MySQL Cluster works by connecting the NDB engine to a standard MySQL server. NDB is where you get the clustering, thanks to its in-memory storage engine. You then run the pair of apps on a shared set of machines. These hosts, along with some supporting applications, such as a management center, then provide the MySQL Cluster.
It is important to realize that this is a shared-nothing setup, where each host works independently from all the other ones. It is just in memory NDB that is communicating with the other systems. Obviously, the larger the network of hosts running MySQL servers with NDB, the more reliable the clustered network becomes, because if any one were to fail, so would the entire system.
How to Setup MySQL Cluster
There are different setup instructions available online to get you started with MySQL Cluster on both Windows and Linux. Be sure to grab the instructions for the system version you plan on installing. The first step would be to acquire the binaries. Linux users have three different options: Download the source and build yourself, contact Oracle to provide you with the binaries, or just get the binaries with the help of your operating system’s package manager.
After acquiring the binaries, setup involves creating some users and groups to make sure permissions are set up right for the SQL node and its supporting tasks. The startup script is then placed in the right directory for the subsystem to restart on all reboots. For the data nodes, you just have to make sure that ndbd and ndbmtd commands are extracted from the .tar.gz file. For the management node, you’ll want to get a couple of different files out of the tar file: ndb_mgmd and ndb_mgm. After that, you’ll have all the commands for the different nodes set up to get started.
Is MySQL Cluster right for you? You have to ask yourself what costs are involved if the existing system setup went down. How quickly would you be able to recover to a fully working state? How much faster would the existing setup be by adding MySQL Cluster, or could things actually get worse? Overall, if you’re built out for growth, the longer it takes to add, the longer the line.