Date: Tue, 17 Mar 2020 16:07:02 -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: <20200317210702.GA26184@telsasoft.com>
References: <20200316124713.GH26184@telsasoft.com>
 <9c9b7d19d17ad5e4e0ee10b8426fa1b78dd24c0a.camel@cybertec.at>
 <20200316201315.dgkeedl6rp2wdjje@alap3.anarazel.de>
 <19be41199e7b303bbecc1066bd9f011d95882aa8.camel@cybertec.at>
 <20200316213459.kh6xln3z35hgjodd@alap3.anarazel.de>
 <7c132215f0e947af7d9ccb7a340ad6e81b369c24.camel@cybertec.at>
 <20200317152409.GW26184@telsasoft.com>
 <e0b452925b00e2712e299a3370f85ee6be32889c.camel@cybertec.at>
 <20200317195616.GZ26184@telsasoft.com>
 <ade9403df7c3051ad68b58e98710c408511a4491.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <ade9403df7c3051ad68b58e98710c408511a4491.camel@cybertec.at>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Tue, Mar 17, 2020 at 10:01:15PM +0100, Laurenz Albe wrote:
> On Tue, 2020-03-17 at 14:56 -0500, Justin Pryzby wrote:
> > I still suggest scale_factor maximum of 1e10, like
> > 4d54543efa5eb074ead4d0fadb2af4161c943044
> > 
> > Which alows more effectively disabling it than a factor of 100, which would
> > progress like: ~1, 1e2, 1e4, 1e6, 1e8, 1e10, ..
> > 
> > I don't think that 1e4 would be a problem, but 1e6 and 1e8 could be.  With
> > 1e10, it's first vacuumed when there's 10billion inserts, if we didn't previous
> > hit the n_dead threshold.
> > 
> > I think that's ok?  If one wanted to disable it up to 1e11 tuples, I think
> > they'd disable autovacuum, or preferably just implement an vacuum job.
> 
> Assume a scale factor >= 1, for example 2, and n live tuples.
> The table has just been vacuumed.
> 
> Now we insert m number tuples (which are live).
> 
> Then the condition
> 
>   threshold + scale_factor * live_tuples < newly_inserted_tuples
> 
> becomes
> 
>   10000000 + 2 * (n + m) < m
> 
> which can never be true for non-negative n and m.
> 
> So a scale factor >= 1 disables the feature.

No, this is what we mailed about privately yesterday, and I demonstrated that
autovac can still run with factor=100.  I said:

|It's a multiplier, not a percent out of 100 (fraction is not a great choice of
|words).
|
|                &autovacuum_vac_scale,
|                0.2, 0.0, 100.0,
|
|The default is 0.2 (20%), so 100 means after updating/deleting 100*reltuples.

live tuples is an estimate, from the most recent vacuum OR analyze.

If 1.0 disabled the feature, it wouldn't make much sense to allow factor up to
100.

+       {
+               {"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.0, 0.0, 100.0,
+               NULL, NULL, NULL
+       },

-- 
Justin