On Tue, 26 Feb 2019 at 02:06, Joe Conway <m...@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.row...@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.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From 9bb648c44bc6afa3e7e55cd2482f979209622ab4 Mon Sep 17 00:00:00 2001
From: "dgrow...@gmail.com" <dgrow...@gmail.com>
Date: Tue, 26 Feb 2019 02:10:50 +1300
Subject: [PATCH v1] Increase the default vacuum_cost_limit from 200 to 2000
The original 200 default value was set back in f425b605f4e when the cost
delay settings were first added. Hardware has improved quite a bit since
then and we've also made improvements such as sorting buffers during
checkpoints (9cd00c457e6) which should result in less random writes.
This low default value was reportedly causing problems with badly
configured servers and in the absence of a native method to remove
excessive bloat from tables without incurring an AccessExclusiveLock, this
often made cleaning up the damage caused by badly configured auto-vacuums
difficult.
It seems more likely that someone will notice that auto-vacuum is running
too quickly than too slowly, so let's go all out and multiple the default
value for the setting by 10. With the default vacuum_cost_page_dirty and
autovacuum_vacuum_cost_delay (assuming a page size of 8192 bytes), this
allows autovacuum a theoretical maximum dirty write rate of around 39MB/s
instead of just 3.9MB/s.
---
doc/src/sgml/config.sgml | 2 +-
src/backend/utils/init/globals.c | 2 +-
src/backend/utils/misc/guc.c | 2 +-
src/backend/utils/misc/postgresql.conf.sample | 2 +-
4 files changed, 4 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8bd57f376b..d03b2bd0ce 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1929,7 +1929,7 @@ include_dir 'conf.d'
<listitem>
<para>
The accumulated cost that will cause the vacuuming process to sleep.
- The default value is 200.
+ The default value is 2000.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index fd51934aaf..a6ce184537 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -137,7 +137,7 @@ int MaxBackends = 0;
int VacuumCostPageHit = 1; /* GUC parameters for vacuum */
int VacuumCostPageMiss = 10;
int VacuumCostPageDirty = 20;
-int VacuumCostLimit = 200;
+int VacuumCostLimit = 2000;
int VacuumCostDelay = 0;
int VacuumPageHit = 0;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 156d147c85..826c189a96 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2268,7 +2268,7 @@ static struct config_int ConfigureNamesInt[] =
NULL
},
&VacuumCostLimit,
- 200, 1, 10000,
+ 2000, 1, 10000,
NULL, NULL, NULL
},
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 194f312096..5e34b53773 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -159,7 +159,7 @@
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
-#vacuum_cost_limit = 200 # 1-10000 credits
+#vacuum_cost_limit = 2000 # 1-10000 credits
# - Background Writer -
--
2.17.1
