In response to Ow Mun Heng <Ow.Mun.Heng@wdc.com>:
>
> On Thu, 2007-11-08 at 12:02 -0500, Bill Moran wrote:
> > Frequently, when people ask for help because they've exceed max_fsm*,
> > it's because they're not paying attention to their systems, and therefore
> > the problem has been occurring for a while before it got so bad that
> > they couldn't ignore it. As a result, a full vacuum is frequently a
> > necessity.
> >
> > Folks who are monitoring their databases closely don't hit this
> > problem nearly as often.
>
> How does one monitor it closely anyway? the warning comes when one does
> a vacuum verbose and with autovacuum turned on, I don't even see it
> anywhere.
1) Run vacuum verbose from cron on a regular basis and have the output
emailed to you.
2) Capture and graph (I use mrtg) various stats that would indicate to
you that something is wrong. Some suggestions are graphing the
output of pg_database_size(), various stuff captured from
the pg_buffercache addon. I also graph transactions/second and
other stats, but those are useful for detecting _other_ problems,
unrelated to vacuuming.
It's amazing to me how many people just throw up a database and expect
it to just magically work forever. Actually, this isn't isolated to
databases ... I've seen people with fileservers run around one day
saying "the fileserver is full, someone delete some files!" If it's
a fileserver, why aren't you monitoring disk usage so you see this
coming?
If it's a database server, you should be monitoring critical stats on
it. Then you can throw out all those silly "rules of thumb" and use
some actual data!
--
Bill Moran
http://www.potentialtech.com