Using MySQL Cluster

Recently in one project I migrated a database from MySQL using InnoDB to MySQL Cluster with 4 nodes. In theory the process should be simple, but actually there are many problems:

(1), Lack of documentation. The online community of MySQL Cluster is not as strong as the ordinary MySQL. Some blog posts are old, and some of the documents are so ambiguous, it is like, if you know how to do, then you can read it. The concepts of "date node" and "sql node" are very confusing.


(2), The default configuration is not usable. My test database is in normal size, that can fit into a server of 1G memory, using MySQL of InnoDB, with all default configuration. Changing the table creating command from "ENGINE=INNODB" to "ENGINE=NDB", I tried to create the same table structure in MySQL Cluster but failed. I have add these settings to make it work:

MaxNoOfConcurrentOperations = 262144
MaxNoOfLocalOperations = 288360
MaxNoOfOrderedIndexes  = 1024
MaxNoOfAttributes = 4000

(3), It requires more memory during initial setup phase.  As in (2), I am only trying to create the table structure, not loading any data yet, it still complain not enough of memory, so finally I had to increase the memory of each node to 4G from the original 1G, then allocate 2G for data and 200M for index:

DataMemory   = 2000M
IndexMemory  = 200M

(4), The same "create table" command works well in InnoDB, but fails in Cluster. For example, if you have one column that is auto-increment, but this column is not the Primary Key of this table, the InnoDB engine will accept that, and created a hidden auto-increment ID for this table. This table will fail in NDB engine, claiming "you can't have two auto-increment column". Another example, in InnoDB, a varchar column can have the size of 8000. But in NDB, each table, all columns combine together must be less than 2400 characters (including overhead). If you defined a varchar column with size of 3000, you must either make the size smaller, or change it to TEXT or BLOB column when migrating into NDB engine.

(5), When a node is registered in the management configuration file, then the node must be active, for the whole system to run. It doesn't support "hot-plug". If one node is shutdown, the whole system is down, and you need to restart each node to get the whole system running again.


(6), Any view, stored routine or trigger can't be propagate through the cluster automatically. They must be created/modified on each sql node where you will connect to. They are not attached to the ndb engine, so they are not part of the cluster system.

(7), When there are several threads accessing database concurrently, as my program is doing, I see a lot of "Waiting for ndbcluster global schema lock", "System lock", "checking permissions" in the process status. I actually have to increase the TransactionDeadLockDetectionTimeout from default 1.2 second to 1 minute to make it work. It takes longer time to complete the same process than using traditional InnoDB engine.