From: Simon Riggs <simon@2ndquadrant.com>
To: Manfred Spraul <manfred@colorfullife.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Klaus Naumann <kn@mgnet.de>
Cc: markw@osdl.org
Cc: Pgsql-Hackers@Postgresql. Org <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: <NOEFLCFHBPDAFHEIPGBOOENJCCAA.simon@2ndquadrant.com> (raw)
In-Reply-To: <4123895A.6060203@colorfullife.com>
> 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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: simon@2ndquadrant.com, manfred@colorfullife.com, tgl@sss.pgh.pa.us, pgman@candle.pha.pa.us, kn@mgnet.de, markw@osdl.org, pgsql-hackers@postgresql.org
Subject: Re: NOT LOGGED options (was Point in Time Recovery )
In-Reply-To: <NOEFLCFHBPDAFHEIPGBOOENJCCAA.simon@2ndquadrant.com>
References: <4123895A.6060203@colorfullife.com> <NOEFLCFHBPDAFHEIPGBOOENJCCAA.simon@2ndquadrant.com>
* 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