Monitor physical and logical-logging activity

Monitor the physical log to determine the percentage of the physical-log file that gets used before a checkpoint occurs. You can use this information to find the optimal size of the physical-log file. It must be large enough that the database server is not required to force checkpoints too frequently and small enough to conserve disk space and guarantee fast recovery.

Monitor physical-log and logical-log buffers to determine if they are the optimal size for the current level of processing. The important statistic to monitor is the pages-per-disk-write statistic. For more information about tuning the physical-log and logical-log buffers, see your Informix® Performance Guide.

To monitor the physical-log file, physical-log buffers, and logical-log buffers, use the following commands.

Utility Command Additional information
Command line onstat -l The first line displays the following information for each physical-log buffer:
  • The number of buffer pages used (bufused)
  • The size of each physical log buffer in pages (bufsize)
  • The number of pages written to the buffer (numpages)
  • The number of writes from the buffer to disk (numwrits)
  • The ratio of pages written to the buffer to the number of writes to disk (pages/IO)
The second line displays the following information about the physical log:
  • The page number of the first page in the physical-log file (phybegin)
  • The size of the physical-log file in pages (physize)
  • The current position in the log where the next write occurs, specified as a page number (physpos)
  • The number of pages in the log that have been used (phyused)
  • The percentage of the total physical-log pages that have been used (%used)
The third line displays the following information about each logical-log buffer:
  • The number of buffer pages used (bufused)
  • The size of each logical-log buffer in pages (bufsize)
  • The number of records written to the buffer (numrecs)
  • The number of pages written to the buffer (numpages)
  • The number of writes from the buffer to disk (numwrits)
  • The ratio of records to pages in the buffer (recs/pages)
  • The ratio of pages written to the buffer to the number of writes to disk (pages/IO)
Command line onparams -p Moves or resizes the physical log.
Command line onmode -l Advances to the next logical-log file.

For more information about and an example of onstat -l output, see the Informix® Administrator's Reference.

For information about using SQL administration API commands instead of some onparams and onmode commands, see Remote administration with the SQL administration API and the Informix® Guide to SQL: Syntax.