MySQL Forums
Forum List  »  Partitioning

Evolve from Partition to Sharding?
Posted by: John Smith
Date: August 21, 2010 04:56AM

Hi,

Say I have a table:

CREATE TABLE tweets (
tweet_id INT NOT NULL AUTO_INCREMENT,
author_id INT NOT NULL,
text CHAR(140) NOT NULL,
PRIMARY KEY (tweet_id)
)
PARTITION BY HASH(tweet_id)
PARTITIONS 12;

All is good. The table lives on a single server - Server1. But eventually I may want to scale out. So I'd want to shard the table and move 6 of the 12 partitions onto a new server - Server2.

1) Is there any quick & easy way to move those partitions from Server1 to Server2?

2) Now that I have 2 servers, how do I make sure the auto-increment tweet_id's generated by the 2 servers don't have the same value? I'd also need to make sure the tweet_id on each partition stays consistent, i.e. on Partition k every tweet_id's modulo 12 equals to k.

3) Ideally I'd like to continue this scale out process. So later on I'd want to add a 3rd server - Server3. I'd want to re-balance the partitions so that there're 4 partitions on each server. Again how do I make sure the auto-increment tweet_id's generated by the 3 servers are distinct and that the hash of tweet_id's stay consistent within each partition?

Thanks



Edited 2 time(s). Last edit at 08/22/2010 02:24AM by John Smith.

Options: ReplyQuote


Subject
Views
Written By
Posted
Evolve from Partition to Sharding?
4147
August 21, 2010 04:56AM
1881
August 22, 2010 09:02AM
2487
August 23, 2010 12:03AM
1673
August 23, 2010 12:42AM
2518
August 23, 2010 12:48AM
1659
August 23, 2010 09:45PM
1833
August 23, 2010 10:18PM
1878
August 27, 2010 07:41AM


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.