Thread: max_fsm_pages increase

Hello

2010/10/31 AI Rumman <rummandba@gmail.com>:
> I using Postgresql 8.1 and during vacuum at night time, I am getting the
> following log:
> number of page slots needed (2520048) exceeds max_fsm_pages (356656)
> Do I need to increase max_fsm_pages to 2520048? Does it have any bad affect?

I takes a little more memory,

but if you can run VACUUM FULL, then do it. It reduce a free space
inside data files and it reduce a requests on max_fsm_pages.

Regards

Pavel Stehule

On Sun, Oct 31, 2010 at 2:43 AM, AI Rumman <rummandba@gmail.com> wrote:
> I using Postgresql 8.1 and during vacuum at night time, I am getting the
> following log:
> number of page slots needed (2520048) exceeds max_fsm_pages (356656)
> Do I need to increase max_fsm_pages to 2520048? Does it have any bad affect?

No, you should set it to something higher most times.  My production
dbs, which are in the 100G range of size, and have a lot of updates,
and very aggressive autovac setup, floats at around 2.5Million, and
has max fsm set to 10M.  It'll use a bit of shared mem (6 bytes per I
think) so for me that's 60Meg of shared memory.  Considering this
machine has shared_buffers set to 8 Gig, that's not a whole lot of
extra memory being used for me.

Note two things: If your database is not getting vacuumed aggressively
enough to keep up then you need to adjust autovacuum to keep up (more
threads, less sleep, higher cost limits).  Also, moving to 8.4 will
get you out of this hole, as the free space map was moved from
shared_memory to files on the hard drive.

On Sun, Oct 31, 2010 at 4:43 AM, AI Rumman <rummandba@gmail.com> wrote:
> I using Postgresql 8.1 and during vacuum at night time, I am getting the
> following log:
> number of page slots needed (2520048) exceeds max_fsm_pages (356656)
> Do I need to increase max_fsm_pages to 2520048? Does it have any bad affect?

You don't *have* to do it.  The consequences of not doing it are: 1)
your server will not know all of the pages in the files holding your
database that there are empty slots available for use.  2) because of
that lack of knowledge, it may then allocate new pages to hold your
data, causing potentially more bloat, and the need for even more FSM
pages.  3) Allocating new pages usually costs more than just filling
in space on existing page, so your system slows down.

If I were you, I'd set the FSM pages to double what your current need
is, run vacuum again, and you should be good for a while. It will
unfortunately, require a restart of your postgres server.