>-----Original Message----- >From: Kiss Dániel [mailto:n...@dinagon.com] >Sent: Sunday, September 12, 2010 1:47 PM >To: mysql@lists.mysql.com; replicat...@lists.mysql.com >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: je...@gii.co.jp 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, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
