Re: max_fsm_pages Sanity Check - Mailing list pgsql-admin

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@sss.pgh.pa.us> wrote in message
news:10105.1041182337@sss.pgh.pa.us...
> "HT" <htlevine@ebates.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@postgresql.org)