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 need to do database sharding(horizontal) based on MySQL. My database schema is as following:

  1. I have tables A,B,C
  2. A,B is Global(which need not to be sharding)
  3. C have huge data so it need to be sharding to achieve write scalability.
  4. I will have several sharding,each contains one copy of A,B, and a subset of C.

My questions are:

  1. How could i keep A,B on all sharding consistent,i.e, replicate A,B on all sharding so that they should have exactly same data. Or just store the Global Tables on single DB and cache via memcached?
  2. How to do high availability for each sharding,i.e, if one sharding crashed due to unknown reasons how could i do fail over.Through MySQL backup or master-master AP mode replica?
  3. How to do load balance for Global tables A,B on the multiple shardings?

Note that i will not use MySQL read replicas in this case.

share|improve this question

migrated from stackoverflow.com Oct 8 '12 at 11:30

This question came from our site for professional and enthusiast programmers.

add comment

4 Answers 4

It is possible for you to do this yourself rather than using commercial products like dbShards or ScaleBase but you will end up building the same thing and it isn't trivial.

To answer your question though, it makes sense to replicate A and B across all shards so you either need your application to duplicate the writes across all shards or you will need some replication technology to do this. I don't think you can easily configure MySQL replication to do this.

To load balance you will need your client to randomly choose a shard when running queries against these tables, just like you'll need your client to determine which shard to use when accessing table C.

For HA you will want to have master/slave setup for each shard so that you can failover in the case of losing a master shard.

Regards,

Andy Grove Chief Architect http://www.dbshards.com

share|improve this answer
    
@ Andy, thanks. Your proposal is hitting my problems. For now i prefer use a global DB for A and B, rather than replicate to all shards. I will setup master-master replica via mysql, and keep global data in memcached. –  Simon Wang Oct 15 '12 at 2:02
add comment
  1. Keep Global Tables A,B on Global DB,saying that DB1. Which all data need to be cached via memcached.
  2. HA via mysql master-master replica. Only write to the Active server.
  3. Since Global data on DB1, so no necessary to do load balance. Just getGlobalDBConnection() is enough.
share|improve this answer
add comment

You have asked multiple questions in your single question - Sharding, High Availability and Load Balancing. These are vast topics in themselves.
There are lot of resources available on Internet on these technologies. I suggest you should first read about them and you can always come back on StackOverflow if you are unclear on some concept or you get stuck somewhere in implementing these. Thanks.
EDIT:
I would like to share a link which I just found:
How to implement MySQL Sharding

Hope it helps.

share|improve this answer
add comment

I work for ScaleBase, which is a maker of a complete scale-out solution an "automatic sharding machine" if you like, analyzes the data and SQL stream, splits the data across DB nodes, load-balances reads, and aggregates results in runtime – so you won’t have to!

ScaleBase supports and maintains global tables transparently and safely, also enable high availability over replicated MySQLs for each "shard", read-repl and failover are configurable and automatic.

True, it's a commercial product, but developing it all in-house is such a pain... And even after all that pain it'll never be a complete as ScaleBase with the support for the application and the administration around it.

I invite you to see a demo of everything you wrote in your 1,2,3 in 5 mins.

Also, take a look on my post in http://database-scalability.blogspot.com/ about Pinterest, then and now...

I hoped I helped!

share|improve this answer
1  
I've noticed that it feel's like you're plugging a product and inviting him to a demo. ScaleBase may solve the problem, but this answer is not addressing why ScaleBase solves the problem posted in the question. Can you elaborate on that any further? Also, I encourage you to read faq –  jcolebrand Oct 9 '12 at 19:58
1  
Just to add my 2c - self or product promotion is generally acceptable here providing there is a very clear effort to answer the question, but is less likely to be flagged as spam if it is not your only contribution to the site. Judging by your profile, you could be a great help with one or two of the Oracle questions we get ;-) –  Jack Douglas Oct 9 '12 at 20:32
    
@ Doron Levari, thanks for your links. I already know ScaleBase before, yes,it is a good product. But currently we will focus on the open source community. Thanks anyway:) –  Simon Wang Oct 10 '12 at 1:10
    
@ Jack, thanks for your comment~ –  Simon Wang Oct 10 '12 at 1:12
    
@Grace just spotted your comment—I only see it automatically if there is no space after the '@', thought you'd like to know! Doron would see his regardless as this is his answer so he gets notification of all comments (and sorry Doron for spamming you on this thread) –  Jack Douglas Oct 15 '12 at 12:34
show 1 more comment

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.