Re: in-transaction insert performance in 7.5devel

Tom Lane wrote:
> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> > I am batch inserting insert statements into a database with fsync =
on.
> > My single disk system is on a 10k drive...even though I am inside a
> > transaction there is at least 1 file sync per row insert.
>
> Are you certain you're inside a transaction?
>
> Tracing a process doing simple inserts within a transaction block,
> I don't see the process doing any I/O at all, just send/recv.  The
> background writer process is doing the work, but it shouldn't block
> the inserter.
>
> [ thinks for a bit... ]  Hmm.  I see that XLogFlush for a buffer's LSN
> is done while holding share lock on the buffer (see FlushBuffer in
> bufmgr.c).  This would mean that anyone trying to acquire exclusive
lock
> on the buffer would have to wait for WAL fsync.  In a situation where
> you were repeatedly inserting into the same table, it's somewhat
likely
> that the inserter would block this way while the bgwriter is trying to
> flush a previous update of the same page.  But that shouldn't happen
for
> *every* insert; it could happen at most once every bgwriter_delay
msec.
>
> Does it help if you change FlushBuffer to release buffer lock while
> flushing xlog?

Putting your change in resulted in about a 15% increase in insert
performance.  There may be some quirky things going on here with NTFS...

I did an update clean from cvs and I noticed big speedup across the
board.  Right now sync performance is right in line with my
expectations.  In any case, I checked and confirm that there are no
spurious fsyncs running when they are not supposed to be.

Merlin