On IDS version 11.10 a significant amount of focus has been given to automatically keep administrators up-to-date on database performance. Several performance advisory triggers added to Informix server to flag administrator once a threshold reached. The IDS will automatically calculate different thresholds based on current database activities and generate an appropriate performance advisory in message log (MSGPATH).
Following is a list of performance advisory available in IDS version 11.10:
- Physical log too small for RTO_SERVER_RESTART
- IDS server boot time too long
- Physical log too small
- Logical log too small
- Long transactions blocking checkpoints
- Physical log too small to accommodate bufferpool flushing
- Logical log too small to accommodate bufferpool flushing
- Physical log too small to accommodate automatic checkpoints
- Logical log too small to accommodate automatic checkpoints
- Bufferpool flushing
In this article we will discuss about the checkpoint related advisory. The ‘onstat -g ckp’ command has introduced with IDS version 11.10 to get checkpoint information and checkpoint related advisory. This command will display checkpoint related configuration parameters recommendation, once a sub-optimal configuration detected.
Following configuration parameters can significantly impact performance of IDS:
During checkpoint IDS will evaluate checkpoint related configuration parameters and produce a performance advisory once a sub-optimal configuration detected. If configuration parameters is not tune properly, database server can encounter block during checkpoint.
Following is an example of ‘onstat -g ckp’ command output:
The description of ‘onstat -g ckp’ command output:
- The first section of the display describes checkpoint information
- The second section of ‘onstat -g ckp’ output describe the checkpoint related advisory
In the above example physical log file size (PHYSFILE) was set to 15000 Kbytes. During checkpoint IDS evaluate the configuration parameters and found out physical log size was small, which can cause checkpoint block, and suggested PHYSFILE value as 16800 Kbytes.
The checkpoint performance advisory also available in the message log file. Following is an example of message log with a checkpoint performance advisory: