Re: synchronous commit vs. hint bits

On Mon, Nov 7, 2011 at 12:26 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> 5. Make the WAL writer more responsive, maybe using latches, so that
>> it doesn't take as long for the commit record to make it out to disk.
>
> I'm working on this already as part of the update for power
> reduction/group commit/replication performance.

OK. Here's an interesting result on that front that I so far can't
explain. I lowered wal_writer_delay to 20 ms and repeated my test:

tps = 10175.265689 (including connections establishing) [unpatched]
tps = 10159.597727 (including connections establishing) [patched]

Now, that's odd. I expect that to improve performance on the
unpatched code, by reducing the amount of time we have to wait for the
commit record to hit the disk. I did *not* expect it to improve the
performance of the patched code, since one would think that setting
the hint bit the first time through would be about as good as we could
possibly do. And yet, those are the numbers. Apparently, there's
some other effect whereby a more responsive walwriter improves
performance on this setup (beats me what it is, though).

Here it is with wal_writer_delay=50 ms:

tps = 9964.225358 (including connections establishing) [unpatched]
tps = 10048.396729 (including connections establishing) [patched]

And back to wal_writer_delay=200ms:

tps = 8119.121633 (including connections establishing) [unpatched]
tps = 9602.645495 (including connections establishing) [patched]

So it seems like there is quite a bit of win available here, though at
the moment I don't know why.

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