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

191–200 of 246 posts

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

#191

Post by masklinn »

MAGZine wrote: post #168
colanderman wrote: post #128

Earlier quoted context omitted.

Length isn't the primary issue. Locality is. UUIDs are generally generated randomly. This results in terrible insert performance into B+tree-based indexes, and terrible (= no) lookup locality with basically any database. In a large table, successive entries ends up in separate disk pages. Even with time-based UUIDs, the time fields are ordered backward, which produces the same issue. One way to fix this (beside the m…

I can't say I've had much issue with this in postgres, but even still, this is easily worked around by having a pkey/cluster key of a monotomically increasing int--which is useful for other things such as data migrations, as you can pick defined start and end targets without worrying about new things being inserted in between.

Pg does not cluster, so the only impact of a uuid is that you’re inserting in a random location location of the index, which is not great but not the end of the world (probably).

InnoDB clusters on the PK by default, so when you're inserting a UUID you're not only inserting in the middle of the index (on average) you're also inserting in the middle of the table.

And I don't know how much the on-disk storage has been optimised for this sort of things, but if the answer is "not" and sparse pages are not really a thing, you might need to rewrite half the table in order to do so.

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

#192

Post by colanderman »

dllthomas wrote: post #171
colanderman wrote: post #128

Earlier quoted context omitted.

Length isn't the primary issue. Locality is. UUIDs are generally generated randomly. This results in terrible insert performance into B+tree-based indexes, and terrible (= no) lookup locality with basically any database. In a large table, successive entries ends up in separate disk pages. Even with time-based UUIDs, the time fields are ordered backward, which produces the same issue. One way to fix this (beside the m…

> Or reverse the time fields during indexing (slower, but still not as slow as an unnecessary disk read!). When you're IO bound, I'm not sure it actually is slower in practice.

Depending on caching strategy, using random UUIDs also decreases the maximum working set size before you become I/O bound by a huge factor. Postgres e.g. relies heavily on the O/S block cache and is subject to this.

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

#193

Post by layer8 »

davidjytang wrote: post #147
carfacts wrote: post #139

Earlier quoted context omitted.

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.

You still have to know that 0 is 0 and not O, and that 1 is 1 and not I or l.

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

#194

Post by inetknght »

tehlike wrote: post #181
web007 wrote: post #174

This article talks about random IDs leading to page thrashing, and MySQL b-tree indexes not handling them well. They are bad for _MySQL performance_. It doesn't talk about NoSQL or sharding, where random IDs usually perform much better than sequential due to a lack of hot shards. If you distribute your reads and writes at random across N machines you can get ~Nx performance vs one machine. If you make your writes seq…

Even in MySQL - one can use Sequential UUIDs.

I never understood why people would use sequential UUIDs. That rather defeats the purpose of a UUID. If you need something sequential then just use a much more simple number

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

#195

Post by gnabgib »

hermanradtke wrote: post #146
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

Is the spec not a better link than a go implementation?

https://github.com/ulid/spec

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

#196

Post by rco8786 »

inetknght wrote: post #194
tehlike wrote: post #181

Earlier quoted context omitted.

Even in MySQL - one can use Sequential UUIDs.

I never understood why people would use sequential UUIDs. That rather defeats the purpose of a UUID. If you need something sequential then just use a much more simple number

Because simple integers are not universally unique, a major feature of UUIDs…

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

#197

Post by rco8786 »

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…

We use crockford tokens heavily where I work, highly recommend them. We typically use a prefix to denote the type of thing being identified, like “U-“ for User followed by a 10-15ish long crockford token. Works great.

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

#198

Post by ryanbrunner »

globular-toast wrote: post #189
ryanbrunner wrote: post #97

Earlier quoted context omitted.

People use UUIDs for more reasons than just making things distributed. You can generate them client side if that's advantageous, you prevent leaking information about how many records there are in the system and prevent guessing of other potential PKs and potential unauthorized access, and there's some optimization strategies that benefit from not relying on a serial PK.

Well they shouldn't. UUIDs are for distributed systems. Generating keys client side? That's a distributed system.

I've had occasional uses for it in systems that aren't distributed at all. It's a handy property of UUIDs, if UUIDs are useful for a particular use case, I'm going to use them, what they're 'supposed' to be for is irrelevant.

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

#199

Post by dpark »

staticassertion wrote: post #167
pkulak wrote: post #150

Earlier quoted context omitted.

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.

The linked article discusses the issues - the storage size when encoded as char, as 16byte values, as well as the impact on read/write locality.

Where does it discuss any of this? It compares base 10/16/32/64. There’s no mention at all of UUIDs or GUIDs.

(Or do you mean the original article, rather than the linked Crockford article?)

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

#200

Post by pottertheotter »

mattacular wrote: post #182
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…

Which book?

https://www.manning.com/books/api-design-patterns
Post reply on HN