On Fri, 29 Jun 2007, Jim Nasby wrote:

On Jun 26, 2007, at 11:57 PM, Greg Smith wrote:
I have a complete set of working code that tracks buffer usage statistics...

Even if it's not used by bgwriter for self-tuning, having that information available would be very useful for anyone trying to hand-tune the system.

The stats information that's in pg_stat_bgwriter combined with an occasional snapshot of the current pg_stat_buffercache (now with usage counts!) is just as useful. Right before freeze, I made sure everything I was using for hand-tuning in this area made it into one of those. Really all I do is collect that data as I happen to be scanning the buffer cache anyway.

The way I'm keeping track of things internally is more intrusive to collect than something I'd like to be turned on by default just for information, and exposing what it knows to user-space isn't done yet. I was hoping to figure out a way to use it to help justify its overhead before bothering to optimize and report on it. The only reason I mentioned the code at all is because I didn't want anybody else to waste time writing that particular routine when I already have something that works for this purpose sitting around.

Is this still a serious issue with LDC?

Part of the reason I'm bugged about this area is that the scenario I'm bringing up--lots of dirty and high usage buffers in a pattern the BGW isn't good at writing causing buffer pool allocations to be slow--has the potential to get even worse with LDC. Right now, if you're in this particular failure mode, you can be "saved" by the next checkpoint because it is going to flush all the dirty buffers out as fast as possible and then you get to start over with a fairly clean slate. Once that stops happening, I've observed the potential to run into this sort of breakdown increase.

I share Greg Stark's concern that we're going to end up wasting a lot of writes.

I don't think the goal is to write buffers significantly faster than they have to in order to support new allocations; the idea is just to stop from ever scanning the same section more than once when it's not possible for it to find new things to do there. Right now there are substantial wasted CPU/locking resources if you try to tune the LRU writer up for a heavy load (by doing things like like increasing the percentage), as it just keeps scanning the same high-usage count buffers over and over. With the LRU now running during LDC, my gut feeling is its efficiency is even more important now than it used to be. If it's wasteful of resources, that's now even going to impact checkpoints, where before the two never happened at the same time.

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?


Reply via email to