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

Commita734fd5

Browse files
committed
autovacuum: Drop orphan temp tables more quickly but with more caution.
Previously, we only dropped an orphan temp table when it became oldenough to threaten wraparound; instead, doing it immediately. Theonly value of waiting is that someone might be able to examine thecontents of the orphan temp table for forensic purposes, but it'spretty difficult to actually do that and few users will wish to do so.On the flip side, not performing the drop immediately generates logspam and bloats pg_class.In addition, per a report from Grigory Smolkin, if a temporary schemacontains a very large number of temporary tables, a backend attemptingto clear the temporary schema might fail due to lock table exhaustion.It's helpful for autovacuum to clean up after such cases, and we don'twant it to wait for wraparound to threaten before doing so. Toprevent autovacuum from failing in the same manner as a backend tryingto drop an entire temp schema, remove orphan temp tables in batches of50, committing after each batch, so that we don't accumulate anunbounded number of locks. If a drop fails, retry other orphan tablesthat need to be dropped up to 10 times before giving up. With thissystem, if a backend does fail to clean a temporary schema due tolock table exhaustion, autovacuum should hopefully put things rightthe next time it processes the database.Discussion: CAB7nPqSbYT6dRwsXVgiKmBdL_ARemfDZMPA+RPeC_ge0GK70hA@mail.gmail.comMichael Paquier, with a bunch of comment changes by me.
1 parentf24cf96 commita734fd5

File tree

1 file changed

+127
-26
lines changed

1 file changed

+127
-26
lines changed

‎src/backend/postmaster/autovacuum.c

Lines changed: 127 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -130,6 +130,17 @@ intLog_autovacuum_min_duration = -1;
130130
#defineMIN_AUTOVAC_SLEEPTIME 100.0/* milliseconds */
131131
#defineMAX_AUTOVAC_SLEEPTIME 300/* seconds */
132132

133+
/*
134+
* Maximum number of orphan temporary tables to drop in a single transaction.
135+
* (If this is too high, we might run out of heavyweight locks.)
136+
*/
137+
#defineMAX_ORPHAN_ITEMS50
138+
139+
/*
140+
* After this many failures, stop trying to drop orphan temporary tables.
141+
*/
142+
#defineMAX_ORPHAN_DROP_FAILURE10
143+
133144
/* Flags to tell if we are in an autovacuum process */
134145
staticboolam_autovacuum_launcher= false;
135146
staticboolam_autovacuum_worker= false;
@@ -1887,6 +1898,8 @@ do_autovacuum(void)
18871898
HeapScanDescrelScan;
18881899
Form_pg_databasedbForm;
18891900
List*table_oids=NIL;
1901+
List*orphan_oids=NIL;
1902+
List*pending_oids=NIL;
18901903
HASHCTLctl;
18911904
HTAB*table_toast_map;
18921905
ListCell*volatilecell;
@@ -1895,6 +1908,7 @@ do_autovacuum(void)
18951908
BufferAccessStrategybstrategy;
18961909
ScanKeyDatakey;
18971910
TupleDescpg_class_desc;
1911+
intorphan_failures;
18981912
inteffective_multixact_freeze_max_age;
18991913

19001914
/*
@@ -2038,33 +2052,11 @@ do_autovacuum(void)
20382052
if (backendID==MyBackendId||BackendIdGetProc(backendID)==NULL)
20392053
{
20402054
/*
2041-
* We found an orphan temp table (which was probably left
2042-
* behind by a crashed backend). If it's so old as to need
2043-
* vacuum for wraparound, forcibly drop it. Otherwise just
2044-
* log a complaint.
2055+
* We found an orphan temp table which was probably left
2056+
* behind by a crashed backend. Remember it, so we can attempt
2057+
* to drop it.
20452058
*/
2046-
if (wraparound)
2047-
{
2048-
ObjectAddressobject;
2049-
2050-
ereport(LOG,
2051-
(errmsg("autovacuum: dropping orphan temp table \"%s\".\"%s\" in database \"%s\"",
2052-
get_namespace_name(classForm->relnamespace),
2053-
NameStr(classForm->relname),
2054-
get_database_name(MyDatabaseId))));
2055-
object.classId=RelationRelationId;
2056-
object.objectId=relid;
2057-
object.objectSubId=0;
2058-
performDeletion(&object,DROP_CASCADE,PERFORM_DELETION_INTERNAL);
2059-
}
2060-
else
2061-
{
2062-
ereport(LOG,
2063-
(errmsg("autovacuum: found orphan temp table \"%s\".\"%s\" in database \"%s\"",
2064-
get_namespace_name(classForm->relnamespace),
2065-
NameStr(classForm->relname),
2066-
get_database_name(MyDatabaseId))));
2067-
}
2059+
orphan_oids=lappend_oid(orphan_oids,relid);
20682060
}
20692061
}
20702062
else
@@ -2161,6 +2153,115 @@ do_autovacuum(void)
21612153
heap_endscan(relScan);
21622154
heap_close(classRel,AccessShareLock);
21632155

2156+
/*
2157+
* Loop through orphan temporary tables and drop them in batches. If
2158+
* we're unable to drop one particular table, we'll retry to see if we
2159+
* can drop others, but if we fail too many times we'll give up and proceed
2160+
* with our regular work, so that this step hopefully can't wedge
2161+
* autovacuum for too long.
2162+
*/
2163+
while (list_length(orphan_oids)>0&&
2164+
orphan_failures<MAX_ORPHAN_DROP_FAILURE)
2165+
{
2166+
Oidrelid=linitial_oid(orphan_oids);
2167+
ObjectAddressobject;
2168+
char*namespace=get_namespace_name(get_rel_namespace(relid));
2169+
char*relname=get_rel_name(relid);
2170+
2171+
orphan_oids=list_delete_first(orphan_oids);
2172+
2173+
PG_TRY();
2174+
{
2175+
ereport(LOG,
2176+
(errmsg("autovacuum: dropping orphan temp table \"%s\".\"%s\" in database \"%s\"",
2177+
namespace,relname,
2178+
get_database_name(MyDatabaseId))));
2179+
object.classId=RelationRelationId;
2180+
object.objectId=relid;
2181+
object.objectSubId=0;
2182+
performDeletion(&object,DROP_CASCADE,PERFORM_DELETION_INTERNAL);
2183+
2184+
/*
2185+
* This orphan table has been dropped correctly, add it to the
2186+
* list of tables whose drop should be attempted again if an
2187+
* error after in the same transaction.
2188+
*/
2189+
pending_oids=lappend_oid(pending_oids,relid);
2190+
}
2191+
PG_CATCH();
2192+
{
2193+
/* Abort the current transaction. */
2194+
HOLD_INTERRUPTS();
2195+
2196+
errcontext("dropping of orphan temp table \"%s\".\"%s\" in database \"%s\"",
2197+
namespace,relname,
2198+
get_database_name(MyDatabaseId));
2199+
2200+
EmitErrorReport();
2201+
2202+
/* this resets the PGXACT flags too */
2203+
AbortOutOfAnyTransaction();
2204+
FlushErrorState();
2205+
2206+
/*
2207+
* Any tables were succesfully dropped before the failure now
2208+
* need to be dropped again. Add them back into the list, but
2209+
* don't retry the table that failed.
2210+
*/
2211+
orphan_oids=list_concat(orphan_oids,pending_oids);
2212+
orphan_failures++;
2213+
2214+
/* Start a new transaction. */
2215+
StartTransactionCommand();
2216+
2217+
/* StartTransactionCommand changed elsewhere the memory context */
2218+
MemoryContextSwitchTo(AutovacMemCxt);
2219+
2220+
RESUME_INTERRUPTS();
2221+
}
2222+
PG_END_TRY();
2223+
2224+
/*
2225+
* If we've successfully dropped quite a few tables, commit the
2226+
* transaction and begin a new one. The main point of this is to
2227+
* avoid accumulating too many locks and blowing out the lock table,
2228+
* but it also minimizes the amount of work that will have to be rolled
2229+
* back if we fail to drop some table later in the list.
2230+
*/
2231+
if (list_length(pending_oids) >=MAX_ORPHAN_ITEMS)
2232+
{
2233+
CommitTransactionCommand();
2234+
StartTransactionCommand();
2235+
2236+
/* StartTransactionCommand changed elsewhere */
2237+
MemoryContextSwitchTo(AutovacMemCxt);
2238+
2239+
list_free(pending_oids);
2240+
pending_oids=NIL;
2241+
}
2242+
2243+
pfree(relname);
2244+
pfree(namespace);
2245+
}
2246+
2247+
/*
2248+
* Commit current transaction to finish the cleanup done previously and
2249+
* restart a new one to not bloat the activity of the following steps.
2250+
* This needs to happen only if there are any items thought as previously
2251+
* pending, but are actually not as the last transaction doing the cleanup
2252+
* has been successful.
2253+
*/
2254+
if (list_length(pending_oids)>0)
2255+
{
2256+
CommitTransactionCommand();
2257+
StartTransactionCommand();
2258+
2259+
/* StartTransactionCommand changed elsewhere */
2260+
MemoryContextSwitchTo(AutovacMemCxt);
2261+
2262+
list_free(pending_oids);
2263+
}
2264+
21642265
/*
21652266
* Create a buffer access strategy object for VACUUM to use. We want to
21662267
* use the same one across all the vacuum operations we perform, since the

0 commit comments

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