Re: Postgres becoming slow, only full vacuum fixes it

Hi Kevin,

On Sep 26, 2012, at 14:39, Kevin Grittner wrote:
>
> I am concerned that your initial email said that you had this
> setting:
>
> autovacuum_naptime = 28800
>
> This is much too high for most purposes; small, frequently-modified
> tables won't be kept in good shape with this setting. Perhaps you're
> not having that problem at the moment, but it's risky to assume that
> you don't and never will. When autovacuum wakes up and there is
> nothing to do it should go back to sleep very quickly.
>

I used the 28800 (8hours) setting after I realized that the default 1min was not helping.
I also changed other parameters when I changed it to 8 hours, to make sure tables would be auto vacuumed.
The problem with my setting was that autovacuum gets stopped if a lock is needed on the table. So, it was very bad choice to run it every 8 hours, because usually it got stopped and never did anything.
So, I turned back to the original setting of 1min but changed the autovacuum_vacuum_scale_factor to 1% instead of 20%. Hopefully tables will be more frequently vacuumed now and the problem will not appear again.

> Don't expect too much from just making autovacuum run more often
> until you have eliminated existing bloat (autovacuum generally just
> limits further growth of bloat) and updated to the latest 8.4 minor
> release. The following bugs fixes are among many you are living
> without until you upgrade:

Can you please suggest of a way to
- find if there is existing bloat
- eliminate it

Thank you