Doc patch making firm recommendation for setting the value of commit_delay - Mailing list pgsql-hackers

Some of you will be aware that I've tried to formulate a good general
recommendation for setting the value of commit_delay, in light of the
improvements made in 9.3. I previously asked for help finding a
methodology for optimising commit_delay [1]. The documentation related
to commit_delay still says that we don't know what might work well,
though I don't think that's true any more.

I found the time to do some benchmarks of my own - Greg Smith made
available a server that he frequently uses for benchmarks. It was
previously my observation that "half of raw single-page sync time as
reported by pg_test_fsync for you wal_sync_method" worked best for
commit_delay. I went so far as to modify pg_test_fsync to output
average time per op in microseconds for each operation with
commit_delay in mind, which is a patch that has already been committed
[2]. This general approach worked really well on my laptop, which has
a slowish fsync of about 8 milliseconds (8,000 microseconds), for
which a commit_delay setting of 4,000 (microseconds) seemed to clearly
work best, on both insert and tpc-b benchmarks [3].

This server has an Intel 320 SSD series. Greg has already written
quite a bit about this drive [4] for unrelated reasons. For the SSD,
results of an insert-based pgbench-tools run are shown, with
commit_delay at 0, 20 and 59 (determined by following my general
advise above):

http://commit-delay-results-ssd-insert.staticloud.com

I also looked at a 3-disk RAID0 of 7200RPM drives connected through a
512MB battery-backed write cache (Areca controller), again using
insert.sql:

http://commit-delay-stripe-insert.staticloud.com

In addition to a tpc-b benchmark with the same data directory on the
same 3-disk stripe:

http://commit-delay-results-stripe-tpcb.staticloud.com

I used the same postgresql.conf in all cases, which you can see for
each report, and did the usual median-of-three thing in all cases
(though each run lasted 120 seconds in all cases, not the default 60
seconds). Settings used for one particular pgbench run can be viewed
here (though they're all exactly the same anyway):

http://commit-delay-results-ssd-insert.staticloud.com/19/pg_settings.txt

Attached is a doc-patch that makes recommendations that are consistent
with my observations about what works best here. I'd like to see us
making *some* recommendation - for sympathetic cases, setting
commit_delay appropriately can make a very large difference to
transaction throughput. Such sympathetic cases - many small write
transactions - are something that tends to be seen relatively
frequently with web applications, that disproportionately use cloud
hosting. It isn't at all uncommon for these cases to be highly bound
by their commit rate, and so it is compelling to try to amortize the
cost of a flush as effectively as possible there. It would be
unfortunate if no one was even aware that commit_delay is now useful
for these cases, since the setting allows cloud hosting providers to
help these cases quite a bit, without having to do something like
compromise durability, which in general isn't acceptable.

The point of all these benchmarks isn't to show how effective
commit_delay now is, or can be - we already had that discussion months
ago, before the alteration to its behaviour was committed. The point
is to put my proposed doc changes in the appropriate context, so that
I can build confidence that they're balanced and helpful, by showing
cases that are not so sympathetic.

Thoughts?

[1] http://archives.postgresql.org/pgsql-performance/2012-08/msg00003.php

[2] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82e429794b348cd80c1d1b011e21ffac98bc6e88

[3] http://pgeoghegan.blogspot.com/2012/06/towards-14000-write-transactions-on-my.html

[4] http://blog.2ndquadrant.com/intel_ssds_lifetime_and_the_32/

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services