Re: in-transaction insert performance in 7.5devel

Tom Lane wrote:
> "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)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