1

I have a problem, I have two servers where I have to save my data. For persistent data I run mySql inno DB. I have a lot of entries, so I need fast (CRUD operations).

For indexing I was thinking to use UUID() or UUID_SHORT(). Auto increment is out of the options, because the data will be replicated.

The problems are that, at UUID() the DB will become very slow. At UUID_SHORT() the problem arise that it's not true unique, if I won't often restart mySql server. According to documentation, mySql DOC I can create only 16000000 entries.

What should I use for indexing?

Any help would be appreciate.

2
  • 1
    Why is auto-increment out of the question exactly? Commented Jan 3, 2014 at 15:26
  • As data is replicated, you can configure your servers with different auto_increment_increment and auto_increment_offset so that they don't use the same IDs. As an example, if you have 2 servers, each one with a different auto_increment_offset, the first one will create odd keys, and the second even keys, so no problem. See dev.mysql.com/doc/refman/5.0/en/replication-options-master.html Commented Jan 4, 2014 at 12:09

1 Answer 1

3

The docs on UUID_SHORT() are a little bit short on actual detail, and I suspect you are not seeing the complete story on how this function works.

As long as you do not have more than one server in the system with the same bits in the low-order byte of the @@server_id, and you don't set the system clock back in time and then restart MySQL server, and don't generate more than 16.7 million calls to UUID_SHORT() per second for every second of every minute that the server is running, then the values will be unique.

--- 64 bits of bigint unsigned comprising the initial value ----
6666555555555544444444443333333333222222222211111111110000000000
3210987654321098765432109876543210987654321098765432109876543210
|------||------------------------------||----------------------|
@@server_id     unix_timestamp of          monotonic counter
low-order      server startup time         starts at 0, increments
byte                                       up +1 with each call,
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< overflows this way 
                                           when it rolls over
                                           past 24 bits

The counter is initialized as shown above with each server start, and generates a value incremented by 1 each time UUID_SHORT() is called. It does not increment the unix_timestamp portion of the value for each tick of the wall clock -- it overflows the counter up into that portion of the value, effectively conserving this resource (of usable values) and avoiding collisions caused by a normal server restart.

Thus, if you don't generate more than 2^24 (16,777,216) calls per second every second, then the next time the server starts, it will initialize at a much higher value (relatively speaking) than where it left off. Setting the system clock back in time would also be unlikely to be problematic unless you set it back close enough to the time of the prior server restart to cause overlapping values.

1
  • great explanation, thx
    – 5er
    Commented Jan 6, 2014 at 9:13

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.