Re: Why do we let autovacuum give up?

From: Craig Ringer <craig(at)2ndquadrant(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>, Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
Cc: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
Subject: Re: Why do we let autovacuum give up?
Date: 2014-01-24 01:38:55
Message-ID: 52E1C42F.5080102@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Craig Ringer 2014-01-24 02:12:20 Re: [v9.4] row level security
Previous Message Tom Lane 2014-01-24 00:45:53 Re: Why do we let autovacuum give up?