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 B5A7D11F6D11 for ; Mon, 3 Apr 2006 20:55:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15642-06 for ; Mon, 3 Apr 2006 20:55:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 6E1A811F6D24 for ; Mon, 3 Apr 2006 20:55:02 -0300 (ADT) Received: from [10.180.0.159] (unknown [66.228.91.67]) (using TLSv1 with cipher RC4-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id B7A6356423; Mon, 3 Apr 2006 18:55:01 -0500 (CDT) In-Reply-To: <6991.1143862141@sss.pgh.pa.us> References: <674d1f8a0603290206u55f114e1y7bfc2f83696fbada@mail.gmail.com> <20060331174802.GS49405@pervasive.com> <20060331185037.GF14204@surnet.cl> <442DA0CA.1050901@zeut.net> <6991.1143862141@sss.pgh.pa.us> Mime-Version: 1.0 (Apple Message framework v746.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <06443D67-9F59-4C98-9A5A-46F80980C611@pervasive.com> Cc: "Matthew T. O'Connor" , Gourish Singbal , "pgsql-admin@postgresql.org" Content-Transfer-Encoding: 7bit From: Jim Nasby Subject: Re: auto vacuuming Date: Mon, 3 Apr 2006 11:09:51 -0500 To: Tom Lane X-Mailer: Apple Mail (2.746.3) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.746 required=5 tests=[DATE_IN_PAST_06_12=0.746] X-Spam-Score: 0.746 X-Spam-Level: X-Archive-Number: 200604/37 X-Sequence-Number: 21206 On Mar 31, 2006, at 9:29 PM, Tom Lane wrote: > "Matthew T. O'Connor" writes: >> I think the closest approximation of disabling autovacuum on a per >> database basis is to connect to the database in question and perform: >> update pg_autovacuum set enabled = 'false'; > > Not really gonna help unless you insert a row into pg_autovacuum for > each table in the database. True. > As I just commented in another reply, I don't actually believe in the > value of disabling autovac entirely --- it should at least be able to > fire when you are risking XID wraparound. What could make sense is to > push the thresholds up to very large values, such that autovac won't > fire until you've forgotten manual vacuums for a very long time. > And that you can already do on a per-database basis, using ALTER > DATABASE SET. (Or at least, it *should* work to do that; if the > autovac > process fails to absorb per-db values for its GUC variables, then we > ought to fix it. I'm too lazy to test it right now...) The problem with that is unless I missed a change in 8.1, autovac knows absolutely nothing about when manual vacuums have been run. To do that I'm pretty sure we'd need a catalog table that captured the statistic counts on each table when vacuum ran (and ideally an XID and a timestamp, too). If we end up with some kind of dirty page bitmap that might remove the need for that. Even if tweaking the thresholds did work you still can't do it at a per-database level. It doesn't seem unreasonable to support different autovac settings at the database level. -- Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461