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

Commitb19e425

Browse files
committed
Fix performance problems with autovacuum truncation in busy workloads.
In situations where there are over 8MB of empty pages at the end ofa table, the truncation work for trailing empty pages takes longerthan deadlock_timeout, and there is frequent access to the table byprocesses other than autovacuum, there was a problem with theautovacuum worker process being canceled by the deadlock checkingcode. The truncation work done by autovacuum up that point waslost, and the attempt tried again by a later autovacuum worker. Theattempts could continue indefinitely without making progress,consuming resources and blocking other processes for up todeadlock_timeout each time.This patch has the autovacuum worker checking whether it isblocking any other thread at 20ms intervals. If such a conditiondevelops, the autovacuum worker will persist the work it has doneso far, release its lock on the table, and sleep in 50ms intervalsfor up to 5 seconds, hoping to be able to re-acquire the lock andtry again. If it is unable to get the lock in that time, it moveson and a worker will try to continue later from the point this oneleft off.While this patch doesn't change the rules about when and what totruncate, it does cause the truncation to occur sooner, with lessblocking, and with the consumption of fewer resources when there iscontention for the table's lock.The only user-visible change other than improved performance isthat the table size during truncation may change incrementallyinstead of just once.This problem exists in all supported versions but is infrequentlyreported, although some reports of performance problems whenautovacuum runs might be caused by this. Initial commit is just themaster branch, but this should probably be backpatched once thebuild farm and general developer usage confirm that there are nosurprising effects.Jan Wieck
1 parente95c4bd commitb19e425

File tree

5 files changed

+279
-64
lines changed

5 files changed

+279
-64
lines changed

‎src/backend/commands/vacuumlazy.c

Lines changed: 166 additions & 64 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,7 @@
4848
#include"commands/vacuum.h"
4949
#include"miscadmin.h"
5050
#include"pgstat.h"
51+
#include"portability/instr_time.h"
5152
#include"postmaster/autovacuum.h"
5253
#include"storage/bufmgr.h"
5354
#include"storage/freespace.h"
@@ -69,6 +70,17 @@
6970
#defineREL_TRUNCATE_MINIMUM1000
7071
#defineREL_TRUNCATE_FRACTION16
7172

73+
/*
74+
* Timing parameters for truncate locking heuristics.
75+
*
76+
* These were not exposed as user tunable GUC values because it didn't seem
77+
* that the potential for improvement was great enough to merit the cost of
78+
* supporting them.
79+
*/
80+
#defineAUTOVACUUM_TRUNCATE_LOCK_CHECK_INTERVAL20/* ms */
81+
#defineAUTOVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL50/* ms */
82+
#defineAUTOVACUUM_TRUNCATE_LOCK_TIMEOUT5000/* ms */
83+
7284
/*
7385
* Guesstimation of number of dead tuples per page. This is used to
7486
* provide an upper limit to memory allocated when vacuuming small
@@ -103,6 +115,7 @@ typedef struct LVRelStats
103115
ItemPointerdead_tuples;/* array of ItemPointerData */
104116
intnum_index_scans;
105117
TransactionIdlatestRemovedXid;
118+
boollock_waiter_detected;
106119
}LVRelStats;
107120

108121

@@ -193,6 +206,8 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
193206
vacrelstats->old_rel_pages=onerel->rd_rel->relpages;
194207
vacrelstats->old_rel_tuples=onerel->rd_rel->reltuples;
195208
vacrelstats->num_index_scans=0;
209+
vacrelstats->pages_removed=0;
210+
vacrelstats->lock_waiter_detected= false;
196211

197212
/* Open all indexes of the relation */
198213
vac_open_indexes(onerel,RowExclusiveLock,&nindexes,&Irel);
@@ -259,10 +274,17 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
259274
vacrelstats->hasindex,
260275
new_frozen_xid);
261276

262-
/* report results to the stats collector, too */
263-
pgstat_report_vacuum(RelationGetRelid(onerel),
264-
onerel->rd_rel->relisshared,
265-
new_rel_tuples);
277+
/*
278+
* Report results to the stats collector, too. An early terminated
279+
* lazy_truncate_heap attempt suppresses the message and also cancels the
280+
* execution of ANALYZE, if that was ordered.
281+
*/
282+
if (!vacrelstats->lock_waiter_detected)
283+
pgstat_report_vacuum(RelationGetRelid(onerel),
284+
onerel->rd_rel->relisshared,
285+
new_rel_tuples);
286+
else
287+
vacstmt->options &= ~VACOPT_ANALYZE;
266288

267289
/* and log the action if appropriate */
268290
if (IsAutoVacuumWorkerProcess()&&Log_autovacuum_min_duration >=0)
@@ -1257,80 +1279,124 @@ lazy_truncate_heap(Relation onerel, LVRelStats *vacrelstats)
12571279
BlockNumberold_rel_pages=vacrelstats->rel_pages;
12581280
BlockNumbernew_rel_pages;
12591281
PGRUsageru0;
1282+
intlock_retry;
12601283

12611284
pg_rusage_init(&ru0);
12621285

12631286
/*
1264-
* We need full exclusive lock on the relation in order to do truncation.
1265-
* If we can't get it, give up rather than waiting --- we don't want to
1266-
* block other backends, and we don't want to deadlock (which is quite
1267-
* possible considering we already hold a lower-grade lock).
1268-
*/
1269-
if (!ConditionalLockRelation(onerel,AccessExclusiveLock))
1270-
return;
1271-
1272-
/*
1273-
* Now that we have exclusive lock, look to see if the rel has grown
1274-
* whilst we were vacuuming with non-exclusive lock. If so, give up; the
1275-
* newly added pages presumably contain non-deletable tuples.
1287+
* Loop until no more truncating can be done.
12761288
*/
1277-
new_rel_pages=RelationGetNumberOfBlocks(onerel);
1278-
if (new_rel_pages!=old_rel_pages)
1289+
do
12791290
{
12801291
/*
1281-
*Note: we intentionally don't update vacrelstats->rel_pages withthe
1282-
*new rel size here.If wedid, it would amount to assuming that the
1283-
*new pages are empty, which is unlikely.Leaving the numbers alone
1284-
*amounts to assuming that the new pages have the same tuple density
1285-
*as existing ones, which is less unlikely.
1292+
*We need full exclusive lock onthe relation in order to do
1293+
*truncation.If wecan't get it, give up rather than waiting --- we
1294+
*don't want to block other backends, and we don't want to deadlock
1295+
*(which is quite possible considering we already hold a lower-grade
1296+
*lock).
12861297
*/
1287-
UnlockRelation(onerel,AccessExclusiveLock);
1288-
return;
1289-
}
1298+
vacrelstats->lock_waiter_detected= false;
1299+
lock_retry=0;
1300+
while (true)
1301+
{
1302+
if (ConditionalLockRelation(onerel,AccessExclusiveLock))
1303+
break;
12901304

1291-
/*
1292-
* Scan backwards from the end to verify that the end pages actually
1293-
* contain no tuples. This is *necessary*, not optional, because other
1294-
* backends could have added tuples to these pages whilst we were
1295-
* vacuuming.
1296-
*/
1297-
new_rel_pages=count_nondeletable_pages(onerel,vacrelstats);
1305+
/*
1306+
* Check for interrupts while trying to (re-)acquire the exclusive
1307+
* lock.
1308+
*/
1309+
CHECK_FOR_INTERRUPTS();
12981310

1299-
if (new_rel_pages >=old_rel_pages)
1300-
{
1301-
/* can't do anything after all */
1302-
UnlockRelation(onerel,AccessExclusiveLock);
1303-
return;
1304-
}
1311+
if (++lock_retry> (AUTOVACUUM_TRUNCATE_LOCK_TIMEOUT /
1312+
AUTOVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL))
1313+
{
1314+
/*
1315+
* We failed to establish the lock in the specified number of
1316+
* retries. This means we give up truncating. Suppress the
1317+
* ANALYZE step. Doing an ANALYZE at this point will reset the
1318+
* dead_tuple_count in the stats collector, so we will not get
1319+
* called by the autovacuum launcher again to do the truncate.
1320+
*/
1321+
vacrelstats->lock_waiter_detected= true;
1322+
ereport(LOG,
1323+
(errmsg("automatic vacuum of table \"%s.%s.%s\": "
1324+
"cannot (re)acquire exclusive "
1325+
"lock for truncate scan",
1326+
get_database_name(MyDatabaseId),
1327+
get_namespace_name(RelationGetNamespace(onerel)),
1328+
RelationGetRelationName(onerel))));
1329+
return;
1330+
}
13051331

1306-
/*
1307-
* Okay to truncate.
1308-
*/
1309-
RelationTruncate(onerel,new_rel_pages);
1332+
pg_usleep(AUTOVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL);
1333+
}
13101334

1311-
/*
1312-
* We can release the exclusive lock as soon as we have truncated.Other
1313-
* backends can't safely access the relation until they have processed the
1314-
* smgr invalidation that smgrtruncate sent out ... but that should happen
1315-
* as part of standard invalidation processing once they acquire lock on
1316-
* the relation.
1317-
*/
1318-
UnlockRelation(onerel,AccessExclusiveLock);
1335+
/*
1336+
* Now that we have exclusive lock, look to see if the rel has grown
1337+
* whilst we were vacuuming with non-exclusive lock. If so, give up;
1338+
* the newly added pages presumably contain non-deletable tuples.
1339+
*/
1340+
new_rel_pages=RelationGetNumberOfBlocks(onerel);
1341+
if (new_rel_pages!=old_rel_pages)
1342+
{
1343+
/*
1344+
* Note: we intentionally don't update vacrelstats->rel_pages with
1345+
* the new rel size here. If we did, it would amount to assuming
1346+
* that the new pages are empty, which is unlikely. Leaving the
1347+
* numbers alone amounts to assuming that the new pages have the
1348+
* same tuple density as existing ones, which is less unlikely.
1349+
*/
1350+
UnlockRelation(onerel,AccessExclusiveLock);
1351+
return;
1352+
}
13191353

1320-
/*
1321-
*Update statistics. Here, it *is* correct to adjust rel_pages without
1322-
*also touching reltuples, since the tuple count wasn't changed by the
1323-
*truncation.
1324-
*/
1325-
vacrelstats->rel_pages=new_rel_pages;
1326-
vacrelstats->pages_removed=old_rel_pages-new_rel_pages;
1354+
/*
1355+
*Scan backwards from the end to verify that the end pages actually
1356+
*contain no tuples. This is *necessary*, not optional, because
1357+
*other backends could have added tuples to these pages whilst we
1358+
* were vacuuming.
1359+
*/
1360+
new_rel_pages=count_nondeletable_pages(onerel,vacrelstats);
13271361

1328-
ereport(elevel,
1329-
(errmsg("\"%s\": truncated %u to %u pages",
1330-
RelationGetRelationName(onerel),
1331-
old_rel_pages,new_rel_pages),
1332-
errdetail("%s.",
1333-
pg_rusage_show(&ru0))));
1362+
if (new_rel_pages >=old_rel_pages)
1363+
{
1364+
/* can't do anything after all */
1365+
UnlockRelation(onerel,AccessExclusiveLock);
1366+
return;
1367+
}
1368+
1369+
/*
1370+
* Okay to truncate.
1371+
*/
1372+
RelationTruncate(onerel,new_rel_pages);
1373+
1374+
/*
1375+
* We can release the exclusive lock as soon as we have truncated.
1376+
* Other backends can't safely access the relation until they have
1377+
* processed the smgr invalidation that smgrtruncate sent out ... but
1378+
* that should happen as part of standard invalidation processing once
1379+
* they acquire lock on the relation.
1380+
*/
1381+
UnlockRelation(onerel,AccessExclusiveLock);
1382+
1383+
/*
1384+
* Update statistics. Here, it *is* correct to adjust rel_pages
1385+
* without also touching reltuples, since the tuple count wasn't
1386+
* changed by the truncation.
1387+
*/
1388+
vacrelstats->pages_removed+=old_rel_pages-new_rel_pages;
1389+
vacrelstats->rel_pages=new_rel_pages;
1390+
1391+
ereport(elevel,
1392+
(errmsg("\"%s\": truncated %u to %u pages",
1393+
RelationGetRelationName(onerel),
1394+
old_rel_pages,new_rel_pages),
1395+
errdetail("%s.",
1396+
pg_rusage_show(&ru0))));
1397+
old_rel_pages=new_rel_pages;
1398+
}while (new_rel_pages>vacrelstats->nonempty_pages&&
1399+
vacrelstats->lock_waiter_detected);
13341400
}
13351401

13361402
/*
@@ -1342,6 +1408,12 @@ static BlockNumber
13421408
count_nondeletable_pages(Relationonerel,LVRelStats*vacrelstats)
13431409
{
13441410
BlockNumberblkno;
1411+
instr_timestarttime;
1412+
instr_timecurrenttime;
1413+
instr_timeelapsed;
1414+
1415+
/* Initialize the starttime if we check for conflicting lock requests */
1416+
INSTR_TIME_SET_CURRENT(starttime);
13451417

13461418
/* Strange coding of loop control is needed because blkno is unsigned */
13471419
blkno=vacrelstats->rel_pages;
@@ -1353,6 +1425,36 @@ count_nondeletable_pages(Relation onerel, LVRelStats *vacrelstats)
13531425
maxoff;
13541426
boolhastup;
13551427

1428+
/*
1429+
* Check if another process requests a lock on our relation. We are
1430+
* holding an AccessExclusiveLock here, so they will be waiting. We
1431+
* only do this in autovacuum_truncate_lock_check millisecond
1432+
* intervals, and we only check if that interval has elapsed once
1433+
* every 32 blocks to keep the number of system calls and actual
1434+
* shared lock table lookups to a minimum.
1435+
*/
1436+
if ((blkno %32)==0)
1437+
{
1438+
INSTR_TIME_SET_CURRENT(currenttime);
1439+
elapsed=currenttime;
1440+
INSTR_TIME_SUBTRACT(elapsed,starttime);
1441+
if ((INSTR_TIME_GET_MICROSEC(elapsed) /1000)
1442+
>=AUTOVACUUM_TRUNCATE_LOCK_CHECK_INTERVAL)
1443+
{
1444+
if (LockHasWaitersRelation(onerel,AccessExclusiveLock))
1445+
{
1446+
ereport(elevel,
1447+
(errmsg("\"%s\": suspending truncate "
1448+
"due to conflicting lock request",
1449+
RelationGetRelationName(onerel))));
1450+
1451+
vacrelstats->lock_waiter_detected= true;
1452+
returnblkno;
1453+
}
1454+
starttime=currenttime;
1455+
}
1456+
}
1457+
13561458
/*
13571459
* We don't insert a vacuum delay point here, because we have an
13581460
* exclusive lock on the table which we want to hold for as short a

‎src/backend/storage/lmgr/lmgr.c

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -232,6 +232,24 @@ UnlockRelation(Relation relation, LOCKMODE lockmode)
232232
LockRelease(&tag,lockmode, false);
233233
}
234234

235+
/*
236+
*LockHasWaitersRelation
237+
*
238+
* This is a functiion to check if someone else is waiting on a
239+
* lock, we are currently holding.
240+
*/
241+
bool
242+
LockHasWaitersRelation(Relationrelation,LOCKMODElockmode)
243+
{
244+
LOCKTAGtag;
245+
246+
SET_LOCKTAG_RELATION(tag,
247+
relation->rd_lockInfo.lockRelId.dbId,
248+
relation->rd_lockInfo.lockRelId.relId);
249+
250+
returnLockHasWaiters(&tag,lockmode, false);
251+
}
252+
235253
/*
236254
*LockRelationIdForSession
237255
*

0 commit comments

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