pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Vegard Bønes <vegard.bones@met.no>
To: pgsql-performance@postgresql.org
Subject: Deteriorating performance when loading large objects
Date: Tue, 25 Nov 2008 12:54:23 -0000 (GMT)
Message-ID: <63696.85.166.86.82.1227617663.squirrel@webmailsunny.oslo.dnmi.no> (raw)

Hi,

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.

If I start loading on a freshly installed database, load times for this is
around 13 minutes, including generating the data to be stored. If I let
the database run for a few days, this takes much longer. After one or two
days, this goes down to almost an hour, with logs indicating that this
extra time is solely spent transferring the large objects from file to
database.

Turning autovacuum on or off seems to have no effect on this.

I have only made the following changes to  the default postgresql.conf file:
max_fsm_pages = 25000000
vacuum_cost_delay = 10
checkpoint_segments = 256

So, my question for you is: Why does this happen, and what can I do about it?

Regards,

Vegard Bønes





view thread (7+ messages)  latest in thread

Message-ID: <63696.85.166.86.82.1227617663.squirrel@webmailsunny.oslo.dnmi.no>
Permalink:  ../63696.85.166.86.82.1227617663.squirrel@webmailsunny.oslo.dnmi.no/
Also on:    postgresql.org/message-id/63696.85.166.86.82.1227617663.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
  Subject: Re: Deteriorating performance when loading large objects
  In-Reply-To: <63696.85.166.86.82.1227617663.squirrel@webmailsunny.oslo.dnmi.no>
  References: <63696.85.166.86.82.1227617663.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