The free space map is the number of free slots(rows recovered from deletes via a vacuum).  If you have a 10 GB database and 5 gig of it is free space left over from a delete... and you have a FSM large enough to cover 2 gig, then 3 gig will be wasted as PostgreSQL won't be able to track it.  PostgreSQL will write over the 2 gig of free space it does know about and then start appending onto the existing DB footprint thereby orphaning 3 gig of space.  So in order to make sure PostgreSQL can track all recovered disk space, you'd need to give it enough room for the FSM to track 5 gig of free space.  From my experience, if you shut down PostgreSQL and then up the free space, you still need to issue a vacuum as the old FSM it 'saved' to disk won't have on record the other 3 gig.  The vacuum will force PostgreSQL to re-learn the of the 5 gig total free space at the expense of all the IO of scanning your table space.



From: Erik G. Burrows
Sent: Sat 7/17/2004 12:17 PM
To: slony1-general at gborg.postgresql.org
Subject: Re: [Slony1-general] Performance


> >> What is the size of your database, the size of your shared buffer 
> >> configuration, the work_mem settings and what is the read/write ratio of 
> >> your applications database access pattern? What is the average, min and 
> >> max row size of the replicated data?
> > 
> > DB size is about 3GB. System RAM is 2GB. Shared buffers are at 16384
> > (130MB). Read/write ratio is probably over 99% read oriented. Row sizes
> > are fairly average. No huge strings, and column counts are sane, 5-20
> > columns per table, about 60 tables.
> 
> Under all those conditions you listed, I would shoot for the FSM. Adjust 
> max_fsm_pages to 250000 (yes, that's a quarter million), restart the 
> postmaster and do a "vacuum full analyze" on the whole database. Then 
> arrange for frequent vacuuming with pg_autovacuum or your own schedule.

After turning up max_fsm_pages to 250,000, and max_fsm_relations to
12,500 (to maintain the 20:1 ratio in the default configuration), and
did a vacuum full analyze, server load went up (cpu idle time down,
context switches/sec up), but gradually reduced to the levels before I
started having these troubles.

In fact it worked so well, I had to check to make sure Slony-I was still
running! It is, and the fetch statement durations are down to < 1ms
times.

So, that helped enormously. Would you mind explaining why?

I can't seem to find a good explanation of the function the free space
map serves, other than a message from Tom Lane regarding some
enhancements to it, which gives some hints.

Was it just Slony-I's high-turnover log tables which triggered this
problem?

Thanks,
  -Erik
-- 
Erik G. Burrows - KG6HEA                          www.erikburrows.com
PGP Key: http://www.erikburrows.com/files/erik.erikburrows.com.pgpkey

_______________________________________________
Slony1-general mailing list
Slony1-general at gborg.postgresql.org
http://gborg.postgresql.org/mailman/listinfo/slony1-general
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://gborg.postgresql.org/pipermail/slony1-general/attachments/20040718/7928612a/attachment.html