July 14th, 2008

amazon

MySQL UUID() function, so fast it makes me nervous

So I was hacking around in the MySQL server code this weekend, and took at look at the implementation of the UUID() function.

Instead of using Theodore Tso uuid library, which now ships by default on all Linux and MacOS machines (I dont know about BSD and Solaris), we implemented our own from scratch. With a global mutex lock on generation, which will utterly blow on a multicore machine.

So I pulled down a branch of the server source code from Launchpad, and then branched it again twice locally. In those two branches, I replaced the implementation with a call to libuuid.so uuid_generate_random() and with a call to libuuid.so uuid_generate_time(). Then I built all three trees, and benchmarked 10 million calls to UUID (SELECT BENCHMARK(10000000,UUID());).

I was surprised at the results.

implementationtime in seconds
built-in1.95
uuid_generate_random()96.76
uuid_generate_time()70.62


How is the builtin one so fast?!

Something is either very wrong with the Tso implementation, that nobody has cared about because they aren't using it for thousands of calls per second, or else something is very wrong with the MySQL internal implementation, and we're not safely generating properly unique UUIDs.

My money is on the second guess...