1

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.

2 Answers 2

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.

2
  • UUID is a good idea. I don't know if I use only char PK-s than performance remain or lost? I don't find datas. Insert is slower, but the selects? Joins? Do you know anything?
    – fchris82
    Mar 3, 2013 at 2:13
  • I made some test. I created 3 tables: int auto_increment, char(36) and binary(16) ; with ~360 000 rows . The select count(*) from table query run: ~0.12, ~0.20 and ~0.12 s . The table sizes: 9.5, 20.6 and 13.5 MB . It looks like it is good compromise to use UUID as binary ( UNHEX ( REPLACE ( UUID (), '-','')) ) Have you other information maybe?
    – fchris82
    Mar 3, 2013 at 4:28
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.