Re: Maximum reasonable free space map

On Tue, Dec 16, 2008 at 5:55 PM, Phillip Berry
<pberry(at)stellaconcepts(dot)com> wrote:
> Hi Everyone,
>
> Just wondering what the maximum reasonable free space map setting should be? I'm receiving the
> following advice from vacuum:
>
> INFO: free space map contains 170803 pages in 117 relations
> DETAIL: A total of 185000 page slots are in use (including overhead).
> 733008 page slots are required to track all free space.
> Current limits are: 185000 page slots, 5000 relations, using 1623 KB.
> NOTICE: number of page slots needed (733008) exceeds max_fsm_pages (185000)
> HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 733008.
>
> I thought 185K was pretty high, is going to 700K+ reasonable? I've got 16GB of ram and am running
> very high volume 100GB+ DBs.

It's all about the size of your tables. If you've got 1 table with
100k rows that's updated a lot then an fsm of 100k is likely
reasonable, assuming you've got autovac keeping things in check. Got
4G rows but none are ever updated, then you don't need much if any
fsm.

If you've got 40M rows and 10% are updated each day, then it's likely
you'll want 4M fsm entries avaialble for those dead rows.

I think that as long as you're not using a huge amount of shared
memory it's nothing to worry about much, as long as it's not too
small. We had to go to 1Million fsm entries because we routinely have
400k to 600k dead rows in our db at work.