From: Scott Marlowe <scott.marlowe@gmail.com>
To: Sachchida Ojha <sojha@secure-elements.com>
Cc: Michael Glaesemann <grzm@seespotcode.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Autovacuum is running forever
Date: Tue, 21 Aug 2007 18:16:00 -0500
Message-ID: <dcc563d10708211616s75121fc8k7c2750de3b6260fd@mail.gmail.com> (raw)
In-Reply-To: <E099DB9EF394534CBA0F62ED03CF3BBF05FDBF77@EXVS01.IceMAIL.com>
References: <dcc563d10708211505h7907a283y7c1b88023709e3fe@mail.gmail.com>
<E099DB9EF394534CBA0F62ED03CF3BBF05FDBF77@EXVS01.IceMAIL.com>
On 8/21/07, Sachchida Ojha <sojha@secure-elements.com> wrote:
> Thanks to all of you. I have changed the settings and reloaded the
> config. Let me run this system overnight. I will update this forum if
> new settings works for me. I am also asking management to upgrade the
> hardware.
You need to run vacuum verbose on the database (not an individual
table) and note the output at the end. It will tell you how bloated
your current db is. If vacuums have been delayed for too long, you
may need to vacuum full and / or reindex the bloated tables and
indexes to reclaim the lost space.
Assuming that there's not too much dead space, or that if there is
you've used vacuum full / reindexdb to reclaim it, then vacuum running
regularly and in the background should fix this issue...
The output of vacuum verbose you're looking for is like this:
DETAIL: A total of 2096 page slots are in use (including overhead).
2096 page slots are required to track all free space.
Current limits are: 20000 page slots, 1000 relations, using 182 KB.
If it comes back with some huge number for page slots (like in the
millions) needed to track all the dead tuples you'll need that vacuum
full / reindex. A certain amount of dead space is ok, even a good
thing, since you don't have to extend your table / index files to
insert. 10-30% dead space is normal. anything around 100% or heading
up from there is bad.
You'll also want to look through the rest of the vacuum verbose output
for things like this:
INFO: vacuuming "abc.zip_test"
INFO: index "zip_test_pkey" now contains 1000000 row versions in 3076 pages
DETAIL: 8589 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.37s/0.23u sec elapsed 28.23 sec.
INFO: "zip_test": removed 8589 row versions in 55 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "zip_test": found 8589 removable, 1000000 nonremovable row
versions in 6425 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.36s/0.34u sec elapsed 100.52 sec.
If the number of rows removed and the pages they held were a large
percentage of the table, then you'll likely need to reindex them to
get the space back. Or cluster on an index.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, sojha@secure-elements.com, grzm@seespotcode.net
Subject: Re: Autovacuum is running forever
In-Reply-To: <dcc563d10708211616s75121fc8k7c2750de3b6260fd@mail.gmail.com>
References: <dcc563d10708211505h7907a283y7c1b88023709e3fe@mail.gmail.com> <E099DB9EF394534CBA0F62ED03CF3BBF05FDBF77@EXVS01.IceMAIL.com> <dcc563d10708211616s75121fc8k7c2750de3b6260fd@mail.gmail.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