Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
This sounds to me like an apples-to-oranges comparison. InnoDB and Clustering are two totally separate technologies, meant to solve two separate problem spaces. Clustering will almost certainly be slower than a simple InnoDB setup since clustering has the overhead of dealing with the HA/DR code, whereas InnoDB does not. Having said that you would likely find the difference in speed to be so small and the HA/DR advantages of clustering to be so large that you would go with the clustering anyway. –  Max Vernon Mar 1 '13 at 18:38
    
Asking "how much data" is a very broad question that is not really answerable, since it depends on what type of data you are expecting, what kinds of client access you are using, etc, etc. –  Max Vernon Mar 1 '13 at 18:40
    
I thought that Cluster would be more performant for a very large data set, which was probably influenced by its advertisements. What I hear from you is that Cluster is more about High Availability than Large Datasets (though High Availability certainly implies some size of dataset) –  Ryan Mar 1 '13 at 19:45
    
If my goal is to partition data between machines in order to increase the performance of an aggregate operation, should I be researching a different technology? –  Ryan Mar 1 '13 at 19:46
    
You can use clustering for horizontal scalability, however it will never be as fast as using a single machine for a single client. If you want to increase the performance of a smallish system like yours you should look at single-machine performance improvements like multi-core hardware, etc. –  Max Vernon Mar 2 '13 at 0:38

2 Answers 2

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/

share|improve this answer

If it's analytics you're after, you might want to look at InfiniDB's offerings. They're Open Source and are based on a MySQL columnar storage engine (reviewed here). It's v. interesting and they've implemented windowing functions which might suit your use case. Infobright have done something similar, but don't appear to be as far along in the space. There is a comparison here, but it's over 4 years old.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.