diff options
Diffstat (limited to 'src')
-rw-r--r-- | src/backend/access/common/reloptions.c | 22 | ||||
-rw-r--r-- | src/backend/catalog/system_views.sql | 1 | ||||
-rw-r--r-- | src/backend/postmaster/autovacuum.c | 31 | ||||
-rw-r--r-- | src/backend/postmaster/pgstat.c | 16 | ||||
-rw-r--r-- | src/backend/utils/adt/pgstatfuncs.c | 16 | ||||
-rw-r--r-- | src/backend/utils/misc/guc.c | 20 | ||||
-rw-r--r-- | src/backend/utils/misc/postgresql.conf.sample | 5 | ||||
-rw-r--r-- | src/bin/psql/tab-complete.c | 4 | ||||
-rw-r--r-- | src/include/catalog/catversion.h | 2 | ||||
-rw-r--r-- | src/include/catalog/pg_proc.dat | 5 | ||||
-rw-r--r-- | src/include/pgstat.h | 1 | ||||
-rw-r--r-- | src/include/postmaster/autovacuum.h | 2 | ||||
-rw-r--r-- | src/include/utils/rel.h | 2 | ||||
-rw-r--r-- | src/test/regress/expected/rules.out | 3 |
14 files changed, 125 insertions, 5 deletions
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index ec207d3b26c..e136116d7ba 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -235,6 +235,15 @@ static relopt_int intRelOpts[] = }, { { + "autovacuum_vacuum_insert_threshold", + "Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + ShareUpdateExclusiveLock + }, + -2, -1, INT_MAX + }, + { + { "autovacuum_analyze_threshold", "Minimum number of tuple inserts, updates or deletes prior to analyze", RELOPT_KIND_HEAP, @@ -400,6 +409,15 @@ static relopt_real realRelOpts[] = }, { { + "autovacuum_vacuum_insert_scale_factor", + "Number of tuple inserts prior to vacuum as a fraction of reltuples", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + ShareUpdateExclusiveLock + }, + -1, 0.0, 100.0 + }, + { + { "autovacuum_analyze_scale_factor", "Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples", RELOPT_KIND_HEAP, @@ -1514,6 +1532,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind) offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, enabled)}, {"autovacuum_vacuum_threshold", RELOPT_TYPE_INT, offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_threshold)}, + {"autovacuum_vacuum_insert_threshold", RELOPT_TYPE_INT, + offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_threshold)}, {"autovacuum_analyze_threshold", RELOPT_TYPE_INT, offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, analyze_threshold)}, {"autovacuum_vacuum_cost_limit", RELOPT_TYPE_INT, @@ -1538,6 +1558,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind) offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_cost_delay)}, {"autovacuum_vacuum_scale_factor", RELOPT_TYPE_REAL, offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_scale_factor)}, + {"autovacuum_vacuum_insert_scale_factor", RELOPT_TYPE_REAL, + offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_scale_factor)}, {"autovacuum_analyze_scale_factor", RELOPT_TYPE_REAL, offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, analyze_scale_factor)}, {"user_catalog_table", RELOPT_TYPE_BOOL, diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 5a6dc616301..83d00c6cdef 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -573,6 +573,7 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_live_tuples(C.oid) AS n_live_tup, pg_stat_get_dead_tuples(C.oid) AS n_dead_tup, pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze, + pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum, pg_stat_get_last_vacuum_time(C.oid) as last_vacuum, pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum, pg_stat_get_last_analyze_time(C.oid) as last_analyze, diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index da75e755f0f..7e97ffab27d 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -117,6 +117,8 @@ int autovacuum_work_mem = -1; int autovacuum_naptime; int autovacuum_vac_thresh; double autovacuum_vac_scale; +int autovacuum_vac_ins_thresh; +double autovacuum_vac_ins_scale; int autovacuum_anl_thresh; double autovacuum_anl_scale; int autovacuum_freeze_max_age; @@ -2969,16 +2971,20 @@ relation_needs_vacanalyze(Oid relid, /* constants from reloptions or GUC variables */ int vac_base_thresh, + vac_ins_base_thresh, anl_base_thresh; float4 vac_scale_factor, + vac_ins_scale_factor, anl_scale_factor; /* thresholds calculated from above constants */ float4 vacthresh, + vacinsthresh, anlthresh; /* number of vacuum (resp. analyze) tuples at this time */ float4 vactuples, + instuples, anltuples; /* freeze parameters */ @@ -3005,6 +3011,15 @@ relation_needs_vacanalyze(Oid relid, ? relopts->vacuum_threshold : autovacuum_vac_thresh; + vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0) + ? relopts->vacuum_ins_scale_factor + : autovacuum_vac_ins_scale; + + /* -1 is used to disable insert vacuums */ + vac_ins_base_thresh = (relopts && relopts->vacuum_ins_threshold >= -1) + ? relopts->vacuum_ins_threshold + : autovacuum_vac_ins_thresh; + anl_scale_factor = (relopts && relopts->analyze_scale_factor >= 0) ? relopts->analyze_scale_factor : autovacuum_anl_scale; @@ -3059,9 +3074,11 @@ relation_needs_vacanalyze(Oid relid, { reltuples = classForm->reltuples; vactuples = tabentry->n_dead_tuples; + instuples = tabentry->inserts_since_vacuum; anltuples = tabentry->changes_since_analyze; vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples; + vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples; anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples; /* @@ -3069,12 +3086,18 @@ relation_needs_vacanalyze(Oid relid, * reset, because if that happens, the last vacuum and analyze counts * will be reset too. */ - elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)", - NameStr(classForm->relname), - vactuples, vacthresh, anltuples, anlthresh); + if (vac_ins_base_thresh >= 0) + elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)", + NameStr(classForm->relname), + vactuples, vacthresh, instuples, vacinsthresh, anltuples, anlthresh); + else + elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: (disabled), anl: %.0f (threshold %.0f)", + NameStr(classForm->relname), + vactuples, vacthresh, anltuples, anlthresh); /* Determine if this table needs vacuum or analyze. */ - *dovacuum = force_vacuum || (vactuples > vacthresh); + *dovacuum = force_vacuum || (vactuples > vacthresh) || + (vac_ins_base_thresh >= 0 && instuples > vacinsthresh); *doanalyze = (anltuples > anlthresh); } else diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 4763c24be94..ab42df7e1b4 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -4701,6 +4701,7 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create) result->n_live_tuples = 0; result->n_dead_tuples = 0; result->changes_since_analyze = 0; + result->inserts_since_vacuum = 0; result->blocks_fetched = 0; result->blocks_hit = 0; result->vacuum_timestamp = 0; @@ -5831,6 +5832,7 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len) tabentry->n_live_tuples = tabmsg->t_counts.t_delta_live_tuples; tabentry->n_dead_tuples = tabmsg->t_counts.t_delta_dead_tuples; tabentry->changes_since_analyze = tabmsg->t_counts.t_changed_tuples; + tabentry->inserts_since_vacuum = tabmsg->t_counts.t_tuples_inserted; tabentry->blocks_fetched = tabmsg->t_counts.t_blocks_fetched; tabentry->blocks_hit = tabmsg->t_counts.t_blocks_hit; @@ -5860,10 +5862,12 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len) { tabentry->n_live_tuples = 0; tabentry->n_dead_tuples = 0; + tabentry->inserts_since_vacuum = 0; } tabentry->n_live_tuples += tabmsg->t_counts.t_delta_live_tuples; tabentry->n_dead_tuples += tabmsg->t_counts.t_delta_dead_tuples; tabentry->changes_since_analyze += tabmsg->t_counts.t_changed_tuples; + tabentry->inserts_since_vacuum += tabmsg->t_counts.t_tuples_inserted; tabentry->blocks_fetched += tabmsg->t_counts.t_blocks_fetched; tabentry->blocks_hit += tabmsg->t_counts.t_blocks_hit; } @@ -6098,6 +6102,18 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len) tabentry->n_live_tuples = msg->m_live_tuples; tabentry->n_dead_tuples = msg->m_dead_tuples; + /* + * It is quite possible that a non-aggressive VACUUM ended up skipping + * various pages, however, we'll zero the insert counter here regardless. + * It's currently used only to track when we need to perform an + * "insert" autovacuum, which are mainly intended to freeze newly inserted + * tuples. Zeroing this may just mean we'll not try to vacuum the table + * again until enough tuples have been inserted to trigger another insert + * autovacuum. An anti-wraparound autovacuum will catch any persistent + * stragglers. + */ + tabentry->inserts_since_vacuum = 0; + if (msg->m_autovacuum) { tabentry->autovac_vacuum_timestamp = msg->m_vacuumtime; diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index cea01534a5e..6d66ff8b448 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -197,6 +197,22 @@ pg_stat_get_mod_since_analyze(PG_FUNCTION_ARGS) Datum +pg_stat_get_ins_since_vacuum(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int64 result; + PgStat_StatTabEntry *tabentry; + + if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL) + result = 0; + else + result = (int64) (tabentry->inserts_since_vacuum); + + PG_RETURN_INT64(result); +} + + +Datum pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS) { Oid relid = PG_GETARG_OID(0); diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 53665971f53..79bc7ac8cac 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -3103,6 +3103,15 @@ static struct config_int ConfigureNamesInt[] = NULL, NULL, NULL }, { + {"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM, + gettext_noop("Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums"), + NULL + }, + &autovacuum_vac_ins_thresh, + 1000, -1, INT_MAX, + NULL, NULL, NULL + }, + { {"autovacuum_analyze_threshold", PGC_SIGHUP, AUTOVACUUM, gettext_noop("Minimum number of tuple inserts, updates, or deletes prior to analyze."), NULL @@ -3549,6 +3558,17 @@ static struct config_real ConfigureNamesReal[] = 0.2, 0.0, 100.0, NULL, NULL, NULL }, + + { + {"autovacuum_vacuum_insert_scale_factor", PGC_SIGHUP, AUTOVACUUM, + gettext_noop("Number of tuple inserts prior to vacuum as a fraction of reltuples."), + NULL + }, + &autovacuum_vac_ins_scale, + 0.2, 0.0, 100.0, + NULL, NULL, NULL + }, + { {"autovacuum_analyze_scale_factor", PGC_SIGHUP, AUTOVACUUM, gettext_noop("Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f01e43b8189..e9f8ca775da 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -598,9 +598,14 @@ #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum +#autovacuum_vacuum_insert_threshold = 1000 # min number of row inserts + # before vacuum. -1 disables insert + # vacuums #autovacuum_analyze_threshold = 50 # min number of row updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum +#autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of inserts over table + # size before insert vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index ae35fa4aa98..ca8f0d75a62 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1078,6 +1078,8 @@ static const char *const table_storage_parameters[] = { "autovacuum_multixact_freeze_table_age", "autovacuum_vacuum_cost_delay", "autovacuum_vacuum_cost_limit", + "autovacuum_vacuum_insert_scale_factor", + "autovacuum_vacuum_insert_threshold", "autovacuum_vacuum_scale_factor", "autovacuum_vacuum_threshold", "fillfactor", @@ -1092,6 +1094,8 @@ static const char *const table_storage_parameters[] = { "toast.autovacuum_multixact_freeze_table_age", "toast.autovacuum_vacuum_cost_delay", "toast.autovacuum_vacuum_cost_limit", + "toast.autovacuum_vacuum_insert_scale_factor", + "toast.autovacuum_vacuum_insert_threshold", "toast.autovacuum_vacuum_scale_factor", "toast.autovacuum_vacuum_threshold", "toast.log_autovacuum_min_duration", diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 8aa18483e4e..58ff619e8a5 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202003241 +#define CATALOG_VERSION_NO 202003281 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 87d25d4a4bd..ac8ad8dbf08 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5129,6 +5129,11 @@ proname => 'pg_stat_get_mod_since_analyze', provolatile => 's', proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', prosrc => 'pg_stat_get_mod_since_analyze' }, +{ oid => '8872', + descr => 'statistics: number of tuples inserted since last vacuum', + proname => 'pg_stat_get_ins_since_vacuum', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', + prosrc => 'pg_stat_get_ins_since_vacuum' }, { oid => '1934', descr => 'statistics: number of blocks fetched', proname => 'pg_stat_get_blocks_fetched', provolatile => 's', proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index a07012bf4ba..763c1ee2bdd 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -647,6 +647,7 @@ typedef struct PgStat_StatTabEntry PgStat_Counter n_live_tuples; PgStat_Counter n_dead_tuples; PgStat_Counter changes_since_analyze; + PgStat_Counter inserts_since_vacuum; PgStat_Counter blocks_fetched; PgStat_Counter blocks_hit; diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h index d40ed55531d..7277effe034 100644 --- a/src/include/postmaster/autovacuum.h +++ b/src/include/postmaster/autovacuum.h @@ -33,6 +33,8 @@ extern int autovacuum_work_mem; extern int autovacuum_naptime; extern int autovacuum_vac_thresh; extern double autovacuum_vac_scale; +extern int autovacuum_vac_ins_thresh; +extern double autovacuum_vac_ins_scale; extern int autovacuum_anl_thresh; extern double autovacuum_anl_scale; extern int autovacuum_freeze_max_age; diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 39cdcddc2b1..2a13d8aad0c 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -257,6 +257,7 @@ typedef struct AutoVacOpts { bool enabled; int vacuum_threshold; + int vacuum_ins_threshold; int analyze_threshold; int vacuum_cost_limit; int freeze_min_age; @@ -268,6 +269,7 @@ typedef struct AutoVacOpts int log_min_duration; float8 vacuum_cost_delay; float8 vacuum_scale_factor; + float8 vacuum_ins_scale_factor; float8 analyze_scale_factor; } AutoVacOpts; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index a2077bbad4d..7245b0e13b8 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1778,6 +1778,7 @@ pg_stat_all_tables| SELECT c.oid AS relid, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze, + pg_stat_get_ins_since_vacuum(c.oid) AS n_ins_since_vacuum, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, @@ -2052,6 +2053,7 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.n_mod_since_analyze, + pg_stat_all_tables.n_ins_since_vacuum, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, @@ -2095,6 +2097,7 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.n_mod_since_analyze, + pg_stat_all_tables.n_ins_since_vacuum, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, |