To install MySQL Cluster from a binary tarball, you need to locate the correct tarball for your platform at the MySQL download page (for version 5.0, this is http://dev.mysql.com/downloads/mysql/5.0.html) and then obtain the relevant file. You then need to extract it, move it to the correct folder, add the mysql user, set up the initial tables, and start the server:

Note that a folder is created, with the “full” name of the distribution (mysql-max-5.0.12-beta-linux-i686, in the preceding example). For the sake of neatness and upgradeability, you should create a soft link (that is, a symlink) to this folder mysql. This also helps with upgrading because you can install both the older and new versions and then just stop MySQL, change the link, and start the upgraded version very easily. You can create this link easily:

The MySQL database server can run as either root or any other user on the system. From a security point of view, it’s a bad idea (and the developers have made it quite difficult) to run the MySQL database server as root; therefore, you should create a special mysql user and group for this purpose:

Now you need to tell MySQL where you want it to store its data. Unless you want it to store the databases in the same folder as the actual programs (/usr/local/mysql/data/), you should create a file called /etc/my.cnf that has the following contents:

Next, you need to initialize the system database and tables. This means creating the mysql and test databases as well as the system tables that MySQL needs in order to run. You must also make sure that these new tables are owned by the mysql user:

On distributions of Linux that support the service servicename start command and syntax, you should copy the helpful mysql.server script into the /etc/rc.d/init.d/ folder (where the service command can find it):

Otherwise, you should copy the mysql server to wherever your distribution keeps init scripts and execute it.

You now need to add the MySQL bin directory to the PATH variable so you do not need to append /usr/local/mysql/bin/ before each binary name every time you want to use it. If you use the BASH shell, as most distributions do, you need to edit your .bash_profile file in your home directory. You need to make sure to change this file for each user on the system. Essentially, this involves opening up $HOME/.bash_profile with a text editor (such as vi/vim, pico, or emacs) and adding /usr/local/mysql/bin to the line that defines the variable $PATH, which may look something like this:

You change it to this:

Then you log out and log back in again. The quickest way to test whether it is all working is to try to run the MySQL client:

If you get an error such as -bash: mysql: command not found, you have not correctly changed the path. If you get the correct version, you have MySQL-Max installed.

There are many other things you would need to do if you were installing a server in a production environment (for example, set a root password, optimize my.cnf), but we do not cover these steps here. If you are not entirely confident with any of these things, you should consult the MySQL manual.

Installing from a binary RPM is the easiest method of installing MySQL-Max. Essentially, all you need to do is download and install the RPMs you need. Table 1.1 lists the RPMs you need for each node.

TABLE 1.1 The RPM Packages Provided by MySQL

1. You don’t strictly need this or the MySQL-ndb-extra package to run a storage node, but we strongly recommend that you install it because this book often uses packages that appear in all three storage node packages.

Running multiple nodes on one machine is absolutely fine: You just need to install all the packages you might need. For example, to run a storage and SQL node on a server (but not a management node), you should install MySQL-server, MySQL-ndb-storage, MySQL-ndb-tools, MySQL-ndb-extra, and MySQL-Max.

The actual installation process is as simple as obtaining the RPMs you need and installing them. To install all the packages (for SQL, storage, and management nodes as well as benchmarking tools), you use the following commands. Note that this is the process for RPM packages; however, you can easily change this to suit other forms of package, such as those found on OS X. First of all, you obtain the packages:

Then you install them. For RPMs, this should be as simple as using the following command:

This installs MySQL with every optional extra package. You should be aware that the benchmarking tools require perl-DBI, which you may have to obtain before you can install the RPM.

Remember that unless you have a really good reason to install MySQL Cluster by compiling from source, it is probably a bad idea. However, if for some reason you want to compile your own copy, this section provides the steps you should follow. You need GNU bison 1.75 (or newer), make, autoconf 2.58 (or newer), automake 1.8, libtool 1.5, and m4 to run the next set of commands. Even though many operating systems come with their own implementations of make, chances are good that the compilation will fail with strange error messages if you use their versions; therefore, it is highly recommended that you use GNU make (sometimes called gmake) instead.

First, you need to download the source tarball from MySQL.com:

When you have obtained the source code, it is time to compile it. At this stage, you can issue whatever flags you want to the compiler in addition to the basic ones listed here:

You then install MySQL and add the mysql user and group.

Now you need to tell MySQL where you want it to store its data. Unless you want it to store the databases in a subdirectory in the same folder as the actual programs (/usr/local/mysql/var), you should create the file /etc/my.cnf, with the following contents:

Next, you need to initialize the tables. This means you need to create the system databases that MySQL needs to run. You should then make sure that these new tables are owned by the mysql user:

You should now have a working MySQL installation, which you can start like this:

If you want to update the BitKeeper code any time after you first install it, you simply run these commands:

Then you continue with the following commands:

After you have installed the relevant packages on each node in your cluster, you need to log in to the server on which you are going to run your management node and start to define your cluster.

First, you need to check whether the directory /var/lib/mysql-cluster exists, and if it does not, you need to create it. Within it, using your favorite text editor, you create a file called config.ini in which you define the nodes in your cluster (this is where all settings go for all nodes). All the other nodes need is a one-line configuration line called a connect string, which goes in my.cnf and tells the nodes where they can find the management node.

You should be aware that you can put config.ini wherever you like. What matters is that when you start the management daemon, you must be in the folder that contains config.ini. Some people therefore put it in /etc/. You still need to create the directory /var/lib/mysql-cluster, which your nodes will use to store data that they need to store to disk.

You can also define your settings within config.ini in my.cnf; however, this is not recommended if you want to keep your cluster configuration away from your database configuration, which in most cases is a good idea.

If you have three separate servers and want to run storage nodes on two of them, SQL nodes on all three, and the management node on the server that does not have a storage node, you produce a configuration like this, where 10.0.0.1, 10.0.0.2, and 10.0.0.3 are the IP addresses of the servers:

We use this sample configuration for the rest of this chapter, and if you are able to get your hands on three servers, we strongly recommend that you start out using this configuration.

You should now be able to produce a simple cluster with a slightly different format than shown in the preceding example—perhaps with three replicas and three storage nodes² or two replicas and four storage nodes. However you want to set up your first cluster, write out your config.ini file and check it before advancing to the next step.

You only need to define ID if you are planning on running multiple storage nodes on the same physical machines. If you do not define this parameter, the management daemon will assign IDs in the order in which they connect.

If you define IDs for storage nodes, it is good practice to also define an ID for the management node, typically 1. You do this by including an ID line in the [NDB_MGMD] section.

You can choose a number between 1 and 64 for this parameter; node groups are formed from consecutive IDs, so if you have four storage nodes and NumberOfReplicas is equal to 2 (that is, each node group contains two nodes), the first two IDs will form Node Group 1, and the second two will form Node Group 2.

If you have two physical servers, you need to make sure that you define the IDs such that storage nodes on the same physical machine are in different node groups, as in this example:

This means that Server 1 has a node in both node groups on it, which is good because each node group holds only 50% of the data, so you need members of both node groups to actually have a complete set of data in the cluster. This means that a failure of either server does not remove all nodes in one node group, so your cluster can survive.

You now need to start the management daemon. This is the program that runs in the background and controls your cluster.

To start the management daemon, you log in as root to the server you want to run your management node, change directory to /var/lib/mysql-cluster, and start the ndb_mgmd daemon:

Alternatively, you can start the ndb_mgmd daemon from any folder and pass the location of the configuration file to the daemon:

Note that this example assumes that you have the MySQL bin directory in your path; if you do not, you need to add /usr/local/mysql/bin/ (or wherever you installed your binaries) in front of ndb_mgmd and in front of every other MySQL binary in this book. Refer to the end of section “Installing MySQL Cluster from a Binary Tarball,” earlier in this chapter, for instructions on how to add to your path.

If the output from the ndb_mgmd daemon is nothing at all, you are okay. If you get an error, you almost certainly made a mistake in your config.ini file, so you need to go back and compare it with the preceding example, and if you are still stuck, refer to Chapter 2, which explains exactly what to put where in a config.ini file.

The next step is to enter the management console, which is the client to the management node—rather like the mysql binary is a client to the MySQL server. The management console is called ndb_mgm. You log in to the management node and issue the command SHOW, which asks the management daemon to send it details about the current status of the cluster:

If, on the other hand, you get an error like this when you attempt to use SHOW, your management daemon has failed to start properly:

If this is what you get, you should turn to Chapter 6, “Troubleshooting,” for ideas on solving this sort of issue.

In order to get your cluster to work, you need to connect your storage nodes. The process is identical on all storage nodes, so you can complete it with one and then repeat the same process for all your other storage nodes. Essentially, all you need to do is tell the daemon where to find the management server and then start the ndbd daemon (which is the storage daemon).

To tell the daemon where to find the management server, you can either put some lines in /etc/my.cnf or you can pass it as a command-line parameter. We suggest that you use my.cnf, but we illustrate both methods here. Note that it is not a bad idea to add these lines to /etc/my.cnf on the management server as well, even if it is not acting as a storage node. If you do not, it will just connect to the local machine (127.0.0.1), which normally works (we relied on it working earlier), but when it becomes possible to bind the management daemon to one IP address, you may no longer be able to rely on it working without an IP address (even if it is just the primary IP of the local machine) in my.cnf.

To use my.cnf, you create the file /etc/my.cnf, if it does not already exist, and add the following:

Then you need to start the storage node daemon, ndbd:

This should return no output if it works.

The other way to do it does not involve a my.cnf file. When you start ndbd, you just pass the connect string to it, like this:

The only way to tell if this method works is to return to the management console (by using ndb_mgm on the management server) and see if the status has changed. If you do this after starting one storage node, you get output like this:

If it has not changed and you still get all lines reporting “not connected,” then something has gone wrong. Refer to Chapter 6 for further help.

When you have all your storage nodes started, you should see that the output from SHOW in ndb_mgm changes to Started:

This means that your cluster is working. However, you need to connect at least one SQL node before you can start using it.

The requirement for a SQL node is that it must have MySQL-Max rather than just MySQL. Apart from that, you can use any recent version of MySQL to connect to your new cluster. All you need to do is add two lines to the [mysqld] section of my.cnf:

In this example, 10.0.0.1 is the IP address or hostname (not recommended) of your management node.

You then simply have to restart MySQL, with a command such as service mysql restart.

If MySQL fails to come up, you should check the error log—typically /var/lib/mysql/hostname.err. If you have an error such as the following:

you do not have the MySQL-Max package installed or you did not include support for NDB during your compilation process if you compiled from source. Refer to the earlier section in this chapter “Installing MySQL-Max” for further advice.

If you have created the config.ini file described in this chapter, with three servers and two storage nodes, one management node, and three SQL nodes, you should get the following output from a SHOW command:

Notice how the storage nodes (called “NDB nodes” in the management client) have a Nodegroup parameter. Both storage nodes are in Node Group 0; you should have been expecting this because you have NumberOfReplicas defined as two, and you have two storage nodes; therefore, you have one node group. If you added another two storage nodes, you would have two node groups.

Now that you have MySQL Cluster working, you can start to create some tables. To do so, you log in to the MySQL client on any one of the SQL nodes (using your mysql root user and password if you have created one), and then you create a database and table and insert and select a row:

You can see that this has all worked. If the cluster is not actually working properly, you may get an error such as ERROR 1015 (HY000): Can’t lock file (errno: 4009) or ERROR 1050 (42S01): Table ‘ctest’ already exists while attempting to create the table or while selecting or inserting the row. In this case, you need to go back to ndb_mgm and ensure that all nodes in the cluster are correctly started, referring to Chapter 6 for help, if needed.

The final thing you are likely to want to check is that all your SQL nodes are actually working, so if you insert one row on one node, it does appear on all the others. You can do this by logging in to each storage node and inserting another row before selecting all rows. However, before you try to do this, you should read the next section on auto-discovery of databases.

You will inevitably have to restart your cluster from time to time. We cover upgrading a cluster later on (see the section “Upgrading MySQL Cluster,” later in this chapter), but here we describe how nodes resynchronize their data and cover the procedure to recover from the process of shutting down the complete cluster or just a single node.

The procedure for upgrading across major versions—for example, from 4.1 to 5.0 or 5.0 to 5.1—is simple but requires a short period of downtime:

We suggest that whenever possible, you set up a test cluster on the new version to make sure your data can work nicely. There is a possibility that things might change between major versions, and this might mean, for example, that you would need to increase some parameters in config.ini to get things to work. You probably don’t want to find this out for the first time when you have to revert to the older version after attempting to upgrade or have to start searching mailing lists for the solution to your problem with your database down.

You might find that it is extremely inconvenient to have to log in to each machine to start ndbd, particularly if you have a very large number of nodes in your cluster and/or exotic authentication methods on the servers that make up your cluster which make logging in to lots of servers a time-consuming and tedious process. Several tricks are worth mentioning; the first is to use SSH to start all your data nodes (using the ssh -t command to issue a command and then log straight out). You issue the following commands on the management server to completely start your sample cluster (which you should make sure is shut down first):

Then, after a few minutes, your cluster should start as usual:

If you set up SSH authentication using private/public keys, you can complete this process without using passwords, which means you can script it very easily.

Another trick you can use is to start the ndbd daemon on a node but not actually get it to join the cluster; in this case, it just connects and waits for the management server to instruct it to start. You do this by passing the -n command as an argument to ndbd when you start it (that is, -n = nostart). If you were to start both storage nodes in the sample cluster by using -n, you would get this:

You can start these storage nodes by issuing the <id> START command in the management client:

You can extend this trick further by writing a simple shell script to detect whether ndbd is not running on a node and then get it to “half start” like this so that you can always restart nodes that die from within the management client. This can make dealing with a large cluster much easier because it means you do not have to log in to each server to start the cluster, and as soon as nodes crash or reboot, they will automatically half start, allowing you to completely start them from the management client—eliminating the need for you to actually log in to your storage nodes. A very simple script such as this should do the trick:

Note that you should make sure that the ndbd line contains the full path to the ndbd binary (that is, /usr/local/mysql/bin/ndbd or /usr/sbin/ndbd) if the script does not work without it (which it might not do, particularly if you run it from cron).

You should test the script by starting up your cluster. After the cluster is started, you add the crontab line (crontab -e on most Linux distributions) to the storage nodes, like this:

This runs the script every 2 minutes.

When the cluster is up and running, you should stop a storage node, and within 3 minutes, it should reconnect so you can start it properly from within the management client:

At this stage, you should wait 3 minutes for the script to kick in on Storage Node 2 and then restart it. Then, you issue a SHOW command, like this:

Notice how Storage Node 2 has now gone to the status “not started.” You can now start it by using 2 START:

If the script restarts a node, it will add a log entry with the date and time in /root/ndbd-restart-log. You could also have it email an administrator so that that person knows to come investigate the cause of the cluster crash and complete the startup process.

You might wonder why the preceding script uses ndbd -n rather than just ndbd (which would start the node completely and not require an <id> START command to be entered into the management client by an administrator). The answer is that you should never have a script automatically restart cluster nodes. If cluster nodes die, you should always investigate what the problem is. Of course, it would be perfectly possible to remove the nostart flag from the script and to also write a similar version for ndb_mgmd on the management node; this would allow you to reboot all your machines, and they would then restart the cluster when they started back up. We strongly recommend that you not do this; there are many situations in which you may not actually want both nodes to attempt to start (for example, if you suspect that one node has gotten a corrupt copy of the database, you might want to start the other node(s) in the same node group and then start the dodgy node by using --initial). We believe it is important for an administrator to be around to investigate the cause of node failure.

If you are considering deploying a cluster in MySQL Cluster, in order to determine how many nodes you need and how much RAM you will need installed in each, you need to be able to calculate the memory usage per table.

The first step is calculating the amount of RAM that each row uses in each table. You then multiply the size of each row by the number of rows to get the size of each table. Then you add all the tables together to get the size of the database. Finally, you work out the exact memory usage on each storage node. To finish off, you can test your prediction and see how much RAM the cluster in MySQL Cluster is actually using.

If you have never actually thought about how much space each table occupies, as many administrators do not, this process can be quite daunting. Something that confuses many new administrators and that you should always bear in mind is the difference between bits and bytes: There are 8 bits in 1 byte. In almost all cases, you will be dealing with bytes.

Calculating the RAM Usage of a Table

It is important to remember that MySQL Cluster does not support variable-width fields, which means all rows occupy the same space. This means that VARCHAR fields are actually stored as CHAR fields internally (note that this should change in version 5.1). Many poorly designed tables have massive VARCHAR fields (for example, VARCHAR(200) for email addresses). This is because when using storage engines that support variable-width columns, such as MyISAM, it is easier to make the field far too large than to risk making it too small. However, in MySQL Cluster, such tables take up a colossal quantity of RAM. It is always worth attempting to reduce the size of variable-width fields if at all possible.

The calculation of the memory requirement for NDB tables follows the same principles as for the other table engines, with some differences. We now list the memory requirements for different types of fields.

In the following tables, M refers to the defined size of the string field (for example, in a VARCHAR(200) field, M = 200.) Note that character sets are 1 to 3 bytes in size. If you are unsure of how big your character set is, you can find the information by using the SQL command SHOW CHARACTER SET and viewing the Maxlen field.

TABLE 1.2 String Types

TABLE 1.3 Numeric Types

TABLE 1.4 Date Types

To calculate DataMemory, you first need to work out how many bytes the fields are going to use. Then you add a fixed row overhead, which is 16 bytes, or 12 bytes if you declare all your columns as NOT NULL. Then, for each ordered index, 10 bytes of storage are used. You get an ordered index for each INDEX you define. In addition, you automatically get one ordered index for each primary key and each unique key you specify, unless you tell it not to create one in the CREATE TABLE syntax. Remember that you can change tables at a later time by using ALTER TABLE or CREATE/DROP INDEX commands within MySQL; this, of course, will change your memory allocation.

However, this is not the complete story if you are trying to work out how much RAM you are going to use; you must take into account the storage space used by primary keys. Each primary key (if you have defined it) or hash index (which NDB automatically creates if you have not defined a primary key) occupies 25 bytes of storage (25 bytes + the size of the key in MySQL 4.1). You add this number to the number you calculated earlier and write this number down as the size of each row.

In addition, for each unique constraint in your schema, other than primary keys, an additional hidden table is created to enforce the constraint. In this hidden table, there are at least two columns. The first columns are the ones that you declared as unique. These columns are treated as the primary key in the hidden table. The other columns in this table make up the primary key from the base table. This hidden table has all the normal space requirements as a regular table (that is, 12 bytes overhead per row). You can see that this causes unique constraints to be quite a bit of extra size overhead. However, this setup does allow for additional speed in accessing constraints, which is discussed further in Chapter 5, “Performance.”

You then have to consider the way that NDB stores information: in pages. All data is stored in pages (a page is a certain part of RAM), and each page holds 32,768 bytes of data. Each page of data can store data from only one table (or hidden table). You therefore have to work out how many rows you get per page, which tells you how many pages you need. You divide 32,768 by the number you produced earlier and round the number down. This tells you how many rows you will get per page of 128 bytes.

You next divide the number of rows by the number of rows per page to get the number of pages you need. This is important because you will need to add a fixed overhead per page.

You can now calculate the total memory requirement, in bytes, for one copy (replica) of your table:

(Size of each row x Number of Rows) + (Number of pages x 128)

You repeat this calculation for all tables in your cluster to get a figure for the total memory requirement for one copy of your tables.

Of course, you also want to know how much RAM you need on each storage node. You use the following calculation for this:

(Total memory requirement for one copy of your tables x Number of replicas³ / Number of storage nodes

You divide this figure by 1,024 to get kilobytes and finally by another 1,024 to get megabytes.

Remember that this calculation gives you an estimate for the RAM required for data storage. More RAM is required for buffers, temporary storage, and so on; exactly how much depends entirely on the environment, so you need to experiment.

A Sample Calculation of the RAM Required for a Table

Because calculating the RAM usage of a table is so complex, this section gives a sample calculation for the following table, which contains both integer and string types, as well as variable- and fixed-width fields:

mysql> desc testtable;

4 rows in set (0.00 sec)

This example assumes that the table uses latin1 (so the size of the character set is 1 byte). It also assumes that this table has 500 rows.

The first step is to calculate the size of each row:

   Each INT field occupies 4 bytes.

   Each VARCHAR(60) field occupies 64 bytes (60 + 2, rounded to next multiple of 4).

   Each VARCHAR(80) field occupies 84 bytes (80 + 2, rounded to next multiple of 4).

   Each small ENUM field is 1 byte, rounded to next multiple of 4.

   There are two indexes, so there is a 20-byte overhead per row.

This makes the total size of each row 176 bytes.

You can now divide 32,768 (the maximum size of a page) by 176 to get 186.2, so you know that each page will hold 186 rows. You can therefore work out that for 500 rows, you will need 3 pages. You can now work out the extra overhead of 128 bytes per page: 384 bytes.

Therefore, the RAM required for data storage will be (176 × 500) [Size of row × Number of rows] + (16 × 500) [Number of rows × Fixed overhead per row of 16 bytes] + 384 [Page overhead]. This comes out to 96,000 bytes, or 94KB.

You now have to work out the RAM requirement for indexes, which is easy: 25 bytes per row, so in this case, 12,500 bytes, or 12.2KB.

This gives you the quantity, in data and index RAM requirement, of this one very small, very simple, table. Of course, you need to take account of your total number of nodes and number of replicas; if you have four nodes and two replicas, you divide these figures in half to work out the storage requirement per node (because each node has half of each table).

Of course, there are significant overheads, and it is always a good idea to allow at least an extra 10% for temporary storage and so on—and for a small table, you should add a lot more.

So far we have concentrated on standard systems. Over the past 18 months, the hype surrounding 64-bit processors has increased massively, and many new servers are now able to run in either 64-bit or 32-bit modes.

From the point of view of MySQL Cluster, 64-bit processors have a massive advantage: They can address more RAM. If you have a 32-bit system, you have two problems: First, there is no possibility of the system addressing (that is, using) more than about 4GB of RAM, and second, the system will generally fail to start if the operating system attempts to allocate more than a few gigabytes of RAM in one chunk (for example, to a storage node), with an error such as this:

The obvious solution to this is to upgrade to 64-bit hardware and a 64-bit operating system. However, if you want to address the full 4GB of RAM and keep your 32-bit hardware, you can simply run multiple NDBD nodes on the same machines, with DataMemory plus IndexMemory for each ndbd process set to the maximum that you can safely address (say, 2GB). If you do this, you must specify which node group each storage node is a members of, as explained earlier in this chapter, in the section “An Example of a config.ini File.” You must ensure that you do not have all the nodes in any node group running on the same physical machine; otherwise, failure of that machine will take the whole cluster down.

You will see in Chapter 2 that there are actually two separately configurable options that control RAM usage: DataMemory and IndexMemory. DataMemory controls everything except the primary key or hash index that every single row has. (If you do not create a primary key, NDB will create a hash index for you and hide it.) So DataMemory includes other regular indexes. IndexMemory can be calculated as 25 bytes per row for the primary key and 25 additional bytes for each unique key (due to the hidden table). Additional hidden tables created by secondary hash indexes use DataMemory, not IndexMemory.

If you discover that your current table structure is too large to fit viably into RAM, you have several options. The first, and often most viable, is to normalize the data to eliminate duplicate data. For example, say you have a table like this:

You might find that you have perhaps five colors, which make up 95% of the colors that people select. If you have, say 1,000,000 rows, you could save yourself a vast amount of RAM by changing the structure to two tables:

You can then make queries with a simple join.

The second commonly used trick is to reduce the size of variable-width fields that are often set too large. For example, tables often have a very large number of VARCHAR(225) fields defined because whoever produced the table wanted to err on the side of caution, and it worked at the time. However, often the largest field is actually much smaller than that—perhaps 50 bytes. In table engines that support variable-width fields, this is not a problem, but as we have seen, NDB not only does not support variable-width fields (so each VARCHAR(225) field is stored as a CHAR(225) with an additional overhead) but it also stores at least two copies of each piece of data.

To illustrate this point, if we have VARCHAR(225) fields that have 100,000 rows with an average field length of 50 bytes, the following gives an idea of the size the field will use:

This represents a 350% increase from MyISAM to NDB before you account for the fact that NDB requires at least two copies of each piece of data. If you started off with a very large MyISAM table with lots of these VARCHAR fields, you could require such a ridiculous amount of RAM that the cluster in MySQL Cluster would cease to be a viable and cost-effective solution.

The best solution to this problem is to go back through the applications that use the database and establish what the actual maximum acceptable size is (if they have any validation before inserts or updates) and either reduce it to something sensible or add validation at a sensible level. You can then change the table structure to this new lower size, which makes an enormous difference.

The easiest way to move your table across is simply to alter an existing table. The syntax for this is very simple, and you can carry out this process from any SQL node. You can use the command SHOW TABLE STATUS to check what engine a table has:

You also get a lot more statistics, but the interesting one is Engine. To change from any other engine to NDBCLUSTER, you issue the following commands:

This does not tell you very much, so it is worth checking that it has worked:

Notice how the engine has changed from MyISAM to ndbcluster. This means that it has worked.

If you want to be 100% sure that this has worked, you should move over to a SQL node on a different physical machine and see if the table has appeared on it. (Remember that you must create the database that the table is in first of all on all SQL nodes.) If it has appeared, you know for certain that your cluster is working and that this table is clustered.

You used the second method, the CREATE TABLE command, when you created the ctest table during the MySQL Cluster installation process. To convert a database to NDBCLUSTER, you get a SQL statement of the table structure, as in this example:

Now, you simply replace the last line with this:

Then you execute the query at a SQL node. You should get an empty table created in the new format. If you are trying to move a table into NDB, we recommend the following course of action:

This copies all your data from the old table to the new table.

This error means that you do not have enough DataMemory or IndexMemory. You will learn how to change these values in Chapter 2, but for now you just need to know how to find out what values you should set them to. In theory, you should either work it out manually or use a script such as ndb_size.pl, as discussed earlier in this chapter. However, if you want to find out your actual usage, you can use the command ALL DUMP 1000. In the management client, you issue ALL DUMP 1000 like this:

You should now see the recorded memory usage of each node in the cluster log on the management daemon. You exit the management client and tail this log file:

Now all you need is some simple math to work out how much DataMemory and IndexMemory are currently being used. To work this out, you choose the highest value for the data usage and index usage if there is a difference between the results for the different storage nodes (which there should not be).

19 32K pages of total 2560 means that the actual usage is 19 × 32KB, which is 2560KB (which is 2.5MB). Similarly, 34 8K pages of total 2336 means 2336KB of space.

You should make sure that your values in config.ini are significantly larger than the minimum values you have just calculated because there will be times when storage nodes will need more space for temporary tables, and you don’t want to run the risk of a table suddenly filling up and hitting its size limit, causing downtime.

This error means “too many indexes.” As you’ll learn in Chapter 2, you need to increase MaxNoOfUniqueIndexes and/or MaxNoOfOrderedIndexes.

This error indicates a problem with too many auto-increment fields. Only one auto-increment column is allowed per table. Having a table without a primary key uses an auto-incremented hidden key. That is, a table without a primary key cannot have an auto-incremented column, and a table with a primary key cannot have two auto-incremented columns.

This error means “increase MaxNoOfConcurrentTransactions.” When the MySQL server needs to start a transaction, it looks for a transaction object in a pool. Each object in this pool is connected to a certain data node. When starting a scan of a table, a transaction object is also used. Thus, in the worst case, a connection could potentially allocate the following at the same time on one data node:

This means if say you use a maximum of 20 table joins, you would get a maximum of 22 transaction objects per connection, so you can work out what value to set MaxNoOfConcurrentTransactions to. Of course, 22 would be a worst-case scenario. Most transactions use 1 or 2 transaction objects, but you should be aware that this is an error that you may encounter.

Chapter 2 provides more information on changing config.ini parameters.

This error may be generated by a query such as this:

It means the cluster is not working. As a preventive measure, if the cluster is not working, you will not be able to create tables simply because the SQL node does not know what tables actually exist in NDB format and so would run the risk of overwriting the tables that are already stored in the cluster.

In this case, you should go back to ndb_mgm and make sure all nodes are connected; if they are not, you should get them to connect. If all the storage nodes are connected, you should make sure that the /etc/my.cnf configuration on each storage node is correct and points to the correct management daemon.

This error can occur during migration of tables, particularly during INSERT INTO...SELECT style queries because each INSERT becomes an operation. The solution is to increase the parameter MaxNoOfConcurrentOperations in config.ini.