Thread: determining max_fsm_pages
Pg: 7.4.5 8G ram 200G RAID5 I have my fsm set as such: max_fsm_pages = 300000 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 500 # min 100, ~50 bytes each I just did a vacuum full on one table and saw this result: INFO: analyzing "cdm.cdm_fed_agg_purch" INFO: "cdm_fed_agg_purch": 667815 pages, 3000 rows sampled, 52089570 estimated total rows My question is this: I have about 8 databases running on this server. When I do a vacuum full on each of these databases, there is a INFO section that I assume is the total pages used for that database. Should add ALL these individual pages together and pad the total and use this as my new max_fsm_pages? Should I do the same thing with max_fsm_relations? TIA Patrick
> Pg: 7.4.5 > 8G ram > 200G RAID5 > > I have my fsm set as such: > max_fsm_pages = 300000 # min max_fsm_relations*16, 6 bytes each > max_fsm_relations = 500 # min 100, ~50 bytes each > > > I just did a vacuum full on one table and saw this result: > INFO: analyzing "cdm.cdm_fed_agg_purch" > INFO: "cdm_fed_agg_purch": 667815 pages, 3000 rows sampled, 52089570 > estimated total rows > > > My question is this: I have about 8 databases running on this server. > When I do a vacuum full on each of these databases, there is a INFO > section that I assume is the total pages used for that database. Should > add ALL these individual pages together and pad the total and use this > as my new max_fsm_pages? Should I do the same thing with max_fsm_relations? I think that's too much and too big FSM affects performance in my opinion. The easiest way to calculate appropreate FSM size is doing vacuumdb -a -v and watching the message. At the very end, you would see something like: INFO: free space map: 13 relations, 1447 pages stored; 1808 total pages needed DETAIL: Allocated FSM size: 100 relations + 1600 pages = 19 kB shared memory. In this case 1808 is the minimum FSM size. Of course this number would change depending on the frequency of VACUUM. Therefore you need some room for the FSM size. -- Tatsuo Ishii
Patrick Hatcher <pathat@comcast.net> writes:
> My question is this: I have about 8 databases running on this server.
> When I do a vacuum full on each of these databases, there is a INFO
> section that I assume is the total pages used for that database. Should
> add ALL these individual pages together and pad the total and use this
> as my new max_fsm_pages? Should I do the same thing with max_fsm_relations?
No, the numbers shown at the end of a vacuum verbose printout reflect
the current cluster-wide FSM demand. BTW you do *not* want to use FULL
because that's not going to reflect the FSM requirements when you are
just running normal vacuums.
I would vacuum all your databases (to make sure each one's FSM contents
are pretty up-to-date) and then take the numbers shown by the last one
as your targets.
If you find yourself having to raise max_fsm_relations, it may be
necessary to repeat the vacuuming cycle before you can get a decent
total for max_fsm_pages. IIRC, the vacuum printout does include in
"needed" a count of pages that it would have stored if it'd had room;
but this is only tracked for relations that have an FSM relation entry.
regards, tom lane