Jim Nasby wrote:
> On Jul 22, 2007, at 8:54 AM, Josh Berkus wrote:
>> Tom,
>>> Note to all: we ***HAVE TO*** settle on some reasonable default
>>> vacuum_cost_delay settings before we can ship 8.3. With no cost delay
>>> and two or three workers active, 8.3's autovac does indeed send
>>> performance into the tank.
>>
>> I've been using 20ms for most of my setups. That's aimed at reducing
>> autovac to almost no impact at all, but taking a long time. Maybe 10ms?
>
> I've found 20ms to be a pretty good number for run-of-the-mill IO
> capability, and 10ms to be good for a good RAID setup (RAID10, 8+ drives,
> BBU).
>
> For a default setting, I think it'd be better to lean towards 20ms.
OK, 20ms it is then. Here is a patch. I am taking the liberty to also
lower the vacuum and analyze threshold default values to 50, per
previous discussion.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
? msg
? src/tools/entab/entab
? src/tools/entab/entab.fix.diff
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.405
diff -c -p -r1.405 guc.c
*** src/backend/utils/misc/guc.c 10 Jul 2007 13:14:21 -0000 1.405
--- src/backend/utils/misc/guc.c 23 Jul 2007 17:50:01 -0000
*************** static struct config_int ConfigureNamesI
*** 1348,1354 ****
GUC_UNIT_MS
},
&autovacuum_vac_cost_delay,
! -1, -1, 1000, NULL, NULL
},
{
--- 1348,1354 ----
GUC_UNIT_MS
},
&autovacuum_vac_cost_delay,
! 20, -1, 1000, NULL, NULL
},
{
*************** static struct config_int ConfigureNamesI
*** 1656,1662 ****
NULL
},
&autovacuum_vac_thresh,
! 500, 0, INT_MAX, NULL, NULL
},
{
{"autovacuum_analyze_threshold", PGC_SIGHUP, AUTOVACUUM,
--- 1656,1662 ----
NULL
},
&autovacuum_vac_thresh,
! 50, 0, INT_MAX, NULL, NULL
},
{
{"autovacuum_analyze_threshold", PGC_SIGHUP, AUTOVACUUM,
*************** static struct config_int ConfigureNamesI
*** 1664,1670 ****
NULL
},
&autovacuum_anl_thresh,
! 250, 0, INT_MAX, NULL, NULL
},
{
/* see varsup.c for why this is PGC_POSTMASTER not PGC_SIGHUP */
--- 1664,1670 ----
NULL
},
&autovacuum_anl_thresh,
! 50, 0, INT_MAX, NULL, NULL
},
{
/* see varsup.c for why this is PGC_POSTMASTER not PGC_SIGHUP */
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.219
diff -c -p -r1.219 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample 10 Jul 2007 13:14:21 -0000 1.219
--- src/backend/utils/misc/postgresql.conf.sample 23 Jul 2007 17:50:01 -0000
***************
*** 381,389 ****
#log_autovacuum = -1 # -1 is disabled, 0 logs all actions
# and their durations, > 0 logs only
# actions running at least N msec.
! #autovacuum_vacuum_threshold = 500 # min # of tuple updates before
# vacuum
! #autovacuum_analyze_threshold = 250 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
# vacuum
--- 381,389 ----
#log_autovacuum = -1 # -1 is disabled, 0 logs all actions
# and their durations, > 0 logs only
# actions running at least N msec.
! #autovacuum_vacuum_threshold = 50 # min # of tuple updates before
# vacuum
! #autovacuum_analyze_threshold = 50 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
# vacuum
***************
*** 391,397 ****
# analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
! #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
--- 391,397 ----
# analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
! #autovacuum_vacuum_cost_delay = 20 # default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
