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 2BD7ED1C4D4; Fri, 21 Nov 2003 22:41:21 +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 99775-06; Fri, 21 Nov 2003 18:40:48 -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 E9ED6D1B553; Fri, 21 Nov 2003 18:40:45 -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 <0HOQ00L974CHVV@mta1.srv.hcvlny.cv.net>; Fri, 21 Nov 2003 17:41:07 -0500 (EST) Date: Fri, 21 Nov 2003 17:40:45 -0500 From: "Matthew T. O'Connor" Subject: Re: [HACKERS] More detail on settings for pgavd? In-reply-to: <200311211349.58788.josh@agliodbs.com> To: josh@agliodbs.com Cc: Robert Treat , Shridhar Daithankar , pgsql-performance@postgresql.org, PostgreSQL-development Message-id: <3FBE946D.4050708@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> <200311211323.19003.josh@agliodbs.com> <3FBE891D.8010309@zeut.net> <200311211349.58788.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/196 X-Sequence-Number: 4756 Josh Berkus wrote: >Matthew, > > >>I certainly agree that less than 10% would be excessive, I still feel >>that 10% may not be high enough though. That's why I kinda liked the >>sliding scale I mentioned earlier, because I agree that for very large >>tables, something as low as 10% might be useful, but most tables in a >>database would not be that large. >> >> > >Yes, but I thought that we were taking care of that through the "threshold" >value? > > Well the threshold is a combination of the base value and the scaling factor which you are proposing is 0.1, so the threshold is base + (scaling factor)(num of tuples) So with the default base of 1000 and your 0.1 you would have this: Num Rows threshold Percent 1,000 1,100 110% 10,000 2,000 20% 100,000 11,000 11% 1,000,000 102,000 10% I don't like how that looks, hence the thought of some non-linear scaling factor that would still allow the percent to reach 10%, but at a slower rate, perhaps just a larger base value would suffice, but I think small table performance is going to suffer much above 1000. Anyone else have an opinion on the table above? Good / Bad / Indifferent? >A sliding scale would also be OK. However, that would definitely require a >leap to storing per-table pg_avd statistics and settings. > > > I don't think it would, it would correlate the scaling factor with the number of tuples, no per-table settings required. >>Only that pg_autovacuum isn't smart enough to kick off more than one >>vacuum at a time. Basically, pg_autovacuum issues a vacuum on a table >>and waits for it to finish, then check the next table in it's list to >>see if it needs to be vacuumed, if so, it does it and waits for that >>vacuum to finish. >> >> > >OK, then, we just need to detect the condition of the vacuums "piling up" >because they are happening too often. > > > That would be good to look into at some point, especially if vacuum is going to get slower as a result of the page loop delay patch that has been floating around.