Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 7B5409FBAE0 for ; Wed, 17 Oct 2007 13:07:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 24663-04 for ; Wed, 17 Oct 2007 13:07:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 9718B9FB9A1 for ; Wed, 17 Oct 2007 13:07:46 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-220-127-162.bk10-dsl.surnet.cl [201.220.127.162]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l9HG7jkP008106; Wed, 17 Oct 2007 09:07:46 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id DF46647C7A; Wed, 17 Oct 2007 13:07:42 -0300 (CLST) Date: Wed, 17 Oct 2007 13:07:42 -0300 From: Alvaro Herrera To: Stefano Dal Pra Cc: =?iso-8859-1?Q?St=E9phane?= Schildknecht , "pgsql-performance@postgresql.org" Subject: Re: Vacuum goes worse Message-ID: <20071017160742.GJ5737@alvh.no-ip.org> References: <47147AD8.9010704@postgresqlfr.org> <15640.1192544768@sss.pgh.pa.us> <4714D817.30701@postgresqlfr.org> <18249.1192550596@sss.pgh.pa.us> <4715F2ED.20604@postgresqlfr.org> <290a5abc0710170735w601b8336v2645e90a54be0e10@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <290a5abc0710170735w601b8336v2645e90a54be0e10@mail.gmail.com> User-Agent: Mutt/1.5.16 (2007-06-11) X-Virus-Scanned: ClamAV 0.91.1/4542/Tue Oct 16 13:31:56 2007 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Wed, 17 Oct 2007 09:07:46 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/206 X-Sequence-Number: 27228 Stefano Dal Pra escribió: > Your first post says vacuum goes worse (slower). > I see that you do not issue the -f option (FULL VACUUM). > > I had a similar situation with a server (with frequent update) > performing nightly vacuumdb. After a few many days it went > slower and slower. When you have that problem, the solution is to issue more plain vacuum (not full) more frequently. If it's a highly updated table, then maybe once per hour or more. It depends on the update rate. > With psql 8.2.x we adopted pg_autovacuum which seems to perform good, > even thought > i do not clearly understand whether it occasionally performs a full > vacuum (i think he does'nt). It doesn't because it's normally not necessary. Also, we don't want to be acquiring exclusive locks in a background automatic process, so if you really need vacuum full (and I question your need to) then you must issue it yourself. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.