Sunday, October 12, 2014

MySQL Cluster - Migrating a large InnoDB table to NDB Cluster



If this is the first time you try to migrate a large InnoDB table into a NDB cluster, most probably you will run into some issues.
By nature, both storage engines (InnoDB and NDBCluster) have many differences - its highly recommended to read the following:
It really depends on your MySQL cluster architecture, setup and the amount of data you are trying to migrate. But in general, you will probably run into common issues:
1. The table 'table_1' is full
2. Lock wait timeout exceeded; try restarting transaction

These errors can be related to many different configuration issues, but usually you need to consider setting the following important config.ini parameters:
1. DataMemory and IndexMemory - 
The DataMemory value is in bytes and defines the available space to store database records. Note that this will allocate the entire amount specified in your config file, so make sure that you have the required actual space. 
IndexMemory value is in bytes as well and in case you got index, you will need to define this parameter. It controls the amount of storage used to hash the MySQL cluster indexes - depends on your cluster configuration. Lets say you got 4 Nodes (fragments), 2 replicas and there are 1M rows, you can calculate the size using the following formula (see MySQL documentation):

size  = ( (fragments * 32K) + (rows * 18) ) * replicas

2. Lock wait timeout error can be caused by many different configuration issues, but usually its related to the TransactionDeadlockDetectionTimeout parameter. 
Setting the timeout will require you to set the MaxBufferedEpochs parameter as well.
NOTE: while setting your TransactionDeadlockDetectionTimeout, its highly recommended to make sure that your mysqld innodb_lock_wait_timeout set to the correct timeout (http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html).


I suggest to read the following blog post as well: 


Finally, don't forget to delete the ndbd_mgm config bin (cache file located in the config location) each time you make changes to your config.ini.


Good luck
Eran