X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7EB035E46BF for ; Wed, 18 Aug 2004 18:12:03 -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 54839-09 for ; Wed, 18 Aug 2004 21:12:06 +0000 (GMT) Received: from cmailg3.svr.pol.co.uk (cmailg3.svr.pol.co.uk [195.92.195.173]) by svr1.postgresql.org (Postfix) with ESMTP id C87115E46DC for ; Wed, 18 Aug 2004 18:11:55 -0300 (ADT) Received: from modem-54.lion.dialup.pol.co.uk ([217.135.160.54] helo=happyplace) by cmailg3.svr.pol.co.uk with smtp (Exim 4.14) id 1BxXim-0006SE-Bu; Wed, 18 Aug 2004 22:11:48 +0100 From: "Simon Riggs" To: "Manfred Spraul" Cc: "Tom Lane" , "Bruce Momjian" , "Klaus Naumann" , , "Pgsql-Hackers@Postgresql. Org" Subject: Re: NOT LOGGED options (was Point in Time Recovery ) Date: Wed, 18 Aug 2004 22:28:12 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) In-Reply-To: <4123895A.6060203@colorfullife.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal 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: 200408/1071 X-Sequence-Number: 58040 > Manfred Spraul > Simon@2ndquadrant.com wrote: > > >>Tom Lane wrote > >> > >> > >>>NOT LOGGED options on CREATE INDEX and COPY, to allow users to take > >>>advantage of the no logging optimization without turning off > PITR system > >>>wide. (Just as this is possible in Oracle and Teradata). > >>> > >>> > >>Isn't this in direct conflict with your opinion above? And I cannot say > >>that I think this one is a good idea. We do not have support for > >>selective catalog xlogging; > >> > Is it possible to skip the xlog fsync for NOT LOGGED transactions? > Hmm...good thinking...however, For very large operations, its the volume of the xlog writes thats the problem, not the fsync of the logs. The type of things I'm thinking about are large CREATE INDEX and large COPY operations, for very large tasks i.e. > 1Gb. These are most useful in data warehousing operations - which is about 20% of the user base according to the survey stats from www.postgresql.org. The wal buffer only gets synced at end of transaction, or when the buffer is full. On long operations there is still only one commit, so not fsyncing there won't gain much. The buffer will fill up repeatedly and require flushing - which you can't really skip because when you get to the commit you need to be certain that everything is down to disk - there's not much point fsyncing the commit if the previous wal records haven't been. If there was a way to tell whether a block in the wal buffer had been written by a NOT LOGGED transaction, then it might be possible to vary the fsync behaviour accordingly. That's a good idea if thats what you meant, though it would mean changing some critical, well tested code that every wal record goes through. I'd rather simply not write wal at all for the certain specific situations when the user requests it - there are already decision points in the code for both the situations I've mentioned, since these have been optimised in 8.0 for when archive_command has not been set. It would be a simply matter to add in a check at that point. Anyway...this is probably 8.1 stuff now. Best Regards, Simon Riggs