Re: Calculation for Max_FSM_pages : Any rules of thumb?

On Thu, 2007-11-01 at 21:22 -0400, Tom Lane wrote:
> Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> writes:
> > OK.. Vacuum verbose took 2 hours.. Vacuum full will likely take 2x that
> > I presume.
>
> Probably a lot more, and it'll bloat your indexes while it's at it.
> Do you have a *reason* to run a vacuum full?

Maybe you didn't read my original post. I did a vacuum verbose (playing
with pgfouine) and found that my max_fsm_pages was exceeded and based on
archives, I have to do a vacuum full.

I decided to bite the bullet and go ahead with the vacuum full anyway.
It's been ~3 hours already and I _think_ it's about to finish.. (is
there a way to determine which tables are left to vacuum? Is there a
_list_ which it transverse etc? tailing the vacuum_log, I can see where
it's at, but not where it is in terms of overall status.

> I'd suggest using contrib/pgstattuple to get a fix on how much dead
> space there is in your tables. If it's really horrid (like more than
> 50%) then VACUUM FULL followed by REINDEX might be called for, but
> otherwise you should probably not sweat it.

pgstattuple.. Okay.. anyone have a centos rpm for it? Don't really have
access to a compiler on the (server) machine.

> If you do have a problem you need to reconsider your regular vacuuming
> policy, because it's not running often enough. See if autovacuum makes
> sense for you.

autovacuum _is_ running on a regular basis. (I'm not sure if it's
supposed to catch the max_fsm pages being exceeded etc)

> Also, if you are not low on disk space overall, consider CLUSTER as a
> substitute for VACUUM FULL + REINDEX. It'll be faster and you might get
> a speed boost for subsequent queries using whichever index you cluster
> on. The only drawback is that CLUSTER uses temp space equal to the
> table + index sizes ...

I'm not low.. I have ~300G available. Total DB size is ~60G.
I guess I need to read up on CLUSTER. Thanks.