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
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