List:Replication« Previous MessageNext Message »
From:Jerry Schwartz Date:September 13 2010 2:01pm
Subject:RE: Unique ID's across multiple databases
View as plain text  
>-----Original Message-----
>Sent: Sunday, September 12, 2010 1:47 PM
>To: mysql@stripped; replication@stripped
>Subject: Unique ID's across multiple databases
>
>Hi,
>
>I'm designing a master-to-master replication architecture.
>I wonder what the best way is to make sure both databases generate unique
>row ID's, so there won't be ID conflicts when replicating both directions.
>
>I read on forums about pro's and con's using UUID's, also about setting the
>*auto-increment-increment *and *auto-increment-offset* system variables.
>I'm not a fan of UUID's for this purpose. They are two big, slow to
>generate, and they don't 100% guarantee global uniqueness anyway.
>On the other hand, the *auto-increment-offset *trick limits the number of
>possible databases in the system in advance, which is not so nice.
>
[JS] UUIDs are likely enough to be unique that you don't have to worry about 
it.

I can't speak to the speed.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@stripped
Web site: www.the-infoshop.com




>So, I'm thinking about a composite unique ID system, where the value of the
>*server-id *system variable is always a part of the ID. The server-id has to
>be set uniquely anyway for any replication architecture in MySQL.
>
>A sample would look like this:
>
>CREATE TABLE SampleParents (
>ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
>SID SMALLINT UNSIGNED NOT NULL,
>
>SampleData VARCHAR(50) NOT NULL,
>
>PRIMARY KEY (ID, SID)) ENGINE=InnoDB;
>
>CREATE TABLE SampleChildren (
>ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
>SID SMALLINT UNSIGNED NOT NULL,
>
>SampleParentID INT UNSIGNED NOT NULL,
>SampleParentSID SMALLINT UNSIGNED NOT NULL,
>
>SampleData VARCHAR(50) NOT NULL,
>
>PRIMARY KEY (ID, SID),
>KEY (SampleParentID, SampleParentSID),
>
>CONSTRAINT FOREIGN KEY (SampleParentID, SampleParentSID) REFERENCES
>SampleParents (ID, SID)) ENGINE=InnoDB;
>
>Where SID is always filled with the value of the @@server_id global variable
>according to the server where the record is created.
>
>How I see it is that it has the following advantages.
>
>   - The ID+SID pair guarantees pripary key uniqueness within the
>   replication array.
>   - Auto_increment is a magnitude faster than generating UUID's.
>   - SID adds only 2 bytes in this case to the size of the primary key item.
>   It can be even 1 byte if I'm sure I'll never exceed maximum 255 servers. 
> But
>   anyhow, it is still way smaller than the 16 byte of a UUID field, even if
>   using BIGINT's.
>   - Keeps the efficiency of the primary key indexing, because the key is
>   still very close to a strictly incremental value.
>
>Well, you can see what the obvious disadvantage is:
>
>   - Primary and foreign keys are always double fields. This is not so
>   convinient when you are joining tables and add the WHERE clauses to your
>   queries. It might even negatively affect the evaluation speed of join
>   conditions, although I have no idea yet, how much. (My gut feeling is that
>   it's not a big issue, due to the good query optimizer of MySQL.)
>
>My question is. Does anyone have any better idea, how to approach this
>problem?
>
>Thanks,



Thread
Unique ID's across multiple databasesKiss D├íniel12 Sep
  • Re: Unique ID's across multiple databasesMarcus Bointon12 Sep
    • Re: Unique ID's across multiple databasesMax Schubert12 Sep
  • RE: Unique ID's across multiple databasesJerry Schwartz13 Sep