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.

My first question about the LDC patch was whether I could turn it off and return to the existing mechanism. I would like to see a large pile of data proving this new approach is better before the old one goes away. I think everyone needs to do some more research and measurement here before assuming the problem can be knocked out so easily.

The reason I've been busy working on patches to gather statistics on this area of code is because I've tried most simple answers to getting the background writer to work better and made little progress, and I'd like to see everyone else doing the same at least collecting the right data.

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.

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.

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.

* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to