Thread: FSM settings -- how to tell if they are working?

FSM settings -- how to tell if they are working?

From
Jeff Boes
Date:
We have both test and production databases, about 10 GB total in each.
Recently, I learned about "max_fsm_pages" and "max_fsm_relations". To get
a feel for what changing these settings would do to our production
database, I set the following values in the "test" database:

max_fsm_relations = 100
max_fsm_pages = 350000

but left the production server alone. How can I tell if these new
settings are having any effect?  How can I determine if these numbers are
the best choices?

We perform full vacuums on each of 103 tables every night (including many
very small tables, which exist only to provide references for foreign
keys -- "code tables").

--
Jeff Boes                                      vox 616.226.9550 ext 24
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise

Re: FSM settings -- how to tell if they are working?

From
Robert Treat
Date:
On Mon, 2003-01-27 at 13:25, Jeff Boes wrote:
> We have both test and production databases, about 10 GB total in each.
> Recently, I learned about "max_fsm_pages" and "max_fsm_relations". To get
> a feel for what changing these settings would do to our production
> database, I set the following values in the "test" database:
>
> max_fsm_relations = 100
> max_fsm_pages = 350000
>
> but left the production server alone. How can I tell if these new
> settings are having any effect?  How can I determine if these numbers are
> the best choices?
>
> We perform full vacuums on each of 103 tables every night (including many
> very small tables, which exist only to provide references for foreign
> keys -- "code tables").
>

I think you would have to parse your vacuum verbose output to determine
if disk usage is growing in accordance to how your data is coming in.
You might also be able to simply track the file sizes on disk in your
data directories to determine if any particular table is growing out of
control.

I will say this, you max_fsm_relations is set to low. Right now your
telling it to only keep track of 100 relations, but your vacuuming 103
tables, not to mention system tables as well. You should probably bump
this up to at least 200, for a more specific # search the archives,
there is a query that can tell you a good number to put this at.

Robert Treat