Wednesday, November 11, 2015

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

It is known limitations in current releases of MySQL Cluster as compared with the features available when using the MyISAM and InnoDB storage engines. MySQL will throw errors or failed to convert does DB into supported cluster DB in MySQL (Known as NDB).

Here are the list of basic rules:

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:
  • Avoid having multiple query on same table at the same time.
  • Avoid table scan and query the entire table including the TEXT/BLOB field.
  • Avoid query the entire table using primary or unique key
  • 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


Disk Data object maximums and minimums. Disk data objects are subject to the following maximums and minimums:
  1. Maximum number of tablespaces: 232 (4294967296)
  2. Maximum number of data files per tablespace: 216 (65536)
  3. Maximum data file size: The theoretical limit is 64G; however, the practical upper limit is 32G. This is equivalent to 32768 extents of 1M each.
  4. Since a MySQL Cluster Disk Data table can use at most 1 tablespace, this means that the theoretical upper limit to the amount of data (in bytes) that can be stored on disk by a single NDB table is 32G * 65536 = 2251799813685248, or approximately 2 petabytes.
  5. The theoretical maximum number of extents per tablespace data file is 216 (65536); however, for practical purposes, the recommended maximum number of extents per data file is 215 (32768).
  6. The minimum and maximum possible sizes of extents for tablespace data files are 32K and 2G, respectively. 

Sources:
  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

0 comments :

Share It

Popular Posts