X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id A9B2A11F676B for ; Wed, 12 Apr 2006 12:39:13 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.464 X-Spam-Level: X-Spam-Status: No, score=-2.464 required=5 tests=[BAYES_00=-2.599, FORGED_RCVD_HELO=0.135] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id pl2X-+NnvICo for ; Wed, 12 Apr 2006 12:38:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail2.dbitech.ca (radius.wavefire.com [64.141.13.252]) by postgresql.org (Postfix) with SMTP id 1169F11F690F for ; Wed, 12 Apr 2006 12:38:51 -0300 (ADT) Received: (qmail 25900 invoked from network); 12 Apr 2006 15:59:20 -0000 Received: from dbitech.internal.wavefire.ca (64.141.15.12) by radius.wavefire.com with SMTP; 12 Apr 2006 15:59:20 -0000 From: Darcy Buskermolen Organization: Wavefire Technologies Corp To: pgsql-admin@postgresql.org Subject: Re: Autovacuum ideas Date: Wed, 12 Apr 2006 08:39:08 -0700 User-Agent: KMail/1.8.3 Cc: "Chris Hoover" , "Anthony Ransley" References: <1d219a6f0604111602m62445e60h425c5dd59219606e@mail.gmail.com> <443C39ED.6050706@aurema.com> <1d219a6f0604120753h1cabb4c2n60f53403225dcf81@mail.gmail.com> In-Reply-To: <1d219a6f0604120753h1cabb4c2n60f53403225dcf81@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-6" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200604120839.08978.darcy@wavefire.com> X-Archive-Number: 200604/110 X-Sequence-Number: 21279 On Wednesday 12 April 2006 07:53, 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 > all of this via the config file and/or tables. That way, no external > programs (cron or what ever) would be needed. > > Even nicer would be to have the time exclusion granular enough to be 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 knows will > cause problems during business hours could be excluded until the slow time. What about leaving autovac running, and poke values into the pg_autovacuum table via cron? > > > 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 late > > 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, as > > 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 -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759