Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 50C2F632489 for ; Sat, 14 Mar 2009 13:10:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 37590-06 for ; Sat, 14 Mar 2009 13:10:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id D203363220C for ; Sat, 14 Mar 2009 13:09:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n2EG9nER005078; Sat, 14 Mar 2009 12:09:49 -0400 (EDT) To: Heikki Linnakangas cc: Robert Haas , Scott Carey , Greg Smith , "Jignesh K. Shah" , Kevin Grittner , "pgsql-performance@postgresql.org" Subject: Re: Proposal of tunable fix for scalability of 8.4 In-reply-to: <49BB699D.1070809@enterprisedb.com> References: <603c8f070903121829r31cf1472ha9876e5c480a542e@mail.gmail.com> <12292.1236964604@sss.pgh.pa.us> <49BB699D.1070809@enterprisedb.com> Comments: In-reply-to Heikki Linnakangas message dated "Sat, 14 Mar 2009 10:23:57 +0200" Date: Sat, 14 Mar 2009 12:09:49 -0400 Message-ID: <5077.1237046989@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.015 tagged_above=0 required=5 tests=AWL=0.015 X-Spam-Level: X-Archive-Number: 200903/189 X-Sequence-Number: 33111 Heikki Linnakangas writes: > WALInsertLock is also quite high on Jignesh's list. That I've seen > become the bottleneck on other tests too. Yeah, that's been seen to be an issue before. I had the germ of an idea about how to fix that: ... with no lock, determine size of WAL record ... obtain WALInsertLock identify WAL start address of my record, advance insert pointer past record end *release* WALInsertLock without lock, copy record into the space just reserved The idea here is to allow parallelization of the copying of data into the buffers. The hold time on WALInsertLock would be very short. Maybe it could even become a spinlock, though I'm not sure, because the "advance insert pointer" bit is more complicated than it looks (you have to allow for the extra overhead when crossing a WAL page boundary). Now the fly in the ointment is that there would need to be some way to ensure that we didn't write data out to disk until it was valid; in particular how do we implement a request to flush WAL up to a particular LSN value, when maybe some of the records before that haven't been fully transferred into the buffers yet? The best idea I've thought of so far is shared/exclusive locks on the individual WAL buffer pages, with the rather unusual behavior that writers of the page would take shared lock and only the reader (he who has to dump to disk) would take exclusive lock. But maybe there's a better way. Currently I don't believe that dumping a WAL buffer (WALWriteLock) blocks insertion of new WAL data, and it would be nice to preserve that property. regards, tom lane