X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4B26E11F6F65 for ; Sat, 15 Apr 2006 17:42:12 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.001 X-Spam-Level: X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id zCf9uNv1oQ-y for ; Sat, 15 Apr 2006 17:42:02 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.229]) by postgresql.org (Postfix) with ESMTP id AA23611F6F4C for ; Sat, 15 Apr 2006 17:42:03 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 67so442348wri for ; Sat, 15 Apr 2006 13:42:02 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=oENbbZbBtJSCiQDlxsMu+bDJNdXAPEuYvkPJDcoIMb+lBtN2sdaDI5xMmYTrRuP2srHhPQGhp6gdu+oZszcPbUEbCzv2UIjiVsMdRqoc+E2XTb2rFGIldDM0EAkkkSkkgIFm24dwEAFLKoyjJHDTm6lD53wGyQo6UR4YYUuO4IA= Received: by 10.65.194.11 with SMTP id w11mr749562qbp; Sat, 15 Apr 2006 13:42:02 -0700 (PDT) Received: by 10.65.53.2 with HTTP; Sat, 15 Apr 2006 13:42:02 -0700 (PDT) Message-ID: <1c66bda80604151342q28ed309cme7f0ad6548e24509@mail.gmail.com> Date: Sun, 16 Apr 2006 08:42:02 +1200 From: adey To: "Chris Hoover" Subject: Re: Autovacuum ideas Cc: "Anthony Ransley" , "pgsql-admin@postgresql.org" In-Reply-To: <1d219a6f0604120753h1cabb4c2n60f53403225dcf81@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13421_3216647.1145133722702" References: <1d219a6f0604111602m62445e60h425c5dd59219606e@mail.gmail.com> <443C39ED.6050706@aurema.com> <1d219a6f0604120753h1cabb4c2n60f53403225dcf81@mail.gmail.com> X-Archive-Number: 200604/136 X-Sequence-Number: 21305 ------=_Part_13421_3216647.1145133722702 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline If autovacuum is to be provided with facilities for individual table and time window controls, that can be done currently using VACUUM TABLE FULL. Currently this must be scheduled in crontab - a task scheduler in PGAdmin would be very helpful to do this instead of crontab that usually requires sysadmin access, or is heavily controlled. On 4/13/06, Chris Hoover wrote: > > This is what we are doing today (except we are on Linux :) ). However, > since there is talk of making autovacuum on by default and the advantages= of > having it run, it would be nice to be able to block out hours that it can > not run. It would also be very nice to have the database take care of al= l > of this via the config file and/or tables. That way, no external program= s > (cron or what ever) would be needed. > > Even nicer would be to have the time exclusion granular enough to be tabl= e > to exclude only certain tables. That way autovacuum can do it's work as > needed on the majority of the tables, but tables that the DBA knows will > cause problems during business hours could be excluded until the slow tim= e. > > > Any comments on this? > > > Chris > > > On 4/11/06, Anthony Ransley wrote: > > > > Hi Chris Hoover, > > > > > > There is already a method for this! Can i suggest that you set a O/S > > schedule to simple run vacuumdb.exe at the desired time, preferable lat= e > > evening, as the vacuum of large databases can take all night, based on > > my DBA experience. Don't for get to setup the uses of password files, a= s > > > > vacuumdb.exe will require a password and user account each end every > > time it runs. > > > > Anthony. > > > > Chris Hoover wrote: > > > > > I have an idea/request for the autovacuum daemon. > > > > > > As far as I can tell, there is no way to tell the daemon to only do > > > it's work between certain hours. Could this be added as an option? In > > > my situation, I have tables that if vacuumed during the day would > > > cause a noticeable slowdown to my users and has therefore been deemed > > > unacceptable by management. This is one thing that is preventing me > > > from turning on autovacuun,. However, due to the activity on them, > > > they really need to be vacuumed daily. It would be nice to be able to > > > tell autovacuum to run, but have a way to tell it to always sleep > > > during these hours ( i.e. 7am to 5pm daily). Has there been any > > > thought into a feature like this? > > > > > > Thanks, > > > > > > Chris > > > > > > > ------=_Part_13421_3216647.1145133722702 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline If autovacuum is to be provided with facilities for individual table and ti= me window controls, that can be done currently using VACUUM TABLE FULL. Cur= rently this must be scheduled in crontab - a task scheduler in PGAdmin woul= d be very helpful to do this instead of crontab that usually requires sysad= min access, or is heavily controlled.

On 4/13/06, = Chris Hoover <revoohc@gmail.com= > wrote:
This is what we are doing today (except we ar= e on Linux :) ).  However, since there is talk of making autovacuum on= by default and the advantages of having it run, it would be nice to be abl= e to block out hours that it can not run.  It would also be very nice = to have the database take care of all of this via the config file and/or ta= bles.  That way, no external programs (cron or what ever) would be nee= ded.=20

Even nicer would be to have the time exclusion granular enough to b= e table to exclude only certain tables.  That way autovacuum can do it= 's work as needed on the majority of the tables, but tables that the DBA kn= ows will cause problems during business hours could be excluded until the s= low time.=20


Any comments on this?
 

Chris


On 4/11/06, = Anthony Ransley <anthonyr@aur= ema.com > wrote:=20
Hi Chris Hoover,
=

There is already a method for this! Can i suggest that you set a O/= S=20
schedule to simple run vacuumdb.exe at the desired time, preferable lat= e
evening, as the vacuum of large databases can take all night, based on=
my DBA experience. Don't for get to setup the uses of password files, a= s=20
vacuumdb.exe will require a password and user account each end everytime it runs.

Anthony.

Chris Hoover wrote:

> I hav= e an idea/request for the autovacuum daemon.
>
> As far as I ca= n tell, there is no way to tell the daemon to only do=20
> it's work between certain hours. Could this be added as an option?= In
> my situation, I have tables that if vacuumed during the day wou= ld
> cause a noticeable slowdown to my users and has therefore been d= eemed=20
> unacceptable by management. This is one thing that is preventing m= e
> from turning on autovacuun,. However, due to the activity on them= ,
> they really need to be vacuumed daily. It would be nice to be abl= e to=20
> tell autovacuum to run, but have a way to tell it to always sleep<= br>> during these hours ( i.e. 7am to 5pm daily). Has there been any
= > thought into a feature like this?
>
> Thanks,
>
> Chris




------=_Part_13421_3216647.1145133722702--