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


>You need 100k pages minimum here.
>
>  
>
>>I don't actually understand exactly what this is telling me though!
>>    
>>
>
>Take a look at
>http://www.pervasivepostgres.com/lp/newsletters/2005/Insights_opensource_Nov.asp#3
>It's a good overview of the FSM, how it works, and how to understand
>vacuum verbose output. Though, I guess my opinion is biased since I
>wrote it... ;)
>  
>
OK, I read that and understand now what is happening - thanks!

(1) I think the FSM was a possible cause of the problem - the 
pg_statistics table had nowhere to store the space released by 
vacuuming.  I have fixed that by increasing FSM space by 10x for now and 
will monitor vacuumdb -av output periodically to make sure the database 
stays within the bounds of the FSM.

(2) I still think that the 8.0 default vacuum base threshold and scale 
mean that the pg_statistic table might never trigger the vacuum 
threshold and would like to check my understanding is correct:

The process of analyzing my tables causes some or all of the rows in the 
pg_statistic table to be updated.
.
Each row update in postgres causes a new tuple to be created to 
represent the new row version.  So each updated statistic makes the 
pg_stat_all_tables.n_tup_upd for pg_statistic climb by 1.  It also makes 
the pg_class.reltuples for pg_statistic climb by 1 (new row version).

 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")

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.

Thanks for all the help you've offered so far.

Robin




view thread (13+ messages)  latest in thread

Message-ID: <44001213.6060005@edesix.com>
Permalink:  ../44001213.6060005@edesix.com/
Also on:    postgresql.org/message-id/44001213.6060005@edesix.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: robin@edesix.com
  Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
  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>

* 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