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 6351BD1E9CD for ; Wed, 24 Mar 2004 15:35:36 +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 68486-02 for ; Wed, 24 Mar 2004 11:35:36 -0400 (AST) Received: from mail.unisoftbg.com (mail.unisoftbg.com [194.12.229.207]) by svr1.postgresql.org (Postfix) with ESMTP id 38470D1DD17 for ; Wed, 24 Mar 2004 11:35:29 -0400 (AST) Received: (qmail 1439 invoked by uid 507); 24 Mar 2004 16:40:15 -0000 Received: from unknown (HELO t1.unisoftbg.com) (pginfo%t1.unisoftbg.com@194.12.229.193) by 0 with SMTP; 24 Mar 2004 16:40:15 -0000 Message-ID: <4061996C.E230AF23@t1.unisoftbg.com> Date: Wed, 24 Mar 2004 15:21:32 +0100 From: pginfo X-Mailer: Mozilla 4.03 [en] (Win95; I) MIME-Version: 1.0 To: Bill Moran Cc: "pgsql-performance@postgresql.org" Subject: Re: slow vacuum performance References: <406160DF.44D0988C@t1.unisoftbg.com> <4061961F.6080009@potentialtech.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/350 X-Sequence-Number: 6206 Hi Bill, I am vacuuming every 24 h. I have a cron script about i. But if I make massive update (for example it affects 1 M rows) and I start vacuum, it take this 2 h. Also I will note, that this massive update is running in one transaction ( I can not update 100K and start vacuum after it). regards, ivan. Bill Moran wrote: > 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