Re: Autovacuum is running forever

On 8/21/07, Sachchida Ojha <sojha(at)secure-elements(dot)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.