Re: Full Vacuum/Reindex vs autovacuum

On Mon, Feb 28, 2011 at 01:48:30PM -0600, Jason Long wrote:

> I stopped doing the nightly vacuum full and reindex.  After 3 months
> some queries would not complete within 2 minutes.  Normally these take
> less than 5 seconds.  I tried vacuum without full and reindex, but the
> problem was still there.  Only vacuum full and reindex returned
> performance to normal.  Now I am back to my previous nightly full vacuum
> and reindex.

Are you logging autovacuum?  Is it actually doing what it needs to?  I
haven't had any experience with autovacuum under 9.x, and I certainly
won't say that there's no problem there, but I have a suspicion that
your settings are such that either the full vacuum or (at least as
likely) the reindex is having an effect that autovacuum ought to be
catching.

The first thing I'd do is log all the vacuums so that you can see how
much vacuum full is recovering.  I suggest this only so as not to
disrupt your regular operations; otherwise, I'd suggest going back to
autovacuum and seeing whether reindex alone would help you.

A


--
Andrew Sullivan
ajs@crankycanuck.ca