Re: Berserk Autovacuum (let's save next Mandrill)

On Wed, Apr 10, 2019 at 5:21 PM Andres Freund <andres@anarazel.de> wrote:

> Hi,
>
> On April 10, 2019 8:13:06 AM PDT, Alvaro Herrera <alvherre@2ndquadrant.com>
> wrote:
> >On 2019-Mar-31, Darafei "Komяpa" Praliaskouski wrote:
> >
> >> Alternative point of "if your database is super large and actively
> >written,
> >> you may want to set autovacuum_freeze_max_age to even smaller values
> >so
> >> that autovacuum load is more evenly spread over time" may be needed.
> >
> >I don't think it's helpful to force emergency vacuuming more
> >frequently;
> >quite the contrary, it's likely to cause even more issues.  We should
> >tweak autovacuum to perform freezing more preemtively instead.
>
> I still think the fundamental issue with making vacuum less painful is
> that the all indexes have to be read entirely. Even if there's not much
> work (say millions of rows frozen, hundreds removed). Without that issue we
> could vacuum much more frequently. And do it properly in insert only
> workloads.
>

So I see a couple of issues here and wondering what the best approach is.

The first is to just skip lazy_cleanup_index if no rows were removed.  Is
this the approach you have in mind?  Or is that insufficient?

The second approach would be to replace the whole idea of this patch with a
lazy freeze worker which would basically periodically do a vacuum freeze on
relations matching certain criteria.  This could have a lower max workers
than autovacuum and therefore less of a threat in terms of total IO usage.

Thoughts?

>
>
> Andres
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
>
>

-- 
Best Regards,
Chris Travers
Head of Database

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin