From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Darafei "Komяpa" Praliaskouski <me@komzpa.net>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
Date: Wed, 27 Mar 2019 19:01:48 -0300
Message-ID: <20190327220148.GA22422@alvherre.pgsql> (raw)
In-Reply-To: <CAC8Q8tL7WK=pK4rp8U6-VU59Y=JqneATSEK0+wKA5ekHnUz02w@mail.gmail.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.
> > * 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, me@komzpa.net, pgsql-hackers@lists.postgresql.org
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
In-Reply-To: <20190327220148.GA22422@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox