<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On 8 December 2016 at 12:16, Roy Golan <span dir="ltr"><<a href="mailto:rgolan@redhat.com" target="_blank">rgolan@redhat.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote"><div><div class="h5">On 8 December 2016 at 10:06, Yedidyah Bar David <span dir="ltr"><<a href="mailto:didi@redhat.com" target="_blank">didi@redhat.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="m_-1908282014880511197HOEnZb"><div class="m_-1908282014880511197h5">On Wed, Dec 7, 2016 at 10:56 PM, Eldad Marciano <<a href="mailto:emarcian@redhat.com" target="_blank">emarcian@redhat.com</a>> wrote:<br>
> just forgot to mention that no customization required just plug & play he<br>
> will collect a large set of informative data by deafult<br>
><br>
> On Wed, Dec 7, 2016 at 10:54 PM, Eldad Marciano <<a href="mailto:emarcian@redhat.com" target="_blank">emarcian@redhat.com</a>> wrote:<br>
>><br>
>> In terms of measuring I used pgclu couple of times and it powerfull,easy<br>
>> to use, and provide very nice HTML reports<br>
>> <a href="http://pgcluu.darold.net/" rel="noreferrer" target="_blank">http://pgcluu.darold.net/</a><br>
>><br>
>> And also provide autovacum analysis<br>
>> <a href="http://pgcluu.darold.net/example/dolibarr-table-vacuums-analyzes.html" rel="noreferrer" target="_blank">http://pgcluu.darold.net/examp<wbr>le/dolibarr-table-vacuums-<wbr>analyzes.html</a><br>
>><br>
>><br>
>><br>
>> On Wed, Dec 7, 2016 at 9:55 PM, Roy Golan <<a href="mailto:rgolan@redhat.com" target="_blank">rgolan@redhat.com</a>> wrote:<br>
>>><br>
>>><br>
>>><br>
>>> On 7 December 2016 at 21:44, Roy Golan <<a href="mailto:rgolan@redhat.com" target="_blank">rgolan@redhat.com</a>> wrote:<br>
>>>><br>
>>>><br>
>>>><br>
>>>> On 7 December 2016 at 21:00, Michal Skrivanek <<a href="mailto:mskrivan@redhat.com" target="_blank">mskrivan@redhat.com</a>><br>
>>>> wrote:<br>
>>>>><br>
>>>>><br>
>>>>><br>
>>>>> On 07 Dec 2016, at 11:28, Yaniv Kaul <<a href="mailto:ykaul@redhat.com" target="_blank">ykaul@redhat.com</a>> wrote:<br>
>>>>><br>
>>>>><br>
>>>>><br>
>>>>> On Wed, Dec 7, 2016 at 10:57 AM, Roy Golan <<a href="mailto:rgolan@redhat.com" target="_blank">rgolan@redhat.com</a>> wrote:<br>
>>>>>><br>
>>>>>> Hi all,<br>
>>>>>><br>
>>>>>> This is a discussion on the RFE[1] to provide a tool to perform full<br>
>>>>>> vacuum on our DBs.<br>
>>>>>><br>
>>>>>> First if you are not familiar with vacuum please read this [2]<br>
>>>>>><br>
>>>>>> # Backgroud<br>
>>>>>> ovirt 'engine' DB have several busy table with 2 differnt usage<br>
>>>>>> patten. One is audit_log and the others are the 'v*_statistics' tables and<br>
>>>>>> the difference between them is mostly inserts vs mostly hot updates.<br>
>>>>>> Tables with tons of updates creates garbage or 'dead' records that<br>
>>>>>> should be removed, and for this postgres have the aforementioned autovacuum<br>
>>>>>> cleaner. It will make the db reuse its already allocated space to perform<br>
>>>>>> future updates/inserts and so on.<br>
>>>>>> Autovacuum is essential for a db to function optimally and tweaking it<br>
>>>>>> is out of the scope of the feature.<br>
>>>>>><br>
>>>>>> Full vacuum is designed to reclaim the disk space and reset the table<br>
>>>>>> statistics. It is a heavy maintenance task, it takes an exclusive lock on<br>
>>>>>> the table and may take seconds to minutes. In some situations it is<br>
>>>>>> effectively a downtime due to the long table lock and should not be running<br>
>>>>>> when the engine is running.<br>
>>>>><br>
>>>>><br>
>>>>> So, effectively this should be interesting mostly/only for the audit<br>
>>>>> log. All other busy table are mostly in-place updates<br>
>>>><br>
>>>><br>
>>>> Given that autovacuum is performing well the yes but if it starts to<br>
>>>> fall behind this may help a bit.<br>
>>>> audit_log is insert mostly and also delete, we remove a day, each day.<br>
>>>>><br>
>>>>><br>
>>>>>><br>
>>>>>> # Critiria<br>
>>>>>> Provide a way to reclaim disk space claimed by the garbage created<br>
>>>>>> over time by the engine db and dwh.<br>
>>>>>><br>
>>>>>> # Usage<br>
>>>>>> Either use it as part of the upgrade procedure (after all dbscipts<br>
>>>>>> execution)<br>
>>>>><br>
>>>>><br>
>>>>> That does sound as a good start not requiring much user involvement<br>
>>>>><br>
>>>>>> or just provide the tool and admin will run in on demand<br>
>>>>>> - engine db credentials read from /etc/ovirt-engine/engine.conf.<wbr>d/<br>
>>>>>> - invocation:<br>
>>>>>> ```<br>
>>>>>> tool: [dbname(default engine)] [table: (default all)]<br>
>>>>>> ```<br>
>>>>>> - if we invoke it on upgrade than an installation plugin should be<br>
>>>>>> added to invoke with default, no interaction<br>
>>>>><br>
>>>>><br>
>>>>> +1<br>
>>>>><br>
>>>>>> - since VACUUM ANALYZE is consider a recommended maintenance task we<br>
>>>>>> can to it by default and ask the user for FULL.<br>
>>>>><br>
>>>>><br>
>>>>> When would you run it? ANALYZE nightly?<br>
>>>>><br>
>>>> No I'd still avoid doing this repeatedly, autovaccum should handle that<br>
>>>> as well, but this would cover situations where it isn't functioning<br>
>>>> optimally.<br>
>>>><br>
>>>> I think its worth adding a report of the db status and the rate of the<br>
>>>> autovacuum (a slight midifed version of the query mskrivanek ran on one of<br>
>>>> the production systems [3]) that will go to the logcollector. Perhaps the<br>
>>>> output of the ANALYZE will help as well.<br>
<br>
</div></div>I think that if possible, we should aim for automatic tuning of auto-vacuum.<br>
Either by checking the logs for failures and give it e.g. more time, or by<br>
checking analyze and deduce from that (if possible).<br></blockquote><div><br></div></div></div><div>This would be tricky and error prone. The autovacuum already can be configured using factors and costs<br></div><div>to respond changes. <br></div><span class=""><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
Another option is to disable autovacuum, and routinely run vacuum (not full<br>
vacuum), but then always let it finish successfully before starting the next<br>
run of it.<br></blockquote></span><div>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<br></div><div>in pre 9 releases of PG and now this is not the case anymore<br><br></div><div><div class="h5"><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="m_-1908282014880511197HOEnZb"><div class="m_-1908282014880511197h5"><br>
>>>><br>
>>>> [3]<br>
>>>> <a href="https://gist.github.com/rgolangh/049cff30b89c5b29284ceee80a35dbb4#file-table_status_by_dead_rows-sql" rel="noreferrer" target="_blank">https://gist.github.com/rgolan<wbr>gh/049cff30b89c5b29284ceee80a3<wbr>5dbb4#file-table_status_by_<wbr>dead_rows-sql</a><br>
>>><br>
>>><br>
>>><br>
>>> Very interesting collection of pg scrips to measure bloat and vacuum -<br>
>>> needs access to postgres objects though<br>
>>><br>
>>> - <a href="https://github.com/pgexperts/pgx_scripts" rel="noreferrer" target="_blank">https://github.com/pgexperts/p<wbr>gx_scripts</a><br>
>>> -<br>
>>> <a href="https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql" rel="noreferrer" target="_blank">https://github.com/pgexperts/p<wbr>gx_scripts/blob/master/bloat/t<wbr>able_bloat_check.sql</a><br>
>>> -<br>
>>> <a href="https://github.com/pgexperts/pgx_scripts/blob/master/vacuum/last_autovacuum.sql" rel="noreferrer" target="_blank">https://github.com/pgexperts/p<wbr>gx_scripts/blob/master/vacuum/<wbr>last_autovacuum.sql</a><br>
>>><br>
>>>><br>
>>>><br>
>>>>>><br>
>>>>><br>
>>>>> Will the user know to answer intelligently if vacuum is needed or not?<br>
>>>>> Except for 'yes, you need it', we cannot even provide a time estimate (I<br>
>>>>> assume a disk space estimate is available!)<br>
>>>><br>
>>>> perhaps we can estimate the bloat, there should be a github script to<br>
>>>> calculate that [4] not sure how good it is.<br>
>>>>><br>
>>>>> I would suggest to run ANALYZE for sure and provide an option at the<br>
>>>>> end of installation, to run the required command line - so make it as<br>
>>>>> accessible as possible, but not part of the flow.<br>
>>>>><br>
>>>>><br>
>>>>> If there are no significant gains why bother any other time but on<br>
>>>>> upgrade when it can be run unconditionally?<br>
>>>>><br>
>>>>><br>
>>>>> I'm wondering if the community can run ANALYZE on their database, and<br>
>>>>> we can estimate how many are in dire need for full vacuum already.<br>
>>>>> Y.<br>
>>>><br>
>>>> I'll send a different mail for that.<br>
>>>><br>
>>>>><br>
>>>>><br>
>>>>>> - remote db is supported as well, doesn't have to be local<br>
>>>>><br>
>>>>><br>
>>>>> Well, not sure if we need to bother. It was introduced for large<br>
>>>>> deployments where the host can't fit both engine and db load. Do we still<br>
>>>>> have this issue? I wouldn't say so for 4.1. It may be very niche case<br>
>>>>><br>
>>>> Running full vacuum is anyway a psql command, so there is no hidden cost<br>
>>>> here (to the development side I mean)<br>
>>>><br>
>>>>><br>
>>>>> Thanks,<br>
>>>>> michal<br>
>>>>><br>
>>>>>><br>
>>>>>> # Questions<br>
>>>>>> - Will remote dwh have the credentials under<br>
>>>>>> /etc/ovirt-engine/engine.conf.<wbr>d?<br>
>>>>>> - Should AAA schema be taken into account as well?<br>
>>>>>><br>
>>>>>> Please review, thanks<br>
>>>>>> Roy<br>
>>>>>><br>
>>>>>> [1] <a href="https://bugzilla.redhat.com/show_bug.cgi?id=1388430" rel="noreferrer" target="_blank">https://bugzilla.redhat.com/sh<wbr>ow_bug.cgi?id=1388430</a><br>
>>>>>> [2]<br>
>>>>>> <a href="https://www.postgresql.org/docs/9.2/static/runtime-config-autovacuum.html" rel="noreferrer" target="_blank">https://www.postgresql.org/doc<wbr>s/9.2/static/runtime-config-<wbr>autovacuum.html</a><br>
>>>>>> [3] <a href="https://www.postgresql.org/docs/devel/static/sql-vacuum.html" rel="noreferrer" target="_blank">https://www.postgresql.org/doc<wbr>s/devel/static/sql-vacuum.html</a><br>
>>>>>><br>
>>>>>> ______________________________<wbr>_________________<br>
>>>>>> Users mailing list<br>
>>>>>> <a href="mailto:Users@ovirt.org" target="_blank">Users@ovirt.org</a><br>
>>>>>> <a href="http://lists.ovirt.org/mailman/listinfo/users" rel="noreferrer" target="_blank">http://lists.ovirt.org/mailman<wbr>/listinfo/users</a><br>
>>>>>><br>
>>>>><br>
>>>>> ______________________________<wbr>_________________<br>
>>>>> Users mailing list<br>
>>>>> <a href="mailto:Users@ovirt.org" target="_blank">Users@ovirt.org</a><br>
>>>>> <a href="http://lists.ovirt.org/mailman/listinfo/users" rel="noreferrer" target="_blank">http://lists.ovirt.org/mailman<wbr>/listinfo/users</a><br>
>>>><br>
>>>><br>
>>><br>
>>><br>
>>> ______________________________<wbr>_________________<br>
>>> Devel mailing list<br>
>>> <a href="mailto:Devel@ovirt.org" target="_blank">Devel@ovirt.org</a><br>
>>> <a href="http://lists.ovirt.org/mailman/listinfo/devel" rel="noreferrer" target="_blank">http://lists.ovirt.org/mailman<wbr>/listinfo/devel</a><br>
>><br>
>><br>
>><br>
>><br>
>> --<br>
>> -Eldad<br>
><br>
><br>
><br>
><br>
> --<br>
> -Eldad<br>
><br>
> ______________________________<wbr>_________________<br>
> Users mailing list<br>
> <a href="mailto:Users@ovirt.org" target="_blank">Users@ovirt.org</a><br>
> <a href="http://lists.ovirt.org/mailman/listinfo/users" rel="noreferrer" target="_blank">http://lists.ovirt.org/mailman<wbr>/listinfo/users</a><br>
><br>
<br>
<br>
<br>
</div></div><span class="m_-1908282014880511197HOEnZb"><font color="#888888">--<br>
Didi<br>
</font></span></blockquote></div></div></div><br></div></div></blockquote><div><br><br></div><div>The simplest approach I find ATM is to put a post upgrade script under *share/ovirt-engine/dbscripts/upgrade/postupgrade/vacuum.sql *<br></div><div>```sql<br></div><div>VACUUM (FULL, ANALYZE, VERBOSE);<br>```<br></div><div>and that is performed for us, with no intervention, with no need to get the credentials and also the output goes to the setup log.<br></div></div><br><br></div></div>