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.
