Re: Berserk Autovacuum (let's save next Mandrill)

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