Re: [repost] Help me develop new commit_delay advice

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