Logging Modes

The OnLine engine provides for three modes of logging operation. The selection of logging mode is important to the issue of data recovery in the event of a system crash.

Until the contents of the logical log buffer are flushed to disk, the transaction log records that the buffer holds are at risk should the system go down. If the system crashes with data in the logical log buffers, any changes to the database held in the buffer will not be recoverable. The three logging modes represent increasing degrees of data survivability.

No Logging

If the database is created with no logging, there is still a small amount of activity directed towards the logical logs and logical log buffers. Any statement that uses data definition language (DDL) statements such as CREATE TABLE or CREATE INDEX will result in entries into the logical logs, as will some internal activities of the engine.

In fast recovery of a database that is not logged, only the physical log records are read back into the system during fast recovery. After these pages are read back into shared memory, the system does an automatic checkpoint as part of the fast recovery process. This brings the database into consistency as of the last checkpoint prior to the crash. In databases that use logging, this checkpoint is followed by rolling forward the transaction data from the time of the last checkpoint, followed by rolling back transactions that have not been committed. These final two steps do not occur in the case of a database created with no logging. The effect of this is that without the use of logging, you can never recover any further than your last checkpoint. Work that occurred between the last checkpoint and the crash will be lost.

In IDS systems, it is possible to specify the clause, "WITH NO LOG" in a table creation DDL statement. In this case, the table is treated as though it were in a no logging database regardless of the logging status of the overall database.

Non-logging databases are useful in cases where the data flow is such that transactions can be recreated in the event of a crash. Often, batch operations against a non-logging database will meet this criteria. Also, non-logging databases are useful for the initial loading of database rows from other sources. Loading tables into a non-logging database will save the overhead of logging and will make the loads go much faster.

Changing of logging modes can occur only when the engine is quiescent. Also, any change from non-logging to any form of logging requires completing an immediate archive. A tb/ontape archive to /dev/null is a useful tool here.

Buffered Logging

If a database is created with buffered logging, the contents of the logical log buffer are flushed to the disk logfiles only when the buffer is full. This results in more efficient database operations because expensive disk I/O operations are avoided.

Buffered logging also results in more efficient use of logical logfile space on disk. Writes from the logical log buffer to the logical logs on disk occur in page units. Whether there is a full page of transaction entries or only one entry occupying just a small part of a page, the entire page gets written to the disk file. Since no writes occur to the disk logfile until the buffer is full, the logfiles on disk will hold a higher percentage of useful information. The logfile data on disk will thus be more efficiently packed than with unbuffered logging.

All databases in an instance share the same buffers and disk logfiles. Thus, if some of your databases use buffered logging and some use nonbuffered logging, the nonbuffered databases could still force high I/O rates to the disks. When a nonbuffered database completes a transaction, it will force the buffer to flush whether or not the buffer is full. The other databases will just sort of be dragged along.

The cost of buffered logging is the risk of losing data in the case of a crash. Although not as costly as crashing a non-logging database, crashing a buffered-logging database will result in loss of any transactions in the buffer that have not been flushed to disk. Although not as bad as losing all data changed since the last checkpoint, this data loss can still be costly. It's up to the DBA to make the judgment call as to whether the improved performance is worth the risk of data loss.

Unbuffered Logging

In a database created with unbuffered logging, the logical log buffer flushes every time a transaction completes. Note that this includes jobs that are included in a BEGIN WORK/COMMIT WORK section as well as singleton transactions that are implicitly considered as transactions. A singleton transaction is any SQL statement that inserts or modifies data. It either completes or fails. It cannot partially complete. You can count on a log flush every time a loggable SQL transaction completes. Even with unbuffered logging, there is some data still at risk. If a crash occurs and leaves uncommitted data in the logical log buffer since the last transaction committed, this data may be lost. There are some pathological instances involving a crash between the START COMMIT record and the COMMIT COMPLETE record that may require that Informix Support call into the system and manually remove the orphaned START COMMIT record.

There is one drawback to using unbuffered logging. Since the logical log buffers flush with essentially every transaction, the buffers often contain only a small amount of data when they flush. Since the flushing occurs in page increments, the empty space is written to the logfiles, causing the logfiles to fill up more rapidly. This can happen if you have only one database set up for unbuffered logging, as every time it flushes, it will flush for all the databases. Log flushing is for the entire instance, not just for particular databases.

Unbuffered logging offers the ultimate in data survivability, at a cost of disk I/O and some bloating of your logfiles. Again, this requires a judgment call by the DBA.