On Tue, 2007-11-13 at 09:49 -0500, Bill Moran wrote:
> In response to Ow Mun Heng <Ow.Mun.Heng@wdc.com>:
> > 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.
I'm doing this on a regular basis now coupled with pgfouine, I get a
nicely formatted HTML report. With the nightly vacuum, I noticed that I
can actually reduce my max_fsm_pages. (I raised it from 200,000 to
400,000 then to 800,000 currently, but with the regular vacuum, it's
gone down to 300,000 range)
> 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.
Currently I use cacti to monitor Disk Size (dedicated Raid), have yet to
play with pg_buffercache and needing more ideas to monitor. (anyone?)
tps is not very important to me, (I look more at cpu usage and load avg
as it's a (very!) low end server)
> I also graph transactions/second and
> other stats, but those are useful for detecting _other_ problems,
> unrelated to vacuuming.
Even with the regular vacuuming and even a vacuum full ( on my test DB)
I still see that perhaps something is wrong (from the below)
(I got this gem from the mailling list archives)
hmxmms=> SELECT
c.relname,
c.reltuples::bigint as rowcnt,
pg_stat_get_tuples_inserted(c.oid) AS inserted,
pg_stat_get_tuples_updated(c.oid) AS updated,
pg_stat_get_tuples_deleted(c.oid) AS deleted
FROM pg_class c
WHERE c.relkind = 'r'::"char"
GROUP BY c.oid, c.relname, c.reltuples
HAVING pg_stat_get_tuples_updated(c.oid) +
pg_stat_get_tuples_deleted(c.oid) > 1000
ORDER BY pg_stat_get_tuples_updated(c.oid) +
pg_stat_get_tuples_deleted(c.oid) DESC;
relname | rowcnt | inserted | updated | deleted
-----------------------+----------+----------+---------+----------
tst_r | 11971691 | 0 | 0 | 22390528 <--
pg_statistic | 1465 | 280 | 7716 | 153
dr_ns | 2305571 | 1959 | 0 | 1922
pg_attribute | 3787 | 1403 | 184 | 1292
No matter how many times I vacuum/full the deleted number still doesn't
go down.
