I'm running Postgres 8.1.8 on RHEL. I have a very large database that
consists of a single table with a large object column. The table had just
under 2 million rows. The database looks to be about 700GB is size.
I have autovacuum running on the system and performance had been good. I'm
not sure what the size threshold was, maybe 500GB, for when it began to
deteriorate. However, we are experiencing some performance issues right now
and I am archiving data to bring the size down to about 250GB.
Last night, my archiving script slowed by a factor of 2 or 3, so I figured
that enough rows had been removed that a manual vacuum was necessary. From
the command prompt (as the postgres user) I ran the following:
-bash-3.00$ vacuumdb --verbose --analyze <dbname>
It has been running for close to 12 hours. The strange thing is that it
seems to be "looping". I see repeating "INFO" messages for pg_largeobject
and pg_largeobject_loid_pn_index relations. I've included the output below
my sig. This database is in constant production use, so records are being
added during the vacuum.
Am I stuck in a loop, or is this happening because the size of the relation
is so large that postgres is operating on smaller chunks?
Thanks,
Mike
LOG DETAIL:
INFO: vacuuming "pg_catalog.pg_largeobject"
INFO: index "pg_largeobject_loid_pn_index" now contains 274033339 row
versions in 1109450 pages
DETAIL: 11184556 index row versions were removed.
7414 index pages have been deleted, 0 are currently reusable.
CPU 41.53s/111.63u sec elapsed 650.60 sec.
INFO: "pg_largeobject": removed 11184556 row versions in 3753500 pages
DETAIL: CPU 168.15s/37.34u sec elapsed 1498.92 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 262883150 row
versions in 1109575 pages
DETAIL: 11184556 index row versions were removed.
15907 index pages have been deleted, 0 are currently reusable.
CPU 44.25s/107.33u sec elapsed 654.02 sec.
INFO: "pg_largeobject": removed 11184556 row versions in 3755190 pages
DETAIL: CPU 205.56s/42.92u sec elapsed 1604.51 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 251843838 row
versions in 1110114 pages
DETAIL: 11184557 index row versions were removed.
22473 index pages have been deleted, 0 are currently reusable.
CPU 47.55s/104.48u sec elapsed 642.06 sec.
INFO: "pg_largeobject": removed 11184557 row versions in 3758621 pages
DETAIL: CPU 124.28s/39.16u sec elapsed 1347.28 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 240671903 row
versions in 1110161 pages
DETAIL: 11184558 index row versions were removed.
29228 index pages have been deleted, 0 are currently reusable.
CPU 46.78s/100.50u sec elapsed 663.27 sec.
INFO: "pg_largeobject": removed 11184558 row versions in 3757819 pages
DETAIL: CPU 218.24s/48.46u sec elapsed 1573.08 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 229491795 row
versions in 1110179 pages
DETAIL: 11184557 index row versions were removed.
47283 index pages have been deleted, 0 are currently reusable.
CPU 52.15s/95.28u sec elapsed 705.06 sec.
INFO: "pg_largeobject": removed 11184557 row versions in 3741040 pages
DETAIL: CPU 188.51s/47.72u sec elapsed 1406.88 sec.
INFO: index "pg_largeobject_loid_pn_index" now contains 218339098 row
versions in 1110300 pages
DETAIL: 11184557 index row versions were removed.
90877 index pages have been deleted, 0 are currently reusable.
CPU 63.27s/90.99u sec elapsed 727.05 sec.
INFO: "pg_largeobject": removed 11184557 row versions in 3708216 pages
DETAIL: CPU 114.99s/41.80u sec elapsed 935.06 sec.
