Re: [repost] Help me develop new commit_delay advice - Mailing list pgsql-performance

On 08/02/2012 02:02 PM, Peter Geoghegan wrote:
> I made what may turn out to be a useful observation during the
> development of the patch, which was that for both the tpc-b.sql and
> insert.sql pgbench-tools scripts, a commit_delay of half of my
> wal_sync_method's reported raw sync speed looked optimal.

I dug up what I wrote when trying to provide better advice for this
circa V8.3.  That never really gelled into something worth publishing at
the time.  But I see some similar patterns what what you're reporting,
so maybe this will be useful input to you now.  That included a 7200RPM
drive and a system with a BBWC.

In the BBWC case, the only useful tuning I found was to add a very small
amount of commit_delay, possibly increasing the siblings too.  I was
using http://benjiyork.com/blog/2007/04/sleep-considered-harmful.html to
figure out the minimum sleep resolution on the server (3us at the time)
and setting commit_delay to that; then increasing commit_siblings to 10
or 20.  Jignesh Shah came back with something in the same sort of range
then at
http://jkshah.blogspot.com/2007/07/specjappserver2004-and-postgresql_09.html
, setting commit_delay=10.

On the 7200RPM drive ~= 115 TPS, 1/2 of the drive's rotation was
consistently what worked best for me across multiple tests too.  I also
found lowering commit_siblings all the way to 1 could significantly
improve the 2 client case when you did that.  Here's my notes from then:

commit_delay=4500, commit_siblings=1:  By waiting 1/2 a revolution if
there's another active transaction, I get a small improvement at the
low-end (around an extra 20 TPS between 2 and 6 clients), while not
doing much damage to the higher client loads.  This might
be a useful tuning if your expected number of active clients are low,
you don't have a good caching controller, but you'd like a little more
oomph out of things.  The results for 7000 usec were almost as good.
But in general, if you're stuck choosing between two commit_delay values
you should use the smaller one as it will be less likely to have a bad
impact on low client loads.

I also found considering a high delay only when a lot of clients were
usually involved worked a bit better than a 1/2 rotation:

commit_delay=10000, commit_siblings=20:  At higher client loads, there's
almost invariably another commit coming right behind yours if you wait a
bit.  Just plan to wait a bit more than an entire rotation between
commits.  This buys me about an extra 30 TPS on the high client loads,
which is a small fraction of an improvement (<5%) but might be worthwhile.

The fact that it seemed the optimal delay needed to vary a bit based on
the number of the siblings was one of the challenges I kept butting into
then.  Making the GUC settings even more complicated for this doesn't
seem a productive step forward for the average user.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com