Re: Initial 9.2 pgbench write results

On Tue, Mar 6, 2012 at 4:35 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> I don't think reseting the stats has anything to do with it, it is
> just that the shared_buffers warmed up over time.

Yes.

> 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.
>
> If I disable that, then the bgwriter remains "just in time", just
> slightly ahead of the clock-sweep, and the dirty-before-evict drops a
> lot.
>
> 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?

I'm not sure that 2 minutes is a bad value (although maybe it is) but
I think you've definitely got a good point as regards
min_scan_buffers. It seems like the main LRU scan that begins here:

while (num_to_scan > 0 && reusable_buffers < upcoming_alloc_est)

Ought to be doing something like this:

while (num_to_scan > 0 && (reusable_buffers <
upcoming_alloc_est || num_already_scanned < min_scan_buffers))

...and the logic that changes upcoming_alloc_est based on
min_scan_buffers ought to be ripped out. Unless I'm misunderstanding
this logic, this will cause the background writer to scan the buffer
pool considerably FASTER than once every two minutes when there are
lots of high-usage-count buffers.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company