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

Commit06eae9e

Browse files
Trigger more frequent autovacuums with relallfrozen
Calculate the insert threshold for triggering an autovacuum of arelation based on the number of unfrozen pages.By only considering the unfrozen portion of the table when calculatinghow many tuples to add to the insert threshold, we can trigger morefrequent vacuums of insert-heavy tables. This increases the chances ofvacuuming those pages when they still reside in shared buffersThis also increases the number of autovacuums triggered by tuplesinserted and not by wraparound risk. We prefer to freeze these pagesduring insert-triggered autovacuums, as anti-wraparound vacuums are notautomatically canceled by conflicting lock requests.We calculate the unfrozen percentage of the table using the recentlyadded (99f8f3f) relallfrozen column of pg_class.Author: Melanie Plageman <melanieplageman@gmail.com>Reviewed-by: Nathan Bossart <nathandbossart@gmail.com>Reviewed-by: Greg Sabino Mullane <htamfids@gmail.com>Reviewed-by: Robert Treat <rob@xzilla.net>Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com>Discussion:https://postgr.es/m/flat/CAAKRu_aj-P7YyBz_cPNwztz6ohP%2BvWis%3Diz3YcomkB3NpYA--w%40mail.gmail.com
1 parent35c8dd9 commit06eae9e

File tree

4 files changed

+37
-16
lines changed

4 files changed

+37
-16
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2072,9 +2072,10 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
20722072
</para>
20732073
<para>
20742074
Number of pages that are marked all-frozen in the table's visibility
2075-
map. This is only an estimate and can be used along with
2076-
<structfield>relallvisible</structfield> for scheduling vacuums and
2077-
tuning <link linkend="runtime-config-vacuum-freezing">vacuum's freezing
2075+
map. This is only an estimate used for triggering autovacuums. It can
2076+
also be used along with <structfield>relallvisible</structfield> for
2077+
scheduling manual vacuums and tuning <link
2078+
linkend="runtime-config-vacuum-freezing">vacuum's freezing
20782079
behavior</link>.
20792080

20802081
It is updated by

‎doc/src/sgml/config.sgml

Lines changed: 7 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -8773,14 +8773,13 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
87738773
</term>
87748774
<listitem>
87758775
<para>
8776-
Specifies a fraction of the table size to add to
8777-
<varname>autovacuum_vacuum_insert_threshold</varname>
8778-
when deciding whether to trigger a <command>VACUUM</command>.
8779-
The default is <literal>0.2</literal> (20% of table size).
8780-
This parameter can only be set in the <filename>postgresql.conf</filename>
8781-
file or on the server command line;
8782-
but the setting can be overridden for individual tables by
8783-
changing table storage parameters.
8776+
Specifies a fraction of the unfrozen pages in the table to add to
8777+
<varname>autovacuum_vacuum_insert_threshold</varname> when deciding
8778+
whether to trigger a <command>VACUUM</command>. The default is
8779+
<literal>0.2</literal> (20% of unfrozen pages in table). This
8780+
parameter can only be set in the <filename>postgresql.conf</filename>
8781+
file or on the server command line; but the setting can be overridden
8782+
for individual tables by changing table storage parameters.
87848783
</para>
87858784
</listitem>
87868785
</varlistentry>

‎src/backend/postmaster/autovacuum.c

Lines changed: 24 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2938,7 +2938,6 @@ relation_needs_vacanalyze(Oid relid,
29382938
{
29392939
boolforce_vacuum;
29402940
boolav_enabled;
2941-
float4reltuples;/* pg_class.reltuples */
29422941

29432942
/* constants from reloptions or GUC variables */
29442943
intvac_base_thresh,
@@ -3052,7 +3051,11 @@ relation_needs_vacanalyze(Oid relid,
30523051
*/
30533052
if (PointerIsValid(tabentry)&&AutoVacuumingActive())
30543053
{
3055-
reltuples=classForm->reltuples;
3054+
float4pcnt_unfrozen=1;
3055+
float4reltuples=classForm->reltuples;
3056+
int32relpages=classForm->relpages;
3057+
int32relallfrozen=classForm->relallfrozen;
3058+
30563059
vactuples=tabentry->dead_tuples;
30573060
instuples=tabentry->ins_since_vacuum;
30583061
anltuples=tabentry->mod_since_analyze;
@@ -3061,11 +3064,29 @@ relation_needs_vacanalyze(Oid relid,
30613064
if (reltuples<0)
30623065
reltuples=0;
30633066

3067+
/*
3068+
* If we have data for relallfrozen, calculate the unfrozen percentage
3069+
* of the table to modify insert scale factor. This helps us decide
3070+
* whether or not to vacuum an insert-heavy table based on the number
3071+
* of inserts to the more "active" part of the table.
3072+
*/
3073+
if (relpages>0&&relallfrozen>0)
3074+
{
3075+
/*
3076+
* It could be the stats were updated manually and relallfrozen >
3077+
* relpages. Clamp relallfrozen to relpages to avoid nonsensical
3078+
* calculations.
3079+
*/
3080+
relallfrozen=Min(relallfrozen,relpages);
3081+
pcnt_unfrozen=1- ((float4)relallfrozen /relpages);
3082+
}
3083+
30643084
vacthresh= (float4)vac_base_thresh+vac_scale_factor*reltuples;
30653085
if (vac_max_thresh >=0&&vacthresh> (float4)vac_max_thresh)
30663086
vacthresh= (float4)vac_max_thresh;
30673087

3068-
vacinsthresh= (float4)vac_ins_base_thresh+vac_ins_scale_factor*reltuples;
3088+
vacinsthresh= (float4)vac_ins_base_thresh+
3089+
vac_ins_scale_factor*reltuples*pcnt_unfrozen;
30693090
anlthresh= (float4)anl_base_thresh+anl_scale_factor*reltuples;
30703091

30713092
/*

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -675,8 +675,8 @@ autovacuum_worker_slots = 16# autovacuum worker slots to allocate
675675
#autovacuum_analyze_threshold = 50# min number of row updates before
676676
# analyze
677677
#autovacuum_vacuum_scale_factor = 0.2# fraction of table size before vacuum
678-
#autovacuum_vacuum_insert_scale_factor = 0.2# fraction ofinserts over table
679-
# size before insert vacuum
678+
#autovacuum_vacuum_insert_scale_factor = 0.2# fraction ofunfrozen pages
679+
# before insert vacuum
680680
#autovacuum_analyze_scale_factor = 0.1# fraction of table size before analyze
681681
#autovacuum_vacuum_max_threshold = 100000000 # max number of row updates
682682
# before vacuum; -1 disables max

0 commit comments

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