pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
To: Matthew T. O'Connor <matthew@zeut.net>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org, PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] More detail on settings for pgavd?
Date: Thu, 20 Nov 2003 20:29:47 +0530
Message-ID: <200311202029.47351.shridhar_daithankar@myrealbox.com> (raw)
In-Reply-To: <3FBCD013.3030402@zeut.net>
References: <200311181558.45847.josh@agliodbs.com>
	<3FBC6BED.9090809@myrealbox.com>
	<3FBCD013.3030402@zeut.net>

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

Attachments:

difffile (text/x-diff, 1.9K) download *** 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: ---------

view thread (35+ messages)  latest in thread

Message-ID: <200311202029.47351.shridhar_daithankar@myrealbox.com>
Permalink:  ../200311202029.47351.shridhar_daithankar@myrealbox.com/
Also on:    postgresql.org/message-id/200311202029.47351.shridhar_daithankar@myrealbox.com

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: shridhar_daithankar@myrealbox.com, matthew@zeut.net, josh@agliodbs.com, pgsql-hackers@postgresql.org
  Subject: Re: [HACKERS] More detail on settings for pgavd?
  In-Reply-To: <200311202029.47351.shridhar_daithankar@myrealbox.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox