From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | Chris Travers <chris(dot)travers(at)adjust(dot)com> |
Cc: | Andres Freund <andres(at)anarazel(dot)de>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Darafei Komяpa Praliaskouski <me(at)komzpa(dot)net>, Michael Banck <mbanck(at)gmx(dot)net>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Berserk Autovacuum (let's save next Mandrill) |
Date: | 2019-04-13 19:50:48 |
Message-ID: | 20190413195048.nfuysawke5xytcdg@development |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)anarazel(dot)de> wrote:
>
> Hi,
>
> On April 10, 2019 8:13:06 AM PDT, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)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.
> 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?
>
Not sure. I find it rather difficult to manage more and more different
types of cleanup workers.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2019-04-13 19:56:50 | Re: Commit message / hash in commitfest page. |
Previous Message | Robert Treat | 2019-04-13 18:46:27 | Re: Checksum errors in pg_stat_database |