Horton

Re: Initial 9.2 pgbench write results

Robert Haas <robertmhaas@gmail.com>

View thread
From: Robert Haas <robertmhaas@gmail.com>
To: Ants Aasma <ants.aasma@eesti.ee>
Cc: Greg Smith <greg@2ndquadrant.com>, simon@2ndquadrant.com, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2012-02-28T17:49:06Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix I/O-conversion-related memory leaks in plpgsql.

Attachments

On Tue, Feb 28, 2012 at 11:46 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Feb 28, 2012 at 1:15 AM, Ants Aasma <ants.aasma@eesti.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