Re: Uh, I change my mind about commit_delay + commit_siblings (sort of)

On 29 May 2012 07:16, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> On 29.05.2012 04:18, Peter Geoghegan wrote:
>> Benchmark results, with and without a delay of 3000ms (commit_siblings
>> is 0 in both cases) are available from:
>>
>> http://leadercommitdelay.staticloud.com

Sorry, I do of course mean a commit_delay of 3000 (microseconds, not
milliseconds).

> Can you elaborate how to read those results? What do "insert delay test" and
> "patch, commit_delay=0" mean? Which graph should I be looking at?

It's very simple. It's an insert.sql based workload, for the patch,
with and without commit_delay set to 3000 (and a commit_delay of 0
makes the commit_delay code inert, just as before). Setting
commit_delay appears to be far more effective with the patch.

Here is a new benchmark:

http://leadermastercommitdelay.staticloud.com/

This is more or less my usual group commit benchmark - median of 3
runs of insert.sql across a wide range of client counts. The
pgbench-tools config is attached for your reference.

On this occasion, I set shared_buffers to 1GB once more (and
consequently, wal_buffers was 16MB).

The green line should look familiar to you. With commit_delay = 0,
it's equivalent to commit_delay=0 on master. In other words, it's very
similar to a number of benchmarks that have already been performed. I
believe that the numbers will line up pretty well with my original
group commit benchmark from back in January, for example, as that was
also performed on my Thinkpad.

The effect that Jeff Janes observed on master is apparent here at low
client counts - master with commit_delay = 3000 is almost as effective
at 8 clients as it is for the patch. After that, the effect is almost
entirely eliminated, which is consistent with my earlier observations
about commit_delay post new group commit.

There obviously appears to be a further significant increase in
transaction throughput with this configuration and patch. Even the
average latency of the patch with commit_delay=3000 is a bit better
than either master with group_commit = 3000 or baseline (as I've said
baseline could have equally well been on master or with master + the
patch).

I suppose my main beef with commit_delay before, apart from the simple
fact that it wasn't actually useful to any significant degree, was
that the code didn't usefully interact with the group commit
improvements, and we didn't revisit commit_delay for 9.2 even in light
of its assumptions not necessarily continuing to hold. Perhaps we
should revisit commit_delay for 9.2 now - we never adequately answered
the question of how useful it was in light of new group commit, so
revisiting that question can be justified as closing an open item,
rather than adding new functionality.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services