Re: max_fsm_pages Sanity Check

"HT" <htlevine(at)ebates(dot)com> writes:
> We have quite large production Postgres 7.2 DB which is out of control in
> terms of disk consumption. We made it thru the holiday shopping season,
> but it isn't over yet. We have taken the DB down once for a vacuum analyze
> but only vacuum'd 2 large tables which took FIVE HOURS WITH NO
> RESULTS.

1. You don't need to take down the DB to do vacuuming.
2. What do you mean by "WITH NO RESULTS"?

> Posts to the newsgroup advised that I crank up the max_fsm_pages. Right
> now it is at roughly 65,000.

> select relname, relpages from pg_class where relkind in ('r', 't', 'i')
> users | 408711
> merchant_sessions | 236333
> batch_load_awaiting | 173785
> orders | 92241

If you have not been vacuuming regularly then these relpages figures
cannot be trusted too much, but it looks to me like you might need
max_fsm_pages nearer to 1 million than 64k. If it's not large enough
to cover all (or at least nearly all) pages with free space, then you'll
have space-leakage problems. What is the tuple update/deletion rate in
these tables, anyway?

Also, you should probably think about updating to 7.3.1 sometime soon.
There's a performance problem in the 7.2.* FSM code that shows up when
a single table has more than ~10000 pages with useful amounts of free
space --- VACUUM takes an unreasonable amount of time to record the free
space.

regards, tom lane