vacuumdb question/problem??

From: David Ondrejik <David(dot)Ondrejik(at)noaa(dot)gov>
To: pgsql-admin(at)postgresql(dot)org
Subject: vacuumdb question/problem??
Date: 2011-07-15 15:27:09
Message-ID: 4E205C4D.80604@noaa.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,

I am new to this list and hope I have chosen the appropriate group to
ask this question.

We are running version 8.2.6 of postgres and I am trying to run a full
vacuum on a single table in our database. I started the vacuum about 24
hours ago and it is still running. Within 2-3 hrs of running, the
following info was returned:

INFO: vacuuming "public.pecrsep"
INFO: "pecrsep": found 33781 removable, 10389467 nonremovable row
versions in 35318465 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 1633 to 1637 bytes long.
There were 130850585 unused item pointers.
Total free space (including removable row versions) is 271020146144 bytes.
30292740 pages are or will become empty, including 0 at the end of the
table.
35318465 pages containing 271020146144 free bytes are potential move
destinations.
CPU 236.49s/90.56u sec elapsed 3954.18 sec.
INFO: index "pecrsep_time_ind" now contains 10389467 row versions in
400823 pages
DETAIL: 33781 index row versions were removed.
7202 index pages have been deleted, 7202 are currently reusable.
CPU 3.08s/6.87u sec elapsed 73.42 sec.
INFO: index "pecrsep_pk" now contains 10389467 row versions in 294694 pages
DETAIL: 30179 index row versions were removed.
46 index pages have been deleted, 46 are currently reusable.
CPU 2.52s/9.90u sec elapsed 49.47 sec.

Since then, the process has continued to run (for about 20 hrs) without
any additional information being returned.

The question I have about the vacuumdb is, does it scan through the
table and identify what actions it will take...return the info listed
above, THEN do the actual vacuum? Or did it complete the vacuum before
it returned the information?

The table I am running the full vacuum on has been accumulating data for
a couple years and a full vacuum has never been run (to my knowledge).
In addition, over the last 6 months I posted over 200 million records to
this table. Thus, I am not sure if the process is hung or if it is just
taking a long time since the table has never had a full vacuum and has
had a ton of records posted recently.

Any thoughts are appreciated. Should I let it continue to run or kill
the process? It was suggested to me that I may want to kill the process
and run a CLUSTER on the table...then re-run the full vacuum.

I realize we are on a old version of postgres, but I am locked into this
with no chance to upgrade.

Thanks in advance.

Attachment Content-Type Size
david_ondrejik.vcf text/x-vcard 309 bytes

Browse pgsql-admin by date

  From Date Subject
Next Message David Ondrejik 2011-07-15 16:10:00 vacuumdb question/problem
Previous Message Scott Marlowe 2011-07-15 11:41:02 Re: Importing the dump file in postgresql-7.4.23