UUIDs are popular, but bad for performance (2019)

Post by rossmohax » Sat, Jan 08, 2022, 3:14 PM UTC

kyrra wrote: post #108

yeldarb wrote: post #10

Interestingly, for other systems you sometimes want the exact opposite: for your key space to be distributed across indexes to balance the load (vs wanting them all to hit the same “hot” index for MySQL). For example, Google’s Cloud Firestore is bottlenecked to 500 writes/second if your key is monotonically increasing (like a timestamp or these timestamp-based UUIDs) causing you to “hotspot” the index: https://cloud.…

Your other option is to hash your monotonically increasing numbers. You don't want to use something like SHA, as it does not give good distribution. You use something like murmur hash ( https://en.m.wikipedia.org/wiki/MurmurHash ). I've used it before for indexes at Google for values that may hotspot. See Google's impl here: https://github.com/google/zetasketch/blob/master/java/com/go...

Post by lazide » Sat, Jan 08, 2022, 3:17 PM UTC

JimDabell wrote: post #95

globular-toast wrote: post #40

Earlier quoted context omitted.

Yeah. The main problem is people using them as primary keys in naïve systems like relational databases. You can't just expect a relational database to magically become a distributed system just by using UUIDs. There is a bit more work to do than that.

> You can't just expect a relational database to magically become a distributed system just by using UUIDs. Nobody thinks this, do they?

Post by piaste » Sat, Jan 08, 2022, 3:25 PM UTC

roenxi wrote: post #70

piaste wrote: post #65

Earlier quoted context omitted.

Because you don't want your record IDs to be perfectly sequential data. You only care about them being sequential enough that your database engine will write them down quickly and efficiently (for engines like InnoDB or MSSQL which have such behaviour). But as a developer you typically want them to be random so that you can generate them in a decentralised manner, prevent guessing or accidental joins, safely merge re…

But now the ID has a random concatenation of data in it, like the MAC address of the node that inserted it into the database and a timestamp. It'd be much more orderly for each node to have a sequential ID, the ID of the node that created it, a timestamp when it was created and a real UUID v4 for if someone wants to prevent collisions. Same data, but the primary key on an individual node is a lot smaller (half the si…

Post by da_chicken » Sat, Jan 08, 2022, 3:30 PM UTC

yellowbeard wrote: post #94

qalmakka wrote: post #48

Isn't this easily solved by supporting 128 bit keys and using UUIDs as intended, i.e. as integers and not in their string serialization? This is as nonsensical as storing IPv4 as strings instead of 32 bit integers.

That helps with storage, but still is larger than a bigint, and doesn't help with the random distribution of data. I believe newer versions of MySQL have a data type for this.

Post by da_chicken » Sat, Jan 08, 2022, 3:33 PM UTC

jrochkind1 wrote: post #85

qalmakka wrote: post #48

Isn't this easily solved by supporting 128 bit keys and using UUIDs as intended, i.e. as integers and not in their string serialization? This is as nonsensical as storing IPv4 as strings instead of 32 bit integers.

I'm kind of shocked MySQL doesn't do this?