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