MySQL clustering(NDB) vs MySQL replication (InnoDB)

MySQL InnoDB

InnoDB often comes with master/slave configuration. The drawback of this method is writes are only on master and the slaves provide only reads. Therefore concurrent writes are not supported unless you use a mechanism called shading. 

What happens when a database grows so that it cannot handle in one database and a server. Then comes the shading option. But it has to be done very carefully in order to not to lose  performance. If you want to scale out the system for write operations, using this storage mechanism, then you need to use the shading option.

shading for reads

1. when the data set do not fit into the memory and consists of may read hits from the disk rather than serving from memory.

shading for writes

1. when there are too many writes that the replication lags considerably.
2. the frequency of writes is overloading this servers disks permanently

How ever if you are going for the option of sharding, it is always good to have application level sharding. The reason is, when the application knows where the data resides, the performance becomes better.

Sharding types

1. application level sharding - put the most busy tables into separate servers and access them.
2. sharding by hash key
3. sharding using a lookup service

Why sharding is one of the last options?

1. developer has to write code to handle the shading logic
2. backup, indexing, changing schema makes it more difficult to maitain

MySQL clustering

MySQL clustering how ever supports concurrent writes. Data is partitioned among the data nodes and a copy of a node or a backup is in another node. Therefore availability is assured. 

How ever the problem is even though it provides foreign key join supports the process is slow since the data is partitioned in several nodes. If the join operation results in large volume of data this could be slow. Therefore tasks such as generating reports that takes usually several minutes are not good to be implemented using this method. Also another thing to note in this method is, it supports concurrent writes.

Setting up MySQL clustering can be more tedious than setting up InnoDB. But still it prevents the developer from using shading, since the partitioning happens among the nodes.

Below is a link which provides some hints on how to increase the performance in a mysql cluster

https://blogs.oracle.com/MySQL/entry/mysql_cluster_performance_best_practices