On 7/18/2004 1:12 AM, Guthrie, Jeremy wrote:
> 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.

I suggest to stop thinking in gigabyte or anything like that because 
having 3 out of 10GB being free space does not tell if that is free 
space scattered around all over partially filled blocks or if it is 
fewer completely empty blocks.

Every block that has a certain amount of free space is worth an entry in 
the FSM. One entry is 6 bytes, so a database with a standard blocksize 
would need 768 KB of shared memory for each GB of data worst case, if 
every single block is worth remembering in the FSM.

So the max_fsm_pages parameter should reflect the number of partially 
filled or empty blocks. Looking at the size of it, the 250,000 I 
suggested is still not luxurious.


Jan

> 
> 
> 
> 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
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #