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

Thu Dec 8 10:16:05 UTC 2016

On 8 December 2016 at 10:06, Yedidyah Bar David <didi at redhat.com> wrote:

> On Wed, Dec 7, 2016 at 10:56 PM, Eldad Marciano <emarcian at redhat.com>
> wrote:
> > just forgot to mention that no customization required just plug & play he
> > will collect a large set of informative data by deafult
> >
> > On Wed, Dec 7, 2016 at 10:54 PM, Eldad Marciano <emarcian at redhat.com>
> wrote:
> >>
> >> In terms of measuring I used pgclu couple of times and it powerfull,easy
> >> to use, and provide very nice HTML reports
> >> http://pgcluu.darold.net/
> >>
> >> And also provide autovacum analysis
> >> http://pgcluu.darold.net/example/dolibarr-table-vacuums-analyzes.html
> >>
> >>
> >>
> >> On Wed, Dec 7, 2016 at 9:55 PM, Roy Golan <rgolan at redhat.com> wrote:
> >>>
> >>>
> >>>
> >>> On 7 December 2016 at 21:44, Roy Golan <rgolan at redhat.com> wrote:
> >>>>
> >>>>
> >>>>
> >>>> On 7 December 2016 at 21:00, Michal Skrivanek <mskrivan at redhat.com>
> >>>> wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>> On 07 Dec 2016, at 11:28, Yaniv Kaul <ykaul at redhat.com> wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>> 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.
> >>>>>
> >>>>>
> >>>>> So, effectively this should be interesting mostly/only for the audit
> >>>>> log. All other busy table are mostly in-place updates
> >>>>
> >>>>
> >>>> Given that autovacuum is performing well the yes but if it starts to
> >>>> fall behind this may help a bit.
> >>>> audit_log is insert mostly and also delete, we remove a day, each day.
> >>>>>
> >>>>>
> >>>>>>
> >>>>>> # 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)
> >>>>>
> >>>>>
> >>>>> That does sound as a good start not requiring much user involvement
> >>>>>
> >>>>>> 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
> >>>>>
> >>>>>
> >>>>> +1
> >>>>>
> >>>>>> - since VACUUM ANALYZE is consider a recommended maintenance task we
> >>>>>> can to it by default and ask the user for FULL.
> >>>>>
> >>>>>
> >>>>> When would you run it? ANALYZE nightly?
> >>>>>
> >>>> No I'd still avoid doing this repeatedly, autovaccum should handle
> that
> >>>> as well, but this would cover situations where it isn't functioning
> >>>> optimally.
> >>>>
> >>>> I think  its worth adding a report of the db status and the rate of
> the
> >>>> autovacuum (a slight midifed version of the query mskrivanek ran on
> one of
> >>>> the production systems [3])  that will go to the logcollector.
> Perhaps the
> >>>> output of the ANALYZE will help as well.
>
> I think that if possible, we should aim for automatic tuning of
> auto-vacuum.
> Either by checking the logs for failures and give it e.g. more time, or by
> checking analyze and deduce from that (if possible).
>

This would be tricky and error prone. The autovacuum already can be
configured using factors and costs
to respond changes.

>
> Another option is to disable autovacuum, and routinely run vacuum (not full
> vacuum), but then always let it finish successfully before starting the
> next
> run of it.
>
Also a very dangerous path, I wouldn't try to outsmart autovacuum and I
don't think its common to see. Disabling was maybe common
in pre 9 releases of PG and now this is not the case anymore

>
> >>>>
> >>>> [3]
> >>>> https://gist.github.com/rgolangh/049cff30b89c5b29284ceee80a35db
> b4#file-table_status_by_dead_rows-sql
> >>>
> >>>
> >>>
> >>> Very interesting collection of pg scrips to measure bloat and vacuum -
> >>> needs access to postgres objects though
> >>>
> >>> - https://github.com/pgexperts/pgx_scripts
> >>> -
> >>> https://github.com/pgexperts/pgx_scripts/blob/master/bloat/
> table_bloat_check.sql
> >>> -
> >>> https://github.com/pgexperts/pgx_scripts/blob/master/
> vacuum/last_autovacuum.sql
> >>>
> >>>>
> >>>>
> >>>>>>
> >>>>>
> >>>>> 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!)
> >>>>
> >>>> perhaps we can estimate the bloat, there should be a github script to
> >>>> calculate that [4] not sure how good it is.
> >>>>>
> >>>>> 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.
> >>>>>
> >>>>>
> >>>>> If there are no significant gains why bother any other time but on
> >>>>> upgrade when it can be run unconditionally?
> >>>>>
> >>>>>
> >>>>> 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.
> >>>>
> >>>> I'll send a different mail for that.
> >>>>
> >>>>>
> >>>>>
> >>>>>> - remote db is supported as well, doesn't have to be local
> >>>>>
> >>>>>
> >>>>> Well, not sure if we need to bother. It was introduced for large
> >>>>> deployments where the host can't fit both engine and db load. Do we
> still
> >>>>> have this issue? I wouldn't say so for 4.1. It may be very niche case
> >>>>>
> >>>> Running full vacuum is anyway a psql command, so there is no hidden
> cost
> >>>> here (to the development side I mean)
> >>>>
> >>>>>
> >>>>> Thanks,
> >>>>> michal
> >>>>>
> >>>>>>
> >>>>>> # 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
> >>>>>>
> >>>>>
> >>>>> _______________________________________________
> >>>>> 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
> >>
> >>
> >>
> >>
> >> --
> >> -Eldad
> >
> >
> >
> >
> > --
> > -Eldad
> >
> > _______________________________________________
> > Users mailing list
> > Users at ovirt.org
> > http://lists.ovirt.org/mailman/listinfo/users
> >
>
>
>
> --
> Didi
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/devel/attachments/20161208/140f7e06/attachment-0001.html>