MySQL Forums
Forum List  »  Partitioning

Re: any advice on this sharding and replication setup?
Posted by: Phil Hildebrand
Date: July 29, 2008 10:14AM

Did you post to the high availability forum as well (partitioning really just addresses scaling within a single database)?

If you are thinking of using partitioning in addition to sharding (which is not a bad idea for what you are proposing), you should think about how data will be accessed most of the time when setting up your primary keys and partitions. It sounds like it will be by user id, so it would be good to have user id (or a key associated with user id) be an integer value so that no additional transformations need to be done when creating and evaluating your partitions.

Ideally, the user id key would be part of all your tables that are joined, so that any access via id into any table can take advantage of the partitioning (and in future releases, joins may be able to take more advantage of partitions).

Hash and key partitions are nice because they eliminate much of the need for manual decisions on what partitions to throw data into.

I don't know of any specific limit on the #rows per table, but it's read / write performance will be directly related to your memory and disk I/O, so partitioning and sharding will both help in that area, and you should think about your disk subsystem with that in mind.

You'll need 5.1 for partitioning. there are other solutions for your sharding info store (ldap, ...), but if you use MySQL, partitioning would be a really good choice for scaling that database. You could easily use a hash or key partition on your primary lookup table(s) and throw out a hundred partitions to support 100,000,000 total user shard information rows with a max of 1,000,000 rows per partition. primary key lookups into partitions of that size shouldn't be too bad (and given disk space,speed, and memory you could always add additional partitions).

replication should solve your read access to your primary shard info database (just point x web servers/squid servers to replicants of the primary shard info database)

As far as replication and sharding - it seems to me what you are planning is fairly common and sound, but you might want to check the replication and high availability forums.

Options: ReplyQuote

Written By
Re: any advice on this sharding and replication setup?
July 29, 2008 10:14AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.