Re: Should commit_delay be PGC_SIGHUP?

On 20 March 2013 21:50, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> I realize that this isn't terribly critical, but I'd like to suggest
> that commit_delay be made PGC_SIGHUP on 9.3 (it's currently
> PGC_USERSET). It's not that a poorly chosen commit_delay setting has
> the potential to adversely affect other backends where the setting
> *has* been set in those other backends in a suitable way - the same
> thing can surely be said for work_mem. It just seems to me that
> commit_delay is now something that's intended to work at the cluster
> granularity, and as such it seems like almost a misrepresentation to
> make it PGC_USERSET.
>
> 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().

+1

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services