Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6125A9FB2E8 for ; Thu, 18 Jan 2007 10:14:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 93385-09 for ; Thu, 18 Jan 2007 10:14:16 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.229]) by postgresql.org (Postfix) with ESMTP id 9FD849FB2E6 for ; Thu, 18 Jan 2007 10:14:27 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id i11so145262nzh for ; Thu, 18 Jan 2007 06:14:26 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=J7vAehYYFhPu0+xtcmPkONFSyxPV1kwn3779qrQCJc4CyOl7amif5HD/HF5ZXJQL8U/jDVE5C1DVRKEJSJuE8HxVnxj8F3R8na634/Xh4Sm7luu+t5A1wgZvONPN7Rap4s2RMJ+eP1XiuhKugIlQV+SS2NOqEbo+EALx7m19gQo= Received: by 10.65.222.11 with SMTP id z11mr923951qbq.1169129665933; Thu, 18 Jan 2007 06:14:25 -0800 (PST) Received: by 10.65.188.5 with HTTP; Thu, 18 Jan 2007 06:14:25 -0800 (PST) Message-ID: Date: Thu, 18 Jan 2007 19:14:25 +0500 From: "Shoaib Mir" To: "Bill Moran" Subject: Re: Vacuum v/s Autovacuum Cc: "Gauri Kanekar" , "Michael Glaesemann" , pgsql-performance@postgresql.org In-Reply-To: <20070118090528.eec9e398.wmoran@collaborativefusion.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_84731_22198118.1169129665009" References: <7e4ba9550701180524h5ebe3390v73292cb91aed89fa@mail.gmail.com> <7e4ba9550701180546u38609442ic5103a834f7b7d0d@mail.gmail.com> <20070118090528.eec9e398.wmoran@collaborativefusion.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/309 X-Sequence-Number: 22910 ------=_Part_84731_22198118.1169129665009 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline You will need to properly tune the thresholds for VACUUM and ANALYZE in case of autovacuuming process, so that you do not need to increase the max_fsm_pages oftenly... ------------- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/18/07, Bill Moran wrote: > > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > ------=_Part_84731_22198118.1169129665009 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline You will need to properly tune the thresholds for VACUUM and ANALYZE in case of autovacuuming process, so that you do not need to increase the max_fsm_pages oftenly...

-------------
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)

On 1/18/07, Bill Moran <wmoran@collaborativefusion.com> wrote:
In response to "Gauri Kanekar" <meetgaurikanekar@gmail.com >:
> On 1/18/07, Michael Glaesemann <grzm@seespotcode.net> 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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

------=_Part_84731_22198118.1169129665009--