Re: number of page slots needed (1576544) exceeds max_fsm_pages (204800)]

Does this max_fsm_pages value seem OK for a 46GB database?
I've clustered all the tables that seemed to be exhibiting large amounts
of bloat.

reporting=# SELECT pg_size_pretty(pg_database_size('reporting'));
pg_size_pretty
----------------
46 GB
(1 row)

NOTICE: number of page slots needed (1576544) exceeds max_fsm_pages (204800)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 1576544.

On Mon, Jan 18, 2010 at 1:47 PM, Reid Thompson <reid(dot)thompson(at)ateb(dot)com> wrote:
> Does this max_fsm_pages value seem OK for a 46GB database?
> I've clustered all the tables that seemed to be exhibiting large amounts
> of bloat.

My big DB is about 70 on disk. I have fsm pages set to 3.4 million,
and occasionally that gets overrun. It is nearly catastrophic to us
when that happens as performance takes a serious nose dive. This is
probably the major reason switching to 8.4 is high on our list. Our
DB has a *lot* of data churn, and that makes a lot of pages with space
on them to track.

One more thing you may wish to consider is running re-index on your
tables. I found that a lot of pages with empty space were compacted
and the number of fsm entries went down significantly when I did this
last week. For me this was more important than running cluster to
pack the data tables themselves.

On Mon, Jan 18, 2010 at 02:43:11PM -0500, Vick Khera wrote:
> On Mon, Jan 18, 2010 at 1:47 PM, Reid Thompson <reid(dot)thompson(at)ateb(dot)com> wrote:
> > Does this max_fsm_pages value seem OK for a 46GB database?
> > I've clustered all the tables that seemed to be exhibiting large amounts
> > of bloat.
>
>
> One more thing you may wish to consider is running re-index on your
> tables. I found that a lot of pages with empty space were compacted
> and the number of fsm entries went down significantly when I did this
> last week. For me this was more important than running cluster to
> pack the data tables themselves.

It was my belief that cluster would re-build the indexes as part of the
cluster operation. Is that belief incorrect?

On Mon, Jan 18, 2010 at 12:43 PM, Vick Khera <vivek(at)khera(dot)org> wrote:
> On Mon, Jan 18, 2010 at 1:47 PM, Reid Thompson <reid(dot)thompson(at)ateb(dot)com> wrote:
>> Does this max_fsm_pages value seem OK for a 46GB database?
>> I've clustered all the tables that seemed to be exhibiting large amounts
>> of bloat.
>
> My big DB is about 70 on disk.  I have fsm pages set to 3.4 million,
> and occasionally that gets overrun.  It is nearly catastrophic to us

We have about 2.8Million used and have it set to 10Million for the
same reason as you do.