Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 7985664FC5F for ; Tue, 25 Nov 2008 08:54:36 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 33935-05 for ; Tue, 25 Nov 2008 08:54:33 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp1.oslo.dnmi.no (smtp1.oslo.dnmi.no [128.39.62.241]) by mail.postgresql.org (Postfix) with ESMTP id 8B50964FC38 for ; Tue, 25 Nov 2008 08:54:33 -0400 (AST) Received: from sunny.oslo.dnmi.no ([157.249.32.80] helo=webmailsunny.oslo.dnmi.no) by smtp1.oslo.dnmi.no with esmtp (Exim 4.69) (envelope-from ) id 1L4xQz-0004EO-BA for pgsql-performance@postgresql.org; Tue, 25 Nov 2008 12:54:32 +0000 Received: from www-data by webmailsunny.oslo.dnmi.no with local (Exim 3.35 #1) id 1L4xQt-00036N-00 for pgsql-performance@postgresql.org; Tue, 25 Nov 2008 12:54:23 +0000 Received: from 85.166.86.82 (SquirrelMail authenticated user vegardb) by webmailsunny.oslo.dnmi.no with HTTP; Tue, 25 Nov 2008 12:54:23 -0000 (GMT) Message-ID: <63696.85.166.86.82.1227617663.squirrel@webmailsunny.oslo.dnmi.no> Date: Tue, 25 Nov 2008 12:54:23 -0000 (GMT) Subject: Deteriorating performance when loading large objects From: "=?iso-8859-1?Q?Vegard_B=F8nes?=" To: X-Priority: 3 Importance: Normal Reply-To: vegard.bones@met.no X-Mailer: SquirrelMail (version 1.2.9) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200811/253 X-Sequence-Number: 31984 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