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

From Darafei "Komяpa" Praliaskouski
Subject Re: Berserk Autovacuum (let's save next Mandrill)
Date
Msg-id CAC8Q8tJMzs8fh4Ene0_oLOqmTpPkQM_mYBG7LBWcGJwn23FUqQ@mail.gmail.com
Whole thread Raw
In response to Re: Berserk Autovacuum (let's save next Mandrill)  (Alvaro Herrera <alvherre@2ndquadrant.com>)
List pgsql-hackers


чт, 28 мар. 2019 г. в 01:01, Alvaro Herrera <alvherre@2ndquadrant.com>:
On 2019-Mar-28, Darafei "Komяpa" Praliaskouski wrote:


> чт, 28 мар. 2019 г. в 00:32, Alvaro Herrera <alvherre@2ndquadrant.com>:
>
> > On 2019-Mar-27, Darafei "Komяpa" Praliaskouski wrote:

> > * certain tables would have some sort of partial scan that sets the
> >   visibility map.  There's no reason to invoke the whole vacuuming
> >   machinery.  I don't think this is limited to append-only tables, but
> >   rather those are just the ones that are affected the most.
>
> What other machinery runs on VACUUM invocation that is not wanted there?
> Since Postgres 11 index cleanup is already skipped on append-only tables.

Well, I think it would be useful to set all-visible earlier than waiting
for a vacuum to be necessary, even for tables that are not append-only.
So if you think about this just for the append-only table, you leave
money on the table.

Thing is, problem does not exist for non-append-only tables, they're going to be vacuumed after 50 rows got updated, automatically.
 

> > * tables nearing wraparound danger should use the (yet to be committed)
> >   option to skip index cleaning, which makes the cleanup action faster.
> >   Again, no need for complete vacuuming.
>
> "Nearing wraparound" is too late already. In Amazon, reading table from gp2
> after you exhausted your IOPS burst budget is like reading a floppy drive,
> you have to freeze a lot earlier than you hit several terabytes of unfrozen
> data, or you're dead like Mandrill's Search and Url tables from the link I
> shared.

OK, then start freezing tuples in the cheap mode (skip index updates)
earlier than that.  I suppose a good question is when to start.

Attached (autovacuum_berserk_v1.patch)
 code achieves that. For append-only tables since https://commitfest.postgresql.org/16/952/ vacuum skips index cleanup if no updates happened. You just need to trigger it, and it already will be "cheap".

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

pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: Re: Usage of epoch in txid_current
Next
From: "Tsunakawa, Takayuki"
Date:
Subject: RE: Timeout parameters