16

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).

Improve this question

1 Answer 1

Reset to default
24

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 latency 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 volume 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

Improve this answer
4
  • 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
    Commented Dec 8, 2011 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
    Commented Dec 8, 2011 at 16:57
  • In lieu of my ranting and raving, +1 for your answer !!!
    – RolandoMySQLDBA
    Commented Dec 8, 2011 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
    Commented Dec 19, 2011 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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