Re: Why do we let autovacuum give up?

On 01/24/2014 07:52 AM, Andres Freund wrote:
> On 2014-01-24 12:49:57 +1300, Mark Kirkwood wrote:
>> autovacuum_max_workers = 4
>> autovacuum_naptime = 10s
>> autovacuum_vacuum_scale_factor = 0.1
>> autovacuum_analyze_scale_factor = 0.1
>> autovacuum_vacuum_cost_delay = 0ms
>>
>> 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.

I'm not sure how to tackle that.

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