Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit306dc52

Browse files
Introduce autovacuum_vacuum_max_threshold.
One way autovacuum chooses tables to vacuum is by comparing thenumber of updated or deleted tuples with a value calculated usingautovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor.The threshold specifies the base value for comparison, and thescale factor specifies the fraction of the table size to add to it.This strategy ensures that smaller tables are vacuumed after fewerupdates/deletes than larger tables, which is reasonable in manycases but can result in infrequent vacuums on very large tables.This is undesirable for a couple of reasons, such as very largetables incurring a huge amount of bloat between vacuums.This new parameter provides a way to set a limit on the valuecalculated with autovacuum_vacuum_threshold andautovacuum_vacuum_scale_factor so that very large tables arevacuumed more frequently. By default, it is set to 100,000,000tuples, but it can be disabled by setting it to -1. It can also beadjusted for individual tables by changing storage parameters.Author: Nathan Bossart <nathandbossart@gmail.com>Co-authored-by: Frédéric Yhuel <frederic.yhuel@dalibo.com>Reviewed-by: Melanie Plageman <melanieplageman@gmail.com>Reviewed-by: Robert Haas <robertmhaas@gmail.com>Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at>Reviewed-by: Michael Banck <mbanck@gmx.net>Reviewed-by: Joe Conway <mail@joeconway.com>Reviewed-by: Sami Imseih <samimseih@gmail.com>Reviewed-by: David Rowley <dgrowleyml@gmail.com>Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com>Reviewed-by: Vinícius Abrahão <vinnix.bsd@gmail.com>Reviewed-by: Robert Treat <rob@xzilla.net>Reviewed-by: Alena Rybakina <a.rybakina@postgrespro.ru>Discussion:https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com
1 parenta14707d commit306dc52

File tree

10 files changed

+82
-2
lines changed

10 files changed

+82
-2
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8685,6 +8685,30 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
86858685
</listitem>
86868686
</varlistentry>
86878687

8688+
<varlistentry id="guc-autovacuum-vacuum-max-threshold" xreflabel="autovacuum_vacuum_max_threshold">
8689+
<term><varname>autovacuum_vacuum_max_threshold</varname> (<type>integer</type>)
8690+
<indexterm>
8691+
<primary><varname>autovacuum_vacuum_max_threshold</varname></primary>
8692+
<secondary>configuration parameter</secondary>
8693+
</indexterm>
8694+
</term>
8695+
<listitem>
8696+
<para>
8697+
Specifies the maximum number of updated or deleted tuples needed to
8698+
trigger a <command>VACUUM</command> in any one table, i.e., a limit on
8699+
the value calculated with
8700+
<varname>autovacuum_vacuum_threshold</varname> and
8701+
<varname>autovacuum_vacuum_scale_factor</varname>. The default is
8702+
100,000,000 tuples. If -1 is specified, autovacuum will not enforce a
8703+
maximum number of updated or deleted tuples that will trigger a
8704+
<command>VACUUM</command> operation. This parameter can only be set
8705+
in the <filename>postgresql.conf</filename> file or on the server
8706+
command line; but the setting can be overridden for individual tables
8707+
by changing storage parameters.
8708+
</para>
8709+
</listitem>
8710+
</varlistentry>
8711+
86888712
<varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
86898713
<term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)
86908714
<indexterm>

‎doc/src/sgml/maintenance.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -895,9 +895,11 @@ HINT: Execute a database-wide VACUUM in that database.
895895
<command>VACUUM</command> exceeds the <quote>vacuum threshold</quote>, the
896896
table is vacuumed. The vacuum threshold is defined as:
897897
<programlisting>
898-
vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples
898+
vacuum threshold =Minimum(vacuummax threshold, vacuumbase threshold + vacuum scale factor * number of tuples)
899899
</programlisting>
900-
where the vacuum base threshold is
900+
where the vacuum max threshold is
901+
<xref linkend="guc-autovacuum-vacuum-max-threshold"/>,
902+
the vacuum base threshold is
901903
<xref linkend="guc-autovacuum-vacuum-threshold"/>,
902904
the vacuum scale factor is
903905
<xref linkend="guc-autovacuum-vacuum-scale-factor"/>,

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1712,6 +1712,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
17121712
</listitem>
17131713
</varlistentry>
17141714

1715+
<varlistentry id="reloption-autovacuum-vacuum-max-threshold" xreflabel="autovacuum_vacuum_max_threshold">
1716+
<term><literal>autovacuum_vacuum_max_threshold</literal>, <literal>toast.autovacuum_vacuum_max_threshold</literal> (<type>integer</type>)
1717+
<indexterm>
1718+
<primary><varname>autovacuum_vacuum_max_threshold</varname></primary>
1719+
<secondary>storage parameter</secondary>
1720+
</indexterm>
1721+
</term>
1722+
<listitem>
1723+
<para>
1724+
Per-table value for <xref linkend="guc-autovacuum-vacuum-max-threshold"/>
1725+
parameter.
1726+
</para>
1727+
</listitem>
1728+
</varlistentry>
1729+
17151730
<varlistentry id="reloption-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
17161731
<term><literal>autovacuum_vacuum_scale_factor</literal>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>floating point</type>)
17171732
<indexterm>

‎src/backend/access/common/reloptions.c

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -231,6 +231,15 @@ static relopt_int intRelOpts[] =
231231
},
232232
-1,0,INT_MAX
233233
},
234+
{
235+
{
236+
"autovacuum_vacuum_max_threshold",
237+
"Maximum number of tuple updates or deletes prior to vacuum",
238+
RELOPT_KIND_HEAP |RELOPT_KIND_TOAST,
239+
ShareUpdateExclusiveLock
240+
},
241+
-2,-1,INT_MAX
242+
},
234243
{
235244
{
236245
"autovacuum_vacuum_insert_threshold",
@@ -1843,6 +1852,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
18431852
offsetof(StdRdOptions,autovacuum)+ offsetof(AutoVacOpts,enabled)},
18441853
{"autovacuum_vacuum_threshold",RELOPT_TYPE_INT,
18451854
offsetof(StdRdOptions,autovacuum)+ offsetof(AutoVacOpts,vacuum_threshold)},
1855+
{"autovacuum_vacuum_max_threshold",RELOPT_TYPE_INT,
1856+
offsetof(StdRdOptions,autovacuum)+ offsetof(AutoVacOpts,vacuum_max_threshold)},
18461857
{"autovacuum_vacuum_insert_threshold",RELOPT_TYPE_INT,
18471858
offsetof(StdRdOptions,autovacuum)+ offsetof(AutoVacOpts,vacuum_ins_threshold)},
18481859
{"autovacuum_analyze_threshold",RELOPT_TYPE_INT,

‎src/backend/postmaster/autovacuum.c

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -120,6 +120,7 @@ intautovacuum_max_workers;
120120
intautovacuum_work_mem=-1;
121121
intautovacuum_naptime;
122122
intautovacuum_vac_thresh;
123+
intautovacuum_vac_max_thresh;
123124
doubleautovacuum_vac_scale;
124125
intautovacuum_vac_ins_thresh;
125126
doubleautovacuum_vac_ins_scale;
@@ -2895,6 +2896,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
28952896
* threshold. This threshold is calculated as
28962897
*
28972898
* threshold = vac_base_thresh + vac_scale_factor * reltuples
2899+
* if (threshold > vac_max_thresh)
2900+
* threshold = vac_max_thresh;
28982901
*
28992902
* For analyze, the analysis done is that the number of tuples inserted,
29002903
* deleted and updated since the last analyze exceeds a threshold calculated
@@ -2933,6 +2936,7 @@ relation_needs_vacanalyze(Oid relid,
29332936

29342937
/* constants from reloptions or GUC variables */
29352938
intvac_base_thresh,
2939+
vac_max_thresh,
29362940
vac_ins_base_thresh,
29372941
anl_base_thresh;
29382942
float4vac_scale_factor,
@@ -2974,6 +2978,11 @@ relation_needs_vacanalyze(Oid relid,
29742978
?relopts->vacuum_threshold
29752979
:autovacuum_vac_thresh;
29762980

2981+
/* -1 is used to disable max threshold */
2982+
vac_max_thresh= (relopts&&relopts->vacuum_max_threshold >=-1)
2983+
?relopts->vacuum_max_threshold
2984+
:autovacuum_vac_max_thresh;
2985+
29772986
vac_ins_scale_factor= (relopts&&relopts->vacuum_ins_scale_factor >=0)
29782987
?relopts->vacuum_ins_scale_factor
29792988
:autovacuum_vac_ins_scale;
@@ -3047,6 +3056,9 @@ relation_needs_vacanalyze(Oid relid,
30473056
reltuples=0;
30483057

30493058
vacthresh= (float4)vac_base_thresh+vac_scale_factor*reltuples;
3059+
if (vac_max_thresh >=0&&vacthresh> (float4)vac_max_thresh)
3060+
vacthresh= (float4)vac_max_thresh;
3061+
30503062
vacinsthresh= (float4)vac_ins_base_thresh+vac_ins_scale_factor*reltuples;
30513063
anlthresh= (float4)anl_base_thresh+anl_scale_factor*reltuples;
30523064

‎src/backend/utils/misc/guc_tables.c

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3426,6 +3426,15 @@ struct config_int ConfigureNamesInt[] =
34263426
50,0,INT_MAX,
34273427
NULL,NULL,NULL
34283428
},
3429+
{
3430+
{"autovacuum_vacuum_max_threshold",PGC_SIGHUP,VACUUM_AUTOVACUUM,
3431+
gettext_noop("Maximum number of tuple updates or deletes prior to vacuum."),
3432+
NULL
3433+
},
3434+
&autovacuum_vac_max_thresh,
3435+
100000000,-1,INT_MAX,
3436+
NULL,NULL,NULL
3437+
},
34293438
{
34303439
{"autovacuum_vacuum_insert_threshold",PGC_SIGHUP,VACUUM_AUTOVACUUM,
34313440
gettext_noop("Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums."),

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -670,6 +670,9 @@ autovacuum_worker_slots = 16# autovacuum worker slots to allocate
670670
#autovacuum_vacuum_insert_scale_factor = 0.2# fraction of inserts over table
671671
# size before insert vacuum
672672
#autovacuum_analyze_scale_factor = 0.1# fraction of table size before analyze
673+
#autovacuum_vacuum_max_threshold = 100000000 # max number of row updates
674+
# before vacuum; -1 disables max
675+
# threshold
673676
#autovacuum_freeze_max_age = 200000000# maximum XID age before forced vacuum
674677
# (change requires restart)
675678
#autovacuum_multixact_freeze_max_age = 400000000# maximum multixact age

‎src/bin/psql/tab-complete.in.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1368,6 +1368,7 @@ static const char *const table_storage_parameters[] = {
13681368
"autovacuum_vacuum_cost_limit",
13691369
"autovacuum_vacuum_insert_scale_factor",
13701370
"autovacuum_vacuum_insert_threshold",
1371+
"autovacuum_vacuum_max_threshold",
13711372
"autovacuum_vacuum_scale_factor",
13721373
"autovacuum_vacuum_threshold",
13731374
"fillfactor",
@@ -1384,6 +1385,7 @@ static const char *const table_storage_parameters[] = {
13841385
"toast.autovacuum_vacuum_cost_limit",
13851386
"toast.autovacuum_vacuum_insert_scale_factor",
13861387
"toast.autovacuum_vacuum_insert_threshold",
1388+
"toast.autovacuum_vacuum_max_threshold",
13871389
"toast.autovacuum_vacuum_scale_factor",
13881390
"toast.autovacuum_vacuum_threshold",
13891391
"toast.log_autovacuum_min_duration",

‎src/include/postmaster/autovacuum.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ extern PGDLLIMPORT int autovacuum_max_workers;
3333
externPGDLLIMPORTintautovacuum_work_mem;
3434
externPGDLLIMPORTintautovacuum_naptime;
3535
externPGDLLIMPORTintautovacuum_vac_thresh;
36+
externPGDLLIMPORTintautovacuum_vac_max_thresh;
3637
externPGDLLIMPORTdoubleautovacuum_vac_scale;
3738
externPGDLLIMPORTintautovacuum_vac_ins_thresh;
3839
externPGDLLIMPORTdoubleautovacuum_vac_ins_scale;

‎src/include/utils/rel.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -309,6 +309,7 @@ typedef struct AutoVacOpts
309309
{
310310
boolenabled;
311311
intvacuum_threshold;
312+
intvacuum_max_threshold;
312313
intvacuum_ins_threshold;
313314
intanalyze_threshold;
314315
intvacuum_cost_limit;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp