Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

UUIDs are popular, but bad for performance (2019)

percona.com

Post reply on HN
Print view View original

141–150 of 246 posts

Re: UUIDs are popular, but bad for performance (2019)

#141

Post by kyrra »

rossmohax wrote: post #111
kyrra wrote: post #108

Earlier quoted context omitted.

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

> You don't want to use something like SHA, as it does not give good distribution How come? Even distribution is one of requirements for crypto hash functions.

You know, likely am wrong here. I think murmur is good because it had a good distribution and is much faster than SHA? I did find someone that compared murmur to other non crypt hashes here: https://softwareengineering.stackexchange.com/a/145633. I'd love to see this compared with sha1.

Re: UUIDs are popular, but bad for performance (2019)

#142

Post by mhoad »

carfacts wrote: post #139
mhoad wrote: post #133

I recently read a book by Google’s head guy on API design that was specifically about designing APIs and it had a big section on what makes a good identifier and why people reach for UUIDs and why specifically it is a problem on multiple levels. The thing that he ended up recommending however was super interesting in that I had never seen it mentioned before but it was basically to use this instead http://www.crockfo…

This format was discussed in a HN first page post just this week: > This alphabet, 0123456789ABCDEFGHJKMNPQRSTVWXYZ, is Douglas Crockford's Base32, chosen for human readability and being able to call it out over a phone if required. https://news.ycombinator.com/item?id=29794186

Yeah when he lays out the arguments for it in the book you can clearly see why it makes a huge amount of sense. The usability, the performance, the value of a checksum etc…

Re: UUIDs are popular, but bad for performance (2019)

#143

Post by afhammad »

nostrebored wrote: post #135
mhoad wrote: post #133

I recently read a book by Google’s head guy on API design that was specifically about designing APIs and it had a big section on what makes a good identifier and why people reach for UUIDs and why specifically it is a problem on multiple levels. The thing that he ended up recommending however was super interesting in that I had never seen it mentioned before but it was basically to use this instead http://www.crockfo…

What was the book?

https://livebook.manning.com/book/api-design-patterns/chapte...

Section 6.3.3 gets to the point about base32

Re: UUIDs are popular, but bad for performance (2019)

#145

Post by masklinn »

thomasmg wrote: post #89
smoyer wrote: post #67

Earlier quoted context omitted.

"Bad for performance as primary keys" -> "Bad for performance as primary keys in MySQL". This isn't an issue in PostgreSQL and perhaps the lesson here is that as you scale, you need to understand more about the internals of the DB system you've chosen. This isn't limited to RDBMS as it's pretty easy to show trade-offs in choosing a NoSQL as well.

You are right, the internals are important. When I tested this, a long time ago [1] [2], I found that randomly distributed keys on PostgreSQL were indeed faster than e.g. on MySQL. Which surprised me! I still don't quite understand why. But, even with PostgreSQL, sequential (or nearly sequential) are much faster. [1] https://markmail.org/thread/3jzqjy6cavxcrpbq [2] https://markmail.org/download.xqy?id=3jzqjy6cavxcrpb…

> Which surprised me! I still don't quite understand why.

Because MySQL (specifically innodb) will cluster by the PK by default, while Posgres will not.

Meaning in MySQL, by default, the table will be stored in PK order, so when you insert an UUID at a random location any record which sorts after it has to be moved to make room, for nothing since UUID ordering is not really relevant (at least for the current standard UUIDs).

In pg, clustering is not a property (at least for now) it's an explicit point operation, which is not performed by default. So when you insert a row whether UUID or INTEGER-keyed, it's just tacked on wherever there's free space. The UUID still has to be inserted at the right place in the index' btree which can lead to a lot of cascading changes, and it's more expensive to compute, store, and load than a simple integer (especially 32b), but it doesn't induce an arbitrarily high amount of shuffling in the storage layer of the table itself.

Re: UUIDs are popular, but bad for performance (2019)

#146

Post by hermanradtke »

mhoad wrote: post #133

I recently read a book by Google’s head guy on API design that was specifically about designing APIs and it had a big section on what makes a good identifier and why people reach for UUIDs and why specifically it is a problem on multiple levels. The thing that he ended up recommending however was super interesting in that I had never seen it mentioned before but it was basically to use this instead http://www.crockfo…

ULIDs are base 32 as well. https://github.com/oklog/ulid

Re: UUIDs are popular, but bad for performance (2019)

#147

Post by davidjytang »

carfacts wrote: post #139
mhoad wrote: post #133

I recently read a book by Google’s head guy on API design that was specifically about designing APIs and it had a big section on what makes a good identifier and why people reach for UUIDs and why specifically it is a problem on multiple levels. The thing that he ended up recommending however was super interesting in that I had never seen it mentioned before but it was basically to use this instead http://www.crockfo…

This format was discussed in a HN first page post just this week: > This alphabet, 0123456789ABCDEFGHJKMNPQRSTVWXYZ, is Douglas Crockford's Base32, chosen for human readability and being able to call it out over a phone if required. https://news.ycombinator.com/item?id=29794186

People in my industry in my country has specifically avoid using B and D together as they sound too similar over the phone.

Also 2 and Z can be similar in writing.

However it is nice to not see 0 and O, 1,I,l in the same string.

Re: UUIDs are popular, but bad for performance (2019)

#148

Post by evanelias »

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.

MySQL doesn't have a UUID type, nor 128-bit int type. But MySQL 8 does provide functions UUID_TO_BIN and BIN_TO_UUID, which make it trivial to convert between the human-readable string representation of a UUID and the more efficient BINARY(16) representation.

MariaDB now has a native UUID column type as of MariaDB 10.7. This is brand new -- 10.7 had its RC release in Nov, not GA yet but very soon I'd imagine.

Re: UUIDs are popular, but bad for performance (2019)

#149

Post by datavirtue »

eerikkivistik wrote: post #43
gls2ro wrote: post #23

A lot of things that we do for security or privacy are bad for performance, but I think they are still good tradeoffs.

Agreed. Using UUID-s for keys is useful to exclude entire classes of security issues. Most notable are many kinds of enumeration attacks.

Quit exposing your keys.

Re: UUIDs are popular, but bad for performance (2019)

#150

Post by pkulak »

mhoad wrote: post #133

I recently read a book by Google’s head guy on API design that was specifically about designing APIs and it had a big section on what makes a good identifier and why people reach for UUIDs and why specifically it is a problem on multiple levels. The thing that he ended up recommending however was super interesting in that I had never seen it mentioned before but it was basically to use this instead http://www.crockfo…

I use crockford 32 to _represent_ my UUIDs, but they are obviously stored as binary. Is the only problem with UUIDs that sometimes they get stored as strings?

The only issue I've had with UUIDs is when they don't sort in increasing chronological order. RDBMSs don't appreciate high insert loads into random points in the index. Take care of that, however, and they're a treat.

Post reply on HN