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

[Yedidyah Bar David]

On Wed, Dec 7, 2016 at 12:08 PM, Piotr Kliczewski
<piotr.kliczewski at gmail.com> wrote:
> 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?

The engine needs access to the dwh db. The credentials the engine uses
are saved in above directory.

dwhd also needs access to its db. The credentials it uses are saved in:

/etc/ovirt-engine-dwh/ovirt-engine-dwhd.conf.d/

The keys (DWH_DB_HOST, DWH_DB_PORT etc.) are the same for both.

If dwh is a separate machine, you'll have the latter only on that
machine.

If the scope of current project does include dwh, it should consider
both same and different machines for engine+dwh. E.g., when you full-
vacuum the DBs (each of them separately), you effectively suspend both
engine and dwhd (if indeed also dwhd - did you check?), which can be
on two different machines. So:

1. If you run this tool standalone, on the engine machine, for both DBs,
you have to check also how it affects dwhd and handle.

2. If you run this during engine-setup on the engine machine, for both
DBs, it already asks dwhd to stop, even if on a remote machine. There is
a cronjob/systemd conf that should restart it once an hour.

3. If you want to provide a separate tool for dwh db, and allow running
it on the dwh machine, you have to handle the engine.

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



-- 
Didi