Re: bgwriter tunables vs pg_stat_bgwriter

Jeff wrote:
> while(true)
> {
> if buffers_written > bgwriter_lru_maxpages
> or buffers_written > anticipated_pages_needed *
> bgwriter_lru_multiplier
> {
> sleep(bgwriter_delay ms)
> continue;
> }
> ...
> }
>
> so I should not be able to have more than ~5000 bgwriter_clean pages
> per minute. (this assumes writing takes 0ms, which of course is
> inaccurate)

That's not how the loop is structured. It's actually more like:

-Compute anticipated_pages_needed * bgwriter_lru_multiplier
-Enter a cleaning loop until that many are confirmed free -or-
bgwriter_lru_maxpages is reached
-sleep(bgwriter_delay ms)

> perhaps some stats buffering occurring or something or some general
> misunderstanding of some of these tunables?
With 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.

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.

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.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us