In this article we discuss a few of the most common misconceptions with MySQL Cluster and differences compared to Innodb and MyISAM.
1. Deleting / Updating many records fails - out of MaxNoOfLocalOperations or MaxNoOfConcurrentOperations
The configuration parameter MaxNoOfConcurrentOperations sets an upper limit how many operations that can concurrently be carried out on MySQL Cluster.
mysql> delete from t1;
ERROR 1297 (HY000): Got temporary error 1217 'Out of operation records in local data manager (increase MaxNoOfLocalOperations)' from NDBCLUSTER
Typically, this manifests itself when you try to delete/update more than MaxNoOfConcurrentOperations records at the same time in one transaction.
Severalnines Cluster Configurator sets MaxNoOfConcurrentOperations to 100000. If you want to DELETE / UPDATE more records than that, please use a LIMIT to DELETE/UPDATE a subset of the records at a time.. One operation record takes 1KB of RAM. Usually, there is no need to set it higher than what the Configurator sets it too. Actually, also in a Master/slave setup using Innodb/MyISAM you are recommended to keep writes "short" to avoid lag.
2. FOREIGN KEYs are not supported
3. A single request on MySQL Cluster will never be as fast as on Innodb/MyISAM.
MySQL Servers and Data nodes are separated over network. If a single request can be served out of the Innodb bufferpool it will always be faster than on executing it on MySQL Cluster (ndbcluster engine tables). However, MySQL Cluster is a highly parallel database and can scale phenomaly, thus total throughput can reach high values than on innodb.
4. Innodb / MyISAM tables are not clustered in MySQL Cluster.
You have to specify ENGINE=ndbcluster (in the Severalnines Cluster Configurator, the default-storage-engine is set to 'ndbcluster').
MyISAM/Innodb tables will always be stored locally on the MySQL Server they are created on.
5. FULLTEXT search is not supported on ndbcluster tables
Only MyISAM supports FULLTEXT. MYSQL 5.6 will add FULLTEXT search to Innodb.
6. Separating a Cluster of geographical distance
Is not a good idea generally unless you have a very fast backbone (LAN characteristics, same switch). The two phase commit used in MySQL Cluster will make writes very slow. Also, split brain may be problematic to handle. It is better to use Asynchronous Replication over a geographical distance.
Please sign in to leave a comment.