Bereitgestellt von Rolf Loeben, IBM Deutschland GmbH:

Turn Logging off?

Created By:    Mike J Bracey on 22.03.2001 at 09:57 (Taken from V7 Announcement material)

My first question for people who want to turn off the log is, "Why?". Another way of phrasing the question is to tell me the objective, rather than a specific technique.  If the  objective is to save space on the log and archiving, there are a number of techniques for doing that.  One example is compressing the archive log with a tool from IBM or another  vendor.  If the objective is to improve overall performance or elapsed time by 10%, turning off logging generally cannot do that.

DB2 for OS/390 has options to turn off logging when the lack of logging doesn't compromise data and subsystem integrity. They include

• LOAD utility:  LOG NO does not turn off all logging, but does avoid logging the data, and ensures a good commit frequency.  If the volume to be inserted is very high, then using the LOAD utility is what I'd suggest.
• Work files:    No logging is needed. There are no recovery or restart issues.
• Global temporary tables  - These tables have no logging, recovery or restart problems. The global temportay tables are empty at each new unit of work.
• Declared temporary tables - These tables can have indexes and updates with rollback, so undo logging is done (but not the data for inserts with redo logging).  These tables are empty when the thread terminates, so recovery is not an issue.
• LOBS: LOG NO is an applicable option, while we have the information required for restart and recovery in other places.  If you are storing images and video, this is probably the technique you will use.

Beyond the current capabilities, there are a number of requirements, that ask about turning off logging.  As I understand this area, there are a number of differing objectives.  Some of the objectives will not be met by turning off logging.

The key question is how you expect to handle your COMMIT, ROLLBACK, transaction atomicity, statement atomicity, checkpoint, restart, and any error situations.  Without a log, we lose some of the ACID (atomicity, consistency, isolation and durability) properties that make us a database. Maybe that's really what you are looking for - a flat file with a query language and little ability to share data for multiple users. There are different implementations and issues that depend upon the answers.

There are some major downside risks and changes for turning off logging. Some are similar to cancel with no rollback.  If anything goes wrong, goes wrong, goes wrong, ...  your data is trash.  Drop the tables and start over. DB2 uses the log for statement atomicity, index and data matching, referential integrity errors, and several other purposes.  There is a substantial amount of code to be reworked or the function will have restrictions.  The biggest performance problem with not logging is that we'll need to write the data more often and force all of the data and indexes to disk, waiting at commit. This is not likely to be an improvement for concurrency or transactions.

The performance of some DBMSs is significantly better if logging is turned off.  The DB2 high volume log writes are done asynchronously.  For transactions with high commit rates, DB2 can use one set of log writes for many transactions. DB2 IO optimization generally means that the improvement for avoiding logging, even for an image application, is 5% or less.  If there are a few megabytes to insert, the log writes take a fraction of a second.

There are situations where the log can become a performance constraint. The DB2 logging rate is above 100 GB per day for some customers. Benchmarks with the Enterprise Storage Server and dual logs  were measured at about 8 MB per second or about 28 GB per hour.   That's for a single member of a data sharing group, so the logging rate for a group is several times larger.  VSAM striping can be used on each member to improve the logging rates now.  Striping should be able to improve the logging rate by another factor.

My guess is that we'll implement a no log option some day, and customers will discover that this function is like row level locking, useful for 1% to 4% of the situations, and very helpful  there.  Then we'll have lots of customers coming back to say that we gave them what they asked for, but not what they want.