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

51–60 of 246 posts

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

#51

Post by NavinF »

olliej wrote: post #28

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 n…

It often doesn't matter if you use a 128bit int or a string since either way you're loading 4K/8K/16K from ssd/hdd. Database people do all sorts of silly things like storing datetimes as normalized ISO strings (2022-01-08T08:18:20) instead of using 64bit unix time. They get away with it because both the backend (durable storage) and the clients (webapps responding to a user 10ms away) are incredibly slow compared to CPUs.

That aside, Postgres stores UUIDs as a 128bit int, not as a string, so it almost certainly uses multi-word comparisons via memcmp: https://stackoverflow.com/a/29882952/703382

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

#52

Post by kgeist »

jcelerier wrote: post #42
kgeist wrote: post #41

Just a month ago we migrated many of the columns from integers to UUIDs (encoded as binary(16)) in several critical tables which are pretty large (Percona server, too), and so far I haven't heard about any serious performance degradation after the release.

what's pretty large for you though ? There are fields where 100k entries is a pretty large dataset and others where "large" starts at petabyte

I didn't mention that our DB setup uses sharding, and every tenant has their own DB shard (there are tens of thousands of shards). I just checked that one of the largest tenants has 2.2 mln rows in one of the affected tables, which is usually joined with 2-4 more related tables using UUIDs (another such table is 1.1 mln rows, for example), and they're on the hot code path because it's the core of the system. Maybe with sharding the difference is negligible? During code review I raised the concern that inserts and joins can become much slower after we migrate it to UUIDs, but so far my fears haven't materialized. Usually with these tables we have performance problems on the application side, not in the DB, such as ORM fetching data in a very inefficient way, or using too much RAM. Maybe it'll bite us in the long term as the tenants' shards grow in size, who knows.

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

#53

Post by NicoJuicy »

nchi3 wrote: post #49
VWWHFSfQ wrote: post #47

Earlier quoted context omitted.

I don't think you would ever get a 201 Created in an eventual consistency scenario. You would get a 202 Accepted. Unless the API is lying to the clients.

Fair enough, though what I said remains true for an "Accepted" response.

But Accepted doesn't mean created.

The feedback flow is async from the creation flow.

If you get created through the feedback flow it's created.

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

#54

Post by zelphirkalt »

nchi3 wrote: post #44
VWWHFSfQ wrote: post #34

Earlier quoted context omitted.

It's like, _maybe_ eventual consistency. Hopefully the client doesn't try to do anything important with the ID/new object

If you get a "Created" back, it is possible for it to be guaranteed that it'll be created eventually.

What if the object is deleted again? You could still have an inconsistent picture, no? And you could create and delete over and over again, so that a client has an inconsistent picture all the time.

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

#55

Post by mmoskal »

nielsole wrote: post #30
ummonk wrote: post #17

Earlier quoted context omitted.

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?

In these systems you typically can list blobs based on prefix. If you shard blobs sharing prefix to different nodes that becomes difficult.

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

#56

Post by sterwill »

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.

This used to be the case, but it's not true any more.

For example, previously Amazon S3 performance guidelines recommended randomizing prefix naming with hashed characters to optimize performance for frequent data retrievals. You no longer have to randomize prefix naming for performance, and can use sequential date-based naming for your prefixes.

https://docs.aws.amazon.com/AmazonS3/latest/userguide/optimi...

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

#57

Post by sterwill »

GordonS wrote: post #38
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.

Some years back (maybe 5-10?) I remember doing a test with UUIDs on Postgres, and found no speed difference between UUIDs and integer PKs. I don't remember the parameters of the test, however.

In my testing (years ago, don't have the data) there was no detectable difference between PKs of the uuid and bigint types in my application, but there was a difference between uuid and int (int was faster). uuid is 16 bytes, bigint is 8 bytes, int is 4 bytes, so at some scale there will be a performance difference.

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

#58

Post by busymom0 »

Slightly related question-

does anyone know what data type Reddit uses for their post/comment id? It seems like a short alphanumeric yet unique identifier and much shorter than a uuid.

Also what does twitter use for their post/comment id? Seems like some sort of big int?

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

#59

Post by philipswood »

cerved wrote: post #20
pizza234 wrote: post #9

Earlier quoted context omitted.

There are several problems, one of which is also ergonomy. I've only read it partially (it's a very interesting read nonetheless), however, this is a key point (italic mine): > Let’s assume a table of 1B rows having UUID values as primary key and five secondary indexes. If you read the previous paragraph, you know the primary key values are stored six times for each row. That means a total of 6B char(36) values repre…

> they are not human-readable, so one tends to store them as CHAR(36) instead one is an idiot

One does tend to be an idiot at times...

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

#60

Post by espadrine »

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

That is generally true for most if not all NewSQL databases, as far as I know. Spanner, CockroachDB, TiDB, … Maybe even Aurora?

The second argument on secondary indexes’ size still holds water, if it matters for a given application.

Post reply on HN