Final background writer cleanup for 8.3

In the interest of closing work on what's officially titled the "Automatic
adjustment of bgwriter_lru_maxpages" patch, I wanted to summarize where I
think this is at, what I'm working on right now, and see if feedback from
that changes how I submit my final attempt for a useful patch in this area
this week. Hopefully there are enough free eyes to stare at this now to
wrap up a plan for what to do that makes sense and still fits in the 8.3
schedule. I'd hate to see this pushed off to 8.4 without making some
forward progress here after the amount of work done already, particularly
when odds aren't good I'll still be working with this code by then.

Let me start with a summary of the conclusions I've reached based on my
own tests and the set that Heikki did last month (last results at
http://community.enterprisedb.com/bgwriter/ ); Heikki will hopefully chime
in if he disagrees with how I'm characterizing things:

1) In the current configuration, if you have a large setting for
bgwriter_lru_percent and/or a small setting for bgwriter_delay, that can
be extremely wasteful because the background writer will consume
CPU/locking resources scanning the buffer pool needlessly. This problem
should go away.

2) Having backends write their own buffers out does not significantly
degrade performance, as those turn into cached OS writes which generally
execute fast enough to not be a large drag on the backend.

3) Any attempt to scan significantly ahead of the current strategy point
will result in some amount of premature writes that decreases overall
efficiency in cases where the buffer is touched again before it gets
re-used. The more in advance you go, the worse this inefficiency is.
The most efficient way for many workloads is to just let the backends do
all the writes.

4) Tom observed that there's no reason to ever scan the same section of
the pool more than once, because anything that changes a buffer's status
will always make it un-reusable until the strategy point has passed over
it. But because of (3), this does not mean that one should drive forward
constantly trying to lap the buffer pool and catch up with the strategy
point.

5) There hasn't been any definitive proof that the background writer is
helpful at all in the context of 8.3. However, yanking it out altogether
may be premature, as there are some theorized ways that it may be helpful
in real-world situations with more intermittant workloads than are
generally encountered in a benchmarking situation. I personally feel that
is some potential for the BGW to become more useful in the context of the
8.4 release if it starts doing things like adding pages it expects to be
recycled soon onto the free list, which could improve backend efficiency
quite a bit compared to the current situation where each backend is
normally running their own scan. But that's a bit too big to fit into 8.3
I think.

What I'm aiming for here is to have the BGW do as little work as possible,
as efficiently as possible, but not remove it altogether. (2) suggests
that this approach won't decrease performance compared to the current 8.2
situation, where I've seen evidence some are over-tuning to have a very
aggressive BGW scan an enormous amount of the pool each time because they
have resources to burn. Having a generally self-tuning background writer
that errs on the lazy side stay in the codebase satisfies (5). Here is
what the patch I'm testing right now does to try and balance all this out:

A) Counters are added to pg_stat_bgwriter that show how many buffers were
written by the backends, by the background writer, how many times
bgwriter_lru_maxpages was hit, and the total number of buffers allocated.
This at least allows monitoring what's going on as people run their own
experiments. Heikki's results included data using the earlier version of
this patch I put assembled (which now conflicts with HEAD, I have an
updated one).

B) bgwriter_lru_percent is removed as a tunable. This eliminates (1).
The idea of scanning a fixed percentage doesn't ever make sense given the
observations above; we scan until we accomplish the cleaning mission
instead.

C) bgwriter_lru_maxpages stays as an absolute maximum number of pages that
can be written in one sweep each bgwriter_delay. This allows easily
turning the writer off altogether by setting it to 0, or limiting how
active it tries to be in situations where (3) is a concern. Admins can
monitor the amount that the max is hit in pg_stat_bgwriter and consider
raising it (or lowering the delay) if it proves to be too limiting. I
think the default needs to be bumped to something more like 100 rather
than the current tiny one before the stock configuration can be considered
"self-tuning" at all.

D) The strategy code gets a "passes" count added to it that serves as a
sort of high-order int for how many times the buffer cache has been looked
over in its entirety.

E) When the background writer start the LRU cleaner, it checks if the
strategy point has passed where it last cleaned up to, using the
passes+buf_id "pointer". If so, it just starts cleaning from the strategy
point as it always has. But if it's still ahead it just continues from
there, thus implementing the core of (4)'s insight. It estimates how many
buffers are probably clean in the space between the strategy point and
where it's starting at, based on how far ahead it is combined with
historical data about how many buffers are scanned on average per reusable
buffer found (the exact computation of this number is the main thing I'm
still fiddling with).

F) A moving average of buffer allocations is used to predict how many
clean buffers are expected to be needed in the next delay cycle. The
original patch from Itagaki doubled the recent allocations to pad this
out; (3) suggests that's too much.

G) Scan the buffer pool until either
--Enough reusable buffers have been located or written out to fill the
upcoming allocation need, taking into account the estimate from (E); this
is the normal expected way the scan will terminate.
--We've written bgwriter_lru_maxpages
--We "lap" and catch the strategy point

In addition to removing a tunable and making the remaining two less
critical, one of my hopes here is that the more efficient way this scheme
operates will allow using much smaller values for bgwriter_delay than have
been practical in the current codebase, which may ultimately have its own
value.

That's what I've got working here now, still need some more tweaking and
testing before I'm done with the code but there's not much left. The main
problem I forsee is that this approach is moderately complicated, adding a
lot of new code and regular+static variables, for something that's not
really proven to be valuable. I will not be surprised if my patch is
rejected on that basis. That's why I wanted to get the big picture
painted in this message while I finish up the work necessary to submit it,
'cause if the whole idea is doomed anyway I might as well stop now.

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