From: Kevin Grittner <kgrittn@mail.com>
To: Kiriakos Tsourapas <ktsour@gmail.com>
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Date: Tue, 25 Sep 2012 08:40:55 -0400
Message-ID: <20120925124055.241320@gmx.com> (raw)
Kiriakos Tsourapas wrote:
> When the problem appears, vacuuming is not helping. I ran vacuum
> manually and the problem was still there. Only full vacuum worked.
>
> As far as I have understood, autovacuuming is NOT doing FULL
> vacuum. So, messing around with its values should not help me in
> any way.
That is absolutely wrong. A regular vacuum, or autovacuum not
hopelessly crippled by your configuration, will prevent the table
bloat which is slowing things down. It does not, however, fix bloat
once it has occurred; a normal vacuum then is like closing the barn
door after the horse has already bolted -- it would have prevented
the problem if done in time, but it won't cure it.
A VACUUM FULL in version 8.4 will fix bloat of the table's heap, but
will tend to bloat the indexes. You should probably fix your
autovcauum configuration (making it *at least* as aggressive as the
default), CLUSTER the affected table(s) to fix both table and index
bloat, and schedule an upgrade to the latest bug fix release of major
version 8.4.
http://www.postgresql.org/support/versioning/
Minor releases (where the version number only changes after the
second dot) only contain fixes for bugs and security problems, and
never require a dump/load or pg_upgrade run. If you insist on
running with known bugs, you can expect problems.
-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, mark.kirkwood@catalyst.net.nz
Subject: Re: Postgres becoming slow, only full vacuum fixes it
In-Reply-To: <20120925124055.241320@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