Horton

Re: Should we increase the default vacuum_cost_limit?

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Peter Geoghegan <pg@bowt.ie>, David Rowley <david.rowley@2ndquadrant.com>, Joe Conway <mail@joeconway.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2019-03-05T15:29:08Z
Lists: pgsql-hackers 
On Tue, Mar 5, 2019 at 7:53 AM Tomas Vondra
<tomas.vondra@2ndquadrant.com> wrote:
> But on the other hand it feels a bit weird that we increase this one
> value and leave all the other (also very conservative) defaults alone.

Are you talking about vacuum-related defaults or defaults in general?
In 2014, we increased the defaults for work_mem and
maintenance_work_mem by 4x and the default for effective_cache_size by
32x; in 2012, we increased the default for shared_buffers from by 4x.
It's possible some of those parameters should be further increased at
some point, but deciding not to increase any of them until we can
increase all of them is tantamount to giving up on changing anything
at all.  I think it's OK to be conservative by default, but we should
increase parameters where we know that the default is likely to be too
conservative for 99% of users.  My only question about this change is
whether to go for a lesser multiple (e.g. 4x) rather than the proposed
10x.  But I think even if 10x turns out to be too much for a few more
people than we'd like, we're still going to be better off increasing
it and having some people have to turn it back down again than leaving
it the way it is and having users regularly suffer vacuum-starvation.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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