Known Limitation of MySQL Cluster NDB 7.3 and MySQL Cluster NDB 7.4

Introduction

This tip is about MySQL Cluster NDB version 7.3 and 7.4 issues when converting from MyISAM or InnoDB to NDB for clustering implementation. This tip lists the basic rules that the developers and database administrators shall use when creating MyISAM or InnoDB database, table, SQL statement, etc.

The Basic Rules

The rules shall be used as guidance in software development to avoid issue when exporting MyISAM and InnoDB into NDB format for clustering purposes. Developer/DBA shall apply as close as possible all the rules. If any of the rules are avoided, MySQL will throw errors or failed to convert DB into supported cluster DB in MySQL.

The basic rules are:

1. Do not use temporary table.
2. Do not used key (primary or foreign) that exceed 3072 bytes width (length); equivalent to 3072 characters or 3KB data
3. Do not use TEXT or BLOB as key or unique identifier or index
4. Avoid indexing FULLTEXT (such as name, address, etc.)
5. Avoid using HASH column/data as key or index
6. Ensure the Auto_Increment column is also a key.
7. Ensure the foreign key is referred to a table primary or unique key.
8. Do not implement spatial indexes.
9. Used only Latin-1 character for logging
10. Avoid using READ UNCOMMITTED, REPEATABLE READ, and SERIALIZABLE. NDB only support READ COMMITTED.
11. Avoid having BLOB and TEXT as part of table column. If need to have either those two types:
    1. Avoid having multiple query on same table at the same time.
    2. Avoid table scan and query the entire table including the TEXT/BLOB field.
    3. Avoid query the entire table using primary or unique key
    4. Avoid query and extracting the TEXT/BLOB field
12. Database and table name shall not more than 63 characters.
13. Number of database, table and index in a single cluster shall not more than 20320.
14. Number of column (attributes), key and index in a single table shall not be more than 512.
15. The maximum number of row size shall not exceed 14000 bytes (14Kb).
16. Avoid using PREFIXs

There is also space limitation. Please read http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster.html for a complete list of space of limitation.

References

1. http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster.html
2. http://dev.mysql.com/doc/refman/5.6/en/ha-overview.html
3. https://blogs.oracle.com/jsmyth/entry/connection_failures_between_nodes_in
4. https://www.mysql.com/why-mysql/presentations/mysql-cluster-deployment-best-practices/
5. http://cdn.oreillystatic.com/en/assets/1/event/36/MySQL%20Cluster%20-%20Deployment%20Best%20Practices%20Presentation.pdf
6. http://www.clusterdb.com/tag/performance
7. http://dba.stackexchange.com/questions/72585/problem-with-mysql-cluster-in-practice

History

• November 12^th, 2015 - First version of the tip/trick