NOLOGGING option, or ?
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
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
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.
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
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
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
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 tableThere'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
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
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
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>
On Wed, 2005-06-01 at 16:34 +0800, Christopher Kings-Lynne wrote:
There are some other arguments in favour of a LOAD command.... Alon?
We already have LOAD, so you'll have to choose something else :)
Its annoying, I grant you. :-)
LOAD 'library' would still need to be the default.
LOAD LIBRARY 'library' would be the new recommended usage.
LOAD DATA... would be the new command... with most other options hanging
off of that. There's no problem with that, since that is then the same
as Oracle syntax for the load utility.
Best Regards, Simon Riggs
On Wed, 2005-06-01 at 04:44 -0400, Greg Stark wrote:
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.
Yes, all of the above could work.
It would use essentially the same functionality that Manfred suggested
for handling truncated tables. Ignore the first LOAD DATA started
message until recovery completes, then truncate table if the LOAD DATA
complete message was not logged in wal.
Best Regards, Simon Riggs
On K, 2005-06-01 at 00:01 +0100, 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.
Not only recover the DB itself but also having a hot standby (and
hopefully a read-only replica some time in the future).
There are two parts to this proposal. First, when and whether to do this
at all. Second, syntax and invocation.
I think this should be a decision done when creating a table, just like
TEMP tables. So you always know if a certain table is or is not
safe/replicated/recoverable.
This has also the advantage of requiring no changes to actual COPY and
INSERT commands.
--
Hannu Krosing <hannu@skype.net>
Simon Riggs wrote:
On Wed, 2005-06-01 at 04:44 -0400, Greg Stark wrote:
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.
Yes, all of the above could work.
It would use essentially the same functionality that Manfred suggested
for handling truncated tables. Ignore the first LOAD DATA started
message until recovery completes, then truncate table if the LOAD DATA
complete message was not logged in wal.
Well, why not just add this functionality to COPY rather than create a
new command? One optimization is to write the dirty shared buffers to
the kernel then fsync that relation, rather than do a checkpoint.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Greg Stark <gsstark@mit.edu> writes:
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.
Not unless you are proposing to change COPY to acquire a lock strong
enough to lock out other writers to the table for the duration ...
regards, tom lane
Simon Riggs <simon@2ndquadrant.com> writes:
On Wed, 2005-06-01 at 16:34 +0800, Christopher Kings-Lynne wrote:
There are some other arguments in favour of a LOAD command.... Alon?We already have LOAD, so you'll have to choose something else :)
Its annoying, I grant you. :-)
LOAD 'library' would still need to be the default.
LOAD LIBRARY 'library' would be the new recommended usage.
LOAD DATA... would be the new command... with most other options hanging
off of that. There's no problem with that, since that is then the same
as Oracle syntax for the load utility.
Uh, what's wrong with adding an option to COPY? Not like it hasn't got
a ton of 'em already. The Oracle-compatibility angle doesn't interest
me at all, mainly because I find it highly improbable that we'd be exactly
compatible anyway.
regards, tom lane
On 01 Jun 2005 04:44:24 -0400, Greg Stark wrote:
Greg Stark 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.
Checkpoint or fsync?
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.
Why only on an empty table? What is the problem with bypassing WAL on
any table as long as all files of that table are fsync'ed before
commit?
Again this sadly only works in the non-PITR case.
Apart from that problem of course :)
Jochem
Tom Lane wrote:
Greg Stark <gsstark@mit.edu> writes:
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.Not unless you are proposing to change COPY to acquire a lock strong
enough to lock out other writers to the table for the duration ...
Well, if the table is initally empty, what harm is there in locking the
table? How many people query the table while it is being loaded, and
because the transaction isn't committed, the table is empty to everyone
else anyway.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Jochem van Dieten wrote:
On 01 Jun 2005 04:44:24 -0400, Greg Stark wrote:
Greg Stark 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.Checkpoint or fsync?
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.Why only on an empty table? What is the problem with bypassing WAL on
any table as long as all files of that table are fsync'ed before
commit?
Because adding rows to a table might modify existing pages, and if the
COPY fails, you have to restore those pages to a consistent state, and
make sure they are recovered for partial page writes, which we can't do
without WAL. With an initially empty table, you can just throw away the
file system file.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Tom Lane wrote:
Not unless you are proposing to change COPY to acquire a lock strong
enough to lock out other writers to the table for the duration ...
Well, if the table is initally empty, what harm is there in locking the
table?
You cannot *know* whether it is empty unless you lock the table before
you look. So your argument is circular.
I think this only makes sense as an explicit option to COPY, one of the
effects of which would be to take a stronger lock than COPY normally does.
regards, tom lane
