2

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?

Continuation
  • 3,050
  • 5
  • 29
  • 38

1 Answers1

2

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

pQd
  • 29,561
  • 5
  • 64
  • 106