Thread: in-transaction insert performance in 7.5devel

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.

Is this normal?  On my hardware, which is pretty quick, I am limited to
about 50 inserts/sec.  If I operate outside of a transaction, the number
is closer to 30.  With fsync off, I can hit over 1000.

IIRC in previous versions insert performance was a lot faster when in
transaction, is that the case?

Merlin

On Fri, 2004-06-11 at 14:40, Merlin Moncure wrote:
> 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.

Which filesystem?

PostgreSQL isn't issuing the sync except at commit of a transaction, but
some filesystems do wacky things if you ask them too.

Rod Taylor wrote:
> On Fri, 2004-06-11 at 14:40, Merlin Moncure wrote:
> > 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.
>
> Which filesystem?
>
> PostgreSQL isn't issuing the sync except at commit of a transaction,
but
> some filesystems do wacky things if you ask them too.

Um, NTFS. :)  I'm playing with the new 'syncer' to get a feel for the
new performance considerations.

As I understand it, sync() is never called anymore.  mdsync() hits the
all the files 1 by 1 with an fsync.  My understanding of the commit
process is that 30 tps is quite reasonable for my hardware.

What surprised was that I am getting at least 1 seek/insert inside a
transaction.

Merlin

> As I understand it, sync() is never called anymore.  mdsync() hits the
> all the files 1 by 1 with an fsync.  My understanding of the commit
> process is that 30 tps is quite reasonable for my hardware.

Sorry. I didn't see the version in the subject and assumed 7.4 on a
Linux machine with excessive journaling enabled.

"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

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

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> Tom Lane wrote:
>> 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.

Was that 15% before or after updating from CVS?

The more I think about the looping aspect the less I like it, so I'd
prefer not to pursue making the unlock change for real.  But if it's
really a 15% win then maybe we need to...

            regards, tom lane

> Was that 15% before or after updating from CVS?
>
> The more I think about the looping aspect the less I like it, so I'd
> prefer not to pursue making the unlock change for real.  But if it's
> really a 15% win then maybe we need to...
>
>             regards, tom lane

After.  So far, I haven't been able to reproduce original the insert
problem.  A word of warning: the version I was testing with was patched
with some unapproved patches and that may have been part of the issue.

Here are my results (10k drive, NTFS):
fsync off, in xact: ~ 398 i/sec
fsync off, outside xact: ~ 369 i/sec
fsync on, in xact: ~ 374 i/sec
fsync on, outside xact: ~ 35 i/sec

with your code change:
fsync on, in xact: ~ 465 i/sec
fsync on, outside xact: ~ 42 i/sec

Don't put too much faith in these results.  If you are still
contemplating a code change, I'll set up a test unit for more accurate
results and post the code (my current tests are in COBOL).

Merlin