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 637DA52943 for ; Tue, 28 Jun 2005 01:55:23 -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 14100-06 for ; Tue, 28 Jun 2005 04:55:22 +0000 (GMT) Received: from outbound.mailhop.org (outbound.mailhop.org [63.208.196.171]) by svr1.postgresql.org (Postfix) with ESMTP id 07CDB5292A for ; Tue, 28 Jun 2005 01:55:18 -0300 (ADT) Received: from ool-44c11fd8.dyn.optonline.net ([68.193.31.216] helo=[192.168.1.66]) by outbound.mailhop.org with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.51) id 1Dn87y-0005ji-46; Tue, 28 Jun 2005 00:55:18 -0400 X-Mail-Handler: MailHop Outbound by DynDNS.org X-Originating-IP: 68.193.31.216 X-Report-Abuse-To: abuse@dyndns.org (see http://www.mailhop.org/outbound/abuse.html for abuse reporting information) X-MHO-User: zeut Message-ID: <42C0D835.7000603@zeut.net> Date: Tue, 28 Jun 2005 00:55:17 -0400 From: "Matthew T. O'Connor" User-Agent: Mozilla Thunderbird 1.0.2-1.3.3 (X11/20050513) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Enzo D'addario Cc: pgsql-admin@postgresql.org Subject: Re: vacuum taking longer over time References: <1119919340.4885.5.camel@tuppy.pienetworks.com> In-Reply-To: <1119919340.4885.5.camel@tuppy.pienetworks.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200506/251 X-Sequence-Number: 18006 Enzo D'addario wrote: >Hi All, > >I currently have postgres 7.4.2 running on a dedicated server which has >1GB RAM, a Pentium 4 2600 Mhz processor, uses ReiserFS filesystem and is >running Debian stable. > >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. I >never experienced such issues with earlier versions of postgres. > >The DB is approximately 6 GB in size and is vacuumed analyzed nightly >because of a relatively high number of transactions per day. > >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? > > Not fragmented, but bloated. Sounds like you need to vacuum more often and / or increase your FSM settings. >Will upgrading to postgres 8.0 resolve this problem? > > No, I think a more agressive vacuum regiment and / or larger FSM settings will solve this even 7.4. Lots of people either setup cron to hit their more active tables more often, or use pg_autovacuum. Good luck.