Fun with NDBCLUSTER

MySQL 5.0 introduced NDB Cluster, a storage engine which enables running several MySQL servers in a cluster. It uses high-availability, high-redundancy version of MySQL adapted for the distributed computing environment. This all sounds really nice, and in theory, depending on your application and scalability planning, it could spare you MANY hours of developing (squeezing) database logic inside your application logic which then gets highly dependable on master/slave (active-pasive) configuration. And of course best reason of them all – instant failover. So I decided to give it a try. I used MySQL 5.0.45-0, because 5.1 sounded too much bleeding edge.

MySQL Cluster is currently not supported on Microsoft Windows, you’ll have to install it somewhere else. Once you have it running, it’s time to import some data. Database schema of a my test dump mostly used MyIsam storage engine and few InnoDB tables. Before the import, change Engine=NDB in the dump and be aware, import takes ages even on a really fast servers (200 kb dump = cca 1 min ??).

My best guess is, you will see a lot of HY00 errors when you start your import. Be patient, check one by one. Most of them are caused by non supported features of NDB but can be avoided.

Here is a brief coverage of reasons for the problems which I encountered:

• NDB does not support foreign keys or FULLTEXT indexes or indexes on text columns (indexes on char/varchar are ok).
• Attribute names are automatically truncated to 31 characters. Database names and table names can total a maximum of 122 characters – the maximum length for an NDB table name is 122 characters, less the number of characters in the name of the database of which that table is a part.
• The maximum number columns and indexes per table is limited to 128.
• Temporary tables are not supported.
• Every table using the NDBCluster storage engine requires a primary key; if no primary key is defined by the user, then a “hidden” primary key will be created by NDB. This hidden primary key consumes 31-35 bytes per table record.
• The maximum permitted size of any one row is 8KB. Note that each BLOB or TEXT column adds 256 + 8 = 264 bytes towards this total.

The last error is really nasty one and will cause you to rethink your database schema and make a lot of changes to your application):

ERROR 1118 (42000) at line 794: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8052. You have to change some columns to TEXT or BLOBs


Some help:

• The lack of foreign keys, can be resolved by using triggers (Enforcing Foreign Keys Programmatically in MySQL).
• You need a lot of RAM in your severs because all data is in the RAM, unless you are using the ‘DATA ON DISK’ feature of MySQL 5.1.x. In that case you can have the non-indexed data on disk.
• When calculating Cluster memory requirements, very useful is ndb_size.pl utility. This Perl script connects to a current MySQL (non-Cluster) database and creates a report on how much space that database would require if it used the NDBCluster storage engine. When you run the script, compare the suggested parameter values reported for your database to the default values for each shown here. If the default is higher than the recommended value from ndb_size.pl do not adjust the value. If the recommended vales are lower than the defaults use slightly larger numbers.

NDBCluster is something I’ll definitively have my four eyes on and watch it grow. Bugtracker on mysql is crawling with bugs, but this is something to be expected. I’ve tested 5.0, and there is a lot of stuff allready fixed in 5.1. My biggest disappointment was the fact that new nodes can’t be added to the cluster without shutting it down first. My conclusion is, that It is not yet ready for prime time. When it will be? when 5.1 is released into production I guess. Until then… We are on our own. :)