pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Vegard Bønes <vegard.bones@met.no>
To: tgl@sss.pgh.pa.us
Cc: pgsql-performance@postgresql.org
Subject: Re: Deteriorating performance when loading large objects
Date: Thu, 27 Nov 2008 19:04:45 -0000 (GMT)
Message-ID: <49362.85.166.86.82.1227812685.squirrel@webmailsunny.oslo.dnmi.no> (raw)
In-Reply-To: <14976.1227620206@sss.pgh.pa.us>
References: <63696.85.166.86.82.1227617663.squirrel@webmailsunny.oslo.dnmi.no>
	<14976.1227620206@sss.pgh.pa.us>

> "=?iso-8859-1?Q?Vegard_B=F8nes?=" <vegard.bones@met.no> writes:
>> I have a problem with large objects in postgresql 8.1: The performance
>> of loading large objects into a database goes way down after a few
>> days of operation.
>
>> I have a cron job kicking in twice a day, which generates and loads
>> around 6000 large objects of 3.7MB each. Each night, old data is
>> deleted, so there is never more than 24000 large object in the
>> database.
>
> Are you sure you're deleting the large objects themselves (ie,
> lo_unlink), and not just deleting some references to them?
>
> A manual "vacuum verbose" on pg_largeobject might be informative.


I do call lo_unlink via a trigger function. Also, a SELECT count(distinct
loid) FROM pg_largeobject yields the same result as a similar call to the
table which references the large objects.

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

I will try to run VACUUM ANALYZE FULL after the next delete tonight, as
suggested by Ivan Voras in another post. But as I understand it, this will
put an exclusive lock on whatever table is being vacuumed, so it is not
really an option for the database in question, as it needs to be
accessitble 24 hours a day.

Is there any other possible solution to this?

As a side note, I have noticed that loading times seem to have stabilized
at  just above an hour.

Regards,

Vegard Bønes






view thread (7+ messages)  latest in thread

Message-ID: <49362.85.166.86.82.1227812685.squirrel@webmailsunny.oslo.dnmi.no>
Permalink:  ../49362.85.166.86.82.1227812685.squirrel@webmailsunny.oslo.dnmi.no/
Also on:    postgresql.org/message-id/49362.85.166.86.82.1227812685.squirrel@webmailsunny.oslo.dnmi.no

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: vegard.bones@met.no, tgl@sss.pgh.pa.us
  Subject: Re: Deteriorating performance when loading large objects
  In-Reply-To: <49362.85.166.86.82.1227812685.squirrel@webmailsunny.oslo.dnmi.no>
  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>

* 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