Re: Why do we let autovacuum give up?

On Thu, Jan 23, 2014 at 10:38 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>>>
>>> Stops excessive bloat - clearly autovacuum *is* able to vacuum pg_attribute
>>> in this case. Back to drawing board for a test case.
>>
>> Well, I think quite many people don't realize it might be necessary to
>> tune autovac on busy workloads. As it very well might be the case in
>> Josh's case.
>
> Oh, lots of people realise it's a good idea to tune autovac on busy
> workloads.
>
> They just do it in the wrong direction, making it run less often and
> less aggressively, causing more bloat, and making their problem worse.
>
> I've seen this enough times that I'm starting to think the autovauum
> tuning knobs need a child safety lock ;-)
>
> More seriously, people don't understand autovacuum, how it works, or why
> they need it. They notice it when things are already bad, see that it's
> doing lots of work and doing lots of I/O that competes with queries, and
> turn it off to "solve" the problem.

AFAIK, tuning down autovacuum is common advice **when compounded with
manually scheduled vacuuming**.

The problem of autovacuum is that it always picks the wrong time to
work. That is, when the DB is the most active. Because statistically
that's when the thresholds are passed.

If you ask me, I'd like autovac to know when not to run (or rather
wait a bit, not forever), perhaps by checking load factors or some
other tell-tale of an already-saturated I/O system.