Re: Why do we still have commit_delay and commit_siblings?

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.

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, 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.

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