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

On 2019-Mar-28, Darafei "Komяpa" Praliaskouski wrote:

> чт, 28 мар. 2019 г. в 00:32, Alvaro Herrera <alvherre(at)2ndquadrant(dot)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.

> > * 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.

I wonder if Mandrill's problem is related to Mailchimp raising the
freeze_max_age to a point where autovac did not have enough time to
react with an emergency vacuum. If you keep raising that value because
the vacuums cause problems for you (they block DDL), there's something
wrong.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services