Re: Why do we let autovacuum give up?

On 2014-01-23 19:29:23 -0500, Tom Lane wrote:
> 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 generally think we need to work a bit on the data reported back by
vacuum. Adding data and also making the data output when using
autovacuum more consistent with what VACUUM VERBOSE reports. The latter
curiously often has less detail than autovacuum.
I had hoped to get to that for 9.4, but it doesn't look like it.

> 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.

Yes, I think adjusting the default makes sense, most setups that have
enough activity that costing plays a role have to greatly increase the
values. I'd rather increase the cost limit than reduce cost delay so
drastically though, but that's admittedly just gut feeling.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services