Snapshots of PostgreSQL on haumea.nixos.org cause full disk issues

mweinelt commented 1 week ago

Hydra's database on haumea.nixos.org runs PostgreSQL on ZFS with zrepl for snapshot-based backups. Every once in a while we see the size of snapshots increase from <1G to 70-120G which results in a full disk.

My current working theory is

It is not related to WAL, since the WAL is only ~500MB in size, and we use a zrepl hook to force a CHECKPOINT before the snapshot g ets taken
It is likely an index that gets reshuffled (jobsetevalmembers_pk index is ~60GB in size)
We are likely also seeing an effect of write amplification (PostgreSQL uses 8K records, ZFS was configured for 16K records, down from its 128K default), going further down to 8K is not recommended. Instead maybe use 128K records and a dedicated SLOG device?

mweinelt commented 3 days ago

Time-wise I could correlate this with automatic vacuuming.

https://github.com/NixOS/infra/blob/master/build/haumea/postgresql.nix#L79-L87

mweinelt commented 3 days ago

Migrated compression from zstd to lz4, cause that is probably lighter on the CPU.

vcunat commented 3 days ago

Years ago (ec610987a23a80deec6d5e01a4472021396d8a46) @edolstra tweaked some vacuuming parameters to 1/100 of the defaults. Maybe we could ease that a bit, as apparently we're suffering from too much vacuuming?

NixOS / infra

Snapshots of PostgreSQL on haumea.nixos.org cause full disk issues #446