On Mon, Jul 13, 2009 at 3:31 PM, Wayne Conrad<wayne@databill.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.t.wilson@gmail.com
