[ovirt-devel] [Feture discussion] Full vacuum tool

[Roy Golan]

Hi all,

This is a discussion on the RFE[1] to provide a tool to perform full vacuum
on our DBs.

First if you are not familiar with vacuum please read this [2]

# Backgroud
ovirt 'engine' DB have several busy table with 2 differnt usage patten. One
is audit_log and the others are the 'v*_statistics' tables and the
difference between them is mostly inserts vs mostly hot updates.
Tables with tons of updates creates garbage or 'dead' records that should
be removed, and for this postgres have the aforementioned autovacuum
cleaner. It will make the db reuse its already allocated space to perform
future updates/inserts and so on.
Autovacuum is essential for a db to function optimally and tweaking it is
out of the scope of the feature.

Full vacuum is designed to reclaim the disk space and reset the table
statistics. It is a heavy maintenance task, it takes an exclusive lock on
the table and may take seconds to minutes. In some situations it is
effectively a downtime due to the long table lock and should not be running
when the engine is running.

# Critiria
Provide a way to reclaim disk space claimed by the garbage created over
time by the engine db and dwh.

# Usage
Either use it as part of the upgrade procedure (after all dbscipts
execution)
or just provide the tool and admin will run in on demand
- engine db credentials read from /etc/ovirt-engine/engine.conf.d/
- invocation:
 ```
 tool: [dbname(default engine)] [table: (default all)]
 ```
- if we invoke it on upgrade than an installation plugin should be added to
invoke with default, no interaction
- since VACUUM ANALYZE is consider a recommended maintenance task we can to
it by default and ask the user for FULL.
- remote db is supported as well, doesn't have to be local

# Questions
 - Will remote dwh have the credentials under
/etc/ovirt-engine/engine.conf.d?
 - Should  AAA schema be taken into account as well?

Please review, thanks
Roy

[1] https://bugzilla.redhat.com/show_bug.cgi?id=1388430
[2]
https://www.postgresql.org/docs/9.2/static/runtime-config-autovacuum.html
[3] https://www.postgresql.org/docs/devel/static/sql-vacuum.html
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/devel/attachments/20161207/f4cb41e2/attachment.html>