Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 2E9059FB7E0 for ; Fri, 25 May 2007 07:33:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 48703-03 for ; Fri, 25 May 2007 07:33:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 5E9D39FB8AD for ; Fri, 25 May 2007 07:33:04 -0300 (ADT) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 3ADE1739C5; Fri, 25 May 2007 11:33:03 +0100 (BST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 470D2274115; Fri, 25 May 2007 11:33:05 +0100 (BST) Message-ID: <4656BB5E.3000109@archonet.com> Date: Fri, 25 May 2007 11:33:02 +0100 From: Richard Huxton User-Agent: Thunderbird 2.0.0.0 (X11/20070326) MIME-Version: 1.0 To: Bastian Voigt Cc: pgsql-performance@postgresql.org Subject: Re: Performance Problem with Vacuum of bytea table (PG 8.0.13) References: <46569E6A.2060509@bastian-voigt.de> In-Reply-To: <46569E6A.2060509@bastian-voigt.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/491 X-Sequence-Number: 24881 Bastian Voigt wrote: > Hi *, > for caching large autogenerated XML files, I have created a bytea table > in my database so that the cached files can be used by multiple servers. > There are about 500 rows and 10-20 Updates per minute on the table. The > files stored in the bytea are anything from 10kB to 10MB. My PostgreSQL > version is 8.0.13 on Gentoo Linux (x86) with PostGIS 1.2.0. > > For vacuum I use the pg_autovacuum daemon. It decided to vacuum my cache > table about every 3 hours, the vacuum process takes 20-30 minutes > (oops!) every time. Try vacuuming every 3 minutes and see what happens. -- Richard Huxton Archonet Ltd