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

Commit3ecab37

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 parent32c50af commit3ecab37

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>
@@ -651,7 +654,10 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
651654
As a safety device, a whole-table vacuum scan will occur for any table
652655
whose multixact-age is greater than
653656
<xref linkend="guc-autovacuum-multixact-freeze-max-age">.
654-
This will occur even if autovacuum is nominally disabled.
657+
This will occur even if autovacuum is nominally disabled. Whole-table
658+
vacuum scans will also occur progressively for all tables, starting with
659+
those that have the oldest multixact-age, if the amount of used member
660+
storage space exceeds the amount 25% of the addressible storage space.
655661
</para>
656662
</sect3>
657663
</sect2>

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

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

169+
/* Multixact members wraparound thresholds. */
170+
#defineMULTIXACT_MEMBER_SAFE_THRESHOLD(MaxMultiXactOffset / 4)
171+
#defineMULTIXACT_MEMBER_DANGER_THRESHOLD\
172+
(MaxMultiXactOffset - MaxMultiXactOffset / 4)
173+
169174

170175
/*
171176
* Links to shared-memory data structures for MultiXact control
@@ -2597,6 +2602,89 @@ find_multixact_start(MultiXactId multi)
25972602
returnoffset;
25982603
}
25992604

2605+
/*
2606+
* Determine how many multixacts, and how many multixact members, currently
2607+
* exist.
2608+
*/
2609+
staticvoid
2610+
ReadMultiXactCounts(uint32*multixacts,MultiXactOffset*members)
2611+
{
2612+
MultiXactOffsetnextOffset;
2613+
MultiXactOffsetoldestOffset;
2614+
MultiXactIdoldestMultiXactId;
2615+
MultiXactIdnextMultiXactId;
2616+
2617+
LWLockAcquire(MultiXactGenLock,LW_SHARED);
2618+
nextOffset=MultiXactState->nextOffset;
2619+
oldestMultiXactId=MultiXactState->oldestMultiXactId;
2620+
nextMultiXactId=MultiXactState->nextMXact;
2621+
LWLockRelease(MultiXactGenLock);
2622+
2623+
oldestOffset=find_multixact_start(oldestMultiXactId);
2624+
*members=nextOffset-oldestOffset;
2625+
*multixacts=nextMultiXactId-oldestMultiXactId;
2626+
}
2627+
2628+
/*
2629+
* Multixact members can be removed once the multixacts that refer to them
2630+
* are older than every datminxmid. autovacuum_multixact_freeze_max_age and
2631+
* vacuum_multixact_freeze_table_age work together to make sure we never have
2632+
* too many multixacts; we hope that, at least under normal circumstances,
2633+
* this will also be sufficient to keep us from using too many offsets.
2634+
* However, if the average multixact has many members, we might exhaust the
2635+
* members space while still using few enough members that these limits fail
2636+
* to trigger full table scans for relminmxid advancement. At that point,
2637+
* we'd have no choice but to start failing multixact-creating operations
2638+
* with an error.
2639+
*
2640+
* To prevent that, if more than a threshold portion of the members space is
2641+
* used, we effectively reduce autovacuum_multixact_freeze_max_age and
2642+
* to a value just less than the number of multixacts in use. We hope that
2643+
* this will quickly trigger autovacuuming on the table or tables with the
2644+
* oldest relminmxid, thus allowing datminmxid values to advance and removing
2645+
* some members.
2646+
*
2647+
* As the fraction of the member space currently in use grows, we become
2648+
* more aggressive in clamping this value. That not only causes autovacuum
2649+
* to ramp up, but also makes any manual vacuums the user issues more
2650+
* aggressive. This happens because vacuum_set_xid_limits() clamps the
2651+
* freeze table and and the minimum freeze age based on the effective
2652+
* autovacuum_multixact_freeze_max_age this function returns. In the worst
2653+
* case, we'll claim the freeze_max_age to zero, and every vacuum of any
2654+
* table will try to freeze every multixact.
2655+
*
2656+
* It's possible that these thresholds should be user-tunable, but for now
2657+
* we keep it simple.
2658+
*/
2659+
int
2660+
MultiXactMemberFreezeThreshold(void)
2661+
{
2662+
MultiXactOffsetmembers;
2663+
uint32multixacts;
2664+
uint32victim_multixacts;
2665+
doublefraction;
2666+
2667+
ReadMultiXactCounts(&multixacts,&members);
2668+
2669+
/* If member space utilization is low, no special action is required. */
2670+
if (members <=MULTIXACT_MEMBER_SAFE_THRESHOLD)
2671+
returnautovacuum_multixact_freeze_max_age;
2672+
2673+
/*
2674+
* Compute a target for relminmxid advancement. The number of multixacts
2675+
* we try to eliminate from the system is based on how far we are past
2676+
* MULTIXACT_MEMBER_SAFE_THRESHOLD.
2677+
*/
2678+
fraction= (double) (members-MULTIXACT_MEMBER_SAFE_THRESHOLD) /
2679+
(MULTIXACT_MEMBER_DANGER_THRESHOLD-MULTIXACT_MEMBER_SAFE_THRESHOLD);
2680+
victim_multixacts=multixacts*fraction;
2681+
2682+
/* fraction could be > 1.0, but lowest possible freeze age is zero */
2683+
if (victim_multixacts>multixacts)
2684+
return0;
2685+
returnmultixacts-victim_multixacts;
2686+
}
2687+
26002688
/*
26012689
* SlruScanDirectory callback.
26022690
*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
@@ -426,6 +426,7 @@ vacuum_set_xid_limits(Relation rel,
426426
{
427427
intfreezemin;
428428
intmxid_freezemin;
429+
inteffective_multixact_freeze_max_age;
429430
TransactionIdlimit;
430431
TransactionIdsafeLimit;
431432
MultiXactIdmxactLimit;
@@ -482,17 +483,24 @@ vacuum_set_xid_limits(Relation rel,
482483

483484
*freezeLimit=limit;
484485

486+
/*
487+
* Compute the multixact age for which freezing is urgent. This is
488+
* normally autovacuum_multixact_freeze_max_age, but may be less if we
489+
* are short of multixact member space.
490+
*/
491+
effective_multixact_freeze_max_age=MultiXactMemberFreezeThreshold();
492+
485493
/*
486494
* Determine the minimum multixact freeze age to use: as specified by
487495
* caller, or vacuum_multixact_freeze_min_age, but in any case not more
488-
* than halfautovacuum_multixact_freeze_max_age, so that autovacuums to
496+
* than halfeffective_multixact_freeze_max_age, so that autovacuums to
489497
* prevent MultiXact wraparound won't occur too frequently.
490498
*/
491499
mxid_freezemin=multixact_freeze_min_age;
492500
if (mxid_freezemin<0)
493501
mxid_freezemin=vacuum_multixact_freeze_min_age;
494502
mxid_freezemin=Min(mxid_freezemin,
495-
autovacuum_multixact_freeze_max_age /2);
503+
effective_multixact_freeze_max_age /2);
496504
Assert(mxid_freezemin >=0);
497505

498506
/* compute the cutoff multi, being careful to generate a valid value */
@@ -501,7 +509,7 @@ vacuum_set_xid_limits(Relation rel,
501509
mxactLimit=FirstMultiXactId;
502510

503511
safeMxactLimit=
504-
ReadNextMultiXactId()-autovacuum_multixact_freeze_max_age;
512+
ReadNextMultiXactId()-effective_multixact_freeze_max_age;
505513
if (safeMxactLimit<FirstMultiXactId)
506514
safeMxactLimit=FirstMultiXactId;
507515

@@ -556,7 +564,7 @@ vacuum_set_xid_limits(Relation rel,
556564
if (freezetable<0)
557565
freezetable=vacuum_multixact_freeze_table_age;
558566
freezetable=Min(freezetable,
559-
autovacuum_multixact_freeze_max_age*0.95);
567+
effective_multixact_freeze_max_age*0.95);
560568
Assert(freezetable >=0);
561569

562570
/*

‎src/backend/postmaster/autovacuum.c

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

305305
staticautovac_table*table_recheck_autovac(Oidrelid,HTAB*table_toast_map,
306-
TupleDescpg_class_desc);
306+
TupleDescpg_class_desc,
307+
inteffective_multixact_freeze_max_age);
307308
staticvoidrelation_needs_vacanalyze(Oidrelid,AutoVacOpts*relopts,
308309
Form_pg_classclassForm,
309310
PgStat_StatTabEntry*tabentry,
311+
inteffective_multixact_freeze_max_age,
310312
bool*dovacuum,bool*doanalyze,bool*wraparound);
311313

312314
staticvoidautovacuum_do_vac_analyze(autovac_table*tab,
@@ -1147,7 +1149,7 @@ do_start_worker(void)
11471149

11481150
/* Also determine the oldest datminmxid we will consider. */
11491151
recentMulti=ReadNextMultiXactId();
1150-
multiForceLimit=recentMulti-autovacuum_multixact_freeze_max_age;
1152+
multiForceLimit=recentMulti-MultiXactMemberFreezeThreshold();
11511153
if (multiForceLimit<FirstMultiXactId)
11521154
multiForceLimit-=FirstMultiXactId;
11531155

@@ -1936,6 +1938,7 @@ do_autovacuum(void)
19361938
BufferAccessStrategybstrategy;
19371939
ScanKeyDatakey;
19381940
TupleDescpg_class_desc;
1941+
inteffective_multixact_freeze_max_age;
19391942

19401943
/*
19411944
* StartTransactionCommand and CommitTransactionCommand will automatically
@@ -1965,6 +1968,13 @@ do_autovacuum(void)
19651968
*/
19661969
pgstat_vacuum_stat();
19671970

1971+
/*
1972+
* Compute the multixact age for which freezing is urgent. This is
1973+
* normally autovacuum_multixact_freeze_max_age, but may be less if we
1974+
* are short of multixact member space.
1975+
*/
1976+
effective_multixact_freeze_max_age=MultiXactMemberFreezeThreshold();
1977+
19681978
/*
19691979
* Find the pg_database entry and select the default freeze ages. We use
19701980
* zero in template and nonconnectable databases, else the system-wide
@@ -2057,6 +2067,7 @@ do_autovacuum(void)
20572067

20582068
/* Check if it needs vacuum or analyze */
20592069
relation_needs_vacanalyze(relid,relopts,classForm,tabentry,
2070+
effective_multixact_freeze_max_age,
20602071
&dovacuum,&doanalyze,&wraparound);
20612072

20622073
/*
@@ -2185,6 +2196,7 @@ do_autovacuum(void)
21852196
shared,dbentry);
21862197

21872198
relation_needs_vacanalyze(relid,relopts,classForm,tabentry,
2199+
effective_multixact_freeze_max_age,
21882200
&dovacuum,&doanalyze,&wraparound);
21892201

21902202
/* ignore analyze for toast tables */
@@ -2275,7 +2287,8 @@ do_autovacuum(void)
22752287
* the race condition is not closed but it is very small.
22762288
*/
22772289
MemoryContextSwitchTo(AutovacMemCxt);
2278-
tab=table_recheck_autovac(relid,table_toast_map,pg_class_desc);
2290+
tab=table_recheck_autovac(relid,table_toast_map,pg_class_desc,
2291+
effective_multixact_freeze_max_age);
22792292
if (tab==NULL)
22802293
{
22812294
/* someone else vacuumed the table, or it went away */
@@ -2482,7 +2495,8 @@ get_pgstat_tabentry_relid(Oid relid, bool isshared, PgStat_StatDBEntry *shared,
24822495
*/
24832496
staticautovac_table*
24842497
table_recheck_autovac(Oidrelid,HTAB*table_toast_map,
2485-
TupleDescpg_class_desc)
2498+
TupleDescpg_class_desc,
2499+
inteffective_multixact_freeze_max_age)
24862500
{
24872501
Form_pg_classclassForm;
24882502
HeapTupleclassTup;
@@ -2528,6 +2542,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
25282542
shared,dbentry);
25292543

25302544
relation_needs_vacanalyze(relid,avopts,classForm,tabentry,
2545+
effective_multixact_freeze_max_age,
25312546
&dovacuum,&doanalyze,&wraparound);
25322547

25332548
/* ignore ANALYZE for toast tables */
@@ -2655,6 +2670,7 @@ relation_needs_vacanalyze(Oid relid,
26552670
AutoVacOpts*relopts,
26562671
Form_pg_classclassForm,
26572672
PgStat_StatTabEntry*tabentry,
2673+
inteffective_multixact_freeze_max_age,
26582674
/* output params below */
26592675
bool*dovacuum,
26602676
bool*doanalyze,
@@ -2715,8 +2731,8 @@ relation_needs_vacanalyze(Oid relid,
27152731
:autovacuum_freeze_max_age;
27162732

27172733
multixact_freeze_max_age= (relopts&&relopts->multixact_freeze_max_age >=0)
2718-
?Min(relopts->multixact_freeze_max_age,autovacuum_multixact_freeze_max_age)
2719-
:autovacuum_multixact_freeze_max_age;
2734+
?Min(relopts->multixact_freeze_max_age,effective_multixact_freeze_max_age)
2735+
:effective_multixact_freeze_max_age;
27202736

27212737
av_enabled= (relopts ?relopts->enabled : true);
27222738

‎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