Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-01-23 16:15:50 -0500, Tom Lane wrote:
>> [ thinks... ] It's possible that what you saw is not the
>> kick-out-autovacuum-entirely behavior, but the behavior added in commit
>> bbb6e559c, whereby vacuum (auto or regular) will skip over pages that it
>> can't immediately get an exclusive buffer lock on. On a heavily used
>> table, we might skip the same page repeatedly, so that dead tuples don't
>> get cleaned for a long time.
> I don't think it's too likely as an explanation here. Such workloads are
> likely to fill a page with only dead tuples, right? Once all tuples are
> safely dead they will be killed from the btree which should cause the
> page not to be visited anymore and thus safely vacuumable.
I added some instrumentation to vacuumlazy.c to count the number of pages
skipped in this way. You're right, it seems to be negligible, at least
with Mark's test case. I saw at most two pages skipped per vacuum, and
usually none; so there's no way that a whole lot of tuples are going
unvacuumed because of this. (I wonder though if we ought to add such
counting as a permanent feature ...)
I concur with the other reports that the main problem in this test case is
just that the default cost delay settings throttle autovacuum so hard that
it has no chance of keeping up. If I reduce autovacuum_vacuum_cost_delay
from the default 20ms to 2ms, it seems to keep up quite nicely, on my
machine anyway. Probably other combinations of changes would do it too.
Perhaps we need to back off the default cost delay settings a bit?
We've certainly heard more than enough reports of table bloat in
heavily-updated tables. A system that wasn't hitting the updates as hard
as it could might not need this, but on the other hand it probably
wouldn't miss the I/O cycles from a more aggressive autovacuum, either.
regards, tom lane