Date: Tue, 17 Mar 2020 18:32:18 -0500
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 =?utf-8?Q?Kom=D1=8Fpa?= 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>
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
Message-ID: <20200317233218.GD26184@telsasoft.com>
References: <f04ff0a3a556c5578a95e335cc07f4fea1f7e247.camel@cybertec.at>
 <CA+fd4k6DJVU-TzOedMsDmynz7O4fXd9ZNEd19HzCstk=X1PWYg@mail.gmail.com>
 <CAApHDvoUhTFWCGmhfwct6qcXUe1BjaN9KvMEricbEShetZhAQQ@mail.gmail.com>
 <9c312999bb8e0495b88d1422980ecc0abc1b1b86.camel@cybertec.at>
 <CA+fd4k7J95OYDTJn5FT5szPgciwyDGbwP7jkc6FPp63i4a8j6Q@mail.gmail.com>
 <CAApHDvq=Gaycio_60j9bHs3CTgkeLkUCH5kbyHbVuYryQbDfpA@mail.gmail.com>
 <CA+fd4k6MhGk95y53k-tLRB9zz5fecwiJhTrma9p0b3=rDaEr7g@mail.gmail.com>
 <CAApHDvrACF7jB_hbq75XiaRtFRUq9BcXm=f15icu3+Q9xD+YQA@mail.gmail.com>
 <CA+fd4k5HwScjcUbjSuZXWyU6JNENsOebktdi1ft39rJqLZ9N2A@mail.gmail.com>
 <20200316124713.GH26184@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20200316124713.GH26184@telsasoft.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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