Skip site navigation (1) Skip section navigation (2)

Re: Postgres becoming slow, only full vacuum fixes it

From: "Kevin Grittner" <kgrittn(at)mail(dot)com>
To: "Kiriakos Tsourapas" <ktsour(at)gmail(dot)com>,"Mark Kirkwood" <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Date: 2012-09-25 12:40:55
Message-ID: 20120925124055.241320@gmx.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


pgsql-performance by date

Next:From: Ondrej IvaničDate: 2012-09-25 23:14:06
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Previous:From: Thomas KellererDate: 2012-09-25 11:24:04
Subject: Re: Postgres becoming slow, only full vacuum fixes it

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group