Thanks! One more thing to keep in mind when optimizing an installation.
You said if I have enough RAM to have a FSM big enough for the entire
database, I should do it. How much data does one FSM page cover?

Thanks again,
  -Erik G. Burrows

On Sat, 2004-07-17 at 11:17, Jan Wieck wrote:
> >> >> 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?
> 
> Your problems start when the update activity on your database once 
> overran the freespace map size. This could have happened during the 
> initial copying of the database (while the master is only collecting 
> changes and the slave cannot yet apply them) or when adding a table to a 
> set and generating a serial key for it (don't know if your schema 
> contained such non-pkey-crap-tables).
> 
> Now once your freespace map was too small, you start leaking blocks and 
> unless you start more or less constantly vacuuming, which is not a good 
> thing without the vacuum delay changes that come with 7.5, you will 
> probably leak blocks more and more. This now causes the database to get 
> expanded further and further instead of reusing freed space after vacuum.
> 
> Another little detail here is that hashtables, and the fsm is organized 
> in shared memory hashtables, work better even for small numbers of 
> entries actually stored, if they are prepared for more content. This is 
> because the number of hash buckets is larger and the number of key 
> conflicts and therefore the size of the hash chains decreases. So if you 
> have the memory to build an FSM for theoretically the whole DB, do it.
> 
> > 
> > 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.
> 
> Vacuum remembers in the FSM where in the DB blocks with free space in 
> them can be found. If a backend cannot find a FSM entry for a relation, 
> it will extend the relation with new blocks on INSERT and UPDATE. If you 
> touch more blocks between vacuum runs than your FSM can hold, vacuum 
> cannot record all of them, and you will lose the space in them.
> 
> > 
> > Was it just Slony-I's high-turnover log tables which triggered this
> > problem?
> 
> Could well be. I have seen problems with that and I will implement the 
> log switching for 1.1, so that Slony will switch back and forth and 
> fully reorganize the log once in a while.
> 
> 
> Jan
-- 
Erik G. Burrows - KG6HEA                          www.erikburrows.com
PGP Key: http://www.erikburrows.com/files/erik.erikburrows.com.pgpkey