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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org

Reply via email to