X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1B3ABD1EC44 for ; Wed, 24 Mar 2004 14:07:31 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 07124-05 for ; Wed, 24 Mar 2004 10:07:31 -0400 (AST) Received: from internet.potentialtech.com (h-66-167-251-6.phlapafg.covad.net [66.167.251.6]) by svr1.postgresql.org (Postfix) with ESMTP id 607AED1B905 for ; Wed, 24 Mar 2004 10:07:27 -0400 (AST) Received: from potentialtech.com (pa-plum1c-102.pit.adelphia.net [24.53.179.102]) by internet.potentialtech.com (Postfix) with ESMTP id C823469A7C; Wed, 24 Mar 2004 09:07:30 -0500 (EST) Message-ID: <4061961F.6080009@potentialtech.com> Date: Wed, 24 Mar 2004 09:07:27 -0500 From: Bill Moran User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.4) Gecko/20031005 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pginfo Cc: "pgsql-performance@postgresql.org" Subject: Re: slow vacuum performance References: <406160DF.44D0988C@t1.unisoftbg.com> In-Reply-To: <406160DF.44D0988C@t1.unisoftbg.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/349 X-Sequence-Number: 6205 pginfo wrote: > Hi, > > I am running pg 7.4.1 on linux box. > I have a midle size DB with many updates and after it I try to run > vacuum full analyze. > It takes about 2 h. > If I try to dump and reload the DB it take 20 min. > > How can I improve the vacuum full analyze time? How often are you vacuuming? If you've gone a LONG time since the last vacuum, it can take quite a while, to the point where a dump/restore is faster. A recent realization that I've had some misconceptions about vacuuming led me to re-read section 8.2 of the admin guide (on vacuuming) ... I highly suggest a review of these 3 pages of the admin manual, as it contains an excellent description of why databases need vacuumed, that one can use to determine how often vacuuming is necessary. -- Bill Moran Potential Technologies http://www.potentialtech.com