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

21–30 of 246 posts

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

#21

Post by brabel »

ebingdom wrote: post #8

> Let’s begin by the base64 notation. The cardinality of each byte is 64 so it takes 3 bytes in base64 to represent 2 bytes of actual value. Wait, what? I thought it takes 4 base-64 digits to represent 3 bytes of data. Not 3 base-64 digits to represent 2 bytes of data.

base64 means the "vocabulary" used has 6 bits (2^6 = 64). Hence, to complete a full number of bytes (without using any padding), you need 4 b64-letters:

    4 * 6 = 24 = 8 * 3
So, you're correct... the exact amount of bytes that it takes to represent "actual" bytes goes like this:

    Actual bytes | b64 bytes required | overhead
    1            | 2                  | 2x
    2            | 3                  | 1.5x
    3            | 4                  | 1.33x
    4            | 6                  | 1.5x
    5            | 7                  | 1.4x
    6            | 8                  | 1.33x
    7            | 10                 | 1.43x
    8            | 11                 | 1.37x
    9            | 12                 | 1.33x
EDIT: As you can see, this averages with an overhead of between 33% (best case scenario where the encoding requires no padding, happens every 3 rows above) and something like 37%, decreasing with the number of bytes being encoded and approaching the minimum, 33% (e.g. to encode 1024 bytes, you need 1366 b64 digits, an overhead of 1.333984375x).

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

#22

Post by cerved »

radicalbyte wrote: post #5
hobs wrote: post #4

Earlier quoted context omitted.

A lot of the problems listed in the post are physical issues with larger data types that are somewhat random - eg the size, how clustered indexes work, and you will have the same problems with them in SQL Server.

If you order the data based on the uuid and your uuid is randomly distributed, then you will almost always be writing the data in the middle of your table, physically. You can cut the impact somewhat by using spare tables (leaving lots of empty space) but eventually you'll be re-writing the data. SQL Server has a sequential uuid type which avoids exactly this problem.

> SQL Server has a sequential uuid type which avoids exactly this problem.

you refer to the uuid generated by sql server?

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

#24

Post by tticvs »

masklinn wrote: post #18
tticvs wrote: post #15

Earlier quoted context omitted.

Postgres is somewhat different mainly because it doesn't use clustered index primary keys. So the row's position on disk is not related to the primary key index entry's position on disk. Additionally using the less cryptographically secure uuid v1 can be a performance optimization since it has implicit time based sorting.

> Additionally using the less cryptographically secure uuid v1 can be a performance optimization since it has implicit time based sorting. Except the way the fields are laid out basically defeats the point: UUIDv1 lays a 60 bits timestamp starting from the lower 32 bits, so it only sorts within a 7 minutes (2*32 * 100ns) bucket. Hence the proposal for UUIDv6, which lays the exact same timestamp in reverse order (star…

Huh, you're right. I guess I gotta message an old coworker and tell them their "perf optimization" didn't work.

Lesson learned, thanks!

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

#25

Post by roenxi »

> and purely random (version 3)

This is a typo, v3 isn't random. It is generated deterministically from inputs.

> The only “repeated” value is the version, “4”, at the beginning of the 3rd field. All the other 124 bits are random.

And this is close but not quite correct. UUID v4 has a couple of other fixed bits, there are only 121-122 random ones. There are patterns in the text representation other than constant numbers. :)

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

#26

Post by roenxi »

radicalbyte wrote: post #5
hobs wrote: post #4

Earlier quoted context omitted.

A lot of the problems listed in the post are physical issues with larger data types that are somewhat random - eg the size, how clustered indexes work, and you will have the same problems with them in SQL Server.

If you order the data based on the uuid and your uuid is randomly distributed, then you will almost always be writing the data in the middle of your table, physically. You can cut the impact somewhat by using spare tables (leaving lots of empty space) but eventually you'll be re-writing the data. SQL Server has a sequential uuid type which avoids exactly this problem.

Why use a sequential UUID over integers as an id and then associating them with a random UUID?

Integers are a superior way to record sequential data.

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

#27

Post by Aardwolf »

> The missing 4 bits is the version number used as a prefix to the time-hi field.

Why would you use 4 bits for a version number in something that's supposed to be unique? What is the benefit of following this specification despite such cost, versus creating 128 unique bits based on time / random generators / machine IDs yourself?

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

#28

Post by olliej »

I am very much not a database person, so forgive me if this is a dumb question.

I'm reading this article and it says that UUID are compared byte by byte, and seems to be indicating they're stored as string. Is that actually the case? I would have assumed that SQL supported 128 bit ints, but this seems to imply it does not.

Another question: if a column is set to char(fixed size) do the various sequel engines really not optimise to do multi word comparisons? (e.g. 8byte at a time, then 4, 2, 1, as size requires)

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

#30

Post by nielsole »

ummonk wrote: post #17
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.…

Yeah S3 has similar performance issues where accessing objects with the same prefixes has lower throughput because they get sharded onto the same server. It's very counterintuitive when you're used to how performance works on single computers where you want to optimize for cache-locality.

Ugh really? Why would they not hash the whole filename for shard assignment?
Post reply on HN