Main differences compared to Innodb and MyISAM
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.
The optimal value depends on how many operations should be possible to be executed concurrently and also depends on the number of data nodes, the number of mysql servers, the number of concurrent connections to the database, how many records those can update etc etc. There is a cost in RAM for each operation record.
However, it is not feasible to do large/huge transactions on NDB (this is also for all distributed databases in general).
All in all, when you have huge update/delete/multi-insert transactions you should apply them in batches. In the Delete case you would apply a "LIMIT", e.g:
delete from radacct where acctinputoctets=0 and acctoutputoctets=0 LIMIT 10000;
and then delete until done.
The upper limit for the LIMIT is basically MaxNoOfConcurrentOperations, but deleting/updating huge chunks of data will cause a lot of pressure on different subsystems inside NDB (such as redo log, and then keep a number undo records until the trans is committed or rolled back.Comment actions
I have above parameters in config.ini, when i try to delete rows in a table i get below error
mysql> delete from radacct where acctinputoctets=0 and acctoutputoctets=0;
ERROR 1297 (HY000): Got temporary error 1217 'Out of operation records in local data manager (increase MaxNoOfLocalOperations)' from NDBCLUSTER.
How to determine the optimum value for maxnooflocaloperations ?
Please sign in to leave a comment.