Re: Initial 9.2 pgbench write results

On 03/06/2012 04:35 PM, Jeff Janes wrote:
> On my testing, this dirty-before-evict is because the bgwriter is
> riding too far ahead of the clock sweep, because of
> scan_whole_pool_milliseconds. Because it is far ahead, that leaves a
> lot of run between the two pointers for re-dirtying cache hits to
> land.
>
> Not only is 2 minutes likely to be too small of a value for large
> shared_buffers, but min_scan_buffers doesn't live up to its name. It
> is not the minimum buffers to scan, it is the minimum to find/make
> reusable. If lots of buffers have a nonzero usagecount (and if your
> data doesn't fix in shared_buffers, it is hard to see how more than
> half of the buffers can have zero usagecount) or are pinned, you are
> scanning a lot more than min_scan_buffers.

The naming could be better in spots. If I wanted to blame a past
version of myself for predicting this but doing nothing, I could dig up
disclaimer e-mails I wrote in 2007, about whether fragility to base
"magic constants" in the proposed model was too much. 9.2 and current
generation hardware seems to have finally pushed on enough soft spots to
crack more of those assumptions.

> If scan_whole_pool_milliseconds is to be used at all, it seems like it
> should not be less than checkpoint_timeout. If I don't want
> checkpoints trashing my IO, why would I want someone else to do it
> instead?

The idea of the BGW LRU scan is to find things that can be written
usefully now due to low usage. The checkpoint one writes regardless of
usage count. Your can find both opportunity and problem in that overlap.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com