List:Replication« Previous MessageNext Message »
From:Kiss Dániel Date:September 12 2010 5:47pm
Subject:Unique ID's across multiple databases
View as plain text  
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.

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