Upgrading Uber's MySQL Fleet

(www.uber.com)

234 points benocodes | 1 comments | 14 Oct 24 12:08 UTC | HN request time: 0.216s | source

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]▶

>>41836959 #

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]▶

>>41837062 #

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. vbezhenar ◴[15 Oct 24 07:13 UTC] No.41845775[source]▶

>>41837317 #

When postgres deletes a row, it marks a disk space unused. If you delete a lot of rows, there will be plenty of unused space, but that space will not be released to OS. To release that space to OS, you need to run vacuum full or perform backup/truncate/restore.

In normal circumstances it's not needed. The unused space is reused for further inserts and if your database maintains steady rate of inserts/deletes, this won't be an issue.

However I experienced situation when database starts to grow uncontrollably and it was happening for weeks (when actual workload didn't change). I don't know what causes that behaviour. The solution was to run `vacuum full`.

↑