Date: Wed, 25 Mar 2020 10:06:56 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Andres Freund <andres@anarazel.de>,
	Masahiko Sawada <masahiko.sawada@2ndquadrant.com>,
	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>,
	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: <20200325150656.GX21443@telsasoft.com>
References: <19be41199e7b303bbecc1066bd9f011d95882aa8.camel@cybertec.at>
 <20200316213459.kh6xln3z35hgjodd@alap3.anarazel.de>
 <7c132215f0e947af7d9ccb7a340ad6e81b369c24.camel@cybertec.at>
 <20200317152409.GW26184@telsasoft.com>
 <e0b452925b00e2712e299a3370f85ee6be32889c.camel@cybertec.at>
 <20200318010254.sra6fldiqz7ey6is@alap3.anarazel.de>
 <de6d9ccecb92dca7c1420139ae569f96fa930a2e.camel@cybertec.at>
 <20200320062031.uwagypenawujwajx@alap3.anarazel.de>
 <1a5a8273535742114b776ab60b0cc0a48e5bfaa3.camel@cybertec.at>
 <7f4c22e2ddd742b083c71683b00f29bbd13a9b9d.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <7f4c22e2ddd742b083c71683b00f29bbd13a9b9d.camel@cybertec.at>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Mon, Mar 23, 2020 at 02:27:29PM +0100, Laurenz Albe wrote:
> Here is version 10 of the patch, which uses a scale factor of 0.2.

Thanks

> Any table that has received more inserts since it was
> last vacuumed (and that is not vacuumed for another
> reason) will be autovacuumed.

Since this vacuum doesn't trigger any special behavior (freeze), you can remove
the parenthesized part: "(and that is not vacuumed for another reason)".

Maybe in the docs you can write this with thousands separators: 10,000,000

It looks like the GUC uses scale factor max=1e10, but the relopt is still
max=100, which means it's less possible to disable for a single rel.

> +++ b/src/backend/access/common/reloptions.c
> @@ -398,6 +407,15 @@ static relopt_real realRelOpts[] =
>  		},
>  		-1, 0.0, 100.0
>  	},
> +	{
> +		{
> +			"autovacuum_vacuum_insert_scale_factor",
> +			"Number of tuple inserts prior to vacuum as a fraction of reltuples",
> +			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
> +			ShareUpdateExclusiveLock
> +		},
> +		-1, 0.0, 100.0
> +	},
>  	{
>  		{
>  			"autovacuum_analyze_scale_factor",

> +++ b/src/backend/utils/misc/guc.c
> @@ -3549,6 +3558,17 @@ static struct config_real ConfigureNamesReal[] =
>  		0.2, 0.0, 100.0,
>  		NULL, NULL, NULL
>  	},
> +
> +	{
> +		{"autovacuum_vacuum_insert_scale_factor", PGC_SIGHUP, AUTOVACUUM,
> +			gettext_noop("Number of tuple inserts prior to vacuum as a fraction of reltuples."),
> +			NULL
> +		},
> +		&autovacuum_vac_ins_scale,
> +		0.2, 0.0, 1e10,
> +		NULL, NULL, NULL
> +	},
> +
>  	{
>  		{"autovacuum_analyze_scale_factor", PGC_SIGHUP, AUTOVACUUM,
>  			gettext_noop("Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples."),