<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On 7 December 2016 at 21:44, 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:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote"><span class="gmail-">On 7 December 2016 at 21:00, Michal Skrivanek <span dir="ltr"><<a href="mailto:mskrivan@redhat.com" target="_blank">mskrivan@redhat.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto"><span class="gmail-m_-6204533235685619933gmail-"><div></div><div><br></div><div><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></div><blockquote type="cite"><div><div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Dec 7, 2016 at 10:57 AM, 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:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><div><div>Hi all, <br><br></div>This is a discussion on the RFE[1] to provide a tool to perform full vacuum on our DBs.<br><br></div>First if you are not familiar with vacuum please read this [2]<br><br></div><div># Backgroud<br></div><div>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. <br>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.<br>Autovacuum is essential for a db to function optimally and tweaking it is out of the scope of the feature.<br><br>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.<br></div></div></div></blockquote></div></div></div></div></blockquote><div><br></div></span>So, effectively this should be interesting mostly/only for the audit log. All other busy table are mostly in-place updates</div></blockquote><div><br></div></span><div>Given that autovacuum is performing well the yes but if it starts to fall behind this may help a bit.<br>audit_log is insert mostly and also delete, we remove a day, each day. <br></div><span class="gmail-"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto"><div><span class="gmail-m_-6204533235685619933gmail-"><br><blockquote type="cite"><div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><br></div><div># Critiria<br>Provide a way to reclaim disk space claimed by the garbage created over time by the engine db and dwh.<br></div><div><br># Usage<br>Either use it as part of the upgrade procedure (after all dbscipts execution)<br></div></div></div></blockquote></div></div></div></div></blockquote><div><br></div></span>That does sound as a good start not requiring much user involvement</div><div><span class="gmail-m_-6204533235685619933gmail-"><br><blockquote type="cite"><div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><div><div>or just provide the tool and admin will run in on demand<br></div><div>- engine db credentials read from /etc/ovirt-engine/engine.conf.<wbr>d/<br></div><div>- invocation:<br> ```<br></div><div> tool: [dbname(default engine)] [table: (default all)]<br></div><div> ```<br></div><div>- if we invoke it on upgrade than an installation plugin should be added to invoke with default, no interaction<br></div></div></div></div></div></blockquote></div></div></div></div></blockquote><div><br></div></span>+1</div><div><span class="gmail-m_-6204533235685619933gmail-"><br><blockquote type="cite"><div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><div><div>- since VACUUM ANALYZE is consider a recommended maintenance task we can to it by default and ask the user for FULL.</div></div></div></div></div></blockquote></div></div></div></div></blockquote><div><br></div></span>When would you run it? ANALYZE nightly?</div><div><span class="gmail-m_-6204533235685619933gmail-"><br></span></div></div></blockquote></span><div>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.<br><br></div><div>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.<br><br>[3] <a href="https://gist.github.com/rgolangh/049cff30b89c5b29284ceee80a35dbb4#file-table_status_by_dead_rows-sql" target="_blank">https://gist.github.com/<wbr>rgolangh/<wbr>049cff30b89c5b29284ceee80a35db<wbr>b4#file-table_status_by_dead_<wbr>rows-sql</a></div></div></div></div></blockquote><div><br></div><div><br></div><div>Very interesting collection of pg scrips to measure bloat and vacuum - needs access to postgres objects though<br><br>- <a href="https://github.com/pgexperts/pgx_scripts">https://github.com/pgexperts/pgx_scripts</a><br>- <a href="https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql">https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql</a><br>- <a href="https://github.com/pgexperts/pgx_scripts/blob/master/vacuum/last_autovacuum.sql">https://github.com/pgexperts/pgx_scripts/blob/master/vacuum/last_autovacuum.sql</a><br> <br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><div><br></div><span class="gmail-"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto"><div><span class="gmail-m_-6204533235685619933gmail-"><blockquote type="cite"><div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><div><div><br></div></div></div></div></div></blockquote><div><br></div><div>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!)<br></div></div></div></div></div></blockquote></span></div></div></blockquote></span><div>perhaps we can estimate the bloat, there should be a github script to calculate that [4] not sure how good it is. <br></div><span class="gmail-"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto"><div><span class="gmail-m_-6204533235685619933gmail-"><blockquote type="cite"><div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><div></div><div>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.</div></div></div></div></div></blockquote><div><br></div></span><div>If there are no significant gains why bother any other time but on upgrade when it can be run unconditionally?</div><span class="gmail-m_-6204533235685619933gmail-"><div><br></div><blockquote type="cite"><div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><div><br></div><div>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.</div><div>Y.</div></div></div></div></div></blockquote></span></div></div></blockquote></span><div>I'll send a different mail for that.<br></div><span class="gmail-"><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto"><div><span class="gmail-m_-6204533235685619933gmail-"><blockquote type="cite"><div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><div><div></div><div>- remote db is supported as well, doesn't have to be local<br></div></div></div></div></div></blockquote></div></div></div></div></blockquote><div><br></div></span>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</div><div><br></div></div></blockquote></span><div>Running full vacuum is anyway a psql command, so there is no hidden cost here (to the development side I mean)<br> <br></div><span class="gmail-"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto"><div></div><div>Thanks,</div><div>michal</div><span class="gmail-m_-6204533235685619933gmail-"><div><br><blockquote type="cite"><div><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><div><div><br></div><div># Questions<br></div><div> - Will remote dwh have the credentials under /etc/ovirt-engine/engine.conf.<wbr>d?<br></div><div> - Should AAA schema be taken into account as well?<br><br></div><div>Please review, thanks<br></div><div>Roy <br><br></div><div>[1] <a href="https://bugzilla.redhat.com/show_bug.cgi?id=1388430" target="_blank">https://bugzilla.redhat.com/sh<wbr>ow_bug.cgi?id=1388430</a><br>[2] <a href="https://www.postgresql.org/docs/9.2/static/runtime-config-autovacuum.html" target="_blank">https://www.postgresql.org/doc<wbr>s/9.2/static/runtime-config-au<wbr>tovacuum.html</a><br>[3] <a href="https://www.postgresql.org/docs/devel/static/sql-vacuum.html" target="_blank">https://www.postgresql.org/doc<wbr>s/devel/static/sql-vacuum.html</a><br></div></div></div></div></div>
<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></blockquote></div><br></div></div>
</div></blockquote><blockquote type="cite"><div><span>______________________________<wbr>_________________</span><br><span>Users mailing list</span><br><span><a href="mailto:Users@ovirt.org" target="_blank">Users@ovirt.org</a></span><br><span><a href="http://lists.ovirt.org/mailman/listinfo/users" target="_blank">http://lists.ovirt.org/mailman<wbr>/listinfo/users</a></span><br></div></blockquote></div></span></div>
</blockquote></span></div><br></div></div>
</blockquote></div><br></div></div>