Re: max_fsm_pages Sanity Check

Thanks for the response. See my responses below. I'll crank it up to 1
million fsm pages. and report back when we finish with the results.... I
know they aren't as interesting with 7.2.3 as they would be with 7.3 but it
may help someone else.
"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in message
news:10105(dot)1041182337(at)sss(dot)pgh(dot)pa(dot)us(dot)(dot)(dot)
> "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.

when I tried the vacuum with the site still up, the whole DB came to a
stand-still... i.e. the pg_stat_activity table grew and grew and grew....
users couldn't log in, and the site was "broken".... I tried this several
times and tho this group says you don't need to take the db down, I found
we might as well cause it was so unresponsive to our users that we appeared
busted. I'd rather be "down for maintenance" on purpose than appear
busted.

> 2. What do you mean by "WITH NO RESULTS"?

by "no results" I mean the space was NOT freed up, in fact the db consumed
MORE space after the vacuum full than before.

>
> > 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?
Users has a 0 deletion rate, and a fairly low update rate, unless we do a
"mass" update of the whole table.... which happens a couple times a year
(say sales/mktg want a new user email flag...)

Batch_Load_awaiting has hardly no deletions but 100% of the rows are updated
ONCE (maybe twice) after they are inserted, then never touched after that.

Orders has no deletions, and a small update ratio.

Merchant_Sessions has NO deletions or updates.

there are tons more tables I didn't put in the list cause they are either
small by comparison or completely static.

>
> Also, you should probably think about updating to 7.3.1 sometime soon.

Yes, some bugs may be fixed in 7.3.1, but I fear the ones that may get me in
bigger trouble than I am already :) I"m watching posts to this group.
When I feel comfortable that users are not reporting bugs or problems
against 7.3.1 (or whatever point release is stable) then I will definitly
upgrade.

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)