Re: Doc patch making firm recommendation for setting the value of commit_delay

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