pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: in-transaction insert performance in 7.5devel
Date: Fri, 11 Jun 2004 15:52:10 -0400
Message-ID: <17838.1086983530@sss.pgh.pa.us> (raw)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB34101AE59@Herge.rcsinc.local>
References: <6EE64EF3AB31D5448D0007DD34EEB34101AE59@Herge.rcsinc.local>

"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



view thread (8+ messages)  latest in thread

Message-ID: <17838.1086983530@sss.pgh.pa.us>
Permalink:  ../17838.1086983530@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/17838.1086983530@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, merlin.moncure@rcsonline.com
  Subject: Re: in-transaction insert performance in 7.5devel
  In-Reply-To: <17838.1086983530@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox