Before You Begin with MySQL Cluster

Here is a short list of things you should check before you attempt to install or use MySQL Cluster for a project (if you are just setting up a test database as a proof of concept, you can skip this part):

• Ethernet Make sure you have operable networking between all the nodes in the cluster and make sure that any firewalls you have are disabled while you are installing and testing. (Chapter4, "Security and Management," covers the rules you need to add to your firewall.)
• RAM Make sure you have plenty of RAM free on the servers you plan to use as storage nodes. The Linux command free -m tells you how much RAM you have free. As in the following example, you can add the free and cached columns to get a (very) rough (and high) estimate of available RAM, in megabytes:

  [root@host] free -m total used free shared buffers cached Mem: 3956 3471 484 0 199 2638 -/+ buffers/cache: 633 3322 Swap: 2047 22 2024

  This server has 2638MB + 484MB free, which is just over 3GB.

• Table structure If you are attempting to move an existing database from MyISAM or InnoDB (or any other storage engine), you might find that it requires some changes. The main limitations are that enormous tables (that is, enormous in the width of each row) will not work; there is a hard-coded limit of 8KB per row (not including data stores in BLOB fields; as you will see later on, these are stored differently). Tables can have as many rows as your RAM will allow. You should also remember that variable-width columns become fixed-width columns with an additional overhead, which means that a VARCHAR(200) field uses more than 200 bytes per roweven if it has only 2 bytes of information in it. Depending on the character set, it may take up significantly more space; using utf8, for example, this field will take up at least 600 bytes per row. This explains why most tables end up much larger when they are converted to NDB (Network Database, the storage engine that MySQL Cluster uses).

Obtaining and Installing MySQL Cluster

Before you install MySQL Cluster, you should clearly understand the different processes that you have to undertake to set up the three different type of nodes:

• To set up a SQL node, all you need to do is install the MySQL-Max package and add a few lines to my.cnf.
• To set up management and storage nodes, you need to install the MySQL Cluster binaries, the names of which all start with ndb.

MySQL-Max is needed on all nodes. As described shortly, you should install MySQL-Max on all the nodes that you plan to take part in the cluster before you do anything else.

Designing a Cluster

It is important that you give some thought to the number of physical servers your cluster will require. Shortly, you will see the sample cluster that we use through this book. It is as simple as you can get if you want a highly available setup, and it requires three physical machines.

You will learn later in this chapter how to calculate the RAM usage for your cluster, but this section explains the important decisions you need to make regarding the layout of your new cluster.

First, you have to decide how many copies of each piece of data you would like the cluster to hold. This determines the number of nodes that will be in each node group. (A node group is a group of storage nodes that hold the same data.)

Second, you need to decide how many storage nodes should be part of your cluster. This depends on two things:

• Performance The number of storage nodes, all other things remaining equal, is (very nearly) proportional to performance. However, you should bear in mind that the total number of nodes should be the product of the number of copies of each piece of data you just decided on and a power of 2. For example, if you decide to have two copies of each piece of dataas many clusters doyou should have 2, 4, 8, and so on storage nodes.
• Memory usage If you have a database that takes up 45GB in the cluster format (NDB), you are unlikely to want to use two nodes with 45GB RAM plus operating system overhead! You are more likely to use 8 servers with 12GB of RAM per server (assuming that the cluster is set to hold two copies of each piece of data).

The number of replicas determines the total memory usage required; however, you can spread this out over any number of nodes (and therefore servers) from 1 to 62 to keep the amount of memory required per server to an acceptable level. Note that 64 nodes is the maximum total of all nodesincluding SQL, management, and storage nodesso the realistic maximum number of storage nodes is 62 to allow for 1 management and 1 SQL node.

At this stage, you can estimate your total memory usage based on your current database size (which you can easily determine) if you use the following formula:

Total size of database in NDB format x Number of replicas x 1.1

You then divide this figure by the number of nodes to estimate the memory requirement per node. For initial rough calculations, you can treat the size of the database in NDB as being equal to the current size in whatever storage engine the database is held, subject to the caveat that if you use variable-width columns, you should covert your variable-width columns to fixed-width and calculate the new size and then plug the new size in to the preceding formula to get a more accurate estimate. Of course, you can just make a guestimate or use the ndb_size.pl script that is discussed shortly.

Later in this chapter, you will learn how to calculate your memory usage far more accurately. The purpose of mentioning this basic formula here is to give you some idea of how much RAM you are likely to need. Bear in mind that this estimate could be off by a long way in some circumstances; many new users are surprised by the amount of RAM that a MySQL cluster can and does consume!