Peter Geoghegan wrote: > 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].
[...] > 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? If there is an agreement that half the sync time as reported by pg_test_fsync is a good value, would it make sense to have initdb test sync time and preset commit_delay? Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
