Mysql – How much data is needed to show MySQL Cluster’s performance scaling vs. InnoDB

innodbMySQLndbclusterscalability

I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told MySQL Cluster scales much better.

How much data does it take to show a performance benefit to MySQL Cluster vs. an InnoDB schema? Or, is there a better way to demonstrate MySQL Cluster's merits?

EDIT

Perhaps an important note: My cluster is currently a heterogeneous cluster with 4 machines. On each machine, I have given an equal amount of Data and Index Memory; 4GB, 2GB, 2GB, and 1GB respectively. The machines are running i7's and are connected over a Gigabit Lan. NumOfReplicas is set to 2.

EDIT

This application is a low-usage analytics database, which has roughly 3 tables >= 200M rows and 5 tables <= 10K rows. When we use it, it takes 15 seconds to run our aggregate functions. My boss asked me to research MySQL Cluster, to see if we could increase performance, since we thought aggregate functions could run pretty well in parallel.

Best Answer

MySQL Cluster stores indexed columns in memory, so the largest practical data size for an in-memory cluster is around 3GB. If you store tables with non-indexed columns on disk, then you can scale the dataset further

As MySQL Cluster automatically shards your tables across multiple nodes, with each node being a master. This gives very high performance for OLTP workloads, especially those that are write-intensive. To get the best levels of performance, you need to introduce load to the cluster, ie configure multiple API nodes, with each node configured with multiple threads to access the Cluster.

The latest 7.2GA release of MySQL Cluster includes new functionality called Adaptive Query Localization, which serves to push JOIN queries down to the data nodes, reducing network hops and therefore latency.

To get the best handle on performance, there is a Performance Guide published as follows (note, registration is required): http://www.mysql.com/why-mysql/white-papers/guide-to-optimizing-performance-of-the-mysql-cluster/

Related Question