Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 12DD32E0168 for ; Sun, 2 Mar 2008 11:04:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 89167-10 for ; Sun, 2 Mar 2008 11:04:30 -0400 (AST) X-Greylist: delayed 00:12:07.212044 by SQLgrey-1.7.5 Received: from boston.agmednet.com (boston.agmednet.com [74.92.47.82]) by postgresql.org (Postfix) with ESMTP id 34CCE2E00B7 for ; Sun, 2 Mar 2008 11:04:39 -0400 (AST) Received: from 71.233.123.244 ([71.233.123.244]) by agmednet-dc.boston.agmednet.local ([192.168.67.2]) with Microsoft Exchange Server HTTP-DAV ; Sun, 2 Mar 2008 14:46:27 +0000 User-Agent: Microsoft-Entourage/12.0.0.071130 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C87C74.31A3F19C" X-MimeOLE: Produced By Microsoft Exchange V6.5 User-Agent: Microsoft-Entourage/12.0.0.071130 Content-class: urn:content-classes:message Subject: Is my vacuumdb stuck in a loop? Date: Sun, 2 Mar 2008 09:46:27 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Is my vacuumdb stuck in a loop? Thread-Index: Ach8dQb4bQ6V+kUa00anpFntsoSSpA== From: "Michael Goldner" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.912 tagged_above=0 required=5 tests=AWL=-0.688, HTML_MESSAGE=0.001, RCVD_NUMERIC_HELO=2.599 X-Spam-Level: * X-Archive-Number: 200803/4 X-Sequence-Number: 28487 This is a multi-part message in MIME format. ------_=_NextPart_001_01C87C74.31A3F19C Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 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. ------_=_NextPart_001_01C87C74.31A3F19C Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is my vacuumdb stuck in a loop?

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.

------_=_NextPart_001_01C87C74.31A3F19C--