X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B318CD1B17B for ; Fri, 11 Jun 2004 16:52:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00762-01 for ; Fri, 11 Jun 2004 19:52:12 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id F05A1D1B23C for ; Fri, 11 Jun 2004 16:52:09 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5BJqAFh017839; Fri, 11 Jun 2004 15:52:10 -0400 (EDT) To: "Merlin Moncure" Cc: pgsql-performance@postgresql.org Subject: Re: in-transaction insert performance in 7.5devel In-reply-to: <6EE64EF3AB31D5448D0007DD34EEB34101AE59@Herge.rcsinc.local> References: <6EE64EF3AB31D5448D0007DD34EEB34101AE59@Herge.rcsinc.local> Comments: In-reply-to "Merlin Moncure" message dated "Fri, 11 Jun 2004 14:40:00 -0400" Date: Fri, 11 Jun 2004 15:52:10 -0400 Message-ID: <17838.1086983530@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/137 X-Sequence-Number: 7195 "Merlin Moncure" 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