Re: Postgres becoming slow, only full vacuum fixes it

[Kiriakos Tsourapas <ktsour@xxxxxxxxx>]

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