Re: Why do we still have commit_delay and commit_siblings?

On Tue, May 15, 2012 at 12:07 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> 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?

No.

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

No. Scale factor is 10.

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

I'm pretty sure it is. It was on the IBM POWER7 machine, anyway,
because the pg_usleep calls showed up in the perf call graph I took.

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

I am definitely not getting that.

Let's try this again. Increase scale factor to 40. Decrease
commit_siblings to 0. With 10 clients, and commit_delay=5000, I get
109-132 tps. With commit_delay=0, I get 58-71 tps.

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