Project

General

Profile

Actions

action #100979

open

configure better auto-vacuum for our database(s)

Added by okurz over 2 years ago. Updated about 2 years ago.

Status:
New
Priority:
Low
Assignee:
-
Category:
-
Target version:
Start date:
2021-10-12
Due date:
% Done:

0%

Estimated time:

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


Related issues 1 (1 open0 closed)

Copied from openQA Infrastructure - action #100976: specific alert about the size of our database, maybe even database tables?New2021-10-12

Actions
Actions #1

Updated by okurz over 2 years ago

  • Copied from action #100976: specific alert about the size of our database, maybe even database tables? added
Actions #2

Updated by okurz over 2 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?

Actions #3

Updated by coolo over 2 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.

Actions #4

Updated by mkittler about 2 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).

Actions

Also available in: Atom PDF