From bb45a7fe4d854b2502a6885a18fdfdb77a51e155 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Tue, 24 Jul 2007 01:53:56 +0000 Subject: [PATCH] Set a default autovacuum vacuum_cost_delay value of 20ms, to avoid excessive I/O utilization, per discussion. While at it, lower the autovacuum vacuum and analyze threshold values to 50 tuples. It is a bit higher (i.e. more conservative) than what I originally proposed but much better than the old values for small tables. --- doc/src/sgml/config.sgml | 7 ++++--- src/backend/utils/misc/guc.c | 6 +++--- src/backend/utils/misc/postgresql.conf.sample | 6 +++--- 3 files changed, 10 insertions(+), 9 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 4c94963a03..f8fa0f0834 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3247,7 +3247,7 @@ SELECT * FROM parent WHERE key = 2400; Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. - The default is 500 tuples. + The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by entries in @@ -3265,7 +3265,7 @@ SELECT * FROM parent WHERE key = 2400; Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. - The default is 250 tuples. + The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by entries in @@ -3343,8 +3343,9 @@ SELECT * FROM parent WHERE key = 2400; Specifies the cost delay value that will be used in automatic VACUUM operations. If -1 is - specified (which is the default), the regular + specified, the regular value will be used. + The default value is 20 milliseconds. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by entries in diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 49d1f6b33d..fed0925bdc 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -1348,7 +1348,7 @@ static struct config_int ConfigureNamesInt[] = GUC_UNIT_MS }, &autovacuum_vac_cost_delay, - -1, -1, 1000, NULL, NULL + 20, -1, 1000, NULL, NULL }, { @@ -1656,7 +1656,7 @@ static struct config_int ConfigureNamesInt[] = NULL }, &autovacuum_vac_thresh, - 500, 0, INT_MAX, NULL, NULL + 50, 0, INT_MAX, NULL, NULL }, { {"autovacuum_analyze_threshold", PGC_SIGHUP, AUTOVACUUM, @@ -1664,7 +1664,7 @@ static struct config_int ConfigureNamesInt[] = NULL }, &autovacuum_anl_thresh, - 250, 0, INT_MAX, NULL, NULL + 50, 0, INT_MAX, NULL, NULL }, { /* see varsup.c for why this is PGC_POSTMASTER not PGC_SIGHUP */ diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 738eb9a5f7..51c83ade0a 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -381,9 +381,9 @@ #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 +#autovacuum_vacuum_threshold = 50 # min # of tuple updates before # vacuum -#autovacuum_analyze_threshold = 250 # min # of tuple updates before +#autovacuum_analyze_threshold = 50 # min # of tuple updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before # vacuum @@ -391,7 +391,7 @@ # 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_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 -- 2.39.5