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 <emarc...@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 <rgo...@redhat.com> wrote: > >> >> >> On 7 December 2016 at 21:44, Roy Golan <rgo...@redhat.com> wrote: >> >>> >>> >>> On 7 December 2016 at 21:00, Michal Skrivanek <mskri...@redhat.com> >>> wrote: >>> >>>> >>>> >>>> On 07 Dec 2016, at 11:28, Yaniv Kaul <yk...@redhat.com> wrote: >>>> >>>> >>>> >>>> On Wed, Dec 7, 2016 at 10:57 AM, Roy Golan <rgo...@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. >>> >>> [3] https://gist.github.com/rgolangh/049cff30b89c5b29284ceee80a3 >>> 5dbb4#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/t >> able_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-au >>>>> tovacuum.html >>>>> [3] https://www.postgresql.org/docs/devel/static/sql-vacuum.html >>>>> >>>>> _______________________________________________ >>>>> Users mailing list >>>>> us...@ovirt.org >>>>> http://lists.ovirt.org/mailman/listinfo/users >>>>> >>>>> >>>> _______________________________________________ >>>> Users mailing list >>>> us...@ovirt.org >>>> http://lists.ovirt.org/mailman/listinfo/users >>>> >>>> >>> >> >> _______________________________________________ >> Devel mailing list >> Devel@ovirt.org >> http://lists.ovirt.org/mailman/listinfo/devel >> > > > > -- > -Eldad > -- -Eldad
_______________________________________________ Devel mailing list Devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/devel