X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 061279DCC8D for ; Wed, 1 Feb 2006 14:04:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00574-03 for ; Wed, 1 Feb 2006 14:04:05 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 382CE9DCD17 for ; Wed, 1 Feb 2006 14:04:03 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 51E9F325ED; Wed, 1 Feb 2006 19:04:03 +0100 (MET) From: Chris Browne X-Newsgroups: pgsql.hackers Subject: Re: autovacuum Date: Wed, 01 Feb 2006 12:54:42 -0500 Organization: cbbrowne Computing Inc Lines: 35 Message-ID: <60lkwvklr1.fsf@dba2.int.libertyrms.com> References: <1137992841.28304.48.camel@localhost.localdomain> <5001.71.40.140.99.1138286468.squirrel@mail.tocr.com> <60psm7knlq.fsf@dba2.int.libertyrms.com> <20060201172905.GA7879@surnet.cl> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.18 (linux) Cancel-Lock: sha1:MxRWahIJ2T9OOIfbylYO6D0qT/g= To: pgsql-hackers@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.254 required=5 tests=[AWL=0.254] X-Spam-Score: 0.254 X-Spam-Level: X-Archive-Number: 200602/16 X-Sequence-Number: 79154 alvherre@alvh.no-ip.org (Alvaro Herrera) writes: > Chris Browne wrote: > >> It strikes me as a slick idea for autovacuum to take on that >> behaviour. If the daily backup runs for 2h, then it is quite futile >> to bother vacuuming a table multiple times during that 2h period when >> none of the tuples obsoleted during the 2h period will be able to be >> cleaned out until the end. > > Hmm, yeah, sounds useful. There's one implementation issue to notice > however, and it's that the autovacuum process dies and restarts for each > iteration, so there's no way for it to remember previous state unless > it's saved somewhere permanent, as the stats info is. Hmm. It restarts repeatedly??? Hmmm... > However this seems at least slightly redundant with the "maintenance > window" feature -- you could set a high barrier to vacuum during the > daily backup period instead. (Anybody up for doing this job?) In effect, this would be an alternative to the "window" feature. You open the window by starting pg_dump; pg_autovacuum would automatically notice that as the eldest XID, and stop work until the pg_dump actually finished. In a way, it strikes me as more elegant; it would automatically notice "backup windows," noticing *exact* start and end times... -- select 'cbbrowne' || '@' || 'ntlug.org'; http://www.ntlug.org/~cbbrowne/linuxxian.html "I'm sorry, Mr. Kipling, but you just don't know how to use the English Language." -- Editor of the San Francisco Examiner, informing Rudyard Kipling, who had one article published in the newspaper, that he needn't bother submitting a second, 1889