Обсуждение: XLogFlush invoked about twice as many times after 9.2 group commit enhancement

Hello,

I have been trying to understand how group commit implementation works
the way it does after 9.2 group commit enhancement patch
(9b38d46d9f5517dab67dda1dd0459683fc9cda9f on REL9_2_STABLE). I have
observed some behavior in this regard that I could not understand.

Profiling results show that XLogFlush() is called about twice as many
times after this patch while for XLogWrite() count remains about same
as before. This patch modifies XLogFlush() such that it  offers the
said performance gain by alleviating the lock contention on
WALWriteLock using the new LWLockAcquireOrWait(). I do not however
understand why XLogFlush is invoked twice as many times (as seen from
the profiling results) as an effect of this patch. Why should this
patch make XLogFlush() being invoked twice as many times?

I used "pgbench -c 32 -t 1000 pgbench" in both cases with TPS result
after applying the patch not being significantly different (as in not
twice as much on my system).

I used:
pgbench scale=10

shared_buffers=256MB
max_connections=1000
checkpoint_segments=15
synchronous_commit=on

Comments?

--

Amit Langote


--

Amit Langote

On Tue, May 7, 2013 at 8:13 AM, Amit Langote <amitlangote09@gmail.com> wrote:
> Profiling results show that XLogFlush() is called about twice as many
> times after this patch while for XLogWrite() count remains about same
> as before. This patch modifies XLogFlush() such that it  offers the
> said performance gain by alleviating the lock contention on
> WALWriteLock using the new LWLockAcquireOrWait(). I do not however
> understand why XLogFlush is invoked twice as many times (as seen from
> the profiling results) as an effect of this patch. Why should this
> patch make XLogFlush() being invoked twice as many times?

Why is that surprising? Most of those XLogFlush() calls will recheck
the flushed-up-to point, and realize that another backend assumed the
role of group commit leader, and flushed their WAL for them, so aside
from the wait, the call to XLogFlush is cheap for that individual
backend. It's being invoked twice as many times because backends *can*
invoke it twice as many times.

For the record, the new group commit code did more than just alleviate
lock contention. If it was true that amelioration of lock contention
fully explained the improvements, then surely sleeping on an exclusive
lock on WALWriteLock within XLogFlush would always hurt throughput,
when in fact it can considerably help throughput, as demonstrated by
9.3's commit_delay implementation.

-- 
Peter Geoghegan

> Why is that surprising? Most of those XLogFlush() calls will recheck
> the flushed-up-to point, and realize that another backend assumed the
> role of group commit leader, and flushed their WAL for them, so aside
> from the wait, the call to XLogFlush is cheap for that individual
> backend. It's being invoked twice as many times because backends *can*
> invoke it twice as many times.

After going through it again, I think, I am getting convinced it is
not surprising. Since, backends are now able to return quickly from
XLogFlush(), on an average, they should also be able to proceed to
next transactions faster and hence cause XLogFlush() to be invoked
more often. So, any rise in number of XLogFlush() calls should roughly
be accounted for by increased throughput. Am I right in interpreting
it this way?

--

Amit Langote

On Tue, May 7, 2013 at 7:52 PM, Amit Langote <amitlangote09@gmail.com> wrote:
> So, any rise in number of XLogFlush() calls should roughly
> be accounted for by increased throughput. Am I right in interpreting
> it this way?

I think so. There certainly isn't any question that the increased
throughput and the increased number of XLogFlush() calls are because
of the new group commit behavior. The cost of a WAL write + flush is
more effectively amortized, and so XLogFlush() calls becomes cheaper.
I'm not prepared to make any predictions as to exactly how they might
relate.


-- 
Peter Geoghegan