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

[Piotr Kliczewski]

On Wed, Dec 7, 2016 at 9:57 AM, Roy Golan <rgolan at redhat.com> wrote:
> 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.

What about not storing this data in db? Do we need it all the time or
just for some amount of time?

>
> # 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
>
> _______________________________________________
> Users mailing list
> Users at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/users
>