Monitoring Checkpoints (Informix) (SAP Library

Monitoring Checkpoints (Informix)

Use

You can use the SAP/Informix database monitor to monitor Informix checkpoints.

Prerequisites

When tuning checkpoints, remember that:

The most efficient writes, called "chunk writes," occur at checkpoint.
Other threads effectively stop working during a checkpoint.

You can use the following approaches:

Approach	Advantage	Disadvantage
Save modified pages to be written at checkpoint	Chunk writes are the most efficient	The checkpoint might last several minutes if you have a large volume of modified data. User response times might significantly deteriorate during the checkpoint.
Write modified pages steadily between checkpoints	Steady performance for the user with no deterioration at checkpoint	The writes between checkpoints are less efficient

Procedure

Choose Tools ® CCMS ® Control/Monitoring ® Performance Menu ® Database ® Activity ® Detail Analysis Menu ® Wait Situations.

You can now see how threads in your system are spending their time. If you see waits on checkpoints, you might need to tune the length of checkpoints in your system.

Choose Tools ® CCMS ® Control/Monitoring ® Performance Menu ® Database ® Activity ® Detail Analysis Menu ® State on disk ® Database Message Log.

You can now see the length of the checkpoints. If there are no very long waits on checkpoints and the checkpoint duration is below 20 to 30 seconds, then you probably do not need to tune checkpoints.

If you need to tune checkpoints, consider the following:

Long checkpoints could be caused by an I/O bottleneck. If this is the case, you can tune your asynchronous I/O virtual processors (AIO VPs) or better distribute data and log dbspaces on disk. Refer to Monitoring the I/O Subsystem (Informix).

Long checkpoints can be caused by there being too much data to write at checkpoint time. In this case, reduce the number of buffers that need cleaning, by reducing the value of the parameters LRU_MAX_DIRTY and LRU_MIN_DIRTY in the Informix ONCONFIG file.

The second measure outlined above – that is, reducing LRU_MAX_DIRTY and LRU_MIN_DIRTY – is the standard measure for reducing checkpoint duration.

The SAP defaults for these parameters are 20 and 10 respectively, and it is not uncommon for large systems to have values as low as 5 and 2. Therefore, in a standard SAP System, pages start being written to disk when 20% are dirty and stop being written when only 10% are dirty. When LRU_MAX_DIRTY is set to 5% on a large highly active system, modified pages are written more or less continuously by the AIO VPs.

See also:

CKPTINTVL (Informix)

LRU_MAX_DIRTY, LRU_MIN_DIRTY (Informix)

PHYSFILE (Informix)