On Mon, 2020-03-16 at 14:34 -0700, Andres Freund wrote: > > > In particularl, I think it'd make sense to *not* have a lower freezing > > > horizon for insert vacuums (because it *will* cause problems), but if > > > the page is dirty anyway, then do the freezing even if freeze_min_age > > > etc would otherwise prevent us from doing so? > > > > I don't quite see why freezing tuples in insert-only tables will cause > > problems - are you saying that more WAL will be written compared to > > freezing with a higher freeze_min_age? > > As far as I understand the patch may trigger additional vacuums e.g. for > tables that have some heavily updated parts / key ranges, and otherwise > are largely insert only (as long as there are in total considerably more > inserts than updates). That's not at all uncommon. > > And for the heavily updated regions the additional vacuums with a 0 min > age could prove to be costly. I've not looked at the new code, but it'd > be particularly bad if the changes were to trigger the > lazy_check_needs_freeze() check in lazy_scan_heap() - it'd have the > potential for a lot more contention.
I think I got it. Here is a version of the patch that does *not* freeze more tuples than normal, except if a prior tuple on the same page is already eligible for freezing. lazy_check_needs_freeze() is only called for an aggressive vacuum, which this isn't. Does that look sane? Yours, Laurenz Albe
From abf3c092e016bbf19059fc104669e92a8de18462 Mon Sep 17 00:00:00 2001 From: Laurenz Albe <laurenz.a...@cybertec.at> Date: Tue, 17 Mar 2020 01:02:56 +0100 Subject: [PATCH] Autovacuum tables that have received only inserts Add "autovacuum_vacuum_insert_threshold" and "autovacuum_vacuum_insert_scale_factor" GUC and reloption. The default value for the threshold is 10000000. The scale factor defaults to 0, which means that it is effectively disabled, but it offers some flexibility to tune the feature similar to other autovacuum knobs. Any table that has received more inserts since it was last vacuumed (and that is not vacuumed for another reason) will be autovacuumed. During such a vacuum run, freeze all tuples in a page that has already been dirtied for any other reason. This should cause little extra overhead. This avoids the known problem that insert-only tables are never autovacuumed until they need to have their anti-wraparound autovacuum, which then can be massive and disruptive. To track the number of inserts since the last vacuum, introduce a StatTabEntry "inserts_since_vacuum" that gets reset to 0 after a vacuum. This value is available in "pg_stat_*_tables" as "n_ins_since_vacuum". Author: Laurenz Albe, based on a suggestion from Darafei Praliaskouski Reviewed-by: David Rowley, Justin Pryzby, Masahiko Sawada, Andres Freund Discussion: https://postgr.es/m/CAC8Q8t+j36G_bLF=+0imo6jgnwnlnwb1tujxujr-+x8zcct...@mail.gmail.com --- doc/src/sgml/config.sgml | 41 ++++++++++++++ doc/src/sgml/maintenance.sgml | 9 ++++ doc/src/sgml/monitoring.sgml | 5 ++ doc/src/sgml/ref/create_table.sgml | 30 +++++++++++ src/backend/access/common/reloptions.c | 22 ++++++++ src/backend/access/heap/vacuumlazy.c | 11 +++- src/backend/catalog/system_views.sql | 1 + src/backend/commands/vacuum.c | 3 ++ src/backend/postmaster/autovacuum.c | 53 ++++++++++++++++--- src/backend/postmaster/pgstat.c | 5 ++ src/backend/utils/adt/pgstatfuncs.c | 16 ++++++ src/backend/utils/misc/guc.c | 20 +++++++ src/backend/utils/misc/postgresql.conf.sample | 4 ++ src/bin/psql/tab-complete.c | 4 ++ src/include/catalog/pg_proc.dat | 5 ++ src/include/commands/vacuum.h | 1 + src/include/pgstat.h | 1 + src/include/postmaster/autovacuum.h | 2 + src/include/utils/rel.h | 2 + src/test/regress/expected/rules.out | 3 ++ 20 files changed, 230 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index c1128f89ec..0ed1bb9d5e 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7244,6 +7244,26 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold"> + <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>) + <indexterm> + <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary> + <secondary>configuration parameter</secondary> + </indexterm> + </term> + <listitem> + <para> + Specifies the number of inserted tuples needed to trigger a + <command>VACUUM</command> in any one table. + The default is 10000000 tuples. + This parameter can only be set in the <filename>postgresql.conf</filename> + file or on the server command line; + but the setting can be overridden for individual tables by + changing table storage parameters. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold"> <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>) <indexterm> @@ -7285,6 +7305,27 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor"> + <term><varname>autovacuum_vacuum_insert_scale_factor</varname> (<type>floating point</type>) + <indexterm> + <primary><varname>autovacuum_vacuum_insert_scale_factor</varname></primary> + <secondary>configuration parameter</secondary> + </indexterm> + </term> + <listitem> + <para> + Specifies a fraction of the table size to add to + <varname>autovacuum_vacuum_insert_threshold</varname> + when deciding whether to trigger a <command>VACUUM</command>. + The default is 0.0, which means that the table size has no effect. + This parameter can only be set in the <filename>postgresql.conf</filename> + file or on the server command line; + but the setting can be overridden for individual tables by + changing table storage parameters. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor"> <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>) <indexterm> diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index ec8bdcd7a4..2d5c437977 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -788,6 +788,15 @@ vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuple since the last vacuum are scanned. </para> + <para> + A threshold similar to the above is calculated from + <xref linkend="guc-autovacuum-vacuum-insert-threshold"/> and + <xref linkend="guc-autovacuum-vacuum-insert-scale-factor"/>. + Tables that have received more inserts than the calculated threshold + since they were last vacuumed will be vacuumed to reduce the impact + of a future anti-wraparound vacuum run. + </para> + <para> For analyze, a similar condition is used: the threshold, defined as: <programlisting> diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 87586a7b06..05fd6260b7 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -2837,6 +2837,11 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i <entry><type>bigint</type></entry> <entry>Estimated number of rows modified since this table was last analyzed</entry> </row> + <row> + <entry><structfield>n_ins_since_vacuum</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Estimated number of rows inserted since this table was last vacuumed</entry> + </row> <row> <entry><structfield>last_vacuum</structfield></entry> <entry><type>timestamp with time zone</type></entry> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 4a2b6f0dae..20acede7e3 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1475,6 +1475,36 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </listitem> </varlistentry> + <varlistentry id="reloption-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold"> + <term><literal>autovacuum_vacuum_insert_threshold</literal>, <literal>toast.autovacuum_vacuum_insert_threshold</literal> (<type>integer</type>) + <indexterm> + <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary> + <secondary>storage parameter</secondary> + </indexterm> + </term> + <listitem> + <para> + Per-table value for <xref linkend="guc-autovacuum-vacuum-insert-threshold"/> + parameter. + </para> + </listitem> + </varlistentry> + + <varlistentry id="reloption-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor"> + <term><literal>autovacuum_vacuum_insert_scale_factor</literal>, <literal>toast.autovacuum_vacuum_insert_scale_factor</literal> (<type>float4</type>) + <indexterm> + <primary><varname>autovacuum_vacuum_insert_scale_factor</varname> </primary> + <secondary>storage parameter</secondary> + </indexterm> + </term> + <listitem> + <para> + Per-table value for <xref linkend="guc-autovacuum-vacuum-insert-scale-factor"/> + parameter. + </para> + </listitem> + </varlistentry> + <varlistentry id="reloption-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold"> <term><literal>autovacuum_analyze_threshold</literal> (<type>integer</type>) <indexterm> diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index 5325dd3f61..245b0af535 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -233,6 +233,15 @@ static relopt_int intRelOpts[] = }, -1, 0, INT_MAX }, + { + { + "autovacuum_vacuum_insert_threshold", + "Minimum number of tuple inserts prior to vacuum", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + ShareUpdateExclusiveLock + }, + -1, 0, INT_MAX + }, { { "autovacuum_analyze_threshold", @@ -385,6 +394,15 @@ static relopt_real realRelOpts[] = }, -1, 0.0, 100.0 }, + { + { + "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", @@ -1501,6 +1519,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, @@ -1525,6 +1545,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/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 03c43efc32..29187e2673 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -1388,17 +1388,26 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats, else { bool tuple_totally_frozen; + bool freeze_all; num_tuples += 1; hastup = true; + /* + * If any tuple was already frozen in the block and this is + * an insert-only vacuum, we might as well freeze all other + * tuples in that block. + */ + freeze_all = params->is_insert_only && has_dead_tuples; + /* * Each non-removable tuple must be checked to see if it needs * freezing. Note we already have exclusive buffer lock. */ if (heap_prepare_freeze_tuple(tuple.t_data, relfrozenxid, relminmxid, - FreezeLimit, MultiXactCutoff, + freeze_all ? 0 : FreezeLimit, + freeze_all ? 0 : MultiXactCutoff, &frozen[nfrozen], &tuple_totally_frozen)) frozen[nfrozen++].offset = offnum; diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index f681aafcf9..b2729f1771 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/commands/vacuum.c b/src/backend/commands/vacuum.c index d625d17bf4..05baf49d1f 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -244,6 +244,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) /* user-invoked vacuum is never "for wraparound" */ params.is_wraparound = false; + /* user-invoked vacuum is never "insert-only" */ + params.is_insert_only = false; + /* user-invoked vacuum never uses this parameter */ params.log_min_duration = -1; diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index e3a43d3296..2ee1a7a792 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; @@ -330,7 +332,8 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, Form_pg_class classForm, PgStat_StatTabEntry *tabentry, int effective_multixact_freeze_max_age, - bool *dovacuum, bool *doanalyze, bool *wraparound); + bool *dovacuum, bool *doanalyze, bool *wraparound, + bool *insert_only); static void autovacuum_do_vac_analyze(autovac_table *tab, BufferAccessStrategy bstrategy); @@ -2056,6 +2059,7 @@ do_autovacuum(void) bool dovacuum; bool doanalyze; bool wraparound; + bool insert_only; if (classForm->relkind != RELKIND_RELATION && classForm->relkind != RELKIND_MATVIEW) @@ -2096,7 +2100,8 @@ do_autovacuum(void) /* Check if it needs vacuum or analyze */ relation_needs_vacanalyze(relid, relopts, classForm, tabentry, effective_multixact_freeze_max_age, - &dovacuum, &doanalyze, &wraparound); + &dovacuum, &doanalyze, &wraparound, + &insert_only); /* Relations that need work are added to table_oids */ if (dovacuum || doanalyze) @@ -2149,6 +2154,7 @@ do_autovacuum(void) bool dovacuum; bool doanalyze; bool wraparound; + bool insert_only; /* * We cannot safely process other backends' temp tables, so skip 'em. @@ -2179,7 +2185,8 @@ do_autovacuum(void) relation_needs_vacanalyze(relid, relopts, classForm, tabentry, effective_multixact_freeze_max_age, - &dovacuum, &doanalyze, &wraparound); + &dovacuum, &doanalyze, &wraparound, + &insert_only); /* ignore analyze for toast tables */ if (dovacuum) @@ -2780,6 +2787,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, PgStat_StatDBEntry *shared; PgStat_StatDBEntry *dbentry; bool wraparound; + bool insert_only; AutoVacOpts *avopts; /* use fresh stats */ @@ -2816,7 +2824,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, relation_needs_vacanalyze(relid, avopts, classForm, tabentry, effective_multixact_freeze_max_age, - &dovacuum, &doanalyze, &wraparound); + &dovacuum, &doanalyze, &wraparound, + &insert_only); /* ignore ANALYZE for toast tables */ if (classForm->relkind == RELKIND_TOASTVALUE) @@ -2894,6 +2903,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, tab->at_params.multixact_freeze_min_age = multixact_freeze_min_age; tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age; tab->at_params.is_wraparound = wraparound; + tab->at_params.is_insert_only = insert_only; tab->at_params.log_min_duration = log_min_duration; tab->at_vacuum_cost_limit = vac_cost_limit; tab->at_vacuum_cost_delay = vac_cost_delay; @@ -2961,7 +2971,8 @@ relation_needs_vacanalyze(Oid relid, /* output params below */ bool *dovacuum, bool *doanalyze, - bool *wraparound) + bool *wraparound, + bool *insert_only) { bool force_vacuum; bool av_enabled; @@ -2969,16 +2980,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 +3020,14 @@ 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; + + vac_ins_base_thresh = (relopts && relopts->vacuum_ins_threshold >= 0) + ? relopts->vacuum_ins_threshold + : autovacuum_vac_ins_thresh; + anl_scale_factor = (relopts && relopts->analyze_scale_factor >= 0) ? relopts->analyze_scale_factor : autovacuum_anl_scale; @@ -3023,6 +3046,9 @@ relation_needs_vacanalyze(Oid relid, av_enabled = (relopts ? relopts->enabled : true); + /* normal autovacuum shouldn't freeze aggressively */ + *insert_only = false; + /* Force vacuum if table is at risk of wraparound */ xidForceLimit = recentXid - freeze_max_age; if (xidForceLimit < FirstNormalTransactionId) @@ -3059,9 +3085,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,13 +3097,24 @@ 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)", + elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)", NameStr(classForm->relname), - vactuples, vacthresh, anltuples, anlthresh); + vactuples, vacthresh, instuples, vacinsthresh, anltuples, anlthresh); /* Determine if this table needs vacuum or analyze. */ *dovacuum = force_vacuum || (vactuples > vacthresh); *doanalyze = (anltuples > anlthresh); + + /* + * If the number of inserted tuples exceeds the threshold and no + * vacuum is necessary for other reasons, run an "insert-only" vacuum + * that freezes as many tuples as possible. + */ + if (!(*dovacuum) && (instuples > vacinsthresh)) + { + *dovacuum = true; + *insert_only = true; + } } else { diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 462b4d7e06..78bd5cf90d 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -4791,6 +4791,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; @@ -5921,6 +5922,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; @@ -5950,10 +5952,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; } @@ -6187,6 +6191,7 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len) tabentry->n_live_tuples = msg->m_live_tuples; tabentry->n_dead_tuples = msg->m_dead_tuples; + tabentry->inserts_since_vacuum = 0; if (msg->m_autovacuum) { diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 7e6a3c1774..aac248a86e 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -196,6 +196,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) { diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 464f264d9a..9f4f6a4272 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -3063,6 +3063,15 @@ static struct config_int ConfigureNamesInt[] = 50, 0, INT_MAX, NULL, NULL, NULL }, + { + {"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM, + gettext_noop("Minimum number of tuple inserts prior to vacuum."), + NULL + }, + &autovacuum_vac_ins_thresh, + 10000000, 0, INT_MAX, + NULL, NULL, NULL + }, { {"autovacuum_analyze_threshold", PGC_SIGHUP, AUTOVACUUM, gettext_noop("Minimum number of tuple inserts, updates, or deletes prior to analyze."), @@ -3510,6 +3519,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.0, 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 e58e4788a8..04ae8784f8 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -598,9 +598,13 @@ #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum +#autovacuum_vacuum_insert_threshold = 10000000 # min number of row inserts + # before vacuum #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.0 # fraction of table size before + # 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 b6b08d0ccb..efc7b20659 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1071,6 +1071,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", @@ -1085,6 +1087,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/pg_proc.dat b/src/include/catalog/pg_proc.dat index 07a86c7b7b..0527b785a6 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/commands/vacuum.h b/src/include/commands/vacuum.h index c27d255d8d..3b0c8ff77c 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -215,6 +215,7 @@ typedef struct VacuumParams int multixact_freeze_table_age; /* multixact age at which to scan * whole table */ bool is_wraparound; /* force a for-wraparound vacuum */ + bool is_insert_only; /* was triggered by inserts */ int log_min_duration; /* minimum execution threshold in ms at * which verbose logs are activated, -1 * to use default */ diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 3a65a51696..7ccbb3191e 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -646,6 +646,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 d40ed55531..7277effe03 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 44ed04dd3f..476d859d81 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -252,6 +252,7 @@ typedef struct AutoVacOpts { bool enabled; int vacuum_threshold; + int vacuum_ins_threshold; int analyze_threshold; int vacuum_cost_limit; int freeze_min_age; @@ -263,6 +264,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 634f8256f7..031005ac59 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, @@ -2034,6 +2035,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, @@ -2077,6 +2079,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, -- 2.21.1
