Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A31709FB2E4 for ; Thu, 18 Jan 2007 10:05:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 91302-08 for ; Thu, 18 Jan 2007 10:05:29 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx00.pub.collaborativefusion.com (mx00.pub.collaborativefusion.com [206.210.89.199]) by postgresql.org (Postfix) with ESMTP id F119E9FB2E3 for ; Thu, 18 Jan 2007 10:05:29 -0400 (AST) Received: from vanquish.pgh.priv.collaborativefusion.com (vanquish.pgh.priv.collaborativefusion.com [192.168.2.61]) (SSL: TLSv1/SSLv3,256bits,AES256-SHA) by wingspan with esmtp; Thu, 18 Jan 2007 09:05:28 -0500 id 00056412.45AF7EA8.0000647B Date: Thu, 18 Jan 2007 09:05:28 -0500 From: Bill Moran To: "Gauri Kanekar" Cc: "Michael Glaesemann" , pgsql-performance@postgresql.org Subject: Re: Vacuum v/s Autovacuum Message-Id: <20070118090528.eec9e398.wmoran@collaborativefusion.com> In-Reply-To: <7e4ba9550701180546u38609442ic5103a834f7b7d0d@mail.gmail.com> References: <7e4ba9550701180524h5ebe3390v73292cb91aed89fa@mail.gmail.com> <7e4ba9550701180546u38609442ic5103a834f7b7d0d@mail.gmail.com> Organization: Collaborative Fusion X-Mailer: Sylpheed 2.3.0 (GTK+ 2.10.7; i386-portbld-freebsd6.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/308 X-Sequence-Number: 22909 In response to "Gauri Kanekar" : > On 1/18/07, Michael Glaesemann wrote: > > > > On Jan 18, 2007, at 22:24 , Gauri Kanekar wrote: > > > > > is autovacuum similar to vacuum full analyse verbose. > > > > http://www.postgresql.org/docs/8.2/interactive/routine- > > vacuuming.html#AUTOVACUUM > > > > Apparently, no FULL, no VERBOSE (which is only really useful if you > > want to see the results, not for routine maintenance). [please don't top-post] Actually, you can raise the debugging level in PostgreSQL and get something similar to VERBOSE. The only problem is that it also increases the amount of logging that occurs with everything. > We have autovacuum ON , but still postgres server warns to > increas max_fsm_pages value. > > Do autovacuum release space after it is over? Yes. If you're still getting warnings about max_fsm_pages while autovac is running, you need to do one of two things: 1) Increase max_fsm_pages 2) Adjust autovacuum's settings so it vacuums more often. Depending on this, you may also need to temporarily adjust max_fsm_pages, then manually vacuum -- you may then find that autovacuum can keep everything clean with lower settings of max_fsm_pages. Overall, the best settings for 1 and 2 depend on the nature of your workload, and simulation and monitoring will be required to find the best values. I feel that the docs on this are very good. If the amount of data that changes between runs of autovacuum is greater than max_fsm_pages, then vacuum will be unable to reclaim all the space. -- Bill Moran Collaborative Fusion Inc.