Re: Calculation for Max_FSM_pages : Any rules of thumb?

On Thu, 2007-11-01 at 20:56 -0400, Bill Moran wrote:
> Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> wrote:
> >
> > I just ran a vacuum verbose on the entire DB and this came out.
> >
> > number of page slots needed (274144) exceeds max_fsm_pages (153600)
> >
> > Hence, I've changed the max to 400,000 (pulled it straight out of the
> > air). How does one calculate what's the number needed anyway?
>
> It's not simple. Every update or delete creates a "dead tuple" that
> needs to be tracked by an fsm entry. So it depends on how frequently
> your database is changing in between vacuum runs.

Quite a lof actually.

>
> In my experience, the best bet is to do vacuum verbose on a regular
> basis and get a feel for what you need. Every database load is
> different.

autovacuum is turned on by default.. so I didn't think of any issues
_might_ occur.. (or rather.. didn't think about murphy's law)

>
> > 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..
>
> You don't _need_ to. But it's generally a good idea to get table
> bloat reduced.

OK.. Vacuum verbose took 2 hours.. Vacuum full will likely take 2x that
I presume.

> > I'm planning to run vacuum verbose full tonight/over the weekend. (is
> > this sane?) Thanks for the advice..
>
> vacuum full is sane, if that's what you mean. The only problem is that
> it locks tables while working on them, so you have to take into account
> what other workload might be blocked while vacuum full is working, and
> how long vacuum full is liable to take.

It's pulling data from the master DB (it's a data mart) every 50 to 120
seconds)
I presume that it's blocked on a table by table basis??