Re: Final background writer cleanup for 8.3 - Mailing list pgsql-hackers

>>> On Wed, Sep 5, 2007 at  1:54 PM, in message
<Pine.GSO.4.64.0709051443300.17248@westnet.com>, Greg Smith
<gsmith@gregsmith.com> wrote:
> On Wed, 5 Sep 2007, Josh Berkus wrote:
>
> While there certainly are some cases where we've heard about people whose
> workloads were such that the background writer worked successfully for
> them, I consider those lucky rather than normal.  I'd like those people to
> test 8.3 because I'd hate to see the changes made to improve the general
> case cause a regression for them.
Being one of the lucky ones, I'm still hopeful that I'll be able to do
these tests.  I think I know how to tailor the load so that we see the
problem often enough to get useful benchmarks (we tended to see the
problem a few times per day in actual 24/7 production).
My plan would be to run 8.2.4 with the background writer turned off to
establish a baseline.  I think that any test, to be meaningful would need
to run for several hours, with the first half hour discarded as just being
enough to establish the testing state.
Then I would test our aggressive background writer settings under 8.2.4 to
confirm that those settings do handle the problem in this test
environment.
Then I would test the new background writer with synchronous commits under
the 8.3 beta, using various settings.  The 0.5, 0.7 and 0.9 settings you
recommended for a test are how far from the LRU end of the cache to look
for dirty pages to write, correct?  Is there any upper bound, as long as I
keep it below 1?  Are the current shared memory and the 1 GB you suggested
enough of a spread for these tests?  (At several hours per test in order
to get meaningful results, I don't want to get into too many permutations.)
Finally, I would try the new checkpoint techniques, with and without the
new background writer.  Any suggestions on where to set the knobs for
those runs?
I'm inclined to think that it would be interesting to try the benchmarks
with the backend writing any dirty page through to the OS at the same time
they are written to the PostgreSQL cache, as a reference point at the
opposite extreme from having the cache hold onto dirty pages for as long
as possible before sharing them with the OS.  Do you see any value in
getting actual numbers for that?
> this causes a bit of a problem for testing
> 8.3 in beta, because if you come from a world-view where the 8.2.4
> background writer was never successful it's hard to figure out a starting
> point for comparing it to the one in 8.3.
In terms of comparing the new technique to the old, one would approach the
new technique by turning off the "all" scan and setting the lru scan
percentage to 50% or more, right?  (I mean, obviously there would be more
CPU time used as it scanned through clean pages repeatedly, but it would
be a rough analogy otherwise, yes?)
-Kevin