From: Mario Weilguni <mweilguni@sime.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: vegard.bones@met.no
Cc: pgsql-performance@postgresql.org
Subject: Re: Deteriorating performance when loading large objects
Date: Fri, 28 Nov 2008 09:30:40 +0100
Message-ID: <492FAC30.7080409@sime.com> (raw)
In-Reply-To: <11058.1227821378@sss.pgh.pa.us>
References: <63696.85.166.86.82.1227617663.squirrel@webmailsunny.oslo.dnmi.no>
<14976.1227620206@sss.pgh.pa.us>
<49362.85.166.86.82.1227812685.squirrel@webmailsunny.oslo.dnmi.no>
<11058.1227821378@sss.pgh.pa.us>
Tom Lane schrieb:
> "=?iso-8859-1?Q?Vegard_B=F8nes?=" <vegard.bones@met.no> writes:
>
>> Running VACUUM VERBOSE pg_largeobject took quite some time. Here's the
>> output:
>>
>
>
>> INFO: vacuuming "pg_catalog.pg_largeobject"
>> INFO: index "pg_largeobject_loid_pn_index" now contains 11060658 row
>> versions in 230587 pages
>> DETAIL: 178683 index pages have been deleted, 80875 are currently reusable.
>> CPU 0.92s/0.10u sec elapsed 199.38 sec.
>> INFO: "pg_largeobject": found 0 removable, 11060658 nonremovable row
>> versions in 6849398 pages
>> DETAIL: 0 dead row versions cannot be removed yet.
>> There were 84508215 unused item pointers.
>> 0 pages are entirely empty.
>> CPU 0.98s/0.10u sec elapsed 4421.17 sec.
>> VACUUM
>>
>
> Hmm ... although you have no dead rows now, the very large number of
> unused item pointers suggests that there were times in the past when
> pg_largeobject didn't get vacuumed often enough. You need to look at
> your vacuuming policy. If you're using autovacuum, it might need to have
> its parameters adjusted. Otherwise, how often are you vacuuming, and
> are you doing it as superuser?
>
>
>> I will try to run VACUUM ANALYZE FULL after the next delete tonight, as
>> suggested by Ivan Voras in another post.
>>
>
> Actually, a CLUSTER might be more effective.
>
> regards, tom lane
>
>
Does CLUSTER really help here? On my 8.2 database, I get:
CLUSTER pg_largeobject_loid_pn_index on pg_largeobject ;
ERROR: "pg_largeobject" is a system catalog
Has this changed in >= 8.3?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mweilguni@sime.com, tgl@sss.pgh.pa.us, vegard.bones@met.no
Subject: Re: Deteriorating performance when loading large objects
In-Reply-To: <492FAC30.7080409@sime.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox