10000 Fix performance problems with autovacuum truncation in busy workloads. · danielcode/postgres@a79ae0b · GitHub
[go: up one dir, main page]
Skip to content

Commit a79ae0b

Browse files
kgrittnkgrittn
committed
Fix performance problems with autovacuum truncation in busy workloads.
In situations where there are over 8MB of empty pages at the end of a table, the truncation work for trailing empty pages takes longer than deadlock_timeout, and there is frequent access to the table by processes other than autovacuum, there was a problem with the autovacuum worker process being canceled by the deadlock checking code. The truncation work done by autovacuum up that point was lost, and the attempt tried again by a later autovacuum worker. The attempts could continue indefinitely without making progress, consuming resources and blocking other processes for up to deadlock_timeout each time. This patch has the autovacuum worker checking whether it is blocking any other thread at 20ms intervals. If such a condition develops, the autovacuum worker will persist the work it has done so far, release its lock on the table, and sleep in 50ms intervals for up to 5 seconds, hoping to be able to re-acquire the lock and try again. If it is unable to get the lock in that time, it moves on and a worker will try to continue later from the point this one left off. While this patch doesn't change the rules about when and what to truncate, it does cause the truncation to occur sooner, with less blocking, and with the consumption of fewer resources when there is contention for the table's lock. The only user-visible change other than improved performance is that the table size during truncation may change incrementally instead of just once. Backpatched to 9.0 from initial master commit at b19e425 -- before that the differences are too large to be clearly safe. Jan Wieck
1 parent 9a3ddec commit a79ae0b

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
@@ -46,6 +46,7 @@
4646
#include "commands/vacuum.h"
4747
#include "miscadmin.h"
4848
#include "pgstat.h"
49+
#include "portability/instr_time.h"
4950
#include "postmaster/autovacuum.h"
5051
#include "storage/bufmgr.h"
5152
#include "storage/freespace.h"
@@ -67,6 +68,17 @@
6768
#define REL_TRUNCATE_MINIMUM 1000
6869
#define REL_TRUNCATE_FRACTION 16
6970

71+
/*
72+
* Timing parameters for truncate locking heuristics.
73+
*
74+
* These were not exposed as user tunable GUC values because it didn't seem
75+
* that the potential for improvement was great enough to merit the cost of
76+
* supporting them.
77+
*/
78+
#define AUTOVACUUM_TRUNCATE_LOCK_CHECK_INTERVAL 20 /* ms */
79+
#define AUTOVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */
80+
#define AUTOVACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */
81+
7082
/*
7183
* Guesstimation of number of dead tuples per page. This is used to
7284
* provide an upper limit to memory allocated when vacuuming small
@@ -101,6 +113,7 @@ typedef struct LVRelStats
101113
ItemPointer dead_tuples; /* array of ItemPointerData */
102114
int num_index_scans;
103115
TransactionId latestRemovedXid;
116+
bool lock_waiter_detected;
104117
} LVRelStats;
105118

106119

@@ -191,6 +204,8 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
191204
vacrelstats->old_rel_pages = onerel->rd_rel->relpages;
192205
vacrelstats->old_rel_tuples = onerel->rd_rel->reltuples;
193206
vacrelstats->num_index_scans = 0;
207+
vacrelstats->pages_removed = 0;
208+
vacrelstats->lock_waiter_detected = false;
194209

195210
/* Open all indexes of the relation */
196211
vac_open_indexes(onerel, RowExclusiveLock, &nindexes, &Irel);
@@ -257,10 +272,17 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
257272
vacrelstats->hasindex,
258273
new_frozen_xid);
259274

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

265287
/* and log the action if appropriate */
266288
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
@@ -1255,80 +1277,124 @@ lazy_truncate_heap(Relation onerel, LVRelStats *vacrelstats)
12551277
BlockNumber old_rel_pages = vacrelstats->rel_pages;
12561278
BlockNumber new_rel_pages;
12571279
PGRUsage ru0;
1280+
int lock_retry;
12581281

12591282
pg_rusage_init(&ru0);
12601283

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

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

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

1304-
/*
1305-
* Okay to truncate.
1306-
*/
1307-
RelationTruncate(onerel, new_rel_pages);
1330+
pg_usleep(AUTOVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL);
1331+
}
13081332

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

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

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

13341400
/*
@@ -1340,6 +1406,12 @@ static BlockNumber
13401406
count_nondeletable_pages(Relation onerel, LVRelStats *vacrelstats)
13411407
{
13421408
BlockNumber blkno;
1409+
instr_time starttime;
1410+
instr_time currenttime;
1411+
instr_time elapsed;
1412+
1413+
/* Initialize the starttime if we check for conflicting lock requests */
1414+
INSTR_TIME_SET_CURRENT(starttime);
13431415

13441416
/* Strange coding of loop control is needed because blkno is unsigned */
13451417
blkno = vacrelstats->rel_pages;
@@ -1351,6 +1423,36 @@ count_nondeletable_pages(Relation onerel, LVRelStats *vacrelstats)
13511423
maxoff;
13521424
bool hastup;
13531425

1426+
/*
1427+
* Check if another process requests a lock on our relation. We are
1428+
* holding an AccessExclusiveLock here, so they will be waiting. We
1429+
* only do this in autovacuum_truncate_lock_check millisecond
1430+
* intervals, and we only check if that interval has elapsed once
1431+
* every 32 blocks to keep the number of system calls and actual
1432+
* shared lock table lookups to a minimum.
1433+
*/
1434+
if ((blkno % 32) == 0)
1435+
{
1436+
INSTR_TIME_SET_CURRENT(currenttime);
1437+
elapsed = currenttime;
1438+
INSTR_TIME_SUBTRACT(elapsed, starttime);
1439+
if ((INSTR_TIME_GET_MICROSEC(elapsed) / 1000)
1440+
>= AUTOVACUUM_TRUNCATE_LOCK_CHECK_INTERVAL)
1441+
{
1442+
if (LockHasWaitersRelation(onerel, AccessExclusiveLock))
1443+
{
1444+
ereport(elevel,
1445+
(errmsg("\"%s\": suspending truncate "
1446+
"due to conflicting lock request",
1447+
RelationGetRelationName(onerel))));
1448+
1449+
vacrelstats->lock_waiter_detected = true;
1450+
return blkno;
1451+
}
1452+
starttime = currenttime;
1453+
}
1454+
}
1455+
13541456
/*
13551457
* We don't insert a vacuum delay point here, because we have an
13561458
* 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(Relation relation, LOCKMODE lockmode)
243+
{
244+
LOCKTAG tag;
245+
246+
SET_LOCKTAG_RELATION(tag,
247+
relation->rd_lockInfo.lockRelId.dbId,
248+
relation->rd_lockInfo.lockRelId.relId);
249+
250+
return LockHasWaiters(&tag, lockmode, false);
251+
}
252+
235253
/*
236254
* LockRelationIdForSession
237255
*

0 commit comments

Comments
 (0)
0