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 CD832D1D790; Fri, 21 Nov 2003 14:14:54 +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 90152-01; Fri, 21 Nov 2003 10:14:27 -0400 (AST) Received: from bramble.mmrd.com (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id 80337D1D7BD; Fri, 21 Nov 2003 10:14:23 -0400 (AST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by bramble.mmrd.com (8.12.8/8.12.8) with ESMTP id hALDL7cM007075; Fri, 21 Nov 2003 08:21:07 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id hALEEEl07166; Fri, 21 Nov 2003 09:14:15 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id V3JR4X5P; Fri, 21 Nov 2003 09:14:13 -0500 Subject: Re: [HACKERS] More detail on settings for pgavd? From: Robert Treat To: "Matthew T. O'Connor" Cc: Josh Berkus , Shridhar Daithankar , pgsql-performance@postgresql.org, PostgreSQL-development In-Reply-To: <3FBD5EEF.7060205@zeut.net> References: <200311181558.45847.josh@agliodbs.com> <3FBC6BED.9090809@myrealbox.com> <3FBCD013.3030402@zeut.net> <200311200918.30838.josh@agliodbs.com> <3FBD5EEF.7060205@zeut.net> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 21 Nov 2003 09:14:14 -0500 Message-Id: <1069424054.29672.12462.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/186 X-Sequence-Number: 4746 On Thu, 2003-11-20 at 19:40, Matthew T. O'Connor wrote: > I'm open to discussion on changing the defaults. Perhaps what it would > be better to use some non-linear (perhaps logorithmic) scaling factor. > So that you wound up with something roughly like this: > > #tuples activity% for vacuum > 1k 100% > 10k 70% > 100k 45% > 1M 20% > 10M 10% > 100M 8% > Just thinking out loud here, so disregard if you think its chaff but... if we had a system table pg_avd_defaults that held what we generally consider the best default percentages based on reltuples/pages, and added a column to pg_class (could be some place better but..) which could hold an overriding percentage, you could then have a column added to pg_stat_all_tables called vacuum_percentage, which would be a coalesce of the override percentage or the default percentages based on rel_tuples (or rel_pages). This would give autovacuum a place to look for each table as to when it should vacuum, and gives administrators the option to tweak it on a per table basis if they find they need a specific table to vacuum at a different rate than the "standard". Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL