Re: postgresql.conf setting for max_fsm_pages

On 04/04/2012 05:22 AM, ahchuan 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
If you using max_fsm_pages, you are using the version 8.3. I recommend
to you that
you should update your system to major version. In PostgreSQL 9.0, for
example, VACUUM
FULL was rewritten and it does a better job.
Try to use autovacumm = on always

> I did a vacuum verbose.
> postgres=# vacuum verbose;
>
> and below is the result i got.
>
> INFO: free space map contains 1045952 pages in 1896 relations
> DETAIL: A total of 819200 page slots are in use (including overhead).
> 1114192 page slots are required to track all free space.
> Current limits are: 819200 page slots, 2000 relations, using 5007 kB.
> NOTICE: number of page slots needed (1114192) exceeds max_fsm_pages
> (819200)
> HINT: Consider increasing the configuration parameter "max_fsm_pages"
> to a value over 1114192.
> VACUUM

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?

My advice that you have to test your environment with a double value to
1114192,
postgres# SET max_fsm_pages = 2228384; if you need to use 8.3 versions yet.

But, again, you should upgrade your system to major version. There are a
lot of performance improvements
in the new versions.

>
> Is that any impact if i set the value to over 2M ?
>
> Thanks.
>
> Regards,
> Chio Chuan
>

--
Marcos Luis Ortíz Valmaseda (@marcosluis2186)
Data Engineer at UCI
http://marcosluis2186.posterous.com

10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS INFORMATICAS...
CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION

http://www.uci.cu
http://www.facebook.com/universidad.uci
http://www.flickr.com/photos/universidad_uci