Re: max_fsm_pages Sanity Check

see my answers below:
"Robert Treat" <xzilla(at)users(dot)sourceforge(dot)net> wrote in message
news:1041547656(dot)32015(dot)38(dot)camel(at)camel(dot)(dot)(dot)
> 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(at)sss(dot)pgh(dot)pa(dot)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.
>
I tried this with vacuum analyze verbose table-name and the whole db went
to sleep :( no worries, I took the site down and let it finish. We plan
to take the site down this weekend and do a vacuum full (after changing the
fsm properties tho)

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

batch_load_awaiting (affectionately known as BLA) gets N inserts directly
followed by N updates once a day. The previous day's update/inserts are not
touched (except in a blue moon) so I assume this would mean a daily vacuum
analyze on this guy?

users depends on how often users decide to update their account, and orders
would be even smaller (customer service changing user's orders)

Got it on the small tables, I'll keep an eye for those too -- thanks.

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

sorry to sound dumb here ... but \d in psql gives me just 510 rows? I was
going to crank it up to 1 million (originally it had been set at 64,000)

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

I'll do this too, thanks

> Hope this helps,
>
> Robert Treat
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html