Date: Mon, 16 Mar 2020 07:47:13 -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: <20200316124713.GH26184@telsasoft.com>
References: 
 <CA+fd4k4N5ixDtE-PZ-AwiwUxcNnP4B0A74qqbkcJ2CzMVdkCxg@mail.gmail.com>
 <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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+fd4k5HwScjcUbjSuZXWyU6JNENsOebktdi1ft39rJqLZ9N2A@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Mon, Mar 16, 2020 at 12:53:43PM +0900, Masahiko Sawada wrote:

> There is already a consensus on introducing new 2 parameters, but as
> the second idea I'd like to add one (or two) GUC(s) to my suggestion,
> say autovacuum_vacuum_freeze_insert_ratio; this parameter is the ratio
> of the number of inserted tuples for total number of tuples modified
> and inserted, in order to trigger insert-only vacuum. For example,
> suppose the table has 1,000,000 tuples and we set threshold = 0,
> scale_factor = 0.2 and freeze_insert_ratio = 0.9, we will trigger
> normal autovacuum when n_dead_tup + n_ins_since_vacuum > 200,000, but
> we will instead trigger insert-only autovacuum, which is a vacuum with
> vacuum_freeze_min_age = 0, when n_ins_since_vacuum > 180,000 (=200,000
> * 0.9). IOW if 90% of modified tuples are insertions, we freeze tuples
> aggressively. If we want to trigger insert-only vacuum only on
> insert-only table we can set freeze_insert_ratio = 1.0. The down side
> of this idea is that we cannot disable autovacuum triggered by the
> number of inserted, although we might be able to introduce more one
> GUC that controls whether to include the number of inserted tuples for
> triggering autovacuum (say, autovacuum_vacuum_triggered_by_insert =
> on|off). The pros of this idea would be that we can ensure that
> insert-only vacuum will run only in the case where the ratio of
> insertion is large enough.

I was thinking about something like this myself.  I would appreciate keeping
separate the thresholds for 1) triggering vacuum; and, 2) the options
autovacuum uses when it runs (in this case, FREEZE).  Someone might want
autovacuum to run with FREEZE on a table vacuumed due to dead tuples (say, on a
partitioned table), or might *not* want to run FREEZE on a table vacuumed due
to insertions (maybe because index scans are too expensive or FREEZE makes it
too slow).

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 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;

One would disable autovacuum triggered by insertions by setting
autovacuum_vacuum_insert_scale_factor=1e10 (which I think should also be the
max for this patch).

It seems to me that the easy thing to do is to implement this initially without
FREEZE (which is controlled by vacuum_freeze_table_age), and defer until
July/v14 further discussion and implementation of another GUC/relopt for
autovacuum freezing to be controlled by insert thresholds (or ratio).

-- 
Justin