Category: mysql

Fun with NDBCLUSTER

Author: seven September 19, 2007

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. :)

Author
seven
CEO/CTO at Nivas®
Neven Jacmenović has been passionately involved with computers since late 80s, the age of Atari and Commodore Amiga. As one of internet industry pioneers in Croatia, since 90s, he has been involved in making of many award winning, innovative and successful online project. He is experienced full stack web developer, analyst and system engineer. In his spare time, Neven is transforming retro-futuristic passion into various golang, Adobe Flash and JavaScript/WebGL projects.

    3 thoughts on “Fun with NDBCLUSTER”

  • It will be interesting following your progress, keep posting.

    The MySQL Cluster forums are quite active, so even if you don’t go for enterprise support from MySQL AB you might find answers there.

    The level of interest in MySQL Cluster is increasing so I would be surprised that MySQL 5.1 loses it beta tag sooner rather than later

    Have Fun

    Paul

  • Hi PaulM and thank you for the comment!

    I definitively think that MySQL Cluster is the way to go. Since we are running Ubuntu 7.04 Feisty Fawn, we are expecting that MySQL 5.1 will be made into production in time Ubuntu 8 is released later next year so we can upgrade in one sweep.

    We are also testing DRBD w/ Linux HA. I’ll post some notes as I perform more tests.

  • Matthew Montgomery
    Matthew Montgomery
    March 16, 2009 at 17:33

    Seven,

    Online Add-Node/Node Group is supported with mysql-5.1.30-ndb-6.4.0 onward.

    http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-online-add-node.html

    Enjoy ;-)

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>