Re: max_fsm_pages Sanity Check - Mailing list pgsql-admin

Haven't been following this list too closely over the holiday break,
hopefully this can still be of some use to you.

On Mon, 2002-12-30 at 13:12, HT Levine wrote:
> "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
> >
> > 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.
>

Are you doing vacuum full or vacuum analyze? I would think you'd not
have that problem with a vacuum analyze, which if your having large
amounts of unused space you probably should be running more often.

<snip>
> > 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.
>
>

Tables with no deletions or updates won't benefit from vacuuming so
there's no reason to vacuum them.  On a table like Batch_Load_awaiting,
you need to do a vacuum after the 100% are updated. If you plan to
insert more into this table just do an --analyze, if you don't plan to
insert, then --full is more appropriate.  Keep an eye on those small
tables if they have frequent turnover. Even a 1000 row table that gets
updated every 5 minutes will generate more than 250,000 dead tuples a
day if your not regularly vacuuming.

I am also skeptical about your max_fsm_relations setting. This should be
equal to at least the number of rows output by \d in psql plus 100. Any
less and I question if your vacuum is being as effective as it should
be.

On a final note, you might want to try reindexing some of your tables to
see if that helps. At least search the archives for "index bloat",
"table out of control", "size grows and grows" or some such combination
anyway, this might also be causing you some pain.

Hope this helps,

Robert Treat