Re: Just-in-time Background Writer Patch+Test Results

On Thu, 6 Sep 2007, Kevin Grittner wrote:

> I thought that the bgwriter_lru_percent was scanned from the lru end
> each time; I would not expect that it would ever get beyond the oldest
> 10%.

You're correct; I stated that badly. What I should have said is that your
LRU writer could potentially scan the pool as fast as once per second if
there were enough allocations going on.

> How low does the count have to go, or does it track the count when it
> becomes dirty and look for a decrease?

The usage count has to be 0 before a page can be re-used for a new
allocation, and the LRU background writer only writes out potentially
reusable pages that are dirty. So the count has to be 0 before it will
write it.

> This should keep us reading new pages, which covers this, yes?

One would hope. Your whole arrangement of shared_buffers,
checkpoint_segments, and related parameters will need to be reconsidered
for 8.3; you've got a delicated balanced arrangement for your 8.2 setup
right now that's working for you, but just translating it straight to 8.3
won't get you what you want. I'll get back to the message you already
sent on that subject when I get enough time to address it fully.

> I'm not clear on the benefit of not writing the recently accessed dirty
> pages when there are no less recently used dirty pages.

This presumes PostgreSQL has some notion of the balance of recently
accessed vs. not accessed dirty pages, which it does not. Buffers get
updated individually, and there's no mechanism summarizing what's in
there; you have to scan the buffer cache yourself to figure that out. I
do some of that in this new patch, tracking things like how many buffers
are scanned on average to find reusable ones.

Many months ago, I wrote a very complicated re-implementation of the
all-scan portion of the background writer that tracked the usage count of
everything it looked at, kept statistics about how many pages were dirty
at each usage count, then targeted how high of a usage count could be
written given some information about what I/O rate you felt your devices
could sustain. This did exactly what you're asking for here: wrote
whatever dirty pages were around starting with the ones that hadn't been
recently used, then worked its way up to pages with a higher usage count
if the recently used ones were all clean.

As far as I've been able to tell, and from Heikki's test results, the load
distributed checkpoint was a better answer to this problem. Rather than
constantly fight to get pages with high usage counts out all the time,
just spread the checkpoint out instead and deal with them only then. I
gave up on that branch of code while he removed the all-scan writer
altogether as part of committing LDC. I suspect the path I was following
was exactly what you think you'd like to have, but it seems that it's not
actually needed.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD