action #100979
openconfigure better auto-vacuum for our database(s)
0%
Description
Motivation¶
Over 80% space of /srv on OSD are used up. In #100859 we already identified that it's mostly our database and a re-import is much smaller so maybe better auto-vacuum helps.
Acceptance Criteria¶
AC1: auto-vacuum is conducted on a useful period for OSD
Suggestions¶
- See if an auto vacuum can be configured or if thresholds can be lowered (https://suse.slack.com/archives/C02AJ1E568M/p1634033225193500?thread_ts=1634030652.186600&cid=C02AJ1E568M)
Updated by okurz about 3 years ago
- Copied from action #100976: specific alert about the size of our database, maybe even database tables? added
Updated by okurz about 3 years ago
coolo ran a manual vacuum full for the "top 3 - top 8 tables". screenshot_links went from 11G to 3G, took 4 minutes (https://suse.slack.com/archives/C02AJ1E568M/p1634243304354100?thread_ts=1634030652.186600&cid=C02AJ1E568M). Now we use 62% (62G) so also increasing the disk size is not necessary right now.
Maybe we should trigger a vacuuming on job modules and screenshots after we delete jobs?
Updated by coolo about 3 years ago
postgresql is doing a vacuum itself once 20% of the rows changed. you could tweak this parameter, but postgresql won't shrink the table - only make deleted rows reaccessible.
Updated by mkittler almost 3 years ago
The manual vacuuming @coolo and me did as part of #100859 helped freeing disk space. As @coolo mentioned, the automatic vacuuming won't free disk space (as it doesn't free space on filesystem level).
Not sure whether we want to automate the "full" vacuuming (VACUUM FULL …) we did manually because while it is running the database is basically not usable and it is also not recommended by upstream.
Tweaking the threshold for the auto-vacuuming might be sufficient. It won't free disk space but could help us to not use that much disk space in the first place (as deleted rows are earlier reused instead).