From: Kevin Grittner <kgrittn@mail.com>
To: Kiriakos Tsourapas <ktsour@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Date: Wed, 26 Sep 2012 08:58:41 -0400
Message-ID: <20120926125841.221020@gmx.com> (raw)
[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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kgrittn@mail.com, ktsour@gmail.com
Subject: Re: Postgres becoming slow, only full vacuum fixes it
In-Reply-To: <20120926125841.221020@gmx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox