Horton

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

Justin Pryzby <pryzby@telsasoft.com>

View thread View on postgr.es
From: Justin Pryzby <pryzby@telsasoft.com>
To: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
Cc: David Rowley <dgrowleyml@gmail.com>, Laurenz Albe <laurenz.albe@cybertec.at>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Amit Kapila <amit.kapila16@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Darafei Komяpa Praliaskouski <me@komzpa.net>, Andres Freund <andres@anarazel.de>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>, Alvaro Herrera <alvherre@2ndquadrant.com>, Michael Banck <mbanck@gmx.net>
Date: 2020-03-17T23:32:18Z
Lists: pgsql-hackers 
On Mon, Mar 16, 2020 at 07:47:13AM -0500, Justin Pryzby wrote:
> Normally, when someone complains about bad plan related to no index-onlyscan,
> we tell them to run vacuum, and if that helps, then ALTER TABLE .. SET
> (autovacuum_vacuum_scale_factor=0.005).
> 
> If there's two thresholds (4 GUCs and 4 relopts) for autovacuum, then do we
> have to help determine which one was being hit, and which relopt to set?

I don't think we came to any resolution on this.

Right now, to encourage IOS, we'd tell someone to set
autovacuum_vacuum_scale_factor=0.005.  That wouldn't work for an insert-only
table, but I've never heard back from someone that it didn't work.

So with this patch, we'd maybe tell them to do this, to also get IOS on
insert-only tables ?
|ALTER TABLE .. SET (autovacuum_vacuum_scale_factor=0.005, autovacuum_vacuum_insert_threshold=50000);

> I wonder if the new insert GUCs should default to -1 (disabled)?  And the
> insert thresholds should be set by new insert relopt (if set), or by new insert
> GUC (default -1), else normal relopt, or normal GUC.  The defaults would give
> 50 + 0.20*n.  When someone asks about IOS, we'd tell them to set
> autovacuum_vacuum_scale_factor=0.005, same as now.
> 
> vac_ins_scale_factor =
> 	(relopts && relopts->vacuum_ins_scale_factor >= 0) ? relopts->vacuum_ins_scale_factor :
> 	autovacuum_vac_ins_scale >= 0 ? autovacuum_vac_ins_scale : 
> 	(relopts && relopts->vacuum_scale_factor >= 0) ? relopts->vacuum_scale_factor :
> 	autovacuum_vac_scale;

-- 
Justin

Commits

  1. Further improve stability fix for partition_aggregate test.

  2. Improve stability fix for partition_aggregate test.

  3. Attempt to stabilize partitionwise_aggregate test

  4. Fix race condition in statext_store().

  5. Attempt to fix unstable regression tests, take 2

  6. Attempt to fix unstable regression tests

  7. Trigger autovacuum based on number of INSERTs

  8. Fix upper limit for vacuum_cleanup_index_scale_factor