X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0C9539DC999 for ; Tue, 7 Feb 2006 21:03:38 -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 87836-09 for ; Tue, 7 Feb 2006 21:03:41 -0400 (AST) 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 D1A719DC80F for ; Tue, 7 Feb 2006 21:03:35 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id AAC9439842; Wed, 8 Feb 2006 01:03:39 +0000 (UTC) Date: Tue, 7 Feb 2006 19:03:39 -0600 From: "Jim C. Nasby" To: Markus Schaber Cc: pgsql-performance@postgresql.org Subject: Re: Default autovacuum settings too conservative Message-ID: <20060208010339.GT38134@pervasive.com> References: <20060201211633.GM95850@pervasive.com> <43E12A03.6070906@zeut.net> <20060207050545.GZ1240@pervasive.com> <20060207072621.GI13592@pervasive.com> <43E89506.40903@logix-tt.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43E89506.40903@logix-tt.com> X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.104 required=5 tests=[AWL=0.104] X-Spam-Score: 0.104 X-Spam-Level: X-Archive-Number: 200602/61 X-Sequence-Number: 17047 On Tue, Feb 07, 2006 at 01:39:34PM +0100, Markus Schaber wrote: > Hi, Jim, > > Jim C. Nasby wrote: > > > What we really need is a replacement for vacuum_delay that takes > > PostgreSQL generated IO activity into account... > > There are also other ideas which can make vacuum less painfull: > > - Use a "delete"-map (like the free space map) so vacuum can quickly > find the pages to look at. Already on TODO. > - Have vacuum end its transaction after a certain amount of work, and > restart at the same page later. AFAIK this isn't possible with the current way vacuum works. > - Have vacuum full search good candidates with non-stopping lock (and > usage of delete-map and fsm), then doing {lock, recheck, move, unlock} > in small amounts of data with delay between. This isn't an issue of locks, it's an issue of long-running transactions. It *might* be possible for vacuum to break work into smaller transactions, but I'm pretty sure that would be a non-trivial amount of hacking. > - Introducing some load measurement, and a pressure measurement (number > of deleted rows, TID wraparound etc.). Then start vacuum when load is > low or pressure is very high. Tune other parameters (like "certain > amount of work" depending on those measures. Which is essentially what I was suggesting... > All of them are a lot of code to hack, but although I'm not a postgresql > core developer, I am keen enough to invite you to send patches. :-) Well, if you know C then you're already 1 step closer to being able to change these kinds of things than I am. -- 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