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.

Considering performance only, can a MySQL Cluster beat a custom data sharding MySQL solution? sharding = horizontal partitioning

When I refer to sharding, I'm considering sharding made in the application layer, for instance, distributing records evenly across independent MySQL instances. For two servers, it could be (key mod 2).

share|improve this question

2 Answers 2

up vote 13 down vote accepted

Disclosure : I am a MySQL employee, working on MySQL Cluster.

I would say that MySQL Cluster could achieve higher throughput / host than sharded MySQL+InnoDB provided that :

  • Queries are simple

  • All data fits in-memory

In terms of latency, MySQL Cluster should have more stable latency than sharded MySQL. Actual latencies for purely in-memory data could be similar.

As queries become more complex, and data is stored on disk, the performance comparison becomes more confusing. To get a more specific answer, you need to describe more about your application and the queries you perform, as well as the number of hosts and volumne of data. MySQL Cluster has recently gained parallel localised query execution (AQL) which means it can be competitive with standalone MySQLD despite having data distributed across multiple hosts.

MySQL Cluster is currently limited to 'sharding' over 48 hosts. Sharded MySQL in theory has no limit. However, for a given target throughput, fewer MySQL Cluster hosts may be needed than sharded MySQL hosts.

More interesting differences are when you look at areas other than performance : - MySQL Cluster supports arbitrary queries across all shards - MySQL Cluster supports arbitrary transactions across all shards - MySQL Cluster supports synchronous replication of shards with automatic failover and recovery - MySQL Cluster supports online add node (cluster expansion) - Sharded MySQL is more 'roll your own'

Having sharding built into your application gives you maximum scaling potential, but adds complexity and limits your flexibility in terms of cross-shard queries and operations. If your sharding is premature then it may be the root of some problems for you. MySQL Cluster lets you get some of the benefits of sharding without having to constrain your application to be single-shard only.

Regarding the previous answer, a few clarifications :

"Although MySQL Cluster is ACID-complaint, it does not provide a suitable storage engine for data with compound keys."

MySQL Cluster supports compound primary and secondary keys. Not sure what's not 'suitable' about it. Perhaps the previous poster can explain?

"In order to have data with the same key characteristics stored in a particular set of data nodes, you could doing the following:

1 Take all data nodes offline, leaving only those data nodes you want to house data with the same key characteristics.

2 Load your data into the MySQL Cluster, which populates only your select data nodes

3 Bring all data nodes back online"

This is incorrect. Data distribution is independent of which nodes happen to be online at any time. MySQL Cluster supports various data distribution schemes to support the optimisations you describe. I describe data distribution in MySQL Cluster in a blog post here : Data distribution in MySQL Cluster

share|improve this answer
    
Hey, Frazier. I read the link you provided. Just for claraification, my 'compound key' comment was based on non-unique indexes. My employer's company tried out MySQL Cluster around 2007 Q1 and did not like it because of poor performance. IMHO it was the client's poor choices for keys (small cardinalities) and his queries. MySQL Cluster must have matured more since then based on your link. As to my second statement, this is how many MongoDB users populate specific shards. Some of my employer's clients have done this with their custom MySQL setups. –  RolandoMySQLDBA Dec 8 '11 at 16:50
    
In your link, it mentioned 'an ordered index scan' that could not be pruned, as matching rows are not guaranteed to be stored in one table fragment. This is why I was suggesting isolating data to specfic shards (data nodes) to minimize the places data would spread. Since your answer brings out the positive side of MySQL Cluster, it better fits the original posted question. My answer errs in favor of caution, pessimism, and being somewhat naive of MySQL Cluster power today. –  RolandoMySQLDBA Dec 8 '11 at 16:57
    
In lieu of my ranting and raving, +1 for your answer !!! –  RolandoMySQLDBA Dec 8 '11 at 16:59
    
Hi Rolando, Thanks for clarifying your statements. It's true that non-pruned ordered index scans are 'expensive' in Cluster, as all data nodes are involved. It sounds like these scans on low cardinality indices would be expensive on any system, but on Cluster they became visibly expensive. Your caution and pessimism have no doubt saved you more than once :) Thanks for the +1 –  Frazer Clement Dec 19 '11 at 16:59

It depends on the situation, but typically sharding is the more optimal approach in order to continue to be able to scale, however, writing sharding code is difficult. This article: http://www.scalebase.com/database-sharding/, looks at the components of the process and the ability to use applications like Scalebase that can transparently shard data automatically. (Disclosure: I do work for ScaleBase).

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.