Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Say I have a MySQL 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?

share|improve this question
add comment

1 Answer

i cannot really comment on "PARTITION BY HASH(tweet_id) PARTITIONS 12;" part... but usual trick with autoincrements when you're partitioning is done on the application level is to use

auto_increment_increment={numberOfShars}
auto_increment_offset={numberOfShars+serverNumber}

in my cnf. so if you have 4 machines and assign them numbers from 0 to 3 your #3 machine would have:

auto_increment_increment=4
auto_increment_offset=7

this guarantees that your ids will be unique across all shards

share|improve this answer
add 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.