Physical and Logical Logs

The physical and logical logs are used by the engine in three major situations:

Transaction Rollback

If a SQL command or a series of SQL commands is executed within a BEGIN WORK/COMMIT WORK structure, the engine will not commit any of the transactions until it receives the OK to "commit work." If something fails, either in a SQL statement or for outside reasons, the engine uses the information in the logical logfiles to roll back the transaction. This brings the database back to a consistent state as though the transaction had never begun.

Restoring from Tape

In the event that the data is completely lost as the result of mechanical or human error and it is necessary to recover the entire system from tape, the tb/ontape program first loads the data from the last archive. This is either one tape from a Level 0 archive, or a combination of Level 0, Level 1, and Level 2 archive tapes. After the last archive tape is loaded, the engine then applies data contained in the logfiles that have been saved to tape. This brings the database up to its condition before the crash. After all of the logged data is back in the system, any transactions that were not yet committed at the time of the crash are rolled back to ensure the consistency of the instance. If the instance was using buffered logging, it is possible that some of the last transaction data will be lost due to the loss of data in the buffer.

Fast Recovery

Most of the time when the system goes down, it is not necessary to recover from tape. Informix has a robust recovery process that is part of its shared memory initialization process. When the shared memory is initialized upon restart after a crash, the engine goes into fast recovery mode, which is automatic and not under control of the DBA. In fast recovery, the instance is first brought back into a known good status as evidenced by the condition at the last checkpoint by restoring data from the physical log. After this known status is attained, the engine then goes to the logical logfiles and finds the location of the last checkpoint. From that point onward, the engine applies all of the transactions that it finds in the logical logfiles. Finally, it rolls back any changes that were made inside a transaction and for which it finds no COMMIT WORK entry in the log.

When the system is halted properly, the physical logfile is flushed to disk as part of a final checkpoint that occurs when the system leaves online status and becomes quiescent. When the system crashes, the physical logfile is left with data in it. This is what tells the tb/oninit process that the physical recovery is necessary.

Often the fast recovery process is not fast at all. The speed of the fast recovery depends upon many factors, including the length of time from the last checkpoint and the amount of work that has been done since then. This time can often be estimated by checking the number of active logfiles. Whenever you are forced into a fast recovery, it helps to note the number of logs that it has to go through and to note the amount of time it takes to recover. Whenever your system crashes in real life, one of the most pressing questions you'll receive is "How long until the database is up?" Any time you find yourself waiting through a fast recovery, note how many logfiles are involved and time the recovery. Developing such a rule of thumb based on logfiles is better than just answering with an "I dunno."

The IDS engine series provides two entries in the $ONCONFIG file that affect fast recoveries and restores. The OFF_RECVRY_THREADS parameter defines the number of virtual processors that are started when the system is in an off-line mode and goes into recovery after a restart. Due to the parallel nature of IDS, the engine is capable of running its recovery in multiple virtual processes and multiple threads. The ON RECVRY THREADS parameter governs the number of recovery threads that are started when the system goes into online mode. If you are experiencing long recovery times after a crash, and if you have sufficient CPU resources, you can try experimenting with these two parameters. Generally the system defaults are adequate, but a system that does heavy transaction processing could possibly benefit from increasing the number of recovery threads.