Horton

Re: Should we increase the default vacuum_cost_limit?

Laurenz Albe <laurenz.albe@cybertec.at>

View thread View on postgr.es
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: David Rowley <david.rowley@2ndquadrant.com>, Joe Conway <mail@joeconway.com>
Cc: Peter Geoghegan <pg@bowt.ie>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2019-02-25T15:43:40Z
Lists: pgsql-hackers 
David Rowley wrote:
> On Tue, 26 Feb 2019 at 02:06, Joe Conway <mail@joeconway.com> wrote:
> >
> > On 2/25/19 1:17 AM, Peter Geoghegan wrote:
> > > On Sun, Feb 24, 2019 at 9:42 PM David Rowley
> > > <david.rowley@2ndquadrant.com> wrote:
> > >> The current default vacuum_cost_limit of 200 seems to be 15 years old
> > >> and was added in f425b605f4e.
> > >>
> > >> Any supporters for raising the default?
> > >
> > > I also think that the current default limit is far too conservative.
> >
> > I agree entirely. In my experience you are usually much better off if
> > vacuum finishes quickly. Personally I think our default scale factors
> > are horrible too, especially when there are tables with large numbers of
> > rows.
> 
> Agreed that the scale factors are not perfect, but I don't think
> changing them is as quite a no-brainer as the vacuum_cost_limit, so
> the attached patch just does the vacuum_cost_limit.
> 
> I decided to do the times by 10 option that I had mentioned.... Ensue
> debate about that...
> 
> I'll add this to the March commitfest and set the target version as PG12.

I think this is a good move.

It is way easier to recover from an over-eager autovacuum than from
one that didn't manage to finish...

Yours,
Laurenz Albe

Commits

  1. Allow fractional input values for integer GUCs, and improve rounding logic.

  2. Reduce the default value of autovacuum_vacuum_cost_delay to 2ms.

  3. Revert "Increase the default vacuum_cost_limit from 200 to 2000"

  4. Convert [autovacuum_]vacuum_cost_delay into floating-point GUCs.

  5. Include GUC's unit, if it has one, in out-of-range error messages.

  6. Disallow NaN as a value for floating-point GUCs.

  7. Increase the default vacuum_cost_limit from 200 to 2000