What are the limitations of implementing MySQL NDB Cluster?

Question

I want to implement NDB Cluster for MySQL Cluster 6. I want to do it for very huge data structure with minimum 2 million records.

I want to know is if there are any limitations of implementing NDB cluster. For example, RAM size, number of databases, or size of database for NDB cluster.

Answer 1

2 million databases? I asssume you meant "rows".

Anyway, concerning limitations: one of the most important things to keep in mind is that NDB/MySQL Cluster is not a general purpose database. Most notably, join operations, but also subqueries and range opertions (queries like: orders created between now and a week ago), can be considerably slower than what you might expect. This is in part due to the fact that the data is distributed across multiple nodes. Although some improvements have been made, Join performance can still be very disappointing.

On the other hand, if you need to deal with many (preferably small) concurrent transactions (typically single row updates/inserts/delete lookups by primary key) and you mangage to keep all of your data in memory, then it can be a very scalable and performant solution.

You should ask yourself why you want cluster. If you simply want your ordinary database that you have now, except with added 99,999% availability, then you may be disappointed. Certainly MySQL cluster can provide you with great availability and uptime, but the workload of your app may not be very well suited for the thtings cluster is good for. Plus you may be able to use another high availability solution to increase the uptime of your otherwise traditional database.

BTW - here's a list of limitations as per the doc: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations.html

But whatever you do, try out cluster, see if its good for you. MySQL cluster is not "MySQL + 5 nines". You'll find out when you try.

Answer 2

NDB cluster comes with two type of storage options.

1.In Memory Storage. 2.Disk storage.

NDB introduced as in memory data storage and in version 7.4(MYSQL 5.6) onwards started supporting disk storage.

current version 7.5(MySQL 5.7) supports disk storage and in this case there will be no size constraints as data is going to reside in disk and limit depend on disk storage space available with you.
Disk Storage configurations - https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-disk-data-symlinks.html

In Memory storage in NDB cluster is also quite mature and you can define memory usage in management node config.ini file. example - DataMemory=3072M IndexMemory=384M

in an average table(depend on data stored in columns) total db size should be less then 1GB which can easily be configured.

Note - in my own implementation i faced one performance challenge as performance of NDB degrades with increasing number of rows in table. Under high load concurrency read will degrade with number of increasing row. Make sure you don't go for full table scan and provide sufficient where clause predicate. For proper performance define secondary index properly as per your query pattern. Defining secondary index will again increase memory consumption so plan your query pattern and memory resources accordingly.