From: | Marcos Ortiz <mlortiz(at)uci(dot)cu> |
---|---|
To: | ahchuan <chiochuan(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: postgresql.conf setting for max_fsm_pages |
Date: | 2012-04-05 13:56:44 |
Message-ID: | 4F7DA49C.2050308@uci.cu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2012-04-05 14:26:12 | Re: bad plan |
Previous Message | Glyn Astill | 2012-04-05 13:10:01 | Re: H800 + md1200 Performance problem |