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)