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

[Yaniv Kaul]

On Wed, Dec 7, 2016 at 10: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.
>
> # 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.
>

Will the user know to answer intelligently if vacuum is needed or not?
Except for 'yes, you need it', we cannot even provide a time estimate (I
assume a disk space estimate is available!)
I would suggest to run ANALYZE for sure and provide an option at the end of
installation, to run the required command line - so make it as accessible
as possible, but not part of the flow.

I'm wondering if the community can run ANALYZE on their database, and we
can estimate how many are in dire need for full vacuum already.
Y.

- 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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/devel/attachments/20161207/369f1536/attachment.html>