Re: Poor overall performance unless regular VACUUM FULL

On Mon, Jul 13, 2009 at 3:31 PM, Wayne Conrad<wayne(at)databill(dot)com> wrote:
> Howdy.  Some months back, when advised on one of these lists that it
> should not be necessary to issue VACUUM FULL/REINDEX DATABASE, we quit
> this nightly "maintenance" practice.  We've been very happy to not
> have to do that, since it locked the database all night.  Since then,
> however, our database performance has decreased.  The decrease took a
> few weeks to become noticable; perhaps six weeks to become awful.

<snip>

> I did a VACUUM VERBOSE and looked at the statistics at the end; they
> seem to indicated that my max_fsm_pages is large enough to keep track
> of all of the dead rows that are being created (we do a fair amount of
> deleting as well as inserting).  Postgres prints no complaint saying
> we need more slots, and we have more than the number of slots needed
> (if I recall, about twice as many).
>
> What options do I have for restoring performance other than VACUUM
> FULL/REINDEX DATABASE?
>

Do you have autovacuum on, or otherwise replaced your VACUUM FULL with
regular VACUUM? The symptoms are pretty classically those of table
bloat. Since it's gotten so out of hand now, a VACUUM FULL/REINDEX is
probably what you'll need to fix it.

Going forward, you need *some* vacuuming strategy. Autovacuum is
probably best, especially if you're on 8.3. If not autovacuum for some
reason, you *must* at least do regular vacuums.

Vacuum full/reindex is for fixing the situation you're in now, but a
regular vacuum strategy should prevent you from getting back into it.

--
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com