diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 7bbe8f5..9618aca 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1845,7 +1845,7 @@ include_dir 'conf.d' - vacuum_cost_delay (integer) + vacuum_cost_delay (floating point) vacuum_cost_delay configuration parameter @@ -1856,18 +1856,19 @@ include_dir 'conf.d' when the cost 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 - of sleep delays is 10 milliseconds; setting - vacuum_cost_delay to a value that is - not a multiple of 10 might have the same results as setting it - to the next higher multiple of 10. 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. + less than 1 millisecond. While vacuum_cost_delay + can be set to fractional-millisecond values, such delays may not be + measured accurately on older platforms. On such platforms, + increasing VACUUM's throttled resource consumption + above what you get at 1ms will require changing the other vacuum cost + parameters. You should, nonetheless, + keep vacuum_cost_delay as small as your platform + will consistently measure; large delays are not helpful. @@ -7020,7 +7021,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; - autovacuum_vacuum_cost_delay (integer) + autovacuum_vacuum_cost_delay (floating point) autovacuum_vacuum_cost_delay configuration parameter @@ -7030,7 +7031,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; Specifies the cost delay value that will be used in automatic VACUUM operations. If -1 is specified, the regular value will be used. - The default value is 20 milliseconds. + The default value is 2 milliseconds. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 22dbc07..e94fe2c 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1385,7 +1385,7 @@ WITH ( MODULUS numeric_literal, REM - autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay (integer) + autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay (floating point) Per-table value for diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index 8ca36ab..3b0b138 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -1359,8 +1359,6 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind) offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_threshold)}, {"autovacuum_analyze_threshold", RELOPT_TYPE_INT, 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_freeze_min_age", RELOPT_TYPE_INT, @@ -1379,6 +1377,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind) offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, log_min_duration)}, {"toast_tuple_target", RELOPT_TYPE_INT, offsetof(StdRdOptions, toast_tuple_target)}, + {"autovacuum_vacuum_cost_delay", RELOPT_TYPE_REAL, + offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_cost_delay)}, {"autovacuum_vacuum_scale_factor", RELOPT_TYPE_REAL, offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_scale_factor)}, {"autovacuum_analyze_scale_factor", RELOPT_TYPE_REAL, diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index e91df21..da13a5a 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -1834,13 +1834,13 @@ vacuum_delay_point(void) if (VacuumCostActive && !InterruptPending && VacuumCostBalance >= VacuumCostLimit) { - int msec; + double msec; msec = VacuumCostDelay * VacuumCostBalance / VacuumCostLimit; if (msec > VacuumCostDelay * 4) msec = VacuumCostDelay * 4; - pg_usleep(msec * 1000L); + pg_usleep((long) (msec * 1000)); VacuumCostBalance = 0; diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 347f91e..e9fe0a6 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -120,7 +120,7 @@ double autovacuum_anl_scale; int autovacuum_freeze_max_age; int autovacuum_multixact_freeze_max_age; -int autovacuum_vac_cost_delay; +double autovacuum_vac_cost_delay; int autovacuum_vac_cost_limit; int Log_autovacuum_min_duration = -1; @@ -189,7 +189,7 @@ typedef struct autovac_table Oid at_relid; int at_vacoptions; /* bitmask of VacuumOption */ VacuumParams at_params; - int at_vacuum_cost_delay; + double at_vacuum_cost_delay; int at_vacuum_cost_limit; bool at_dobalance; bool at_sharedrel; @@ -225,7 +225,7 @@ typedef struct WorkerInfoData TimestampTz wi_launchtime; bool wi_dobalance; bool wi_sharedrel; - int wi_cost_delay; + double wi_cost_delay; int wi_cost_limit; int wi_cost_limit_base; } WorkerInfoData; @@ -1785,7 +1785,7 @@ autovac_balance_cost(void) */ int vac_cost_limit = (autovacuum_vac_cost_limit > 0 ? autovacuum_vac_cost_limit : VacuumCostLimit); - int vac_cost_delay = (autovacuum_vac_cost_delay >= 0 ? + double vac_cost_delay = (autovacuum_vac_cost_delay >= 0 ? autovacuum_vac_cost_delay : VacuumCostDelay); double cost_total; double cost_avail; @@ -1840,7 +1840,7 @@ autovac_balance_cost(void) } if (worker->wi_proc != NULL) - elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, dobalance=%s cost_limit=%d, cost_limit_base=%d, cost_delay=%d)", + elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, dobalance=%s cost_limit=%d, cost_limit_base=%d, cost_delay=%g)", worker->wi_proc->pid, worker->wi_dboid, worker->wi_tableoid, worker->wi_dobalance ? "yes" : "no", worker->wi_cost_limit, worker->wi_cost_limit_base, @@ -2302,7 +2302,7 @@ do_autovacuum(void) autovac_table *tab; bool isshared; bool skipit; - int stdVacuumCostDelay; + double stdVacuumCostDelay; int stdVacuumCostLimit; dlist_iter iter; @@ -2831,7 +2831,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, int multixact_freeze_min_age; int multixact_freeze_table_age; int vac_cost_limit; - int vac_cost_delay; + double vac_cost_delay; int log_min_duration; /* @@ -2993,7 +2993,7 @@ relation_needs_vacanalyze(Oid relid, * table), or the autovacuum GUC variables. */ - /* -1 in autovac setting means use plain vacuum_cost_delay */ + /* -1 in autovac setting means use plain vacuum_scale_factor */ vac_scale_factor = (relopts && relopts->vacuum_scale_factor >= 0) ? relopts->vacuum_scale_factor : autovacuum_vac_scale; diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c index a6ce184..6d1e94f 100644 --- a/src/backend/utils/init/globals.c +++ b/src/backend/utils/init/globals.c @@ -138,7 +138,7 @@ int VacuumCostPageHit = 1; /* GUC parameters for vacuum */ int VacuumCostPageMiss = 10; int VacuumCostPageDirty = 20; int VacuumCostLimit = 2000; -int VacuumCostDelay = 0; +double VacuumCostDelay = 0; int VacuumPageHit = 0; int VacuumPageMiss = 0; diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 5226733..d219b7d 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -2270,28 +2270,6 @@ static struct config_int ConfigureNamesInt[] = }, { - {"vacuum_cost_delay", PGC_USERSET, RESOURCES_VACUUM_DELAY, - gettext_noop("Vacuum cost delay in milliseconds."), - NULL, - GUC_UNIT_MS - }, - &VacuumCostDelay, - 0, 0, 100, - NULL, NULL, NULL - }, - - { - {"autovacuum_vacuum_cost_delay", PGC_SIGHUP, AUTOVACUUM, - gettext_noop("Vacuum cost delay in milliseconds, for autovacuum."), - NULL, - GUC_UNIT_MS - }, - &autovacuum_vac_cost_delay, - 20, -1, 100, - NULL, NULL, NULL - }, - - { {"autovacuum_vacuum_cost_limit", PGC_SIGHUP, AUTOVACUUM, gettext_noop("Vacuum cost amount available before napping, for autovacuum."), NULL @@ -3317,6 +3295,28 @@ static struct config_real ConfigureNamesReal[] = }, { + {"vacuum_cost_delay", PGC_USERSET, RESOURCES_VACUUM_DELAY, + gettext_noop("Vacuum cost delay in milliseconds."), + NULL, + GUC_UNIT_MS + }, + &VacuumCostDelay, + 0, 0, 100, + NULL, NULL, NULL + }, + + { + {"autovacuum_vacuum_cost_delay", PGC_SIGHUP, AUTOVACUUM, + gettext_noop("Vacuum cost delay in milliseconds, for autovacuum."), + NULL, + GUC_UNIT_MS + }, + &autovacuum_vac_cost_delay, + 2, -1, 100, + NULL, NULL, NULL + }, + + { {"autovacuum_vacuum_scale_factor", PGC_SIGHUP, AUTOVACUUM, gettext_noop("Number of tuple updates or deletes prior to vacuum as a fraction of reltuples."), NULL diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 99f1666..acd7daf 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -155,7 +155,7 @@ # - Cost-Based Vacuum Delay - -#vacuum_cost_delay = 0 # 0-100 milliseconds +#vacuum_cost_delay = 0 # 0-100 milliseconds (0 disables) #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits @@ -589,7 +589,7 @@ #autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age # before forced vacuum # (change requires restart) -#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for +#autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h index c9e3500..b677c7e 100644 --- a/src/include/miscadmin.h +++ b/src/include/miscadmin.h @@ -250,7 +250,7 @@ extern int VacuumCostPageHit; extern int VacuumCostPageMiss; extern int VacuumCostPageDirty; extern int VacuumCostLimit; -extern int VacuumCostDelay; +extern double VacuumCostDelay; extern int VacuumPageHit; extern int VacuumPageMiss; diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h index 79e99f0..722ef1c 100644 --- a/src/include/postmaster/autovacuum.h +++ b/src/include/postmaster/autovacuum.h @@ -37,7 +37,7 @@ extern int autovacuum_anl_thresh; extern double autovacuum_anl_scale; extern int autovacuum_freeze_max_age; extern int autovacuum_multixact_freeze_max_age; -extern int autovacuum_vac_cost_delay; +extern double autovacuum_vac_cost_delay; extern int autovacuum_vac_cost_limit; /* autovacuum launcher PID, only valid when worker is shutting down */ diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 9d805ca..5402851 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -243,7 +243,6 @@ typedef struct AutoVacOpts bool enabled; int vacuum_threshold; int analyze_threshold; - int vacuum_cost_delay; int vacuum_cost_limit; int freeze_min_age; int freeze_max_age; @@ -252,6 +251,7 @@ typedef struct AutoVacOpts int multixact_freeze_max_age; int multixact_freeze_table_age; int log_min_duration; + float8 vacuum_cost_delay; float8 vacuum_scale_factor; float8 analyze_scale_factor; } AutoVacOpts; diff --git a/src/test/regress/expected/guc.out b/src/test/regress/expected/guc.out index b0d7351..aa5fe58 100644 --- a/src/test/regress/expected/guc.out +++ b/src/test/regress/expected/guc.out @@ -149,11 +149,11 @@ SELECT '2006-08-13 12:34:56'::timestamptz; (1 row) SAVEPOINT first_sp; -SET vacuum_cost_delay TO 80; +SET vacuum_cost_delay TO 80.1; SHOW vacuum_cost_delay; vacuum_cost_delay ------------------- - 80ms + 80100us (1 row) SET datestyle = 'German, DMY'; @@ -183,7 +183,7 @@ SELECT '2006-08-13 12:34:56'::timestamptz; (1 row) SAVEPOINT second_sp; -SET vacuum_cost_delay TO 90; +SET vacuum_cost_delay TO '900us'; SET datestyle = 'SQL, YMD'; SHOW datestyle; DateStyle @@ -222,7 +222,7 @@ ROLLBACK TO third_sp; SHOW vacuum_cost_delay; vacuum_cost_delay ------------------- - 90ms + 900us (1 row) SHOW datestyle; diff --git a/src/test/regress/sql/guc.sql b/src/test/regress/sql/guc.sql index 3b854ac..4fd8dc3 100644 --- a/src/test/regress/sql/guc.sql +++ b/src/test/regress/sql/guc.sql @@ -47,7 +47,7 @@ SET datestyle = 'MDY'; SHOW datestyle; SELECT '2006-08-13 12:34:56'::timestamptz; SAVEPOINT first_sp; -SET vacuum_cost_delay TO 80; +SET vacuum_cost_delay TO 80.1; SHOW vacuum_cost_delay; SET datestyle = 'German, DMY'; SHOW datestyle; @@ -56,7 +56,7 @@ ROLLBACK TO first_sp; SHOW datestyle; SELECT '2006-08-13 12:34:56'::timestamptz; SAVEPOINT second_sp; -SET vacuum_cost_delay TO 90; +SET vacuum_cost_delay TO '900us'; SET datestyle = 'SQL, YMD'; SHOW datestyle; SELECT '2006-08-13 12:34:56'::timestamptz;