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
