On Mon, Jul 13, 2009 at 3:31 PM, Wayne Conrad<wayne@xxxxxxxxxxxx> 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@xxxxxxxxx -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
