Re: Why do we let autovacuum give up? - Mailing list pgsql-hackers

From Magnus Hagander
Subject Re: Why do we let autovacuum give up?
Date
Msg-id CABUevEyXVpfpXrJcqc-iB4VKZx0=ro96H+-MFq8F4PMs4ieAgA@mail.gmail.com
Whole thread Raw
In response to Re: Why do we let autovacuum give up?  (Harold Giménez <harold@heroku.com>)
List pgsql-hackers
On Thu, Jan 23, 2014 at 10:00 PM, Harold Giménez <harold@heroku.com> wrote:
On Thu, Jan 23, 2014 at 12:53 PM, Josh Berkus <josh@agliodbs.com> wrote:
> On 01/23/2014 12:34 PM, Joshua D. Drake wrote:
>>
>> Hello,
>>
>> I have run into yet again another situation where there was an
>> assumption that autovacuum was keeping up and it wasn't. It was caused
>> by autovacuum quitting because another process requested a lock.
>>
>> In turn we received a ton of bloat on pg_attribute which caused all
>> kinds of other issues (as can be expected).
>>
>> The more I run into it, the more it seems like autovacuum should behave
>> like vacuum, in that it gets precedence when it is running. First come,
>> first serve as they say.
>>
>> Thoughts?
>
> If we let autovacuum block user activity, a lot more people would turn
> it off.
>
> Now, if you were to argue that we should have some way to monitor the
> tables which autovac can never touch because of conflicts, I would agree
> with you.

Agree completely. Easy ways to monitor this would be great. Once you
know there's a problem, tweaking autovacuum settings is very hard and
misunderstood, and explaining how to be effective at it is a dark art
too.

FWIW, I have a patch around somewhere that I never cleaned up properly for submissions that simply added a counter to pg_stat_user_tables indicating how many times vacuum had aborted on that specific table. If that's enough info  (it was for my case) to cover this case, I can try to dig it out again and clean it up...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Re: [COMMITTERS] pgsql: Compress GIN posting lists, for smaller index size.
Next
From: Tom Lane
Date:
Subject: Re: Add %z support to elog/ereport?