Separated databases, separeted datas

Question

We have a global project. We will have some servers all continents. We separate the datas because it is not necessary that they be in one place together. BUT! We need unique IDs (MySQL, InnoDB). I have two ideas.

1. We use the auto_increment_increment and auto_increment_offset each server. The problem: we don't know how many servers will be!
2. We create a custom random ID generator with VARCHAR(16-32) primary keys. The problem: How can we guarantee the unique values (maybe unique server prefix?) and it isn't slower?

Have anybody other idea? I made some fast benchmark, and I don't find differents the integer or varchar primary key.

Answer 1

Use the solution that already exists for this problem. Use a UUID. This would require no communication or management of installations overall. Do not use plain random values. The birthday paradox means that the possibility of a collision increases exponentially as the number of records increases linearly. UUIDs are specifically designed to address this issue. I am not sure how good MySQL's UUID generator is.

Another option would be to use a compound primary key with an installation ID or customer ID that is determined by you for each instance of the application, combined with an auto_increment field to make sure each record is unique within each instance.

Answer 2

The UUID_SHORT() miscellaneous function in MySQL should be exactly what you need. It's something I use on a multi-master setup for an application like you described.

It generates BIGINT UNSIGNED values, assembled with this logic:

  (server_id & 255) << 56
+ (server_startup_time_in_seconds << 24)
+ incremented_variable++;

The "incremented_variable" gets incremented every time the function is called so what you essentially have here is 256 unique 56 bit counters that will never collide unless you need more than ~16 million unique values generated per server, per second, every second, between now and the time the collision occurs... which, for my money, will be "never."