Re: postgresql.conf setting for max_fsm_pages

On Wed, Apr 4, 2012 at 3:22 AM, ahchuan <chiochuan(at)gmail(dot)com> wrote:
> Hi All,
>
>
> I am new in using postgresSQL, I now support a system that been
> running on postgressql. Recently I found that the database are
> consuming the diskspace rapidly, it starting from 9GB and it now grow
> until 40GB within 4-5 month.
>
> I try to do a full vacuum to the database but then i get this error
>
> NOTICE:  number of page slots needed (1277312) exceeds max_fsm_pages
> (819200)
> HINT:  Consider increasing the configuration parameter "max_fsm_pages"
> to a value over 1277312.
> VACUUM

I assume you're on 8.3 or earlier. since 8.3 is going into retirement
soon, you'd be well served to look at upgrading.

> As from the postgres documentation, it was advice to set it to 20K to
> 200K which my current setting is set to 819200 which also over 200K
> already, so i just wonder what is the max number that i can set for
> the max_fsm_pages?

The docs are just a guideline for nominal databases.

> Is that any impact if i set the value to over 2M ?

The fsm uses 6 bytes of memory for each entry, so 2M = 12Megabytes,
I'm sure you can spare that much shared memory. I've run it at 10M or
higher before on production 8.3 servers.

The key is to make sure your vacuuming is aggresive enough. Even in
8.4 and above, where the fsm went away, if autovacuum isn't running or
isn't aggressive enough you'll get lots of dead space and bloat.

Look at the autovacuum_vacuum_cost_[delay|limit] settings.