Commit delay (was Re: beta5 packages) - Mailing list pgsql-hackers

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> So, the change would have to show that doing the delay when some other
> backend has dirtied a buffer is _better_ than doing no delay.

Agreed.  However, we have as yet no data that proves nonzero commit
delay is bad in the presence of multiple active backends.  As Hiroshi
pointed out, all the pgbench results we did last weekend are garbage
(unless they were done with scale factor > 1) because write conflicts on
the single "branch" row would prevent more than one backend from ever
being ready to commit at the same time.  Hiroshi's results suggest that
positive commit delay can be worthwhile when there are nonconflicting
transactions.

Note that with the extension to ignore blocked backends, my proposal
would not count backends waiting on a write conflict, and would
therefore not execute the delay in the scalefactor=1 pgbench case.
So those benchmarks do not prove it would hurt anything to have
commit delay > 0 with my proposal.

> I guess the question is "What is the average time from that bit being
> set to the actual commit,

This is obviously very application-dependent, but we know that pgbench
speeds of 40-200 tr/sec are easily achieved by 7.1 for single backends
with fsync off.  So it's evident that the total transaction time before
commit starts is a small number of milliseconds for transactions of that
complexity.

> and what is its relation to the duration of an fsync()?

fsync is slow, slow, slow, at least on my platform ... I did kernel
traces on pgbench last weekend and saw multiple clock-tick interrupts
during the fsync call.

> I guess the 10ms minimum delay time is a problem for me.

Yeah, the whole thing would be a lot better if we could get a shorter
delay.  But that doesn't mean it's no good at all.

> The good thing
> is that this delay happens _only_ if other backends are actually
> running, though if someone is sitting in psql and the are inside a
> transaction, that is going to cause a wait too.

Hmm.  A further refinement would be to add a waiting-for-client-input
bit to PROC, although if you have a fast-responding client, ignoring
such backends wouldn't necessarily be a good thing.  Notice that the
pgbench transaction involves multiple client requests ...

> Let's keep talking.  I see us so near release, I am not sure if we can
> get something that is a clear win, and we saw how the 5us fix almost got
> out in the final before we realized the performance problems with it.

Yeah, because our attention hadn't been drawn to it.  It won't escape
so easily now ;-).  The real concern here is that I'm not currently
convinced that commit_delay = 0 is a good answer under heavy load.
        regards, tom lane