Date: Thu, 5 Mar 2020 11:27:49 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: David Rowley <dgrowleyml@gmail.com>,
	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>,
	Alvaro Herrera <alvherre@2ndquadrant.com>,
	Michael Banck <mbanck@gmx.net>, pgsql-hackers@lists.postgresql.org
Subject: Re: Berserk Autovacuum (let's save next Mandrill)
Message-ID: <20200305172749.GK684@telsasoft.com>
References: 
 <CAD21AoBPvxnq-8DpDQVzqRO+HPyTv8JF5Mg-X808RvCSzcdtYg@mail.gmail.com>
 <CAA4eK1+rCxS_Pg4GdSa6G8ESOTHK+jDVgqYd_dnO07rGNaewKA@mail.gmail.com>
 <CAD21AoAap+b+o+LMjHbOb2hn3CrjCGrVYHPv7TpG8G07dCk+VQ@mail.gmail.com>
 <CAA4eK1JnHr0ppUEmK7gFF-57z2Z5q2mCUWZf4tVWVHGeStM9tg@mail.gmail.com>
 <20200107180524.mqz456t2pmr7crpw@development>
 <98a1422dece118877ebbdc20cb78209f6d12866e.camel@cybertec.at>
 <0cd8685047ac86fdf9c5446a24ff3aed8755e290.camel@cybertec.at>
 <2207744f6cc70813d9000510b6bf03ce85db6675.camel@cybertec.at>
 <CAApHDvrUQazEd3F491ubZzU65B=e=O37bPcvXtPAH5vSn+9YUw@mail.gmail.com>
 <602873766faa0e9200a60dcc26dc10c636761d5d.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <602873766faa0e9200a60dcc26dc10c636761d5d.camel@cybertec.at>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

On Thu, Mar 05, 2020 at 03:27:31PM +0100, Laurenz Albe wrote:
> On Thu, 2020-03-05 at 19:40 +1300, David Rowley wrote:
> > 1. I'd go for 2 new GUCs and reloptions.
> > autovacuum_vacuum_insert_scale_factor and these should work exactly
> 
> I disagree about the scale_factor (and have not added it to the
> updated version of the patch).  If we have a scale_factor, then the
> time between successive autovacuum runs would increase as the table
> gets bigger, which defeats the purpose of reducing the impact of each
> autovacuum run.

I would vote to include scale factor.  You're right that a nonzero scale factor
would cause vacuum to run with geometrically decreasing frequency.  The same
thing currently happens with autoanalyze as a table grows in size.  I found
that our monthly-partitioned tables were being analyzed too infrequently
towards the end of the month.  (At the beginning of the month, 10% is 2.4 hours
worth of timeseries data, but at the end of the month 10% is 3 days, which was
an issue when querying the previous day may have rowcount estimates near zero.)
If someone wanted to avoid that, they'd set scale_factor=0.  I think this patch
should parallel what's already in place, and we can add documention for the
behavior if need be.  Possibly scale_factor should default to zero, which I
think might make sense since insert-only tables seem to be the main target of
this patch.

> +++ b/doc/src/sgml/maintenance.sgml
> +   <para>
> +    Tables that have received more than
> +    <xref linkend="guc-autovacuum-vacuum-insert-threshold"/>
> +    inserts since they were last vacuumed and are not eligible for vacuuming
> +    based on the above criteria will be vacuumed to reduce the impact of a future
> +    anti-wraparound vacuum run.
> +    Such a vacuum will aggressively freeze tuples, and it will not clean up dead
> +    index tuples.

"BUT will not clean .."

> +++ b/src/backend/postmaster/autovacuum.c
> +		/*
> +		 * If the number of inserted tuples exceeds the limit

I would say "exceeds the threshold"

Thanks for working on this; we would use this feature on our insert-only
tables.

-- 
Justin