Re: Poor overall performance unless regular VACUUM FULL

On 7/14/09 9:53 PM, "David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com> wrote:

> 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.

If you go that route, do a REINDEX first. You probably want to know whether
it is mostly index or table bloat that is the majority of the problem.

Adjusting each table and index FILLFACTOR may also help.

However, if it has bloated this much, you may have some long living
transactions that make it hard for postgres to recycle free space.

And as others have said, certain things can cause a lot of bloat that only
CLUSTER or VACUUM FULL will reclaim well -- especially updating all or most
rows in a table, or otherwise doing very large bulk delete or update.

>
> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>