Re: some longer, larger pgbench tests with various performance-related patches

On Wed, Jan 25, 2012 at 8:49 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Jan 24, 2012 at 4:28 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> I think we should be working to commit XLogInsert and then Group
>> Commit, then come back to the discussion.
>
> I definitely agree that those two have way more promise than anything
> else on the table.  However, now that I understand how badly we're
> getting screwed by checkpoints, I'm curious to do some more
> investigation of what's going on there.  I can't help thinking that in
> these particular cases the full page writes may be a bigger issue than
> the checkpoint itself.  If that turns out to be the case it's not
> likely to be something we're able to address for 9.2, but I'd like to
> at least characterize it.

I did another run with full_page_writes=off:

http://wiki.postgresql.org/wiki/File:Tps-master-nofpw.png

This is run with the master branch as of commit
4f42b546fd87a80be30c53a0f2c897acb826ad52, same as the previous tests,
so that the results are comparable.

The graph is pretty dramatically different from the earlier one:

http://wiki.postgresql.org/wiki/File:Tps-master.png

So are the TPS numbers:

full page writes on: tps = 2200.848350 (including connections establishing)
full page writes off: tps = 9494.757916 (including connections establishing)

It seems pretty clear that, even with full_page_writes=off, the
checkpoint is affecting things negatively: the first 700 seconds or so
show much better and more consistent performance than the remaining
portion of the test. I'm not sure why that is, but I'm guessing there
was a checkpoint around that time. But the effect is much worse with
full_page_writes=on: the distinctive parabolic shape of those graphs
is apparently caused by the gradually decreasing frequency of full
page writes as the number of transactions processed since the last
checkpoint grows.

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