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

Commit53bb309

Browse files
committed
Teach autovacuum about multixact member wraparound.
The logic introduced in commitb69bf30and repaired in commits669c7d2 and7be47c5 helps to ensure that we don'toverwrite old multixact member information while it is still needed,but a user who creates many large multixacts can still exhaust themember space (and thus start getting errors) while autovacuum standsidly by.To fix this, progressively ramp down the effective value (but not theactual contents) of autovacuum_multixact_freeze_max_age as member spaceutilization increases. This makes autovacuum more aggressive and alsoreduces the threshold for a manual VACUUM to perform a full-table scan.This patch leaves unsolved the problem of ensuring that emergencyautovacuums are triggered even when autovacuum=off. We'll need to fixthat via a separate patch.Thomas Munro and Robert Haas
1 parent195fbd4 commit53bb309

File tree

5 files changed

+130
-11
lines changed

5 files changed

+130
-11
lines changed

‎doc/src/sgml/maintenance.sgml

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -628,6 +628,9 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
628628
Like transaction IDs, multixact IDs are implemented as a
629629
32-bit counter and corresponding storage, all of which requires
630630
careful aging management, storage cleanup, and wraparound handling.
631+
There is a separate storage area which holds the list of members in
632+
each multixact, which also uses a 32-bit counter and which must also
633+
be managed.
631634
</para>
632635

633636
<para>
@@ -656,7 +659,10 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
656659
As a safety device, a whole-table vacuum scan will occur for any table
657660
whose multixact-age is greater than
658661
<xref linkend="guc-autovacuum-multixact-freeze-max-age">.
659-
This will occur even if autovacuum is nominally disabled.
662+
This will occur even if autovacuum is nominally disabled. Whole-table
663+
vacuum scans will also occur progressively for all tables, starting with
664+
those that have the oldest multixact-age, if the amount of used member
665+
storage space exceeds the amount 25% of the addressible storage space.
660666
</para>
661667
</sect3>
662668
</sect2>

‎src/backend/access/transam/multixact.c

Lines changed: 88 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -168,6 +168,11 @@
168168
(MXOffsetToFlagsOffset(xid) + MULTIXACT_FLAGBYTES_PER_GROUP + \
169169
((xid) % MULTIXACT_MEMBERS_PER_MEMBERGROUP) * sizeof(TransactionId))
170170

171+
/* Multixact members wraparound thresholds. */
172+
#defineMULTIXACT_MEMBER_SAFE_THRESHOLD(MaxMultiXactOffset / 4)
173+
#defineMULTIXACT_MEMBER_DANGER_THRESHOLD\
174+
(MaxMultiXactOffset - MaxMultiXactOffset / 4)
175+
171176

172177
/*
173178
* Links to shared-memory data structures for MultiXact control
@@ -2578,6 +2583,89 @@ find_multixact_start(MultiXactId multi)
25782583
returnoffset;
25792584
}
25802585

2586+
/*
2587+
* Determine how many multixacts, and how many multixact members, currently
2588+
* exist.
2589+
*/
2590+
staticvoid
2591+
ReadMultiXactCounts(uint32*multixacts,MultiXactOffset*members)
2592+
{
2593+
MultiXactOffsetnextOffset;
2594+
MultiXactOffsetoldestOffset;
2595+
MultiXactIdoldestMultiXactId;
2596+
MultiXactIdnextMultiXactId;
2597+
2598+
LWLockAcquire(MultiXactGenLock,LW_SHARED);
2599+
nextOffset=MultiXactState->nextOffset;
2600+
oldestMultiXactId=MultiXactState->oldestMultiXactId;
2601+
nextMultiXactId=MultiXactState->nextMXact;
2602+
LWLockRelease(MultiXactGenLock);
2603+
2604+
oldestOffset=find_multixact_start(oldestMultiXactId);
2605+
*members=nextOffset-oldestOffset;
2606+
*multixacts=nextMultiXactId-oldestMultiXactId;
2607+
}
2608+
2609+
/*
2610+
* Multixact members can be removed once the multixacts that refer to them
2611+
* are older than every datminxmid. autovacuum_multixact_freeze_max_age and
2612+
* vacuum_multixact_freeze_table_age work together to make sure we never have
2613+
* too many multixacts; we hope that, at least under normal circumstances,
2614+
* this will also be sufficient to keep us from using too many offsets.
2615+
* However, if the average multixact has many members, we might exhaust the
2616+
* members space while still using few enough members that these limits fail
2617+
* to trigger full table scans for relminmxid advancement. At that point,
2618+
* we'd have no choice but to start failing multixact-creating operations
2619+
* with an error.
2620+
*
2621+
* To prevent that, if more than a threshold portion of the members space is
2622+
* used, we effectively reduce autovacuum_multixact_freeze_max_age and
2623+
* to a value just less than the number of multixacts in use. We hope that
2624+
* this will quickly trigger autovacuuming on the table or tables with the
2625+
* oldest relminmxid, thus allowing datminmxid values to advance and removing
2626+
* some members.
2627+
*
2628+
* As the fraction of the member space currently in use grows, we become
2629+
* more aggressive in clamping this value. That not only causes autovacuum
2630+
* to ramp up, but also makes any manual vacuums the user issues more
2631+
* aggressive. This happens because vacuum_set_xid_limits() clamps the
2632+
* freeze table and and the minimum freeze age based on the effective
2633+
* autovacuum_multixact_freeze_max_age this function returns. In the worst
2634+
* case, we'll claim the freeze_max_age to zero, and every vacuum of any
2635+
* table will try to freeze every multixact.
2636+
*
2637+
* It's possible that these thresholds should be user-tunable, but for now
2638+
* we keep it simple.
2639+
*/
2640+
int
2641+
MultiXactMemberFreezeThreshold(void)
2642+
{
2643+
MultiXactOffsetmembers;
2644+
uint32multixacts;
2645+
uint32victim_multixacts;
2646+
doublefraction;
2647+
2648+
ReadMultiXactCounts(&multixacts,&members);
2649+
2650+
/* If member space utilization is low, no special action is required. */
2651+
if (members <=MULTIXACT_MEMBER_SAFE_THRESHOLD)
2652+
returnautovacuum_multixact_freeze_max_age;
2653+
2654+
/*
2655+
* Compute a target for relminmxid advancement. The number of multixacts
2656+
* we try to eliminate from the system is based on how far we are past
2657+
* MULTIXACT_MEMBER_SAFE_THRESHOLD.
2658+
*/
2659+
fraction= (double) (members-MULTIXACT_MEMBER_SAFE_THRESHOLD) /
2660+
(MULTIXACT_MEMBER_DANGER_THRESHOLD-MULTIXACT_MEMBER_SAFE_THRESHOLD);
2661+
victim_multixacts=multixacts*fraction;
2662+
2663+
/* fraction could be > 1.0, but lowest possible freeze age is zero */
2664+
if (victim_multixacts>multixacts)
2665+
return0;
2666+
returnmultixacts-victim_multixacts;
2667+
}
2668+
25812669
/*
25822670
* SlruScanDirectory callback.
25832671
*This callback deletes segments that are outside the range determined by

‎src/backend/commands/vacuum.c

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -471,6 +471,7 @@ vacuum_set_xid_limits(Relation rel,
471471
{
472472
intfreezemin;
473473
intmxid_freezemin;
474+
inteffective_multixact_freeze_max_age;
474475
TransactionIdlimit;
475476
TransactionIdsafeLimit;
476477
MultiXactIdmxactLimit;
@@ -527,17 +528,24 @@ vacuum_set_xid_limits(Relation rel,
527528

528529
*freezeLimit=limit;
529530

531+
/*
532+
* Compute the multixact age for which freezing is urgent. This is
533+
* normally autovacuum_multixact_freeze_max_age, but may be less if we
534+
* are short of multixact member space.
535+
*/
536+
effective_multixact_freeze_max_age=MultiXactMemberFreezeThreshold();
537+
530538
/*
531539
* Determine the minimum multixact freeze age to use: as specified by
532540
* caller, or vacuum_multixact_freeze_min_age, but in any case not more
533-
* than halfautovacuum_multixact_freeze_max_age, so that autovacuums to
541+
* than halfeffective_multixact_freeze_max_age, so that autovacuums to
534542
* prevent MultiXact wraparound won't occur too frequently.
535543
*/
536544
mxid_freezemin=multixact_freeze_min_age;
537545
if (mxid_freezemin<0)
538546
mxid_freezemin=vacuum_multixact_freeze_min_age;
539547
mxid_freezemin=Min(mxid_freezemin,
540-
autovacuum_multixact_freeze_max_age /2);
548+
effective_multixact_freeze_max_age /2);
541549
Assert(mxid_freezemin >=0);
542550

543551
/* compute the cutoff multi, being careful to generate a valid value */
@@ -546,7 +554,7 @@ vacuum_set_xid_limits(Relation rel,
546554
mxactLimit=FirstMultiXactId;
547555

548556
safeMxactLimit=
549-
ReadNextMultiXactId()-autovacuum_multixact_freeze_max_age;
557+
ReadNextMultiXactId()-effective_multixact_freeze_max_age;
550558
if (safeMxactLimit<FirstMultiXactId)
551559
safeMxactLimit=FirstMultiXactId;
552560

@@ -601,7 +609,7 @@ vacuum_set_xid_limits(Relation rel,
601609
if (freezetable<0)
602610
freezetable=vacuum_multixact_freeze_table_age;
603611
freezetable=Min(freezetable,
604-
autovacuum_multixact_freeze_max_age*0.95);
612+
effective_multixact_freeze_max_age*0.95);
605613
Assert(freezetable >=0);
606614

607615
/*

‎src/backend/postmaster/autovacuum.c

Lines changed: 22 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -297,10 +297,12 @@ static void do_autovacuum(void);
297297
staticvoidFreeWorkerInfo(intcode,Datumarg);
298298

299299
staticautovac_table*table_recheck_autovac(Oidrelid,HTAB*table_toast_map,
300-
TupleDescpg_class_desc);
300+
TupleDescpg_class_desc,
301+
inteffective_multixact_freeze_max_age);
301302
staticvoidrelation_needs_vacanalyze(Oidrelid,AutoVacOpts*relopts,
302303
Form_pg_classclassForm,
303304
PgStat_StatTabEntry*tabentry,
305+
inteffective_multixact_freeze_max_age,
304306
bool*dovacuum,bool*doanalyze,bool*wraparound);
305307

306308
staticvoidautovacuum_do_vac_analyze(autovac_table*tab,
@@ -1118,7 +1120,7 @@ do_start_worker(void)
11181120

11191121
/* Also determine the oldest datminmxid we will consider. */
11201122
recentMulti=ReadNextMultiXactId();
1121-
multiForceLimit=recentMulti-autovacuum_multixact_freeze_max_age;
1123+
multiForceLimit=recentMulti-MultiXactMemberFreezeThreshold();
11221124
if (multiForceLimit<FirstMultiXactId)
11231125
multiForceLimit-=FirstMultiXactId;
11241126

@@ -1881,6 +1883,7 @@ do_autovacuum(void)
18811883
BufferAccessStrategybstrategy;
18821884
ScanKeyDatakey;
18831885
TupleDescpg_class_desc;
1886+
inteffective_multixact_freeze_max_age;
18841887

18851888
/*
18861889
* StartTransactionCommand and CommitTransactionCommand will automatically
@@ -1910,6 +1913,13 @@ do_autovacuum(void)
19101913
*/
19111914
pgstat_vacuum_stat();
19121915

1916+
/*
1917+
* Compute the multixact age for which freezing is urgent. This is
1918+
* normally autovacuum_multixact_freeze_max_age, but may be less if we
1919+
* are short of multixact member space.
1920+
*/
1921+
effective_multixact_freeze_max_age=MultiXactMemberFreezeThreshold();
1922+
19131923
/*
19141924
* Find the pg_database entry and select the default freeze ages. We use
19151925
* zero in template and nonconnectable databases, else the system-wide
@@ -2001,6 +2011,7 @@ do_autovacuum(void)
20012011

20022012
/* Check if it needs vacuum or analyze */
20032013
relation_needs_vacanalyze(relid,relopts,classForm,tabentry,
2014+
effective_multixact_freeze_max_age,
20042015
&dovacuum,&doanalyze,&wraparound);
20052016

20062017
/*
@@ -2129,6 +2140,7 @@ do_autovacuum(void)
21292140
shared,dbentry);
21302141

21312142
relation_needs_vacanalyze(relid,relopts,classForm,tabentry,
2143+
effective_multixact_freeze_max_age,
21322144
&dovacuum,&doanalyze,&wraparound);
21332145

21342146
/* ignore analyze for toast tables */
@@ -2235,7 +2247,8 @@ do_autovacuum(void)
22352247
* the race condition is not closed but it is very small.
22362248
*/
22372249
MemoryContextSwitchTo(AutovacMemCxt);
2238-
tab=table_recheck_autovac(relid,table_toast_map,pg_class_desc);
2250+
tab=table_recheck_autovac(relid,table_toast_map,pg_class_desc,
2251+
effective_multixact_freeze_max_age);
22392252
if (tab==NULL)
22402253
{
22412254
/* someone else vacuumed the table, or it went away */
@@ -2442,7 +2455,8 @@ get_pgstat_tabentry_relid(Oid relid, bool isshared, PgStat_StatDBEntry *shared,
24422455
*/
24432456
staticautovac_table*
24442457
table_recheck_autovac(Oidrelid,HTAB*table_toast_map,
2445-
TupleDescpg_class_desc)
2458+
TupleDescpg_class_desc,
2459+
inteffective_multixact_freeze_max_age)
24462460
{
24472461
Form_pg_classclassForm;
24482462
HeapTupleclassTup;
@@ -2488,6 +2502,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
24882502
shared,dbentry);
24892503

24902504
relation_needs_vacanalyze(relid,avopts,classForm,tabentry,
2505+
effective_multixact_freeze_max_age,
24912506
&dovacuum,&doanalyze,&wraparound);
24922507

24932508
/* ignore ANALYZE for toast tables */
@@ -2624,6 +2639,7 @@ relation_needs_vacanalyze(Oid relid,
26242639
AutoVacOpts*relopts,
26252640
Form_pg_classclassForm,
26262641
PgStat_StatTabEntry*tabentry,
2642+
inteffective_multixact_freeze_max_age,
26272643
/* output params below */
26282644
bool*dovacuum,
26292645
bool*doanalyze,
@@ -2684,8 +2700,8 @@ relation_needs_vacanalyze(Oid relid,
26842700
:autovacuum_freeze_max_age;
26852701

26862702
multixact_freeze_max_age= (relopts&&relopts->multixact_freeze_max_age >=0)
2687-
?Min(relopts->multixact_freeze_max_age,autovacuum_multixact_freeze_max_age)
2688-
:autovacuum_multixact_freeze_max_age;
2703+
?Min(relopts->multixact_freeze_max_age,effective_multixact_freeze_max_age)
2704+
:effective_multixact_freeze_max_age;
26892705

26902706
av_enabled= (relopts ?relopts->enabled : true);
26912707

‎src/include/access/multixact.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -126,6 +126,7 @@ extern void MultiXactAdvanceNextMXact(MultiXactId minMulti,
126126
MultiXactOffsetminMultiOffset);
127127
externvoidMultiXactAdvanceOldest(MultiXactIdoldestMulti,OidoldestMultiDB);
128128
externvoidMultiXactSetSafeTruncate(MultiXactIdsafeTruncateMulti);
129+
externintMultiXactMemberFreezeThreshold(void);
129130

130131
externvoidmultixact_twophase_recover(TransactionIdxid,uint16info,
131132
void*recdata,uint32len);

0 commit comments

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