Обсуждение: increasing max_fsm_pages.

On Mon, May 15, 2006 at 12:18:50PM +0530, Gourish Singbal wrote:
> Group.
>
> Need some help in understanding the tuning parameter.
>
> we got the below message on database wide vacuuming :-
> Vacuuming Result outcome on Sun May 14 20:22:47 PDT 2006 :-
> INFO:  free space map contains 195236 pages in 72 relations
> DETAIL:  A total of 200000 page slots are in use (including overhead).
> 201744 page slots are required to track all free space.
> Current limits are:  200000 page slots, 500 relations, using 1226 KB.
> NOTICE:  number of page slots needed (201744) exceeds max_fsm_pages (200000)
> HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a
> value over 201744.
> VACUUM
>
> We have 4 GB of RAM on the machine if we increase the max_fsm_pages to
> 300000 will the database performance be at stake ?.

I'd say more accurately it'll be at stake if you don't. Each page slot
takes 6 bytes, so you'll be using an additional 600k, which is
essentially nothing.

This article was written using the older vacuum output, but it might
still be informative for you:
http://www.pervasivepostgres.com/lp/newsletters/2005/Insights_opensource_Nov.asp#3
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Mon, May 15, 2006 at 09:55:23PM +0530, Gourish Singbal wrote:
> Thanks a lot for the information . We have modified the postgresql.conf file
> to have max_fsm_pages to 300000. And restarted the database after doing a
> Vacuum Full .
>
> INFO:  free space map contains 11518 pages in 56 relations
> DETAIL:  A total of 12320 page slots are in use (including overhead).
> 12320 page slots are required to track all free space.
> Current limits are:  300000 page slots, 500 relations, using 1812 KB.
> VACUUM

If you went from needing 200000 page slots to only needing 12000 you're
not vacuuming nearly often enough. You should either increase your
vacuuming schedule or use autovacuum (though it's defaults are pretty
high for most cases; I usually cut them in half).

> On 5/15/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> >
> >On Mon, May 15, 2006 at 12:18:50PM +0530, Gourish Singbal wrote:
> >> Group.
> >>
> >> Need some help in understanding the tuning parameter.
> >>
> >> we got the below message on database wide vacuuming :-
> >> Vacuuming Result outcome on Sun May 14 20:22:47 PDT 2006 :-
> >> INFO:  free space map contains 195236 pages in 72 relations
> >> DETAIL:  A total of 200000 page slots are in use (including overhead).
> >> 201744 page slots are required to track all free space.
> >> Current limits are:  200000 page slots, 500 relations, using 1226 KB.
> >> NOTICE:  number of page slots needed (201744) exceeds max_fsm_pages
> >(200000)
> >> HINT:  Consider increasing the configuration parameter "max_fsm_pages"
> >to a
> >> value over 201744.
> >> VACUUM
> >>
> >> We have 4 GB of RAM on the machine if we increase the max_fsm_pages to
> >> 300000 will the database performance be at stake ?.
> >
> >I'd say more accurately it'll be at stake if you don't. Each page slot
> >takes 6 bytes, so you'll be using an additional 600k, which is
> >essentially nothing.
> >
> >This article was written using the older vacuum output, but it might
> >still be informative for you:
> >
> >http://www.pervasivepostgres.com/lp/newsletters/2005/Insights_opensource_Nov.asp#3
> >--
> >Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> >Pervasive Software      http://pervasive.com    work: 512-231-6117
> >vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
> >
>
>
>
> --
> Best,
> Gourish Singbal

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461