List:Replication« Previous MessageNext Message »
From:Marcus Bointon Date:September 12 2010 7:21pm
Subject:Re: Unique ID's across multiple databases
View as plain text  
On 12 Sep 2010, at 19:47, Kiss Dániel wrote:

> - 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.

You can maintain your own sequence tables a la postgres if you use transactions to ensure
atomicity, though that doesn't help across databases (I suspect the same is true in
postgres).

FWIW my auto_increment_offset value is usually the same as my server ID.
auto_increment_increment also reduces the number of usable values for integer keys too;
e.g if it's 10, you've reduced your key space by a factor of 10 too, meaning you may need
to use bigints if you potentially have more than 400m records in any given table.

One small observation that I've seen work is to use binary fields for UUIDs and always
wrap access to them in hex/unhex functions. You halve your storage requirements for the
same amount of data that way.

Marcus
Attachment: [application/pkcs7-signature] smime.p7s
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