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@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
