From MGoldner@agmednet.com Sun Nov 4 23:04:24 2007 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6B8D49F98AB for ; Sun, 4 Nov 2007 19:04:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36572-01 for ; Sun, 4 Nov 2007 19:04:18 -0400 (AST) X-Greylist: delayed 00:12:09.600759 by SQLgrey-1.7.5 Received: from boston.agmednet.com (boston.agmednet.com [74.92.47.82]) by postgresql.org (Postfix) with ESMTP id 7351B9F98A0 for ; Sun, 4 Nov 2007 19:04:17 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 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, 4 Nov 2007 22:49:13 +0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C81F34.EBEB21F8" User-Agent: Microsoft-Entourage/11.3.6.070618 Content-class: urn:content-classes:message Subject: Database size with large objects Date: Sun, 4 Nov 2007 17:49:13 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Database size with large objects thread-index: AcgfNURRgup6NosoEdy/6wAX8gFv1g== From: "Michael Goldner" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.777 tagged_above=0 required=5 tests=AWL=-0.292, BAYES_50=0.001, HTML_MESSAGE=0.001, RCVD_NUMERIC_HELO=2.067 X-Spam-Level: * X-Archive-Number: 200711/42 X-Sequence-Number: 27285 This is a multi-part message in MIME format. ------_=_NextPart_001_01C81F34.EBEB21F8 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm running postgresql 8.1.8 on RHEL4. I have a database with a single table that includes an oid reference to = a large object. After loading 100GB of large objects using lo_import(), I find that my total database size has grown by about 270GB. What is the reason for the difference in space usage? There are about 400,000 records in the table, with each large object the same size (on average). I have autovacuum running, and had done a = vacuum --full --analyze prior to the load. Max_fsm_pages is very high (1,500,000), and the vacuum --analyze = confirms that it is sufficient. Is this just something that I have to live with? Thanks, Mike ------_=_NextPart_001_01C81F34.EBEB21F8 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Database size with large objects

I'm running postgresql 8.1.8 on RHEL4.

I have a database with a single table that includes an oid reference to = a
large object.  After loading 100GB of large objects using = lo_import(), I
find that my total database size has grown by about 270GB.  What is = the
reason for the difference in space usage?

There are about 400,000 records in the table, with each large object = the
same size (on average).  I have autovacuum running, and had done a = vacuum
--full --analyze prior to the load.

Max_fsm_pages is very high (1,500,000), and the vacuum --analyze = confirms
that it is sufficient.

Is this just something that I have to live with?

Thanks,

Mike

------_=_NextPart_001_01C81F34.EBEB21F8-- From tgl@sss.pgh.pa.us Mon Nov 5 00:27:12 2007 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 500C59F9418 for ; Sun, 4 Nov 2007 20:27:12 -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 89948-02-5 for ; Sun, 4 Nov 2007 20:26:58 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 067449F9291 for ; Sun, 4 Nov 2007 20:26:55 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lA50Qr5k029839; Sun, 4 Nov 2007 19:26:53 -0500 (EST) To: "Michael Goldner" cc: pgsql-admin@postgresql.org Subject: Re: Database size with large objects In-reply-to: References: Comments: In-reply-to "Michael Goldner" message dated "Sun, 04 Nov 2007 17:49:13 -0500" Date: Sun, 04 Nov 2007 19:26:53 -0500 Message-ID: <29838.1194222413@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/43 X-Sequence-Number: 27286 "Michael Goldner" writes: > I have a database with a single table that includes an oid reference to a > large object. After loading 100GB of large objects using lo_import(), I > find that my total database size has grown by about 270GB. What is the > reason for the difference in space usage? Try VACUUM VERBOSE on your table and also on pg_largeobject to get a sense of where the space went. I'm wondering whether you allowed for indexes and for other data in your table. regards, tom lane From mgoldner@agmednet.com Mon Nov 5 04:04:04 2007 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 52CC79F987A for ; Mon, 5 Nov 2007 00:04:04 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 67465-05 for ; Mon, 5 Nov 2007 00:04:00 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from boston.agmednet.com (boston.agmednet.com [74.92.47.82]) by postgresql.org (Postfix) with ESMTP id 1204D9F9879 for ; Mon, 5 Nov 2007 00:03:59 -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 ; Mon, 5 Nov 2007 04:01:14 +0000 User-Agent: Microsoft-Entourage/11.3.6.070618 Date: Sun, 04 Nov 2007 23:03:43 -0400 Subject: Re: Database size with large objects From: Michael Goldner To: Tom Lane CC: Message-ID: Thread-Topic: [ADMIN] Database size with large objects Thread-Index: AcgfYNrnGTuVeItUEdy/6wAX8gFv1g== In-Reply-To: <29838.1194222413@sss.pgh.pa.us> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.318 tagged_above=0 required=5 tests=AWL=0.361, BAYES_05=-1.11, RCVD_NUMERIC_HELO=2.067 X-Spam-Level: * X-Archive-Number: 200711/44 X-Sequence-Number: 27287 On 11/4/07 8:26 PM, "Tom Lane" wrote: > "Michael Goldner" writes: >> I have a database with a single table that includes an oid reference to a >> large object. After loading 100GB of large objects using lo_import(), I >> find that my total database size has grown by about 270GB. What is the >> reason for the difference in space usage? > > Try VACUUM VERBOSE on your table and also on pg_largeobject to get a > sense of where the space went. I'm wondering whether you allowed for > indexes and for other data in your table. > > regards, tom lane OK, I vacuumed both tables. The single table vacuumed in about 2 seconds and the stats look normal: INFO: vacuuming "public.image" INFO: index "image_pkey" now contains 386749 row versions in 1691 pages DETAIL: 21799 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.10u sec elapsed 0.14 sec. INFO: "image": removed 21799 row versions in 716 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "image": found 21799 removable, 386749 nonremovable row versions in 19589 pages DETAIL: 0 dead row versions cannot be removed yet. There were 183153 unused item pointers. 0 pages are entirely empty. CPU 0.09s/0.19u sec elapsed 0.32 sec. INFO: vacuuming "pg_toast.pg_toast_176283637" INFO: index "pg_toast_176283637_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_176283637": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM The pg_largeobject table, however, seems a bit odd: INFO: vacuuming "pg_catalog.pg_largeobject" INFO: index "pg_largeobject_loid_pn_index" now contains 105110204 row versions in 404151 pages DETAIL: 778599 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 21.24s/48.07u sec elapsed 273.15 sec. INFO: "pg_largeobject": removed 778599 row versions in 775264 pages DETAIL: CPU 54.73s/29.70u sec elapsed 2203.32 sec. INFO: "pg_largeobject": found 778599 removable, 105094846 nonremovable row versions in 34803136 pages DETAIL: 0 dead row versions cannot be removed yet. There were 70 unused item pointers. 0 pages are entirely empty. CPU 1031.40s/339.21u sec elapsed 10875.66 sec. VACUUM In particular, "105094846 nonremovable row versions in 34803136 pages" seems really high given that there are only about 400,000 large objects. The majority of the lobs are less than 1MB. However, a small subset can range as high as 1GB. I see that a single object is spread across multiple pages (loid, pageno) in the pg_largeobject table. Is it necessary or possible to adjust the page size for greater efficiency? Thanks, Mike From tgl@sss.pgh.pa.us Mon Nov 5 04:19:33 2007 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3C0219F987A for ; Mon, 5 Nov 2007 00:19:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79782-06 for ; Mon, 5 Nov 2007 00:19:29 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 209239F9276 for ; Mon, 5 Nov 2007 00:19:28 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lA54JRXp002907; Sun, 4 Nov 2007 23:19:27 -0500 (EST) To: Michael Goldner cc: pgsql-admin@postgresql.org Subject: Re: Database size with large objects In-reply-to: References: Comments: In-reply-to Michael Goldner message dated "Sun, 04 Nov 2007 23:03:43 -0400" Date: Sun, 04 Nov 2007 23:19:27 -0500 Message-ID: <2906.1194236367@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/45 X-Sequence-Number: 27288 Michael Goldner writes: > The pg_largeobject table, however, seems a bit odd: > INFO: vacuuming "pg_catalog.pg_largeobject" > INFO: index "pg_largeobject_loid_pn_index" now contains 105110204 row > versions in 404151 pages > DETAIL: 778599 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > CPU 21.24s/48.07u sec elapsed 273.15 sec. > INFO: "pg_largeobject": removed 778599 row versions in 775264 pages > DETAIL: CPU 54.73s/29.70u sec elapsed 2203.32 sec. > INFO: "pg_largeobject": found 778599 removable, 105094846 nonremovable row > versions in 34803136 pages > DETAIL: 0 dead row versions cannot be removed yet. > There were 70 unused item pointers. > 0 pages are entirely empty. > CPU 1031.40s/339.21u sec elapsed 10875.66 sec. > VACUUM > In particular, "105094846 nonremovable row versions in 34803136 pages" seems > really high given that there are only about 400,000 large objects. Well, each row in pg_largeobject is a 2K (at most) chunk of a large object. There is something funny here because if there's only 100GB of LO data, that would average out to less than 1K per row, which is half what I'd expect. Do you have another source of large objects that are not-so-large-as-all-that and might be dragging down the average? It might be interesting to look at stats such as select sum(length(data)) from pg_largeobject; to confirm that your 100GB estimate for the data payload is accurate. regards, tom lane From mgoldner@agmednet.com Mon Nov 5 08:45:16 2007 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 08D619F986E for ; Mon, 5 Nov 2007 04:45:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76061-07 for ; Mon, 5 Nov 2007 04:45:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from boston.agmednet.com (boston.agmednet.com [74.92.47.82]) by postgresql.org (Postfix) with ESMTP id 103309F9869 for ; Mon, 5 Nov 2007 04:45:11 -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 ; Mon, 5 Nov 2007 08:42:10 +0000 User-Agent: Microsoft-Entourage/11.3.6.070618 Date: Mon, 05 Nov 2007 03:41:48 -0400 Subject: Re: Database size with large objects From: Michael Goldner To: Tom Lane CC: Message-ID: Thread-Topic: [ADMIN] Database size with large objects Thread-Index: Acgfh7Pw8qyjL4t6Edy/6wAX8gFv1g== In-Reply-To: <2906.1194236367@sss.pgh.pa.us> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.483 tagged_above=0 required=5 tests=AWL=1.015, BAYES_00=-2.599, RCVD_NUMERIC_HELO=2.067 X-Spam-Level: X-Archive-Number: 200711/47 X-Sequence-Number: 27290 On 11/5/07 12:19 AM, "Tom Lane" wrote: > Michael Goldner writes: >> The pg_largeobject table, however, seems a bit odd: > >> INFO: vacuuming "pg_catalog.pg_largeobject" >> INFO: index "pg_largeobject_loid_pn_index" now contains 105110204 row >> versions in 404151 pages >> DETAIL: 778599 index row versions were removed. >> 0 index pages have been deleted, 0 are currently reusable. >> CPU 21.24s/48.07u sec elapsed 273.15 sec. >> INFO: "pg_largeobject": removed 778599 row versions in 775264 pages >> DETAIL: CPU 54.73s/29.70u sec elapsed 2203.32 sec. >> INFO: "pg_largeobject": found 778599 removable, 105094846 nonremovable row >> versions in 34803136 pages >> DETAIL: 0 dead row versions cannot be removed yet. >> There were 70 unused item pointers. >> 0 pages are entirely empty. >> CPU 1031.40s/339.21u sec elapsed 10875.66 sec. >> VACUUM > >> In particular, "105094846 nonremovable row versions in 34803136 pages" seems >> really high given that there are only about 400,000 large objects. > > Well, each row in pg_largeobject is a 2K (at most) chunk of a large > object. There is something funny here because if there's only 100GB > of LO data, that would average out to less than 1K per row, which is > half what I'd expect. Do you have another source of large objects > that are not-so-large-as-all-that and might be dragging down the > average? > > It might be interesting to look at stats such as > select sum(length(data)) from pg_largeobject; > to confirm that your 100GB estimate for the data payload is accurate. > > regards, tom lane That select returns the following: image=# select sum(length(data)) from pg_largeobject; sum -------------- 215040008847 (1 row) The actual on-disk data before the import was about half (this is a production environment so data has been added over the last 2 days): # du -sh /pglog/image_lo/ 102G /pglog/image_lo/ -- Mike Goldner Vice President Networks and Technology AG Mednet, Inc. The Pilot House Lewis Wharf Boston, MA 02110 617.854.3225 (office) 617.909.3009 (mobile) From tgl@sss.pgh.pa.us Mon Nov 5 13:57:52 2007 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B77449F959C for ; Mon, 5 Nov 2007 09:57:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 75546-06 for ; Mon, 5 Nov 2007 09:57:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 33FE19F971F for ; Mon, 5 Nov 2007 09:57:46 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lA5DvjgI018364; Mon, 5 Nov 2007 08:57:45 -0500 (EST) To: Michael Goldner cc: pgsql-admin@postgresql.org Subject: Re: Database size with large objects In-reply-to: References: Comments: In-reply-to Michael Goldner message dated "Mon, 05 Nov 2007 03:41:48 -0400" Date: Mon, 05 Nov 2007 08:57:44 -0500 Message-ID: <18363.1194271064@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/51 X-Sequence-Number: 27294 Michael Goldner writes: > On 11/5/07 12:19 AM, "Tom Lane" wrote: >> It might be interesting to look at stats such as >> select sum(length(data)) from pg_largeobject; >> to confirm that your 100GB estimate for the data payload is accurate. > That select returns the following: > image=# select sum(length(data)) from pg_largeobject; > sum > -------------- > 215040008847 > (1 row) Hmm, so given that you had 34803136 pages in pg_largeobject, that works out to just about 75% fill factor. That is to say, you're only getting 3 2K rows per page and not 4. If the rows were full-size then 4 would obviously not fit (there is some overhead...) but the normal expectation in pg_largeobject is that tuple compression will shave enough space to make up for the overhead and let you get 4 rows per page. Are your large objects mostly pre-compressed data? regards, tom lane