Thread: Help me develop new commit_delay advice
Many of you will be aware that the behaviour of commit_delay was recently changed. Now, the delay only occurs within the group commit leader backend, and not within each and every backend committing a transaction: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f11e8be3e812cdbbc139c1b4e49141378b118dee For those of you that didn't follow this development, I should point out that I wrote a blogpost that described the idea, which will serve as a useful summary: http://pgeoghegan.blogspot.com/2012/06/towards-14000-write-transactions-on-my.html 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 use Linux, so my wal_sync_method happened to have been fdatasync. I measured this using pg_test_fsync. The devel docs still say of commit_delay and commit siblings: "Good values for these parameters are not yet clear; experimentation is encouraged". This has been the case since Postgres 7.1 (i.e. it has never been clear what good values were - the folk wisdom was actually that commit_delay should always be set to 0). I hope to be able to formulate some folk wisdom about setting commit_delay from 9.3 on, that may go on to be accepted as an official recommendation within the docs. I am rather curious as to what experimentation shows optimal values for commit_delay to be for a representative cross-section of hardware. In particular, I'd like to see if setting commit_delay to half of raw sync time appears to be optimal for both insert.sql and tpc-b.sql workloads across different types of hardware with different sync times. Now, it may be sort of questionable to take those workloads as general proxies for performance, not least since they will literally give Postgres as many *completely uniform* transactions as it can handle. However, it is hard to think of another, better general proxy for performance that is likely to be accepted as such, and will allows us to reason about setting commit_delay. While I am not completely confident that we can formulate a widely useful, simple piece of advice, I am encouraged by the fact that a commit_delay of 4,000 worked very well for both tpc-b.sql and insert.sql workloads on my laptop, beating out settings of 3,000 and 5,000 on each benchmark. I am also encouraged by the fact that in some cases, including both the insert.sql and tpc-b.sql cases that I've already described elsewhere, there is actually no downside to setting commit_delay - transaction throughput naturally improves, but transaction latency is actually improved a bit too (or at least the average and worst-cases). This is presumably due to the amelioration of resource contention (from greater commit batching) more than compensating for the obvious downside of adding a delay. It would be useful, for a start, if I had numbers for a battery-backed write cache. I don't have access to one right now though, nor do I have access to any more interesting hardware, which is one reason why I'm asking for help with this. I like to run "sync" prior to running pg_test_fsync, just in case. [peter@peterlaptop pg_test_fsync]$ sync I then interpret the following output: [peter@peterlaptop pg_test_fsync]$ pg_test_fsync 2 seconds per test O_DIRECT supported on this platform for open_datasync and open_sync. Compare file sync methods using one 8kB write: (in wal_sync_method preference order, except fdatasync is Linux's default) open_datasync 112.940 ops/sec fdatasync 114.035 ops/sec fsync 21.291 ops/sec *** SNIP *** So if I can perform 114.035 8KiB sync operations per second, that's an average of about 1 per 8.77 milliseconds, or 8770 microseconds to put it in the units that commit_delay speaks. It is my hope that we will find that when this number is halved, we will arrive at a figure that is worth recommending as a general useful setting for commit_delay for the system. I guess I could gain some additional insight by simply changing my wal_sync_method, but I'd find it more interesting to look at organic setups with faster (not slower) sync times than my system's fdatasync. For those who are able to help me here, I'd like to see pgbench-tools workloads for both tpc-b.sql and insert.sql with incrementing values of commit_delay (increments of, say, 1000 microseconds, perhaps with less granularity where it isn't needed), from 0 to $(1.5 times raw sync speed) microseconds. Thanks -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Peter Geoghegan > Sent: Sunday, July 29, 2012 9:09 PM > 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 use Linux, > so my wal_sync_method happened to have been fdatasync. I measured this > using pg_test_fsync. I have done some basic test for commit_delay parameter OS version: suse linux 10.3 postgresql version: 9.3 dev on x86-64, compiled by gcc (GCC) 4.1.2 20070115 Machine details: 8 core cpu, 24GB RAM. Testcase: pgbench tcp_b test. Before running the benchmark suite, the buffers are loaded by using pg_prewarm utility. Test Results are attached with this mail. Run1,Run2,Run3 means the same test has ran 3 times. > It would be useful, for a start, if I had numbers for a battery-backed > write cache. I don't have access to one right now though, nor do I > have access to any more interesting hardware, which is one reason why > I'm asking for help with this. > I like to run "sync" prior to running pg_test_fsync, just in case. > [peter@peterlaptop pg_test_fsync]$ sync >I then interpret the following output: > [peter@peterlaptop pg_test_fsync]$ pg_test_fsync > 2 seconds per test > O_DIRECT supported on this platform for open_datasync and open_sync. > Compare file sync methods using one 8kB write: > (in wal_sync_method preference order, except fdatasync > is Linux's default) > open_datasync 112.940 ops/sec > fdatasync 114.035 ops/sec > fsync 21.291 ops/sec > *** SNIP *** I shall look into this aspect also(setting commit_delay based on raw sync). You also suggest if you want to run the test with different configuration. With Regards, Amit Kapila.
On 1 August 2012 15:14, Amit Kapila <amit.kapila@huawei.com> wrote: > I shall look into this aspect also(setting commit_delay based on raw sync). > You also suggest if you want to run the test with different configuration. Well, I was specifically interested in testing if half of raw sync time was a widely useful setting, across a variety of different, though representative I/O subsystems. Unfortunately, without some context about raw sync speed to go along with your numbers, I cannot advance or disprove that idea. It would also have been nice to see a baseline number of 0 too, to get an idea of how effective commit_delay may now be. However, that's secondary. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
> From: Peter Geoghegan [mailto:peter@2ndquadrant.com]
> Sent: Wednesday, August 01, 2012 8:49 PM
On 1 August 2012 15:14, Amit Kapila <amit.kapila@huawei.com> wrote:
>> I shall look into this aspect also(setting commit_delay based on raw
sync).
>> You also suggest if you want to run the test with different
configuration.
> Well, I was specifically interested in testing if half of raw sync
> time was a widely useful setting, across a variety of different,
> though representative I/O subsystems. Unfortunately, without some
> context about raw sync speed to go along with your numbers, I cannot
> advance or disprove that idea.
Raw sync speed data
--------------------------
2 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default) open_datasync n/a fdatasync
165.506ops/sec fsync 166.647 ops/sec fsync_writethrough
n/a open_sync 164.654 ops/sec
165.506 * 8KB operations can perform in one sec.
so 1 * 8KB operation takes 6.042 msec.
> It would also have been nice to see a baseline number of 0 too, to get
> an idea of how effective commit_delay may now be. However, that's
> secondary.
In the data sent yesterday commit_delay=0 was there.
With Regards,
Amit Kapila.
Peter, For some reason I didn't receive the beginning of this thread. Can you resend it to me, or (better) post it to the pgsql-performance mailing list? I have a linux system where I can test both on regular disk and on SSD. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 29 July 2012 16:39, Peter Geoghegan <peter@2ndquadrant.com> wrote: > Many of you will be aware that the behaviour of commit_delay was > recently changed. Now, the delay only occurs within the group commit > leader backend, and not within each and every backend committing a > transaction: I've moved this to the pgsql-performance list. Please continue the discussion there. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services