←back to thread

Upgrading Uber's MySQL Fleet

(www.uber.com)
234 points benocodes | 8 comments | 14 Oct 24 12:08 UTC | HN request time: 0.984s | source | bottom
Show context
whalesalad ◴[14 Oct 24 12:39 UTC] No.41836959[source]▶
So satisfying to do a huge upgrade like this and then see the actual proof in the pudding with all the reduced latencies and query times.
replies(1): >>41837062 #
hu3 ◴[14 Oct 24 12:52 UTC] No.41837062[source]▶
Yeah some numbers caught my attention like ~94% reduction in overall database lock time.

And to think they never have to worry about VACUUM. Ahh the peace.

replies(4): >>41837227 #>>41837317 #>>41837626 #>>41838255 #
InsideOutSanta ◴[14 Oct 24 13:22 UTC] No.41837317[source]▶
As somebody who has always used MySQL, but always been told that I should be using Postgres, I'd love to understand what the issues with VACUUM are, and what I should be aware of when potentially switching databases?
replies(5): >>41837435 #>>41838160 #>>41838537 #>>41839012 #>>41845775 #
1. djbusby ◴[14 Oct 24 13:40 UTC] No.41837435[source]▶
VACUUM and VACUUM FULL (and/or with ANALYZE) can lock tables for a very long time, especially when the table is large. Incantation may also require 2x the space for the table being operated on. In short: it's slow.
replies(4): >>41837649 #>>41837883 #>>41838660 #>>41838834 #
2. sgarland ◴[14 Oct 24 14:04 UTC] No.41837649[source]▶
`VACUUM` (with or without `ANALYZE`) on its own neither locks tables nor requires additional disk space. This is what the autovacuumdvaemon is doing. `VACUUM FULL` does both, as it's doing a tuple-by-tuple rewrite of the entire table.
3. gomoboo ◴[14 Oct 24 14:29 UTC] No.41837883[source]▶
pg_repack gets rid of the need to lock tables for the duration of the vacuum: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appen...

It is an extension though so downside there is it not being included in most Postgres installs. I’ve used it at work and it felt like a superpower getting the benefits of a vacuum full without all the usual drama.

replies(1): >>41838592 #
4. take-five ◴[14 Oct 24 15:44 UTC] No.41838592[source]▶
pg_repack can generate a lot of WAL, which can generate so much traffic that standby servers can fall behind too much and never recover.

We've been using https://github.com/dataegret/pgcompacttable to clean up bloat without impacting stability/performance as much as pg_repack does.

5. cooljacob204 ◴[14 Oct 24 15:50 UTC] No.41838660[source]▶
This is sorta mitigated by partitioning or sharding though right?

Too bad it's sorta annoying to do on plain old pg.

6. williamdclt ◴[14 Oct 24 16:05 UTC] No.41838834[source]▶
Only FULL takes a serious lock (normal vacuum only takes a weak lock preventing things like other vacuums or table alterations iirc).

Aside: I wish Postgres forced to make explicit the lock taken. Make me write “TAKE LOCK ACCESS EXCLUSIVE VACUUM FULL my_table”, and fail if the lock I take is too weak. Implicit locks are such a massive footgun that have caused countless incidents across the world, it’s just bad design.

replies(1): >>41839370 #
7. luhn ◴[14 Oct 24 16:55 UTC] No.41839370[source]▶
`TAKE LOCK ACCESS EXCLUSIVE VACUUM FULL` is just an incantation that will be blindly copy-pasted. I don't see how it would stop anyone from shooting themselves in the foot.
replies(1): >>41840122 #
8. immibis ◴[14 Oct 24 18:04 UTC] No.41840122{3}[source]▶
Imagine two footguns. One shoots your foot off when you open the window. The second requires you to point a gun at your foot and pull the trigger before the window unlocks. Far fewer people will suffer accidental foot injuries from the latter.