vacuumdb question/problem

David Ondrejik <David.Ondrejik@xxxxxxxx> · Fri, 15 Jul 2011 12:10:00 -0400

    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.

begin:vcard
fn:David Ondrejik
n:Ondrejik;David
org:weather.gov/marfc;NOAA's National Weather Service
adr;dom:Suite #330;;328 Innovation Blvd;State College;PA;16803
email;internet:david.ondrejik@xxxxxxxx
title:Senior Hydrologist. Mid-Atlantic River Forecast Center
tel;work:814-231-2401
version:2.1
end:vcard

-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin