Search Postgresql Archives

Re: Calculation for Max_FSM_pages : Any rules of thumb?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, 2007-11-13 at 09:49 -0500, Bill Moran wrote:
> In response to Ow Mun Heng <Ow.Mun.Heng@xxxxxxx>:
> > 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.




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux