X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5453FD1D767; Fri, 21 Nov 2003 15:18:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 94367-04; Fri, 21 Nov 2003 11:17:33 -0400 (AST) Received: from mta1.srv.hcvlny.cv.net (mta1.srv.hcvlny.cv.net [167.206.5.67]) by svr1.postgresql.org (Postfix) with ESMTP id 4F50BD1D450; Fri, 21 Nov 2003 11:17:29 -0400 (AST) Received: from zeut.net (ool-4352919e.dyn.optonline.net [67.82.145.158]) by mta1.srv.hcvlny.cv.net (iPlanet Messaging Server 5.2 HotFix 1.16 (built May 14 2003)) with ESMTP id <0HOP00KH2JTPEQ@mta1.srv.hcvlny.cv.net>; Fri, 21 Nov 2003 10:17:50 -0500 (EST) Date: Fri, 21 Nov 2003 10:17:31 -0500 From: "Matthew T. O'Connor" Subject: Re: [HACKERS] More detail on settings for pgavd? In-reply-to: <3FBE2A0E.3010308@myrealbox.com> To: Shridhar Daithankar Cc: Josh Berkus , pgsql-performance@postgresql.org, PostgreSQL-development Message-id: <3FBE2C8B.9040304@zeut.net> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7BIT X-Accept-Language: en-us, en User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031013 Thunderbird/0.3 References: <200311181558.45847.josh@agliodbs.com> <200311200918.30838.josh@agliodbs.com> <3FBD5EEF.7060205@zeut.net> <200311202224.45065.josh@agliodbs.com> <3FBE2791.8040404@zeut.net> <3FBE2A0E.3010308@myrealbox.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/190 X-Sequence-Number: 4750 Shridhar Daithankar wrote: > Matthew T. O'Connor wrote: > >> But we track tuples because we can compare against the count given by >> the stats system. I don't know of a way (other than looking at the >> FSM, or contrib/pgstattuple ) to see how many dead pages exist. > > I think making pg_autovacuum dependent of pgstattuple is very good idea. Only if pgstattuple can become much cheaper than it is now. Based on the testing I did when I wrote pg_autovacuum, pgstattuple cost nearly the same amount as a regular vacuum. Given that, what have we gained from that work? Wouldn't it just be better to run a vacuum and actually reclaim space rather than running pgstattuple, and just look and see if there is free space to be reclaimed? Perhaps we could use pgstattuple ocasionally to see if we are going a good job of keeping the amount of dead space to a reasonable level, but I'm still not really sure about this. > Probably it might be a good idea to extend pgstattuple to return pages > that are excessively contaminated and clean them ASAP. Step by step > getting closer to daemonized vacuum. I don't know of anyway to clean a particular set of pages. This is something that has been talked about (partial vacuums and such), but I think Tom has raised several issues with it, I don't remember the details. Right now the only tool we have to reclaim space is vacuum, a whole table at a time.