X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 805E0D1B4E6; Thu, 20 Nov 2003 15:00:53 +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 42189-09; Thu, 20 Nov 2003 11:00:24 -0400 (AST) Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [192.108.102.143]) by svr1.postgresql.org (Postfix) with ESMTP id 99A8BD1B50A; Thu, 20 Nov 2003 11:00:20 -0400 (AST) Received: from daithan.intranet.pspl.co.in shridhar_daithankar@smtp-send.myrealbox.com [202.54.11.72] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 3.44 $ on Novell NetWare via secured & encrypted transport (TLS); Thu, 20 Nov 2003 08:00:08 -0700 From: Shridhar Daithankar To: "Matthew T. O'Connor" Subject: Re: [HACKERS] More detail on settings for pgavd? Date: Thu, 20 Nov 2003 20:29:47 +0530 User-Agent: KMail/1.5.4 Cc: Josh Berkus , pgsql-performance@postgresql.org, PostgreSQL-development References: <200311181558.45847.josh@agliodbs.com> <3FBC6BED.9090809@myrealbox.com> <3FBCD013.3030402@zeut.net> In-Reply-To: <3FBCD013.3030402@zeut.net> MIME-Version: 1.0 Content-Type: Multipart/Mixed; boundary="Boundary-00=_jbNv/4RC6mqXtJy" Message-Id: <200311202029.47351.shridhar_daithankar@myrealbox.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/173 X-Sequence-Number: 4733 --Boundary-00=_jbNv/4RC6mqXtJy Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline On Thursday 20 November 2003 20:00, Matthew T. O'Connor wrote: > Shridhar Daithankar wrote: > > I will submit a patch that would account deletes in analyze threshold. > > Since you want to delay the analyze, I would calculate analyze count as > > deletes are already accounted for in the analyze threshold. Yes. My bad. Deletes are not accounted in initializing analyze count but later they are used. > > I am still wary of inverting vacuum analyze frequency. You think it is > > better to set inverted default rather than documenting it? > > I think inverting the vacuum and analyze frequency is wrong. Me. Too. ATM all I can think of this patch attached. Josh, is it sufficient for you?..:-) Matthew, I am confyused about one thing. Why would autovacuum count updates while checking for analyze threshold? Analyze does not change statistics right? ( w.r.t line 1072, pg_autovacuum.c). For updating statistics, only inserts+deletes should suffice, isn't it? Other than that, I think autovacuum does everything it can. Comments? Shridhar --Boundary-00=_jbNv/4RC6mqXtJy Content-Type: text/x-diff; charset="iso-8859-1"; name="difffile" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="difffile" *** README.pg_autovacuum.orig Thu Nov 20 19:58:29 2003 --- README.pg_autovacuum Thu Nov 20 20:26:39 2003 *************** *** 141,150 **** depending on the mixture of table activity (insert, update, or delete): ! - If the number of (inserts + updates + deletes) > AnalyzeThreshold, then only an analyze is performed. ! - If the number of (deletes + updates) > VacuumThreshold, then a vacuum analyze is performed. VacuumThreshold is equal to: --- 141,150 ---- depending on the mixture of table activity (insert, update, or delete): ! - If the number of (inserts + updates + deletes) >= AnalyzeThreshold, then only an analyze is performed. ! - If the number of (deletes + updates) >= VacuumThreshold, then a vacuum analyze is performed. VacuumThreshold is equal to: *************** *** 158,163 **** --- 158,186 ---- and running ANALYZE more often should not substantially degrade system performance. + Examples: + + Following table shows typical usage of pg_autovacuum settings. + These are put here so that a DBA can have some starting point while + tuning pg_autovacuum. + + Vacuum is triggered by updates and deletes. So in case of vacuum, + last column indicates total of updates and deletes required + to trigger vacuum. In case of analyze, the operations would count total + number of inserts, updates and deletes. + + Threshold Scaling factor Records No. of Operations + 1,000 1 10,000 11,000 + 1,000 2 10,000 21,000 + 1,000 0.5 10,000 6,000 + 1,000 0.1 10,000 2,000 + + Although analyze is cheaper operation compared to vacuum, + it might be needed less often. The default is to analyze twice as much as + vacuum but that might be too aggressive for some installations. It is advised that + such installation tune their analyze threshold separately, rather than relying upon + the default behaviour. + Sleeping: --------- --Boundary-00=_jbNv/4RC6mqXtJy--