pgsql: Fix performance problems with autovacuum truncation in busy work - Mailing list pgsql-committers

From Kevin Grittner
Subject pgsql: Fix performance problems with autovacuum truncation in busy work
Date
Msg-id E1Ty6Db-0008Co-Ty@gemulon.postgresql.org
Whole thread Raw
List pgsql-committers
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
b19e4250b45e91c9cbdd18d35ea6391ab5961c8d -- before that the
differences are too large to be clearly safe.

Jan Wieck

Branch
------
REL9_0_STABLE

Details
-------
http://git.postgresql.org/pg/commitdiff/56d2975c3cae0bc795baf1d2e29e46875f5eaca5

Modified Files
--------------
src/backend/commands/vacuumlazy.c |  230 ++++++++++++++++++++++++++----------
src/backend/storage/lmgr/lmgr.c   |   18 +++
src/backend/storage/lmgr/lock.c   |   92 +++++++++++++++
src/include/storage/lmgr.h        |    1 +
src/include/storage/lock.h        |    2 +
5 files changed, 279 insertions(+), 64 deletions(-)


pgsql-committers by date:

Previous
From: Alvaro Herrera
Date:
Subject: pgsql: isolationtester: add a few fflush(stderr) calls
Next
From: Kevin Grittner
Date:
Subject: pgsql: Fix performance problems with autovacuum truncation in busy work