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 DB40AD1B555; Fri, 21 Nov 2003 17:10:07 +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 42743-08; Fri, 21 Nov 2003 13:09:37 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 3C18BD1B54E; Fri, 21 Nov 2003 13:09:36 -0400 (AST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3955426; Fri, 21 Nov 2003 09:10:21 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Matthew T. O'Connor" , Robert Treat Subject: Re: [HACKERS] More detail on settings for pgavd? Date: Fri, 21 Nov 2003 09:09:00 -0800 User-Agent: KMail/1.4.3 Cc: Shridhar Daithankar , pgsql-performance@postgresql.org, PostgreSQL-development References: <200311181558.45847.josh@agliodbs.com> <1069424054.29672.12462.camel@camel> <3FBE21D5.20508@zeut.net> In-Reply-To: <3FBE21D5.20508@zeut.net> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200311210909.00978.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/191 X-Sequence-Number: 4751 Matthew, > As long as pg_autovacuum remains a contrib module, I don't think any > changes to the system catelogs will be make. If pg_autovacuum is > deemed ready to move out of contrib, then we can talk about the above. But we could create a config file that would store stuff in a flatfile table, OR we could add our own "system table" that would be created when one "initializes" pg_avd. Just an idea. Mind you, I'm not so sure that we want to focus immediately on per-table settings. I think that we want to get the "automatic" settings working fairly well first; a lot of new DBAs would use the per-table settings to shoot themselves in the foot. So we need to be able to make a strong recommendation to "try the automatic settings first." > Are you saying that you can vacuum a 1 million row table in 2-4 > minutes? While a vacuum of the same table with an additional 1 million > dead tuples would take an hour? I'm probably exaggerating. I do know that I can vacuum a fairly clean 1-5 million row table in less than 4 mintues. I've never let such a table get to 50% dead tuples, so I don't really know how long that takes. Call me a coward if you like ... > >I'd be really reluctant to base pv-avd frequency on the fsm settings > > instead. What if the user loads 8GB of data but leaves fsm_pages at the > > default of 10,000? You can't do much with that; you'd have to vacuum if > > even 1% of the data changed. > > Ok, but as you said above it's very easy to set the FSM once you know > your db size. Actually, thinking about this I realize that PG_AVD and the Perl-based postgresql.conf configuration script I was working on (darn, who was doing that with me?) need to go togther. With pg_avd, setting max_fsm_pages is very easy; without it its a bit of guesswork. So I think we can do this: for 'auto' settings: If max_fsm_pages is between 13% and 100% of the total database pages, then set the vacuum scale factor to match 3/4 of the fsm_pages setting, e.g. database = 18,000,000 data pages; max_fsm_pages = 3,600,000; set vacuum scale factor = 3.6mil/18mil * 3/4 = 0.15 If max_fsm_pages is less than 13% of database pages, issue a warning to the user (log it, if possible) and set scale factor to 0.1. If it's greater than 100% set it to 1 and leave it alone. > I don't have a good plan as to how to incorporate > this data, but to a large extent the FSM already tracks table activity > and gives us the most accurate answer about storage growth (short of > using something like contrib/pgstattuple which takes nearly the same > amount of time as an actual vacuum) I don't really think we need to do dynamic monitoring at this point. It would be a lot of engineering to check data page pollution without having significant performance impact. It's doable, but something I think we should hold off until version 3. It would mean hacking the FSM, which is a little beyond me right now. > In my testing, I never changed the default statistics settings. Ah. Well, a lot of users do to resolve query problems. > 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. No, but for scaling you don't need the dynamic count of tuples or of dead tuples; pg_class holds a reasonable accurate count of pages per table as of last vacuum. -- Josh Berkus Aglio Database Solutions San Francisco