Re: Autovacuum different in 9.2.4?

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> Joshua D. Drake escribió:

>> That is what is confusing me, I could be cracked but messages like these:
>>
>> automatic vacuum of table "pg_catalog.pg_attribute": could not
>> (re)acquire exclusive lock for truncate scan
>>
>> Seem to be new?
>
> Yeah, those are new.

Yeah, they started appearing in 9.2.3 and concurrent releases; but
since they seem to be causing more confusion than enlightenment, I
removed them in a subsequent patch.  They should be gone again in
9.2.5.

> In the old code, trying to truncate the free pages
> at the end of a table (which requires an ACCESS EXCLUSIVE lock on the
> table) could lock other processes out of that table.

Right, prior to 9.2.3 all other access to the table could be
blocked by an autovacuum truncation attempt for the duration that
deadlock_timeout was set to.  Those who set deadlock_timeout to
something longer than the default 1 second could experience a lot
of pain.

> The new code
> instead tries to handle this gracefully by giving up the lock if some
> other process is trying to access the table.  The result is that a few
> free pages might be left over after vacuuming the table.  Not a big
> deal, normally, unless you're really short on disk space.

Actually, the old code wound up not truncating anything when the
autovacuum process was killed by the deadlock checker when
deadlock_timeout expired, and needed to start all over for each
attempt; with each attempt coming quickly on the heals of the last.
 So you could have an indefinitely repeating series of
AccessExclusiveLocks on the table, with heavy load making no
progress.  In 9.2.3 and 9.2.4, it will truncate to the point it has
checked and release the AccessExclusiveLock if any other process is
waiting for 20 ms.  It will attempt to reacquire the lock to
continue the truncation every 50 ms for 5 seconds, at which point
it will give up without the drama of the deadlock checker killing
it.  Either way, an incomplete truncation attempt would result in
no statistics update, which would cause another autovacuum on that
table to be attempted soon.

Significantly, the old code *would* update statistics if the
truncation attempt was not able to *begin* due to lock contention,
while that behavior was lost in 9.2.3 and 9.2.4.  This is where
people with certain types of workloads are seeing an increase in
stale statistics with the new code.  This will be fixed in 9.2.5.
We will then update statistics regardless of any problems in the
truncation phase.

> There was discussion about autovacuum being tweaked so that it would
> reattempt to truncate those free pages in a future pass.  I don't know
> if this was done or not.

Well, the old code did not reattempt the truncation very quickly if
it was not able to acquire the initial AccessExclusiveLock, but
would fire again soon if killed by the deadlock checker after it
started.  The new code has a cycle of quick detection of blocked
processes, incremental truncate and sleep, and retry up to 100
times before giving up.  In 9.2.3 and 9.2.4 it *also* reschedules
quickly like the old aborted truncation; in 9.2.5 it will just try
again if it seems needed at the next normally scheduled autovacuum.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company