pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <jnasby@pervasive.com>
To: Robin Iddon <robin@edesix.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
Date: Sat, 25 Feb 2006 17:01:28 -0600
Message-ID: <20060225230128.GO82012@pervasive.com> (raw)
In-Reply-To: <44001213.6060005@edesix.com>
References: <43FD7312.5080701@edesix.com>
	<20060223211835.GZ86022@pervasive.com>
	<43FE4A1A.7050903@edesix.com>
	<20060223235829.GP2068@pervasive.com>
	<44001213.6060005@edesix.com>

On Sat, Feb 25, 2006 at 08:15:15AM +0000, Robin Iddon wrote:
> From README.pg_autovacuum (and I think 22.1.4 of the 8.1.x document 
> says the same thing):
> 
> - 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")

By "number of tuples in the table", I'm pretty sure they mean visible
tuples; ie: the row count. In any case, I've seen pg_autovacuum work
with my own eyes, so as long as it stays running long enough there's no
reason it shouldn't end up vacuuming pg_statistics.

> In general deletes is 0 for the pg_statistic table so for the default 
> values we're looking at
> 
> n_tup_upd >= 1000 + (2.0 * reltuples)
> 
> to trigger a vacuum.
> 
> We know for each increment of n_tup_upd, reltuples will receive the same 
> increment.  Thus if the above expression isn't true for any non-zero 
> starting values of n_tup_upd and reltuples, it won't ever be true:
> 
> If:
> n_tup_upd < 1000 + (2.0 * reltuples)
> Then:
> n_tup_upd+X < 1000 + (2.0 * (reltuples+X))
> 
> I see that in 8.1.x this has been resolved by defaulting the scale to 
> 0.4.  Rightly or wrongly I have set my scale to 0.3.

You should probably drop the threshold to something closer to 200, too.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



view thread (13+ messages)

Message-ID: <20060225230128.GO82012@pervasive.com>
Permalink:  ../20060225230128.GO82012@pervasive.com/
Also on:    postgresql.org/message-id/20060225230128.GO82012@pervasive.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 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-admin@postgresql.org
  Cc: jnasby@pervasive.com, robin@edesix.com
  Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
  In-Reply-To: <20060225230128.GO82012@pervasive.com>
  References: <43FD7312.5080701@edesix.com> <20060223211835.GZ86022@pervasive.com> <43FE4A1A.7050903@edesix.com> <20060223235829.GP2068@pervasive.com> <44001213.6060005@edesix.com> <20060225230128.GO82012@pervasive.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