Thread: 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.thompson@ateb.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.thompson@ateb.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@khera.org> wrote: > On Mon, Jan 18, 2010 at 1:47 PM, Reid Thompson <reid.thompson@ateb.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.