Re: [PERFORM] in-transaction insert performance in 7.5devel
> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] in-transaction insert performance in 7.5devel
"Merlin Moncure" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] in-transaction insert performance in 7.5devel
Tom Lane wrote: > "Merlin Moncure" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] in-transaction insert performance in 7.5devel
"Merlin Moncure" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] in-transaction insert performance in 7.5devel
> 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] in-transaction insert performance in 7.5devel
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] in-transaction insert performance in 7.5devel
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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
