Re: Automatic adjustment of bgwriter_lru_maxpages

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Greg Smith wrote:
> Attached are two patches that try to recast the ideas of Itagaki
> Takahiro's auto bgwriter_lru_maxpages patch in the direction I think this
> code needs to move. Epic-length commentary follows.
>
> The original code came from before there was a pg_stat_bgwriter. The
> first patch (buf-alloc-stats) takes the two most interesting pieces of
> data the original patch collected, the number of buffers allocated
> recently and the number that the clients wrote out, and ties all that into
> the new stats structure. With this patch applied, you can get a feel for
> things like churn/turnover in the buffer pool that were very hard to
> quantify before. Also, it makes it easy to measure how well your
> background writer is doing at writing buffers so the clients don't have
> to. Applying this would complete one of my personal goals for the 8.3
> release, which was having stats to track every type of buffer write.
>
> I split this out because I think it's very useful to have regardless of
> whether the automatic tuning portion is accepted, and I think these
> smaller patches make the review easier. The main thing I would recommend
> someone check is how am_bg_writer is (mis?)used here. I spliced some of
> the debugging-only code from the original patch, and I can't tell if the
> result is a robust enough approach to solving the problem of having every
> client indirectly report their activity to the background writer. Other
> than that, I think this code is ready for review and potentially
> comitting.
>
> The second patch (limit-lru) adds on top of that a constraint of the LRU
> writer so that it doesn't do any more work than it has to. Note that I
> left verbose debugging code in here because I'm much less confident this
> patch is complete.
>
> It predicts upcoming buffer allocations using a 16-period weighted moving
> average of recent activity, which you can think of as the last 3.2 seconds
> at the default interval. After testing a few systems that seemed a decent
> compromise of smoothing in both directions. I found the 2X overallocation
> fudge factor of the original patch way too aggressive, and just pick the
> larger of the most recent allocation amount or the smoothed value. The
> main thing that throws off the allocation estimation is when you hit a
> checkpoint, which can give a big spike after the background writer returns
> to BgBufferSync and notices all the buffers that were allocated during the
> checkpoint write; the code then tries to find more buffers it can recycle
> than it needs to. Since the checkpoint itself normally leaves a large
> wake of reusable buffers behind it, I didn't find this to be a serious
> problem.
>
> There's another communication issue here, which is that SyncOneBuffer
> needs to return more information about the buffer than it currently does
> once it gets it locked. The background writer needs to know more than
> just if it was written to tune itself. The original patch used a clever
> trick for this which worked but I found confusing. I happen to have a
> bunch of other background writer tuning code I'm working on, and I had to
> come up with a more robust way to communicate buffer internals back via
> this channel. I used that code here, it's a bitmask setup similar to how
> flags like BM_DIRTY are used. It's overkill for solving this particular
> problem, but I think the interface is clean and it helps support future
> enhancements in intelligent background writing.
>
> Now we get to the controversial part. The original patch removed the
> bgwriter_lru_maxpages parameter and updated the documentation accordingly.
> I didn't do that here. The reason is that after playing around in this
> area I'm not convinced yet I can satisfy all the tuning scenarios I'd like
> to be able to handle that way. I describe this patch as enforcing a
> constraint instead; it allows you to set the LRU parameters much higher
> than was reasonable before without having to be as concerned about the LRU
> writer wasting resources.
>
> I already brought up some issues in this area on -hackers (
> http://archives.postgresql.org/pgsql-hackers/2007-04/msg00781.php ) but my
> work hasn't advanced as fast as I'd hoped. I wanted to submit what I've
> finished anyway because I think any approach here is going to have cope
> with the issues addressed in these two patches, and I'm happy now with how
> they're solved here. It's only a one-line delete to disable the LRU
> limiting behavior of the second patch, at which point it's strictly
> internals code with no expected functional impact that alternate
> approaches might be built on.
>
> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
Content-Description:

[ Attachment, skipping... ]

Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +