From: Marcos Ortiz <mlortiz@uci.cu>
To: ahchuan <chiochuan@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: postgresql.conf setting for max_fsm_pages
Date: Thu, 05 Apr 2012 09:56:44 -0400
Message-ID: <4F7DA49C.2050308@uci.cu> (raw)
In-Reply-To: <6838c23d-4980-458b-8428-8c5590580011@jx17g2000pbb.googlegroups.com>
References: <6838c23d-4980-458b-8428-8c5590580011@jx17g2000pbb.googlegroups.com>
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.cuhttp://www.facebook.com/universidad.ucihttp://www.flickr.com/photos/universidad_uci
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mlortiz@uci.cu, chiochuan@gmail.com
Subject: Re: postgresql.conf setting for max_fsm_pages
In-Reply-To: <4F7DA49C.2050308@uci.cu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox