onstat -l Logging

The onstat -l command provides a two-part output. The first part contains information on the physical log buffer and the physical log. The second part contains information on the Logical Log buffer and the Logical Logs.

Logical Logging
Buffer bufused  bufsize  numrecs    numpages   numwrits   recs/pages pages/io
  L-3  0        32       1231431680 70510975   26459113   17.5       2.7
        Subsystem    numrecs    Log Space used
        OLDRSAM      1231381002 107233018932
        SBLOB        45445      39880540
        CDR          1          56
        HA           5232       230208

Buffer Waiting
Buffer  ioproc   flags
  L-2   0        0x1      0
  L-3   0        0x1      0

address          number   flags    uniqid   begin                size     used    %used
3d4468fa8        1        U-B----  3622     3:53               200000   200000   100.00
3d803e550        2        U-B----  3623     4:53               200000   200000   100.00
3d803e5b8        3        U-B----  3624     3:200053           200000   200000   100.00
.....
3d810c3d0        79       U-B----  3620     3:7800053          200000   200000   100.00
3d810c438        80       U-B----  3621     4:7800053          200000   200000   100.00
 80 active, 80 total

Physical Log Buffer

Heading Description Format See Also
Buffer Of the two physical log buffers in memory, the one that is currently in use. Str  
bufused The number of pages currently filled. Pages  
bufsize The size of the buffer in pages as defined by the PHYSBUF parameter in the configuration file. Pages  
numpages The total number of pages written to the Physical Log Buffer. Dec  
numwrits The number of times the Physical Log Buffer was written out to the Physical Log on disk. Dec onstat -g iof
pages/io The average number of pages written to the Physical Log as expressed by (numpages / numwrites). Flt onstat -g iof

Physical Log

Heading Description Format See Also
phybegin The physical address identifying the location of the Physical Log on disk. Hex  
physize The size of the Physical Log in pages as defined by the PHYSFILE parameter in the configuration file. Pages  
phypos The offset into the Physical Log in pages where the next buffer write will occur. Pages  
phyused The number of pages used in the Physical Log. Pages  
%used The percentage of the Physical Log that is full as expressed by (phyused / physize) * 100.0. Flt onstat -m

Logical Log Buffer

Heading Description Format
Buffer Of the three Logical Log buffers in memory, the one that is currently in use. Str
bufused The number of pages written to the logical log buffers since initialization (or onstat -z) Pages
bufsize The size of the buffer as identified by the LOGBUFF parameter in the configuration file. Pages
numrecs The number of logical records written to the logical log buffers since initialization (or onstat -z) Dec
numpages The number of pages used to hold the logical log records written. Dec
numwrits The number of logical records written to the logical log buffers since initialization (or onstat -z) Dec
recs/page The average number of logical log records per page. Flt
pages/io The average number of pages written to the logical log as expressed by (numpages / numwrites). Flt
Subsystem The type of log subsystem being used under the Extensible Log Manager. Potential values are:
  OLDRSAM
  RSAM
  SBLOB
  RTREE
Str
numrecs The number of records of this subsystem type that have been written to the logical logs. Dec
Log Space used The total amount of log space used by this subsystem. Bytes

Logical Logs

Column Heading Column Description Format
address The in-memory address of the log. Hex
number Logical # of the log relative to its creation. Dec
flags Describes the status of the Logical Log using the following coded values: Position 1:
  A Newly added
  F Free log
  U Used log
Position 3:
B Backed up
Position 5:
C Currently active log
Position 7:
L Contains last checkpoint record
Str
uniqid A unique number assigned to each Logical Log as it is used. Dec
begin The physical address of the log on disk. Hex
size The size of the log in pages. Pages
used The number of pages used within the log. Dec
%used The percent pages used in the log as expressed by (used / size) * 100.0. Flt

Notes

    Don't confuse the Physical Log Buffer (in memory) with the Physical Log (on disk).

    Writes are made first to the Physical Log Buffer.

    The Physical Log Buffer is then flushed to the Physical Log on disk.

    The Physical Log Buffer is flushed to disk when it hits 75%, during a checkpoint or when a flush of the

    Logical Log Buffer would place a transaction in the Logical Log without itís corresponding Physical Log image existing on disk.

    The Physical Log on disk is flushed when it hits 75% by signaling that a checkpoint is required.

    There are two Physical Log buffers and three Logical Log buffers.

    The number of Physical Log buffers and Logical Log buffers cannot be modified.

    An archive must be performed before a newly added Logical Log can become free and be used.

    You may not create more than 32768 logical logs. However, the unique ID field has a maximum value of the size of an integer.

Tuning and Montoring

    The Physical Log buffer should be tuned relative to how often you want it to flush to disk. Keep in mind that the larger it is, the more you could lose should a memory failure occur. The default is 32K and should not generally be tuned below this value.

    The Physical Log on disk should be tuned based on two items: how often checkpoints should occur and how long fast recovery should take. Checkpoints are not driven just by the value of CKPTINTVL, but when the Physical Log hits 75% full. If checkpoints are occurring too frequently (i.e., in less than CKPTINTVL intervals), then the Physical Log may be too small.

    Oversizing the Physical Log won't hurt anything, but it will waste disk space.

    The Physical Log also drives the length of Fast Recovery. When the system is shut down normally, the Physical Log is logically marked as empty. If the system comes down abnormally, pages will remain in the Physical Log. During Fast Recovery, Informix Dynamic Server restores these pages. These pages must be re-written through the buffer pool and back to disk to synchronize the DBSpaces with the transaction logs. The smaller the Physical Log, the shorter the recovery time.

    There are two items to address when sizing the Logical Logs: the size of each log and the number of logs. Since a Logical Log will not be backed up until it is full, the amount of transactional loss, should a disk failure occur, is directly affected by the size of each Logical Log. As an example, if each Logical Log is 100 Mbytes and a disk failure occurs while the third log is in use, only logs 1 and 2 will have been backed up, even if the third log is 99% full. Therefore, a single logical log should be sized to the amount of information you are willing to possibly loose should a complete disk failure occur. Keep in mind that during a restore, Informix Dynamic Server will attempt to save any logical logs that still exist and have not been backed up.

    The number of Logical Logs required is a function of how frequently you wish to back up your logs. If you wish to back up your logs only once every 8 hours, then you should have sufficient log space to allow for transactions over an 8-hour period. This must be judged by monitoring the onstat -l output over a period of normal system behavior.

    The length of transactions also affects the total size of all logs combined. If a single transaction spans more than the maximum percentage of log space allowed, given by LTXHWM (long transaction high water mark), it will be automatically rolled back. The transaction itself might not be large, however, if it is held open too long, relative to other operations, it could easily span too many logs. The default value for LTXHWM is 50(%). It is not recommended to increase this value since the rollback operation for a long transaction also consumes Logical Log space.

    The variable DYNAMIC_LOGS can be used to automatically add logs when required to avoid a long transaction. Only long transaction will trigger the addition. The new logs will be added to the dbspace that contains the last logical log, once that is full they will be created in rootdbs. Once rootdbs is full the game is over.

    If the engine is in single user mode AND a long transaction is triggered AND the default thresholds are used then the engine will NOT be able to rollback, the recommended settings are 35:45.