Chapter 3. Understanding Logging

Both of the Informix engines keep track of changes to its data by the use of logfiles. There are two types of logfiles, physical and logical. Each logfile is accessed through buffers in the shared memory. You need to make the distinction between the log buffers, which are areas of shared memory and the logfiles, which are areas of the disk that receive data from the buffers. The shared memory is by definition transitory. If the system goes down, this data is lost. The logfiles on disk are persistent. Assuming there are no hardware problems with the disk, their contents survive system crashes.

The essential point of difference between the physical and logical logs is really indicated by their names. The physical logs are simply images of the pages as they come in from the disk system. They are essentially reference points to bring the system back to a known state. The logical logs represent a chronological record of data changes that occur within the database. Should the engine receive a ROLLBACK WORK command, it is able to go to the logical logs and undo any transactions that are affected by the ROLLBACK WORK command.

Whenever a data page is needed by a process, the engine first looks in its shared memory. If the data page is not found in shared memory, it is pulled in from the disk into a page buffer in shared memory. As soon as any data on this page is scheduled for modification, the page is copied into the physical log buffer. This occurs only on the first update to the particular pages. This before image data in the physical log buffer is then flushed out to the physical log on disk. This flushing of the physical log buffer to the physical log on disk occurs when the buffer is full, during checkpoints, or when a page in both the shared memory buffers and the physical log buffer is flushed to disk.

The physical log buffer is actually a pair of buffers in shared memory, each of which is the size indicated in the tb/onconfig file. This "double buffering" allows the system to switch between the first and second buffers so that no time or data is lost while the other is flushing to the physical log on disk. These buffers are identified as P-l and P-2 in the tb/onstat -1 output.

Likewise, the logical log buffer is actually three separate buffers in shared memory, each the size indicated in the tb/onconfig file. This "triple buffering" allows one buffer to flush while one of the others fills, and provides an additional buffer in case the active buffer fills up before the first buffer is completely flushed.

Flushing the physical log buffer to the physical log on disk occurs in three separate circumstances. In the first instance, the physical log buffer becomes full. When this occurs, the system switches to the other physical log buffer and flushes the full buffer to disk.

The second instance is somewhat more subtle. This flush must occur before any page cleaner activity flushes pages in the page cache buffer back to the disk. Here, it is possible that a page in the page cache buffer pool has been modified, causing its before image to be written to the physical log buffer. If a page cleaner fires up in this case, it is possible for the modified page to be on disk while the original before image is still in the physical log buffer. This gives rise to the rule that flushing a before image of a page to disk must occur before the actual modified page can be flushed to disk.

The third instance in which the physical log buffer is flushed is at a checkpoint. The first step of a checkpoint calls for this flushing of the buffer.

When any data is changed in the shared memory page buffer pool, the engine checks to see whether or not anything needs to be logged to the logical logfile. In addition to changes that are either specifically or implicitly part of a transaction, the engine also logs SQL data definition language (DDL) statements, changes to the database configuration such as changes in chunks, dbspaces, and blobspaces, and any checkpoint events. All of these loggable events are first placed into the logical log buffer and then flushed back to disk.