Greg Smith wrote:
On Thu, 5 Apr 2007, Heikki Linnakangas wrote:
Bgwriter has two goals:
1. keep enough buffers clean that normal backends never need to do a
write
2. smooth checkpoints by writing buffers ahead of time
Load distributed checkpoints will do 2. in a much better way than the
bgwriter_all_* guc options. I think we should remove that aspect of
bgwriter in favor of this patch.
...
Let me suggest a different way of looking at this problem. At any
moment, some percentage of your buffer pool is dirty. Whether it's 0%
or 100% dramatically changes what the background writer should be
doing. Whether most of the data is usage_count>0 or not also makes a
difference. None of the current code has any idea what type of buffer
pool they're working with, and therefore they don't have enough
information to make a well-informed prediction about what is going to
happen in the near future.
The purpose of the bgwriter_all_* settings is to shorten the duration of
the eventual checkpoint. The reason to shorten the checkpoint duration
is to limit the damage to other I/O activity it causes. My thinking is
that assuming the LDC patch is effective (agreed, needs more testing) at
smoothening the checkpoint, the duration doesn't matter anymore. Do you
want to argue there's other reasons to shorten the checkpoint duration?
I'll tell you what I did to the all-scan. I ran a few hundred hours
worth of background writer tests to collect data on what it does wrong,
then wrote a prototype automatic background writer that resets the
all-scan parameters based on what I found. It keeps a running estimate
of how dirty the pool at large is using a weighted average of the most
recent scan with the past history. From there, I have a simple model
that predicts how much of the buffer we can scan in any interval, and
intends to enforce a maximum bound on the amount of physical I/O you're
willing to stream out. The beta code is sitting at
http://www.westnet.com/~gsmith/content/postgresql/bufmgr.c if you want
to see what I've done so far. The parts that are done work fine--as
long as you give it a reasonable % to scan by default, it will correct
all_max_pages and the interval in real-time to meet the scan rate
requested you want given how much is currently dirty; the I/O rate is
computed but doesn't limit properly yet.
Nice. Enforcing a max bound on the I/O seems reasonable, if we accept
that shortening the checkpoint is a goal.
Why haven't I brought this all up yet? Two reasons. The first is
because it doesn't work on my system; checkpoints and overall throughput
get worse when you try to shorten them by running the background writer
at optimal aggressiveness. Under really heavy load, the writes slow
down as all the disk caches fill, the background writer fights with
reads on the data that isn't in the mostly dirty cache (introducing
massive seek delays), it stops cleaning effectively, and it's better for
it to not even try. My next generation of code was going to start with
the LRU flush and then only move onto the all-scan if there's time
leftover.
The second is that I just started to get useful results here in the last
few weeks, and I assumed it's too big of a topic to start suggesting
major redesigns to the background writer mechanism at that point (from
me at least!). I was waiting for 8.3 to freeze before even trying. If
you want to push through a redesign there, maybe you can get away with
it at this late moment. But I ask that you please don't remove anything
from the current design until you have significant test results to back
up that change.
Point taken. I need to start testing the LDC patch.
Since we're discussing this, let me tell what I've been thinking about
the lru cleaning behavior of bgwriter. ISTM that that's more
straigthforward to tune automatically. Bgwriter basically needs to
ensure that the next X buffers with usage_count=0 in the clock sweep are
clean. X is the predicted number of buffers backends will evict until
the next bgwriter round.
The number of buffers evicted by normal backends in a bgwriter_delay
period is simple to keep track of, just increase a counter in
StrategyGetBuffer and reset it when bgwriter wakes up. We can use that
as an estimate of X with some safety margin.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
