Re: in-transaction insert performance in 7.5devel

"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?


    /*
     * Protect buffer content against concurrent update.  (Note that
     * hint-bit updates can still occur while the write is in progress,
     * but we assume that that will not invalidate the data written.)
     */
    LockBuffer(buffer, BUFFER_LOCK_SHARE);

    /*
     * Force XLOG flush for buffer' LSN.  This implements the basic WAL
     * rule that log updates must hit disk before any of the data-file
     * changes they describe do.
     */
    recptr = BufferGetLSN(buf);

+    LockBuffer(buffer, BUFFER_LOCK_UNLOCK);

    XLogFlush(recptr);

+    LockBuffer(buffer, BUFFER_LOCK_SHARE);


(This is not a committable change because it breaks the WAL guarantee;
to do this we'd have to loop until the LSN doesn't change during flush,
and I'm not sure that's a good idea.  But you can do it for testing
purposes just to see if this is where the performance issue is or not.)

Prior versions hold this lock during flush as well, but it's less likely
that the same page an active process is interested in is being written
out, since before the bgwriter only the least-recently-used page would
be a candidate for writing.

            regards, tom lane