Re: bgwriter tunables vs pg_stat_bgwriter

On Feb 17, 2010, at 6:23 PM, Greg Smith wrote:

> JWith bgwriter_lru_maxpages=500 and bgwriter_delay=100ms, you can
> get up to 5000 pages/second which makes for 300,000 pages/minute.
> So none of your numbers look funny just via their scale.  This is
> why the defaults are so low--the maximum output of the background
> writer is quite big even before you adjust it upwards.
>

d'oh! that would be the reason.  Sorry folks, nothing to see here :)

> There are however two bits of stats buffering involved.  Stats
> updates don't become visible instantly, they're buffered and only
> get their updates pushed out periodically to where clients can see
> them to reduce overhead.  Also, the checkpoint write update happens
> in one update at the end--not incrementally as the checkpoint
> progresses.  The idea is that you should be able to tell if a
> checkpoint happened or not during a period of monitoring time.  You
> look to be having checkpoints as often as once per minute right now,
> so something isn't right--probably checkpoint_segments is too low
> for your workload.
>

checkpoint_segments is currently 32. maybe I'll bump it up - this db
does a LOT of writes

> By the way, your monitoring code should be saving maxwritten_clean
> and buffers_allocated, too.  While you may not be doing something
> with them yet, the former will shed some light on what you're
> running into now, and the latter is useful later down the road
> you're walking.

It is, I just didn't include them in the mail.

--
Jeff Trout <jeff@jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/