Re: Berserk Autovacuum (let's save next Mandrill) - Mailing list pgsql-hackers

On Mon, Apr 15, 2019 at 10:15 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Sun, Apr 14, 2019 at 4:51 AM Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
> >
> > On Thu, Apr 11, 2019 at 11:25:29AM +0200, Chris Travers wrote:
> > >   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?
> >
> > I don't think that's what Andres had in mind, as he explicitly mentioned
> > removed rows. So just skipping lazy_cleanup_index when there were no
> > deleted would not help in that case.
> >
> > What I think we could do is simply leave the tuple pointers in the table
> > (and indexes) when there are only very few of them, and only do the
> > expensive table/index cleanup once there's anough of them.
>
> Yeah, we now have an infrastructure that skips index vacuuming by
> leaving the tuples pointers. So we then can have a threshold for
> autovacuum to invoke index vacuuming. Or an another idea is to delete
> index entries more actively by index looking up instead of scanning
> the whole index. It's proposed[1].
>
> [1] I couldn't get the URL of the thread right now for some reason but
> the thread subject is " [WIP] [B-Tree] Retail IndexTuple deletion".

Now I got https://www.postgresql.org/message-id/425db134-8bba-005c-b59d-56e50de3b41e%40postgrespro.ru

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center