X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DDE2152881 for ; Mon, 27 Jun 2005 03:13:42 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24996-08 for ; Mon, 27 Jun 2005 06:13:35 +0000 (GMT) Received: from mail.pienetworks.com (unknown [210.9.196.17]) by svr1.postgresql.org (Postfix) with ESMTP id 14D2D52831 for ; Mon, 27 Jun 2005 03:13:33 -0300 (ADT) Received: from tuppy.pienetworks.com (tuppy.pienetworks.com [192.168.1.72]) by mail.pienetworks.com (Postfix) with ESMTP id CD39F3B1714 for ; Mon, 27 Jun 2005 14:13:17 +0800 (WST) Subject: vacuum taking longer over time From: Enzo D'addario To: pgsql-admin@postgresql.org Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0.2-5mdk Date: 27 Jun 2005 14:13:18 +0800 Message-Id: <1119852800.21305.26.camel@tuppy.pienetworks.com> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/245 X-Sequence-Number: 18000 To whom it may concern, I currently have postgres 7.4.2 running on a dedicated server which has 1GB RAM and a Pentium 4 2600 Mhz processor. After approximately 1 month I have to dump & restore the database because vacuum time spirals out of control. To give you an idea of what I experience, when the DB is restored it takes 1 hour to vacuum, however by the time the restore is 30 days old it can take up to 6 hours. The DB is approximately 6 GB in size and is vacuumed analyzed nightly because of a relatively high number of transactions per day. The file system is reiserfs and contains no errors. Another thing that I noticed from the vacuum logs is that the total pages needed shortly after restore is approximately 17000 and ends up over 50000 by the time the restore is 30 days old. Does this mean the DB is becoming fragmented? Will upgrading to postgres 8.0 resolve this problem? Any ideas would be greatly appreciated. Thanks in advance. Enzo D'Addario