From: Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
To: 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:37:13 +0530
Message-ID: <200311202037.13091.shridhar_daithankar@myrealbox.com> (raw)
In-Reply-To: <200311202029.47351.shridhar_daithankar@myrealbox.com>
References: <200311181558.45847.josh@agliodbs.com>
<3FBCD013.3030402@zeut.net>
<200311202029.47351.shridhar_daithankar@myrealbox.com>
On Thursday 20 November 2003 20:29, Shridhar Daithankar wrote:
> 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?..:-)
use this one. A warning added for too aggressive vacuumming. If it is OK by
everybody, we can send it to patches list.
Shridhar
Attachments:difffile (text/x-diff, 2.9K) download
*** README.pg_autovacuum.orig Thu Nov 20 19:58:29 2003
--- README.pg_autovacuum Thu Nov 20 20:35:34 2003
***************
*** 141,163 ****
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:
! vacuum_base_value + (vacuum_scaling_factor * "number of tuples in the table")
!
AnalyzeThreshold is equal to:
! analyze_base_value + (analyze_scaling_factor * "number of tuples in the table")
!
The AnalyzeThreshold defaults to half of the VacuumThreshold since it
represents a much less expensive operation (approx 5%-10% of vacuum),
and running ANALYZE more often should not substantially degrade system
performance.
Sleeping:
---------
--- 141,191 ----
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:
! vacuum_base_value + (vacuum_scaling_factor * "number of tuples in the
! table")
AnalyzeThreshold is equal to:
! analyze_base_value + (analyze_scaling_factor * "number of tuples in the
! table")
The AnalyzeThreshold defaults to half of the VacuumThreshold since it
represents a much less expensive operation (approx 5%-10% of vacuum),
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.+ + Base 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 + thatsuch installation tune their analyze threshold separately, rather than + relying upon the default behaviour.+ + Furthermore, for aggressive vacuum/analyze behaviour, it is recommended that+ scaling factor is set to less than 1. However too aggresive operation can affect+ performance of normal database operations adversely. Do not apply such setting+ to production databases without prior testing.+
Sleeping:
---------
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
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: <200311202037.13091.shridhar_daithankar@myrealbox.com>
References: <200311181558.45847.josh@agliodbs.com> <3FBCD013.3030402@zeut.net> <200311202029.47351.shridhar_daithankar@myrealbox.com> <200311202037.13091.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