Re: Why do we let autovacuum give up?

On Thu, Jan 23, 2014 at 10:00 PM, Harold Giménez <harold(at)heroku(dot)com> wrote:

> On Thu, Jan 23, 2014 at 12:53 PM, Josh Berkus <josh(at)agliodbs(dot)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/