Re: Initial 9.2 pgbench write results

On Tue, Feb 28, 2012 at 11:46 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Feb 28, 2012 at 1:15 AM, Ants Aasma <ants(dot)aasma(at)eesti(dot)ee> wrote:
>> My hypothesis for the TPS regression is that it is due to write combining.
>> When the workload is mainly bound by I/O, every little bit that can be saved
>> helps the bottomline. Larger scalefactors don't get the benefit because
>> there is less write combining going on overall.
>
> This is an interesting hypothesis which I think we can test.  I'm
> thinking of writing a quick patch (just for testing, not for commit)
> to set a new buffer flag BM_BGWRITER_CLEANED to every buffer the
> background writer cleans.   Then we can keep a count of how often such
> buffers are dirtied before they're evicted, vs. how often they're
> evicted before they're dirtied.  If any significant percentage of them
> are redirtied before they're evicted, that would confirm this
> hypothesis.  At any rate I think the numbers would be interesting to
> see.

Patch attached.

I tried it on my laptop with a 60-second pgbench run at scale factor
100, and got this:

LOG: bgwriter_clean: 1387 evict-before-dirty, 10 dirty-before-evict
LOG: bgwriter_clean: 1372 evict-before-dirty, 10 dirty-before-evict
LOG: bgwriter_clean: 1355 evict-before-dirty, 10 dirty-before-evict
LOG: bgwriter_clean: 1344 evict-before-dirty, 8 dirty-before-evict
LOG: bgwriter_clean: 1418 evict-before-dirty, 8 dirty-before-evict
LOG: bgwriter_clean: 1345 evict-before-dirty, 7 dirty-before-evict
LOG: bgwriter_clean: 1339 evict-before-dirty, 6 dirty-before-evict
LOG: bgwriter_clean: 1362 evict-before-dirty, 9 dirty-before-evict

That doesn't look bad at all. Then I reset the stats, tried it again,
and got this:

LOG: bgwriter_clean: 3863 evict-before-dirty, 198 dirty-before-evict
LOG: bgwriter_clean: 3861 evict-before-dirty, 199 dirty-before-evict
LOG: bgwriter_clean: 3978 evict-before-dirty, 218 dirty-before-evict
LOG: bgwriter_clean: 3928 evict-before-dirty, 204 dirty-before-evict
LOG: bgwriter_clean: 3956 evict-before-dirty, 207 dirty-before-evict
LOG: bgwriter_clean: 3906 evict-before-dirty, 222 dirty-before-evict
LOG: bgwriter_clean: 3912 evict-before-dirty, 197 dirty-before-evict
LOG: bgwriter_clean: 3853 evict-before-dirty, 200 dirty-before-evict

OK, that's not so good, but I don't know why it's different. I'm not
sure I can reproduce the exact same scenario Greg is seeing - this is
totally different hardware - but I'll play around with it a little bit
more. Greg, if you happen to feel like testing this on one of your
problem cases I'd be interested in seeing what it spits out.

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