Re: Postgres becoming slow, only full vacuum fixes it

[resending because I accidentally failed to include the list]

Kiriakos Tsourapas wrote:

> I am taking your suggestions one step at a time.
>
> I changed my configuration to a much more aggressive autovacuum
> policy (0.5% for analyzing and 1% for autovacuum).
>
> autovacuum_naptime = 1min
> autovacuum_vacuum_threshold = 50
> #autovacuum_analyze_threshold = 50
> autovacuum_vacuum_scale_factor = 0.01
> autovacuum_analyze_scale_factor = 0.005
>
> I had tables with 180.000 record and another with 2M records, so
> the default values of 0.2 for autovacuum would mean that 18.000 and
> 200K records would have to change respectively, delaying the vacuum
> for many days.

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.

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:

- Prevent show_session_authorization() from crashing within
autovacuum processes (Tom Lane)

- Fix persistent slowdown of autovacuum workers when multiple
workers remain active for a long time (Tom Lane)
The effective vacuum_cost_limit for an autovacuum worker could drop
to nearly zero if it processed enough tables, causing it to run
extremely slowly.

- Fix VACUUM so that it always updates pg_class.reltuples/relpages
(Tom Lane)
This fixes some scenarios where autovacuum could make increasingly
poor decisions about when to vacuum tables.

- Fix btree index corruption from insertions concurrent with
vacuuming (Tom Lane)
An index page split caused by an insertion could sometimes cause a
concurrently-running VACUUM to miss removing index entries that it
should remove. After the corresponding table rows are removed, the
dangling index entries would cause errors (such as "could not read
block N in file ...") or worse, silently wrong query results after
unrelated rows are re-inserted at the now-free table locations. This
bug has been present since release 8.2, but occurs so infrequently
that it was not diagnosed until now. If you have reason to suspect
that it has happened in your database, reindexing the affected index
will fix things.

- Ensure autovacuum worker processes perform stack depth checking
properly (Heikki Linnakangas)
Previously, infinite recursion in a function invoked by auto-ANALYZE
could crash worker processes.

- Only allow autovacuum to be auto-canceled by a directly blocked
process (Tom Lane)
The original coding could allow inconsistent behavior in some cases;
in particular, an autovacuum could get canceled after less than
deadlock_timeout grace period.

- Improve logging of autovacuum cancels (Robert Haas)

-Kevin