diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0cc3296..98358e2 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1165,8 +1165,8 @@ SET ENABLE_SEQSCAN TO OFF;
commands, the system maintains an
internal counter that keeps track of the estimated cost of the
various I/O operations that are performed. When the accumulated
- cost reaches a limit (specified by
- vacuum_cost_limit), the process performing
+ cost reaches a limit (bounded by
+ vacuum_cost_rate_limit), the process performing
the operation will sleep for a short period of time, as specified by
vacuum_cost_delay. Then it will reset the
counter and continue execution.
@@ -1200,7 +1200,7 @@ SET ENABLE_SEQSCAN TO OFF;
The length of time, in milliseconds, that the process will sleep
- when the cost limit has been exceeded.
+ when the cost rate limit has been exceeded.
The default value is zero, which disables the cost-based vacuum
delay feature. Positive values enable cost-based vacuuming.
Note that on many systems, the effective resolution
@@ -1212,9 +1212,20 @@ SET ENABLE_SEQSCAN TO OFF;
When using cost-based vacuuming, appropriate values for
- vacuum_cost_delay are usually quite small, perhaps
- 10 or 20 milliseconds. Adjusting vacuum's resource consumption
- is best done by changing the other vacuum cost parameters.
+ vacuum_cost_delay are usually quite small, with
+ 20 milliseconds (the default setting for
+ autovacuum_vacuum_cost_delay) being appropriate
+ for most systems. Adjusting vacuum's resource consumption is best
+ done by changing the other vacuum cost parameters, normally starting
+ withvacuum_cost_rate_limit. Since that rate
+ limit takes into account this delay sleep time, changes to
+ vacuum_cost_delay beyond whether or not it is
+ zero are not expected to usefully change vacuum behavior.
+ The main reason to use a sleep time value higher than 20
+ milliseconds is to lower server power consumption on a lightly
+ loaded system. The maximum amount of work that can be done will not
+ change by doing that, but lower process wakeup frequency can allow
+ more efficient processor sleeping cycles.
@@ -1264,15 +1275,32 @@ SET ENABLE_SEQSCAN TO OFF;
-
- vacuum_cost_limit (integer)
+
+ vacuum_cost_rate_limit (integer)
- vacuum_cost_limit configuration parameter
+ vacuum_cost_rate_limit configuration parameter
- The accumulated cost that will cause the vacuuming process to sleep.
- The default value is 200.
+ The maximum possible speed cost rate limited vacuum can write dirty
+ blocks to disk, in kilobytes per second. Each time vacuum wakes,
+ it uses this rate limit to set an internal cost limit (referred
+ to as internal_cost_limit below), assuming a worst-case scenario
+ where every block it accesses is dirty. The default value is
+ 4000 kilobytes per second.
+
+
+ The all dirty block cost limit computation takes into account only
+ vacuum_cost_delay and vacuum_cost_page_dirty.
+ How much work a cost-based vacuum could do in other situations
+ depends on the ratio of vacuum_cost_page_dirty to
+ the other page cost parameters. For example, assume the default
+ parameters where vacuum_cost_page_dirty is 20 and
+ vacuum_cost_page_miss is 10. A vacuum that was
+ only encountering only page miss reads in that configuration could
+ do twice as many of them per second as the dirty writes, effectively
+ making for a read rate of 8000 kilobytes per second, while still
+ staying under the rate limit.
@@ -1287,7 +1315,7 @@ SET ENABLE_SEQSCAN TO OFF;
limit. To avoid uselessly long delays in such cases, the actual
delay is calculated as vacuum_cost_delay *
accumulated_balance /
- vacuum_cost_limit with a maximum of
+ internal_cost_limit with a maximum of
vacuum_cost_delay * 4.
@@ -4488,17 +4516,17 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
-
- autovacuum_vacuum_cost_limit (integer)
+
+ autovacuum_vacuum_cost_rate_limit (integer)
- autovacuum_vacuum_cost_limit configuration parameter
+ autovacuum_vacuum_cost_rate_limit configuration parameter
- Specifies the cost limit value that will be used in automatic
- VACUUM operations. If -1 is specified (which is the
- default), the regular
- value will be used. Note that
+ Specifies the maximum possible speed that vacuum can write dirty
+ blocks to disk during automatic VACUUM operations.
+ If -1 is specified (which is the default), the regular
+ value will be used. Note that
the value is distributed proportionally among the running autovacuum
workers, if there is more than one, so that the sum of the limits of
each worker never exceeds the limit on this variable.
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 03cc6c9..debb844 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -671,7 +671,7 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
to prevent transaction ID wraparound.
Another two parameters,
autovacuum_vacuum_cost_delay and
- autovacuum_vacuum_cost_limit, are used to set
+ autovacuum_vacuum_cost_rate_limit, are used to set
table-specific values for the cost-based vacuum delay feature
(see ).
autovacuum_freeze_min_age,
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index f55a001..a32a251 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -958,10 +958,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
- autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit (integer)
+ autovacuum_vacuum_cost_rate_limit, toast.autovacuum_vacuum_cost_rate_limit (integer)
- Custom parameter.
+ Custom parameter.
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 09a7b6f..f97e3ae 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -147,8 +147,8 @@ static relopt_int intRelOpts[] =
},
{
{
- "autovacuum_vacuum_cost_limit",
- "Vacuum cost amount available before napping, for autovacuum",
+ "autovacuum_vacuum_cost_rate_limit",
+ "Vacuum maximum write rate per second, for autovacuum",
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
},
-1, 1, 10000
@@ -1137,8 +1137,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, analyze_threshold)},
{"autovacuum_vacuum_cost_delay", RELOPT_TYPE_INT,
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, vacuum_cost_delay)},
- {"autovacuum_vacuum_cost_limit", RELOPT_TYPE_INT,
- offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, vacuum_cost_limit)},
+ {"autovacuum_vacuum_cost_rate_limit", RELOPT_TYPE_INT,
+ offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, vacuum_cost_rate_limit)},
{"autovacuum_freeze_min_age", RELOPT_TYPE_INT,
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, freeze_min_age)},
{"autovacuum_freeze_max_age", RELOPT_TYPE_INT,
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 353af50..697710b 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -54,7 +54,6 @@
int vacuum_freeze_min_age;
int vacuum_freeze_table_age;
-
/* A few variables that don't seem worth passing around as parameters */
static MemoryContext vac_context = NULL;
static BufferAccessStrategy vac_strategy;
@@ -211,12 +210,12 @@ vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
PG_TRY();
{
ListCell *cur;
-
VacuumCostActive = (VacuumCostDelay > 0);
VacuumCostBalance = 0;
VacuumPageHit = 0;
VacuumPageMiss = 0;
VacuumPageDirty = 0;
+ VacuumCostLimit = cost_limit(VacuumCostRateLimit,VacuumCostDelay);
/*
* Loop to process each selected relation.
@@ -297,6 +296,7 @@ vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
vac_context = NULL;
}
+
/*
* Build a list of Oids for each relation to be processed
*
@@ -1150,6 +1150,54 @@ vac_close_indexes(int nindexes, Relation *Irel, LOCKMODE lockmode)
}
/*
+ * Compute an internal vacuum cost limit from the passed inputs,
+ * system constants, and GUC values that can only be set there.
+ *
+ * Some inputs to this computation can be set in up to three ways:
+ * the regular vacuum settings, the autovacuum settings, and as
+ * a relation option. Anything that can have a different value in those
+ * contexts should be passed into here as a parameter. We can't be expected
+ * to figure that out here, and all of the callers expected will have
+ * checked all of that out anyway.
+ */
+int cost_limit(int rate_limit, int cost_delay)
+{
+ int limit;
+
+ /*
+ * Take in a rate in kibibytes (multiples of 1024 bytes) per second,
+ * output a cost limit that will produce that amount of write I/O
+ * if all pages are dirty. The 1000 here adjusts for cost
+ * delay times being in milliseconds.
+ */
+ Assert(BLCKSZ > 0);
+ limit = rate_limit * 1024 * VacuumCostPageDirty *
+ cost_delay / (1000 * BLCKSZ);
+
+ /*
+ * In situations where the cost delay is 0, cost accounting shouldn't
+ * act on the value returned here. But in that case, as well as when
+ * dirty pages are considered 0 cost, this computation will compute a
+ * limit of 0. What page cost accounting will do in that dysfunctional
+ * case isn't well explored, since the original VacuumCostLimit GUC value
+ * was clamped with a minimum of 1. Let's not find out if division by
+ * zero types of badness are possible if we push an expected <=0
+ * downstream by keeping the original floor here too.
+ */
+ if (limit < 0)
+ limit = 1;
+
+ /*
+ * XXX Log obstrusively for patch review. Similarly chatty
+ * messages from autovac_balance_cost are at DEBUG2
+ */
+ ereport(LOG,
+ (errmsg("cost limit=%d based on rate limit=%d KB/s delay=%d dirty cost=%d",
+ limit, rate_limit, cost_delay, VacuumCostPageDirty)));
+ return limit;
+}
+
+/*
* vacuum_delay_point --- check for interrupts and cost-based delay.
*
* This should be called in each major loop of VACUUM processing,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index f858a6d..bc741a7 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -114,10 +114,13 @@ double autovacuum_anl_scale;
int autovacuum_freeze_max_age;
int autovacuum_vac_cost_delay;
-int autovacuum_vac_cost_limit;
+int autovacuum_vac_cost_rate_limit;
int Log_autovacuum_min_duration = -1;
+/* Derived from GUC parameters */
+int autovacuum_vac_cost_limit;
+
/* how long to keep pgstat data in the launcher, in milliseconds */
#define STATS_READ_DELAY 1000
@@ -1727,17 +1730,24 @@ autovac_balance_cost(void)
* that a worker can consume is determined by cost_limit/cost_delay, so we
* try to equalize those ratios rather than the raw limit settings.
*
- * note: in cost_limit, zero also means use value from elsewhere, because
+ * note: in cost_rate_limit, zero also means use value from elsewhere, because
* zero is not a valid value.
*/
- int vac_cost_limit = (autovacuum_vac_cost_limit > 0 ?
- autovacuum_vac_cost_limit : VacuumCostLimit);
+ int vac_cost_rate_limit = (autovacuum_vac_cost_rate_limit > 0 ?
+ autovacuum_vac_cost_rate_limit : VacuumCostRateLimit);
int vac_cost_delay = (autovacuum_vac_cost_delay >= 0 ?
autovacuum_vac_cost_delay : VacuumCostDelay);
+ int vac_cost_limit;
double cost_total;
double cost_avail;
WorkerInfo worker;
+ /*
+ * Starting with the kilobytes/second setting for rate limit,
+ * recompute the limit used internally here in cost units
+ */
+ vac_cost_limit = cost_limit(vac_cost_rate_limit, vac_cost_delay);
+
/* not set? nothing to do */
if (vac_cost_limit <= 0 || vac_cost_delay <= 0)
return;
@@ -1763,8 +1773,8 @@ autovac_balance_cost(void)
return;
/*
- * Adjust cost limit of each active worker to balance the total of cost
- * limit to autovacuum_vacuum_cost_limit.
+ * Adjust cost limit of each active worker, balance the total to the
+ * cost unit limit derived from autovacuum_vacuum_cost_rate_limit
*/
cost_avail = (double) vac_cost_limit / vac_cost_delay;
worker = (WorkerInfo) SHMQueueNext(&AutoVacuumShmem->av_runningWorkers,
@@ -1788,9 +1798,9 @@ autovac_balance_cost(void)
worker->wi_cost_limit_base),
1);
- elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, cost_limit=%d, cost_limit_base=%d, cost_delay=%d)",
+ elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, cost_rate_limit=%d, cost_limit=%d, cost_limit_base=%d, cost_delay=%d)",
worker->wi_proc->pid, worker->wi_dboid, worker->wi_tableoid,
- worker->wi_cost_limit, worker->wi_cost_limit_base,
+ vac_cost_rate_limit, worker->wi_cost_limit, worker->wi_cost_limit_base,
worker->wi_cost_delay);
}
@@ -2251,7 +2261,7 @@ do_autovacuum(void)
LWLockRelease(AutovacuumScheduleLock);
/*
- * Remember the prevailing values of the vacuum cost GUCs. We have to
+ * Remember the prevailing values of the vacuum cost values. We have to
* restore these at the bottom of the loop, else we'll compute wrong
* values in the next iteration of autovac_balance_cost().
*/
@@ -2360,7 +2370,7 @@ deleted:
MyWorkerInfo->wi_tableoid = InvalidOid;
LWLockRelease(AutovacuumLock);
- /* restore vacuum cost GUCs for the next iteration */
+ /* restore vacuum cost parameters for the next iteration */
VacuumCostDelay = stdVacuumCostDelay;
VacuumCostLimit = stdVacuumCostLimit;
}
@@ -2497,7 +2507,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
{
int freeze_min_age;
int freeze_table_age;
+ int vac_cost_rate_limit;
int vac_cost_limit;
+
int vac_cost_delay;
/*
@@ -2514,12 +2526,14 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
? autovacuum_vac_cost_delay
: VacuumCostDelay;
- /* 0 or -1 in autovac setting means use plain vacuum_cost_limit */
- vac_cost_limit = (avopts && avopts->vacuum_cost_limit > 0)
- ? avopts->vacuum_cost_limit
- : (autovacuum_vac_cost_limit > 0)
- ? autovacuum_vac_cost_limit
- : VacuumCostLimit;
+ /* 0 or -1 in autovac setting means compute from vacuum_cost_rate_limit */
+ vac_cost_rate_limit = (avopts && avopts->vacuum_cost_rate_limit > 0)
+ ? avopts->vacuum_cost_rate_limit
+ : (autovacuum_vac_cost_rate_limit > 0)
+ ? autovacuum_vac_cost_rate_limit
+ : VacuumCostRateLimit;
+
+ vac_cost_limit = cost_limit(vac_cost_rate_limit, vac_cost_delay);
/* these do not have autovacuum-specific settings */
freeze_min_age = (avopts && avopts->freeze_min_age >= 0)
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 4b66bd3..d93f4bf 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -113,7 +113,7 @@ int MaxConnections = 90;
int VacuumCostPageHit = 1; /* GUC parameters for vacuum */
int VacuumCostPageMiss = 10;
int VacuumCostPageDirty = 20;
-int VacuumCostLimit = 200;
+int VacuumCostRateLimit = 4000;
int VacuumCostDelay = 0;
int VacuumPageHit = 0;
@@ -122,6 +122,7 @@ int VacuumPageDirty = 0;
int VacuumCostBalance = 0; /* working state for vacuum */
bool VacuumCostActive = false;
+int VacuumCostLimit = -1; /* XXX Invalid default, make sure it was updated */
int GinFuzzySearchLimit = 0;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 5c910dd..ce33f1e 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1751,12 +1751,12 @@ static struct config_int ConfigureNamesInt[] =
},
{
- {"vacuum_cost_limit", PGC_USERSET, RESOURCES_VACUUM_DELAY,
- gettext_noop("Vacuum cost amount available before napping."),
+ {"vacuum_cost_rate_limit", PGC_USERSET, RESOURCES_VACUUM_DELAY,
+ gettext_noop("Vacuum write rate limit to schedule napping frequency."),
NULL
},
- &VacuumCostLimit,
- 200, 1, 10000,
+ &VacuumCostRateLimit,
+ 4000, 1, 200000,
NULL, NULL, NULL
},
@@ -1783,12 +1783,12 @@ static struct config_int ConfigureNamesInt[] =
},
{
- {"autovacuum_vacuum_cost_limit", PGC_SIGHUP, AUTOVACUUM,
- gettext_noop("Vacuum cost amount available before napping, for autovacuum."),
+ {"autovacuum_vacuum_cost_rate_limit", PGC_SIGHUP, AUTOVACUUM,
+ gettext_noop("Vacuum write rate limit to schedule napping frequency, for autovacuum."),
NULL
},
- &autovacuum_vac_cost_limit,
- -1, -1, 10000,
+ &autovacuum_vac_cost_rate_limit,
+ -1, -1, 200000,
NULL, NULL, NULL
},
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 315db46..9ced7ee 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -136,7 +136,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_rate_limit = 4000 # maximum write rate in kilobytes/second
# - Background Writer -
@@ -458,9 +458,9 @@
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
-#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
+#autovacuum_vacuum_cost_rate_limit = -1 # default vacuum cost rate limit for
# autovacuum, -1 means use
- # vacuum_cost_limit
+ # vacuum_cost_rate_limit
#------------------------------------------------------------------------------
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index a27ef69..b71cd58 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1331,7 +1331,7 @@ psql_completion(char *text, int start, int end)
"autovacuum_freeze_min_age",
"autovacuum_freeze_table_age",
"autovacuum_vacuum_cost_delay",
- "autovacuum_vacuum_cost_limit",
+ "autovacuum_vacuum_cost_rate_limit",
"autovacuum_vacuum_scale_factor",
"autovacuum_vacuum_threshold",
"fillfactor",
@@ -1340,7 +1340,7 @@ psql_completion(char *text, int start, int end)
"toast.autovacuum_freeze_min_age",
"toast.autovacuum_freeze_table_age",
"toast.autovacuum_vacuum_cost_delay",
- "toast.autovacuum_vacuum_cost_limit",
+ "toast.autovacuum_vacuum_cost_rate_limit",
"toast.autovacuum_vacuum_scale_factor",
"toast.autovacuum_vacuum_threshold",
NULL
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 4526648..472d1dc 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -158,8 +158,10 @@ extern void vacuum_set_xid_limits(int freeze_min_age, int freeze_table_age,
TransactionId *freezeLimit,
TransactionId *freezeTableLimit);
extern void vac_update_datfrozenxid(void);
+extern int cost_limit(int rate_limit, int cost_delay);
extern void vacuum_delay_point(void);
+
/* in commands/vacuumlazy.c */
extern void lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
BufferAccessStrategy bstrategy);
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 610cb59..732a320 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -234,7 +234,7 @@ extern PGDLLIMPORT int maintenance_work_mem;
extern int VacuumCostPageHit;
extern int VacuumCostPageMiss;
extern int VacuumCostPageDirty;
-extern int VacuumCostLimit;
+extern int VacuumCostRateLimit;
extern int VacuumCostDelay;
extern int VacuumPageHit;
@@ -243,6 +243,7 @@ extern int VacuumPageDirty;
extern int VacuumCostBalance;
extern bool VacuumCostActive;
+extern int VacuumCostLimit;
/* in tcop/postgres.c */
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index 8009fde..c827a2b 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -25,7 +25,7 @@ extern int autovacuum_anl_thresh;
extern double autovacuum_anl_scale;
extern int autovacuum_freeze_max_age;
extern int autovacuum_vac_cost_delay;
-extern int autovacuum_vac_cost_limit;
+extern int autovacuum_vac_cost_rate_limit;
/* autovacuum launcher PID, only valid when worker is shutting down */
extern int AutovacuumLauncherPid;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index d404c2a..f283116 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -183,7 +183,7 @@ typedef struct AutoVacOpts
int vacuum_threshold;
int analyze_threshold;
int vacuum_cost_delay;
- int vacuum_cost_limit;
+ int vacuum_cost_rate_limit;
int freeze_min_age;
int freeze_max_age;
int freeze_table_age;