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

On Thu, Mar 28, 2019 at 12:32 PM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On Thu, 28 Mar 2019 at 22:04, Darafei "Komяpa" Praliaskouski
> <me(at)komzpa(dot)net> wrote:
> >
> > On Thu, Mar 28, 2019 at 2:36 AM David Rowley <
> david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> >> I thought recently that it would be good to have some sort of
> >> pro-active auto-vacuum mode that made use of idle workers.
> >
> > Problem with "idle" is that it never happens on system that are going to
> wraparound on their lifetime. This has to be a part of normal database
> functioning.
>
> I'd say auto-vacuum is configured to run too slowly if you never have
> an idle worker. The chances that it happens to be running at exactly
> the right speed to keep up with demand must be about close to nil.
>
> > Why not select a table that has inserts, updates and deletes for
> autovacuum just like we do for autoanalyze, not only deletes and updates
> like we do now?
>
> Sounds like a good idea, although I do agree with Alvaro when he
> mentions that it would be good to only invoke a worker that was only
> going to freeze tuples and not look at the indexes.

This is current behavior of VACUUM on tables without dead tuples, already.
Issue is that nothing triggers this VACUUM apart from user performing
VACUUM manually, or super late antiwraparound vacuum.

Any patch not in the current CF is already PG13 or beyond. Having at
> least a freeze only vacuum mode main ease some pain, even if it still
> needs to be done manually for anyone finding themselves in a similar
> situation as mailchimp.
>

If you're in wraparound halt with super large table on Amazon gp2 nothing
will help you - issue is, there's no option to "rewrite all of it quickly".
Burst limit lets you feel the shared drive as if it was an SSD on most of
your load, but reading and re-writing all the storage gets throttled, and
there's no option to escape this quickly.

The process that freezes and marks all-visible pages has to run in parallel
and at the speed of your backend pushing pages to disk, maybe lagging
behind a bit - but not up to "we need to rescan all the table".

>
> The idea I was mentioning was more targeted to ease the sudden rush of
> auto-vacuum activity when suddenly a bunch of large tables require an
> anti-wraparound vacuum all at once.
>
> [1] https://commitfest.postgresql.org/22/1817/
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa