Re: Why do we still have commit_delay and commit_siblings?

On Tue, May 15, 2012 at 7:47 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, May 14, 2012 at 8:42 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> On Sun, May 13, 2012 at 11:07 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>>
>>> Keeping a parameter without any clue as to whether it has benefit is
>>> just wasting people's time.
>>>
>>> We don't ADD parameters based on supposition, why should we avoid
>>> removing parameters that have no measured benefit?
>>
>> Using pgbench -T30 -c 2 -j 2 on a 2 core laptop system, with a scale
>> that fits in shared_buffers:
>>
>> --commit-delay=2000 --commit-siblings=0
>> tps = 162.924783 (excluding connections establishing)
>>
>> --commit-delay=0 --commit-siblings=0
>> tps = 89.237578 (excluding connections establishing)
>
> These results are astonishingly good, and I can't reproduce them.  I
> spent some time this morning messing around with this on the IBM
> POWER7 machine and my MacBook Pro.  Neither of these have
> exceptionally good fsync performance, and in particular the MacBook
> Pro has really, really bad fsync performance.

Did you also set --commit-siblings=0?

Are you using -i -s 1, and therefor serializing on the sole entry in
pgbench_branches?

Could you instrument the call to pg_usleep and see if it is actually
being called?
(Or, simply strace-ing the process would probably tell you that).

> On the IBM POWER7 machine, I'm not able to demonstrate any performance
> improvement at all from fiddling with commit delay.  I tried tests at
> 2 clients, 32 clients, and 80 clients, and I'm getting... nothing.
> No improvement at all.  Zip.  I tried a few different settings for
> commit_delay, too.  On the MacBook Pro, with
> wal_sync_method=obscenely_slow^Wfsync_writethrough,

If one of the methods gives sync times that matches the rotational
speed of your disks, that is the one that I would use. If the sync is
artificially slow because something in the kernel is gummed up, maybe
whatever the problem is also interferes with other things. (Although
I wouldn't expect it to, that is just a theory). I have a 5400 rpm
drive, so 89 single client TPS is almost exactly to be expected.

> I can't
> demonstrate any improvement at 2 clients, but at 80 clients I observe
> a roughly 1.8x performance gain (~50 tps -> ~90 tps).  Whether this is
> anything to get excited about is another matter, since you'd hope to
> get more than 1.1 transactions per second no matter how slow fsync is.

Yeah, you've got something much worse going on there than commit_delay
can solve.

With the improved group-commit code, or whatever we are calling it, if
you get 50tps single-client then at 80 clients you should get almost
40x50 tps, assuming the scale is large enough to not block on row
locks.

Cheers,

Jeff