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

Commite2ac3fe

Browse files
committed
Speed up rechecking if relation needs to be vacuumed or analyze in autovacuum.
After autovacuum collects the relations to vacuum or analyze, it recheckswhether each relation still needs to be vacuumed or analyzed before actuallydoing that. Previously this recheck could be a significant overheadespecially when there were a very large number of relations. This wasbecause each recheck forced the statistics to be refreshed, and the refreshof the statistics for a very large number of relations could cause heavyoverhead. There was the report that this issue caused autovacuum workersto have gotten “stuck” in a tight loop of table_recheck_autovac() thatrechecks whether a relation needs to be vacuumed or analyzed.This commit speeds up the recheck by making autovacuum worker reusethe previously-read statistics for the recheck if possible. Then if that"stale" statistics says that a relation still needs to be vacuumed or analyzed,autovacuum refreshes the statistics and does the recheck again.The benchmark shows that the more relations exist and autovacuum workersare running concurrently, the more this change reduces the autovacuumexecution time. For example, when there are 20,000 tables and 10 autovacuumworkers are running, the benchmark showed that the change improvedthe performance of autovacuum more than three times. On the other hand,even when there are only 1000 tables and only a single autovacuum workeris running, the benchmark didn't show any big performance regression bythe change.Firstly POC patch was proposed by Jim Nasby. As the result of discussion,we used Tatsuhito Kasahara's version of the patch using the approachsuggested by Tom Lane.Reported-by: Jim NasbyAuthor: Tatsuhito KasaharaReviewed-by: Masahiko Sawada, Fujii MasaoDiscussion:https://postgr.es/m/3FC6C2F2-8A47-44C0-B997-28830B5716D0@amazon.com
1 parent4e43ee8 commite2ac3fe

File tree

1 file changed

+89
-19
lines changed

1 file changed

+89
-19
lines changed

‎src/backend/postmaster/autovacuum.c

Lines changed: 89 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -328,6 +328,10 @@ static void FreeWorkerInfo(int code, Datum arg);
328328
staticautovac_table*table_recheck_autovac(Oidrelid,HTAB*table_toast_map,
329329
TupleDescpg_class_desc,
330330
inteffective_multixact_freeze_max_age);
331+
staticvoidrecheck_relation_needs_vacanalyze(Oidrelid,AutoVacOpts*avopts,
332+
Form_pg_classclassForm,
333+
inteffective_multixact_freeze_max_age,
334+
bool*dovacuum,bool*doanalyze,bool*wraparound);
331335
staticvoidrelation_needs_vacanalyze(Oidrelid,AutoVacOpts*relopts,
332336
Form_pg_classclassForm,
333337
PgStat_StatTabEntry*tabentry,
@@ -2797,17 +2801,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
27972801
booldovacuum;
27982802
booldoanalyze;
27992803
autovac_table*tab=NULL;
2800-
PgStat_StatTabEntry*tabentry;
2801-
PgStat_StatDBEntry*shared;
2802-
PgStat_StatDBEntry*dbentry;
28032804
boolwraparound;
28042805
AutoVacOpts*avopts;
2805-
2806-
/* use fresh stats */
2807-
autovac_refresh_stats();
2808-
2809-
shared=pgstat_fetch_stat_dbentry(InvalidOid);
2810-
dbentry=pgstat_fetch_stat_dbentry(MyDatabaseId);
2806+
staticboolreuse_stats= false;
28112807

28122808
/* fetch the relation's relcache entry */
28132809
classTup=SearchSysCacheCopy1(RELOID,ObjectIdGetDatum(relid));
@@ -2831,17 +2827,38 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
28312827
avopts=&hentry->ar_reloptions;
28322828
}
28332829

2834-
/* fetch the pgstat table entry */
2835-
tabentry=get_pgstat_tabentry_relid(relid,classForm->relisshared,
2836-
shared,dbentry);
2830+
/*
2831+
* Reuse the stats to recheck whether a relation needs to be vacuumed or
2832+
* analyzed if it was reloaded before and has not been cleared yet. This
2833+
* is necessary to avoid frequent refresh of stats, especially when there
2834+
* are very large number of relations and the refresh can cause lots of
2835+
* overhead.
2836+
*
2837+
* If we determined that a relation needs to be vacuumed or analyzed,
2838+
* based on the old stats, we refresh stats and recheck the necessity
2839+
* again. Because a relation may have already been vacuumed or analyzed by
2840+
* someone since the last reload of stats.
2841+
*/
2842+
if (reuse_stats)
2843+
{
2844+
recheck_relation_needs_vacanalyze(relid,avopts,classForm,
2845+
effective_multixact_freeze_max_age,
2846+
&dovacuum,&doanalyze,&wraparound);
28372847

2838-
relation_needs_vacanalyze(relid,avopts,classForm,tabentry,
2839-
effective_multixact_freeze_max_age,
2840-
&dovacuum,&doanalyze,&wraparound);
2848+
/* Quick exit if a relation doesn't need to be vacuumed or analyzed */
2849+
if (!doanalyze&& !dovacuum)
2850+
{
2851+
heap_freetuple(classTup);
2852+
returnNULL;
2853+
}
2854+
}
28412855

2842-
/* ignore ANALYZE for toast tables */
2843-
if (classForm->relkind==RELKIND_TOASTVALUE)
2844-
doanalyze= false;
2856+
/* Use fresh stats and recheck again */
2857+
autovac_refresh_stats();
2858+
2859+
recheck_relation_needs_vacanalyze(relid,avopts,classForm,
2860+
effective_multixact_freeze_max_age,
2861+
&dovacuum,&doanalyze,&wraparound);
28452862

28462863
/* OK, it needs something done */
28472864
if (doanalyze||dovacuum)
@@ -2929,13 +2946,66 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
29292946
tab->at_dobalance=
29302947
!(avopts&& (avopts->vacuum_cost_limit>0||
29312948
avopts->vacuum_cost_delay>0));
2949+
2950+
/*
2951+
* When we decide to do vacuum or analyze, the existing stats cannot
2952+
* be reused in the next cycle because it's cleared at the end of
2953+
* vacuum or analyze (by AtEOXact_PgStat()).
2954+
*/
2955+
reuse_stats= false;
2956+
}
2957+
else
2958+
{
2959+
/*
2960+
* If neither vacuum nor analyze is necessary, the existing stats is
2961+
* not cleared and can be reused in the next cycle.
2962+
*/
2963+
reuse_stats= true;
29322964
}
29332965

29342966
heap_freetuple(classTup);
2935-
29362967
returntab;
29372968
}
29382969

2970+
/*
2971+
* recheck_relation_needs_vacanalyze
2972+
*
2973+
* Subroutine for table_recheck_autovac.
2974+
*
2975+
* Fetch the pgstat of a relation and recheck whether a relation
2976+
* needs to be vacuumed or analyzed.
2977+
*/
2978+
staticvoid
2979+
recheck_relation_needs_vacanalyze(Oidrelid,
2980+
AutoVacOpts*avopts,
2981+
Form_pg_classclassForm,
2982+
inteffective_multixact_freeze_max_age,
2983+
bool*dovacuum,
2984+
bool*doanalyze,
2985+
bool*wraparound)
2986+
{
2987+
PgStat_StatTabEntry*tabentry;
2988+
PgStat_StatDBEntry*shared=NULL;
2989+
PgStat_StatDBEntry*dbentry=NULL;
2990+
2991+
if (classForm->relisshared)
2992+
shared=pgstat_fetch_stat_dbentry(InvalidOid);
2993+
else
2994+
dbentry=pgstat_fetch_stat_dbentry(MyDatabaseId);
2995+
2996+
/* fetch the pgstat table entry */
2997+
tabentry=get_pgstat_tabentry_relid(relid,classForm->relisshared,
2998+
shared,dbentry);
2999+
3000+
relation_needs_vacanalyze(relid,avopts,classForm,tabentry,
3001+
effective_multixact_freeze_max_age,
3002+
dovacuum,doanalyze,wraparound);
3003+
3004+
/* ignore ANALYZE for toast tables */
3005+
if (classForm->relkind==RELKIND_TOASTVALUE)
3006+
*doanalyze= false;
3007+
}
3008+
29393009
/*
29403010
* relation_needs_vacanalyze
29413011
*

0 commit comments

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