Re: Calculation for Max_FSM_pages : Any rules of thumb?

In response to Vivek Khera <vivek(at)khera(dot)org>:

>
> On Nov 1, 2007, at 8:51 PM, Ow Mun Heng wrote:
>
> > Another question is, based on what I've read in the archives (in my
> > laptop.. No-Inet conn @ work) Since I've overran my max_FSM, I'm
> > basically screwed and will have to do a vacuum verbose FULL on the
> > entire DB. Crap..
>
> I've seen this repeated many times as well, and I can't think of a
> really good reason why this should be true.

It's not inherently true, it's just likely.

> Once you increase max fsm
> pages, won't the very next regular vacuum find all the free space in
> pages and add them to the map anyway?

Yes.

> Ie, you've not "lost" any free
> space once the next regular vacuum runs. At worst, you've got a
> slightly bloated table because you allocated more pages rather than re-
> using some, but is that worth a full vacuum?

The situation you just described is the reason I recommend a full
vacuum after such a situation has occurred. No, it's not required
in all cases, but it's a lot easier to recommend than the research
required to determine whether or not your table bloat is excessive
enough to warrant it.

If you can make the time to do the full vacuum, it's probably worth
it, just for peace of mind. If it's difficult to schedule a full
vacuum, then you need to carefully review various page usages to
see if any individual tables are worth it and/or all kinds of careful
consideration. As a result, I recommend a full vacuum, and if the
person complains that they can't schedule it, _then_ I go into the
details of how to figure out what else can/should be done.

So I guess I'm recommending it to make my own life easier :)

> I don't think it will be
> unless you're *way* under the fsm pages needed and have been for a
> long time.

Frequently, when people ask for help because they've exceed max_fsm*,
it's because they're not paying attention to their systems, and therefore
the problem has been occurring for a while before it got so bad that
they couldn't ignore it. As a result, a full vacuum is frequently a
necessity.

Folks who are monitoring their databases closely don't hit this
problem nearly as often.

--
Bill Moran
http://www.potentialtech.com