Fuzzy Checkpoint

In a fuzzy checkpoint, the database server does not flush the modified pages in the shared-memory buffer pool to disk for certain types of operations, called fuzzy operations. When a fuzzy checkpoint completes, the pages might not be consistent with each other, because the database server does not flush all data pages to disk. A fuzzy checkpoint completes much more quickly than a full checkpoint and reduces the amount of physical logging during heavy update activity. When necessary, the database server performs a full checkpoint to ensure the physical consistency of all data on disk.

Fuzzy Operations

The following commonly used operations are fuzzy for built-in data types:

• Inserts
• Updates
• Deletes

The following operations are nonfuzzy:

• Inserts, updates, and deletes for rows that contain user-defined data types, smart large objects (CLOB and BLOB data types), or simple large objects (TEXT and Byte data types)
• Table alters and loads
• Operations that create or modify indexes (B-tree, R-tree, or user-defined indexes)

The database server flushes all the modified data pages for nonfuzzy operations to disk during a fuzzy checkpoint in the same way as for a full checkpoint.

Write-Ahead Logging and Fast Recovery

Fuzzy checkpoint uses write-ahead logging for fast recovery. Write-ahead logging means that the logical-log records representing changes to fuzzy data must be on disk before the changed data replaces the previous version of the data on disk. Fast recovery begins with the oldest update not yet flushed to disk rather than with the previous checkpoint.

Fuzzy checkpoints result in slightly longer roll-forward fast-recovery times. The database server occasionally performs a full checkpoint to prevent loss of old logical-log records.

Fuzzy Checkpoints Improve Performance

Fuzzy checkpoints are much faster than full checkpoints and improve transaction throughput. Because the database server does not log fuzzy operations in the physical log, the physical log does not fill as quickly, and checkpoints occur less often. For example, if you are inserting and updating a lot of data, checkpoints occur less frequently and are shorter.

The database server skips a full checkpoint if all data is physically consistent when the checkpoint interval expires. It skips a fuzzy checkpoint only if no pages have been dirtied since the last checkpoint.

To improve transaction throughput, use the BUFFERPOOL configuration parameter to increase the lru_max_dirty and lru_max_dirty values. However, do not change the gap between the lru_max_dirty and lru_max_dirty values.

For information on improving checkpoint performance, see the chapter about configuration impacts on I/O in your IBM Informix Dynamic Server Performance Guide.