Re: Log levels for checkpoint/bgwriter monitoring

On Wed, 7 Mar 2007, ITAGAKI Takahiro wrote:

> Also, my recommended bgwriter_lru_maxpages is "average number of
> recycled buffers per cycle", that is hardly able to tune manually.

This is completely dependent on what percentage of your buffer cache is
pinned. If your load is something like the standard pgbench, the LRU
writer will rarely find anything useful to write, so this entire line of
thinking won't work. The proper behavior for heavily pinned data is to
turn off the LRU writer altogether so there's more time to run the all
scan.

The job I'm trying to take on here is not to presume I can solve these
problems myself yet. I've instead recognized that people need usefully
organized information in order to even move in that direction, and that
informatoin is not even close to being available right now.

What my latest work in progress patches do is summarize each scan of the
buffer pool with information about how much was written by each of the two
writers, along with noting what percentage of the pool was pinned data.
I'm trying to get that one ready to submit this week. Those three values
suggest some powerful techniques for tuning, but it's not quite good
enough to allow auto-tuning. It also needs a feel for how much time is
left before the next checkpoint.

What really needs to go along with all this is a sort of progress bar that
esimates how long we are from a checkpoint based on both a) the timeout,
and b) how many segments have been written. The timeout one is easy to
work with that way (from what I read of your code, you've worked that
angle). The part I had trouble doing was getting the WAL writers to
communicate a progress report on how many segments they filled back to the
bgwriter.

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