Recent test results have shown a substantial performance improvement
(+25%) if WAL logging is disabled for large COPY statements. This is to
be expected, though has a price attached: losing the ability to crash
recover data loaded in this manner.

There are two parts to this proposal. First, when and whether to do this
at all. Second, syntax and invocation.

Why?

Performance.

The performance gain has a price and so should only be enabled if
requested explicitly by the user. It is up to the user whether they
accept this price, since in many useful cases it is a small price
against a huge saving.

The price is that if a crash occurs, then any table that was not empty
to begin with would not be in a transactionally consistent state
following crash recovery. It may have data in it, but it would be up to
the user to determine whether that was satisfactory or not. It could be
possible to sense what to do in this situation automatically, by putting
the table into a needs-recovery type state... I don't propose to handle
this *at this stage*.

Syntax and invocation:

Previously I had discussed adding a NOLOGGING option onto both COPY and
CREATE TABLE AS SELECT that would bypass the creation of wal logging
data. That is still a possibility, but would require manual code changes
to much of the SQL submitted.

Now, I would like to discuss adding an enable_logging USERSET GUC, that
would apply *only* to COPY and CREATE TABLE AS SELECT. The default of
this would be false.

How can we gain this performance benefit for those willing to accept the
restrictions imposed?

Your comments are sought and are most welcome.

Best Regards, Simon Riggs


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Simon Riggs <simon@2ndquadrant.com> writes:
> Recent test results have shown a substantial performance improvement
> (+25%) if WAL logging is disabled for large COPY statements.

How much of that is left after we fix the 64-bit-CRC issue?

> Now, I would like to discuss adding an enable_logging USERSET GUC,

[ fear and loathing ... ]

I don't like the idea of a GUC at all, and USERSET is right out.
I think it would have to be system-wide (cf fsync) to be even
implementable let alone somewhat predictable. Even if it could
be done per-backend with reasonable semantics, random users should
not get to make that decision --- it should be the DBA's call,
which means it needs at least SUSET permissions.

BTW, I'm sure you are the last one who needs to be reminded that
any such thing breaks PITR completely. Which is surely sufficient
reason not to let it be USERSET.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Tue, May 31, 2005 at 10:47:30PM -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Recent test results have shown a substantial performance improvement
> > (+25%) if WAL logging is disabled for large COPY statements.

> BTW, I'm sure you are the last one who needs to be reminded that
> any such thing breaks PITR completely. Which is surely sufficient
> reason not to let it be USERSET.

This doesn't work for COPY, but maybe for CREATE TABLE AS we could log
the fact that the command was executed, so the replayer could execute
the same command again.

Of course, this handwaving doesn't explain how the system in recovery
mode would be able to execute a full query to reconstruct the table, and
also it doesn't say a lot about the extra complexity at the source level
to implement this option.

For people loading big files into the database, maybe we could think
about a command to let a file be loaded directly as initial table
content. So all that we'd need is a program to write the file, which
could be done externally (The filewriter would have to have access to
the catalog and input functions for the involved types, though I think
for simple types it would be straighforward ... we could write frozen
tuples to avoid TransactionId problems.)

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
www.google.com: interfaz de línea de comando para la web.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Wed, 2005-06-01 at 00:40 -0400, Alvaro Herrera wrote:
> This doesn't work for COPY, but maybe for CREATE TABLE AS we could log
> the fact that the command was executed, so the replayer could execute
> the same command again.
>
> Of course, this handwaving doesn't explain how the system in recovery
> mode would be able to execute a full query to reconstruct the table

There's also the typical problem with this kind of approach: how do you
handle non-deterministic queries? (e.g. "CREATE TABLE ... AS SELECT
random(), gettimeofday(), some_func(t1.x) FROM t1 LIMIT 5")

-Neil



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Personally I don't think that it is a good idea to do that.
People will tend to corrupt their systems because they want speed
(sometimes without thinking about the consequences).

I can only think of one scenario where nologging would actually make
sense: Many people use session tables to keep track of user level
information on a website. corrupting a session table (usually not very
large) would not cause a lot of problems.

Doing it for COPY would be fatal. I can tell you from experience that
80% of all users will use that if the manual says that PostgreSQL will
beform better this way. This is a key feature to make people think that
PostgreSQL is reliable.

Best regards,

Hans


Simon Riggs wrote:
> Recent test results have shown a substantial performance improvement
> (+25%) if WAL logging is disabled for large COPY statements. This is to
> be expected, though has a price attached: losing the ability to crash
> recover data loaded in this manner.
>
> There are two parts to this proposal. First, when and whether to do this
> at all. Second, syntax and invocation.
>
> Why?
>
> Performance.
>
> The performance gain has a price and so should only be enabled if
> requested explicitly by the user. It is up to the user whether they
> accept this price, since in many useful cases it is a small price
> against a huge saving.
>
> The price is that if a crash occurs, then any table that was not empty
> to begin with would not be in a transactionally consistent state
> following crash recovery. It may have data in it, but it would be up to
> the user to determine whether that was satisfactory or not. It could be
> possible to sense what to do in this situation automatically, by putting
> the table into a needs-recovery type state... I don't propose to handle
> this *at this stage*.
>
> Syntax and invocation:
>
> Previously I had discussed adding a NOLOGGING option onto both COPY and
> CREATE TABLE AS SELECT that would bypass the creation of wal logging
> data. That is still a possibility, but would require manual code changes
> to much of the SQL submitted.
>
> Now, I would like to discuss adding an enable_logging USERSET GUC, that
> would apply *only* to COPY and CREATE TABLE AS SELECT. The default of
> this would be false.
>
> How can we gain this performance benefit for those willing to accept the
> restrictions imposed?
>
> Your comments are sought and are most welcome.
>
> Best Regards, Simon Riggs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/664/393 39 74
www.cybertec.at, www.postgresql.at


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Tue, 2005-05-31 at 22:47 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Recent test results have shown a substantial performance improvement
> > (+25%) if WAL logging is disabled for large COPY statements.
>
> How much of that is left after we fix the 64-bit-CRC issue?

Well, I don't know. The I/O is the main thing I'm trying to avoid.

> > Now, I would like to discuss adding an enable_logging USERSET GUC,
>
> [ fear and loathing ... ]

OK. I needed to say the idea, to make sure we had considered it. I now
pronounce it dead and buried.

> BTW, I'm sure you are the last one who needs to be reminded that
> any such thing breaks PITR completely. Which is surely sufficient
> reason not to let it be USERSET.

You're right, thank you. But I do need to be reminded to say "this would
only apply when archiving is not enabled" just as the other two existing
WAL-avoidance optimisations do.

In answer to the other points raised, the main use case for this
optimisation is to improve data load performance into an empty table.
This is a very timeconsuming stage on a big warehouse and needs
performance optimization. I agree with Hans-Jurgen that this is a
dangerous option for use on general COPY commands, since these can be
used on empty and already populated tables. I seek a way to improve the
main use case though without leaving any danger in other situations.

I have two suggested approaches:
1. Introduce a new LOAD command that only works on empty tables.
Following a crash, the table is dropped and the user accepts that the
action-on-recovery is to reload the table. (Though in PITR mode, the
data would be logged).

2. Introduce NOT LOGGED INITIALLY mode, as DB2 has done. The first COPY
into an empty table would avoid WAL logging, if the user invokes that
option on the specific COPY command.

There are some other arguments in favour of a LOAD command.... Alon?

Best Regards, Simon Riggs


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Neil Conway <neilc@samurai.com> writes:

> On Wed, 2005-06-01 at 00:40 -0400, Alvaro Herrera wrote:
> > This doesn't work for COPY, but maybe for CREATE TABLE AS we could log
> > the fact that the command was executed, so the replayer could execute
> > the same command again.
> >
> > Of course, this handwaving doesn't explain how the system in recovery
> > mode would be able to execute a full query to reconstruct the table
>
> There's also the typical problem with this kind of approach: how do you
> handle non-deterministic queries? (e.g. "CREATE TABLE ... AS SELECT
> random(), gettimeofday(), some_func(t1.x) FROM t1 LIMIT 5")

For CREATE TABLE AS in the non-PITR case you don't really need to WAL log the
records at all. If it fails in the middle you just drop the table. When it
completes you do a checkpoint before acknowledging the COMMIT.

I think this is already done for CREATE INDEX/REINDEX, also only in the
non-PITR case.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

> There are some other arguments in favour of a LOAD command.... Alon?

We already have LOAD, so you'll have to choose something else

Chris


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Greg Stark <gsstark@MIT.EDU> writes:

> For CREATE TABLE AS in the non-PITR case you don't really need to WAL log the
> records at all. If it fails in the middle you just drop the table. When it
> completes you do a checkpoint before acknowledging the COMMIT.
>
> I think this is already done for CREATE INDEX/REINDEX, also only in the
> non-PITR case.

Sorry to followup to my own message, but it occurs to me that COPY could be
made to automatically do this for the case of an empty destination table too.

I'm not sure if it should automatically check for an empty table or if there
should be an option for the user to indicate he wants COPY to replace the
current contents entirely. The latter might actually be more useful. .

But either way, you just WAL log a record indicating that the table should be
entirely empty. Then you fill it up without logging anything. Do a checkpoint
and then WAL log that the COPY is finished. If any failure occurs replay
leaves it empty.

Again this sadly only works in the non-PITR case.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On K, 2005-06-01 at 09:16 +0100, Simon Riggs wrote:
> On Tue, 2005-05-31 at 22:47 -0400, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
> > > Recent test results have shown a substantial performance improvement
> > > (+25%) if WAL logging is disabled for large COPY statements.
> >
> > How much of that is left after we fix the 64-bit-CRC issue?
>
> Well, I don't know. The I/O is the main thing I'm trying to avoid.

While avoiding IO is a good thing in general, WAL IO traffic can at
least easily made parallel to other IO by allocating own disk for WAL.

> > > Now, I would like to discuss adding an enable_logging USERSET GUC,
> >
> > [ fear and loathing ... ]
>
> OK. I needed to say the idea, to make sure we had considered it. I now
> pronounce it dead and buried.
>
> > BTW, I'm sure you are the last one who needs to be reminded that
> > any such thing breaks PITR completely.

I don't think we do any WAlling of TEMP tables, so it may be easy to
extend this to any table with 'NO_WAL' bit set.

That would create kind of 'extended temp table' - unsafe but fast

--
Hannu Krosing <hannu@skype.net>


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org