Re: Should commit_delay be PGC_SIGHUP?

On Wed, Mar 20, 2013 at 09:50:55PM +0000, Peter Geoghegan wrote:
> The fact is that whichever backend happens to end up becoming the
> group commit leader from one XLogFlush() call to the next is, for all
> practical purposes, unpredictable. You cannot reasonably hope to avoid
> a delay within an important transaction that needs to prioritize
> keeping its own latency low over total cluster throughput. If you set
> commit_delay to 0 in your important transaction with this is mind,
> your chances of becoming the group commit leader and avoiding the
> delay are slim to almost none. Much more often than not, the important
> transaction will end up becoming a group commit follower, and it'll
> still spend a significant fraction of commit_delay (about 1/2, on
> average) blocking on LWLockAcquireOrWait().

I acknowledge that "SET commit_delay = 0" does not usefully reduce latency for
a transaction running on a system subject to a uniform, high commit rate. It
is useful for other things. Suppose you have a low-concurrency system with
commit_delay=0 in postgresql.conf, but you occasionally spin up a parallel
task that benefits from nonzero commit_delay. Changing commit_delay in the
task's sessions is a decent approximation of, and more convenient than,
temporarily modifying postgresql.conf.

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com