Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
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 '13 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 '13 at 4:28
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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