Checkpoints

Periodically, the database server flushes transactions and data
within the buffer pool to disk. Until the transactions and data are
flushed to disk, the data and transactions are in a state of flux.
Instead of forcing every transaction to disk immediately after a transaction
is completed, the database server writes transactions to the logical
log. The database server logs the transactions as they occur. In the
event of a system failure, the server:

Replays the log to redo and restore the transactions.

Returns the database to a state consistent with the state of the
database system at the time of the failure.

To facilitate the restoration or logical recovery of a database
system, the database server generates a consistency point, called
a checkpoint. A checkpoint is a point in time in the
log when a known and consistent state for the database system is established.
Typically, a checkpoint involves recording a certain amount of information
so that, if a failure occurs, the database server can restart at that
established point.

The purpose of a checkpoint is to periodically move the restart
point forward in the logical log. If checkpoints did not exist and
a failure occurred, the database server would be required to process
all the transactions that were recorded in the logical log since the
system restarted.

A checkpoint can occur in one of these situations:

When specific events occur. For example, a checkpoint occurs whenever
a dbspace is added to the server or a database backup is performed.

Typically,
these types of events trigger checkpoints that block transaction processing.
Therefore, these checkpoints are called blocking checkpoints.

When resource limitations occur. For example, a checkpoint is
required for each span of the logical log space to guarantee that
the log has a checkpoint at which to begin fast recovery. The database
server triggers a checkpoint when the physical log is 75 percent full
to avoid physical log overflow.

Checkpoints triggered by resource
limitations usually do not block transactions. Therefore, these checkpoints
are called nonblocking checkpoints.

However, if
the database server begins to run out of resources during checkpoint
processing, transaction blocking occurs in the midst of checkpoint
processing to make sure that the checkpoint completes before a resource
is depleted. If transactions are blocked, the server attempts to trigger
checkpoints more frequently to avoid transaction blocking during checkpoint
processing. For more information, see Strategy for estimating the size of the physical log.

If failover occurs, and the secondary server becomes the primary
server, checkpoint discrepancies between the two servers can affect
re-connection attempts. If a checkpoint on the new secondary server
does not exist on the new primary server, attempts to connect the
secondary server to the primary server fail. The secondary server
must be fully restored before it can connect to the primary server.

Automatic checkpoints cause the database server to
trigger more frequent checkpoints to avoid transaction blocking. Automatic
checkpoints attempt to monitor system activity and resource usage
(physical and logical log usage along with how dirty the buffer pools
are) to trigger checkpoints in a timely manner so that the processing
of the checkpoint can complete before the physical or logical log
is depleted. The database server generates at least one automatic
checkpoint for each span of the logical-log space. This guarantees
the existence of a checkpoint where fast recovery can begin. Use the
AUTO_CKPTS configuration parameter to enable or disable automatic
checkpoints when the database server starts. (You can dynamically
enable or disable automatic checkpoints by using onmode -wm or onmode
-wf.)

Tip:

If automatic checkpoints are triggered too frequently because
of physical log activity, you can increase the physical log size or use a plogspace to automatically tune the physical
log resources.

If automatic checkpoints are triggered too frequently because
of logical log activity, you can set the AUTO_LLOG parameter in the onconfig file
to allow the server to automatically increase the logical log space
to reduce checkpoint frequency.

Manual checkpoints are event-based checkpoints that
you can initiate. The database server provides two methods for determining
how long fast recovery takes in the event of an unplanned outage.

Use the CKPTINTVL configuration parameter to specify how frequently
the server triggers checkpoints.

Use the RTO_SERVER_RESTART configuration parameter to specify
how much time fast recovery takes.

When you use the RTO_SERVER_RESTART
configuration parameter:

The database server ignores the CKPTINTVL configuration parameter.

The database server monitors the physical and logical log usage
to estimate the duration of fast recovery. If the server estimates
that fast recovery exceeds the time specified in the RTO_SERVER_RESTART
configuration parameter, the server automatically triggers a checkpoint.

The RTO_SERVER_RESTART configuration parameter is intended to
be a target amount of time, not a guaranteed amount of time. Several
factors that can increase restart time can also influence fast recovery
time. These factors include rolling back long transactions that were
active at the time of an unplanned outage. For more information about
the RTO_SERVER_RESTART and AUTO_CKPTS configuration parameters, see
the topics on configuration parameters in the IBM®
Informix® Administrator's Reference.