Logstat Command Output – Logging System Summary
The Logging System Summary on the logstat output provides an overall view of the logging system, indicating how well the logging system is tuned.
The summary contains the following informational fields (described in the order in which they appear):
Database add
Indicates the number of times a database has been added to the logging system. This number is incremented whenever a session is the first session to access the database.
Database removes
Indicates the number of times a database has been removed from the logging system. This number is decremented when the last session accessing the database exits.
Transaction begins
Indicates the number of times a transaction has been started in the logging system. This number indicates the total number of transactions started in the logging system.
Transaction ends
Indicates the number of times a transaction has been completed in the system during normal processing. This number corresponds to all the transactions that were committed or rolled back without incident and properly terminated. This value does not include FORCE-ABORT, LOGFULL, or any other rcp actions that terminated a transaction abnormally.
Log read i/o’s
Indicates the number of times a read was performed on the log file. This is a physical operation. This number corresponds to the number of times the log buffer was read from the transaction log file to perform an abort, archive or purge operation.
Log write i/o’s
Indicates the number of times a write was performed on the log file. This is a physical operation. This number corresponds to the number of times a log buffer was written to the transaction log file.
Log writes
Indicates the number of writes from the database buffer into the log buffers. These are memory-to-memory writes.
Log forces
Indicates the number of requests made to the logging system to force the current log buffers to the log file. This is most frequently done to commit a transaction or to guarantee the consistency of the log file before writing an update to the database.
Log optimized writes
Indicates the number of times the logging system was able to combine multiple log pages in a single physical write. Optimized writes are enabled by the CBF parameter “ii.*.rcp.log.optimize_writes: ON”.
Log optimized pages
Indicates the number of pages written by log optimized writes.
Log waits
Indicates the number of times any event wait condition requires a log buffer write to stall. These are events such as LOGFULL, CP writing, RECOVERY, ARCHIVING required, FREE WAIT for log buffers, OPENDB wait, log buffer SPLIT wait, wait for completion of log i/o (that is, from the log buffer to the log file). "Log waits" is the sum of "log waits by type."
Log splits
Indicates the number of log records that spanned two or more buffers. Log splits in and of themselves are not to be interpreted as bad events. What is potentially harmful is the inability of the logging system to proceed with the log record split. This situation can be remedied by adding additional log buffers to the system or by increasing the size of the current buffers to minimize the need for splits. Any modification should be examined in conjunction with the effect that it has on the other wait states.
Log group commit
Indicates the number of times that multiple transactions participate in a log buffer flush to the log file.
Log group count
Indicates the number of transactions that are participating in the flush to the log file. This value is the wait count associated with the Log group commit count above. This value is incremented based on the number of waiters at write completion time.
The ratio of Log group count to Log group commit gives an indication of how effectively the group commit mechanism is working in the current configuration.
Check commit timer
Indicates the number of times the timer associated with the group commits completes. This does not necessarily mean that a write to the log file has to occur, because the log buffer that initiated the timer may have already been written due to being full.
Timer write
Indicates the number of times a log file write actually occurs because of the timer expiration or inactivity in the log buffer. As explained above, this will occur only if the buffer has not completely filled before this timer expires.
Timer write, time
Indicates the number of times those writes were due to the expiration of the group commit timer.
Timer write, idle
Indicates the number of times those writes were due to inactivity in the buffer.
Inconsistent db
Indicates the number of times the logging system has had to mark a database inconsistent due to an inability to recover some portion of work that currently exists in the logging system.
Kbytes written
Indicates the number of bytes written to the log file
ii_log_file read
Indicates the number of physical reads of the primary log file
ii_dual_log read
Indicates the number of physical reads of the dual log file
Write complete
Indicates the number of times a write of the primary log file completes successfully
Dual write complete
Indicates the number of times a file write of the dual log file completes successfully
All logwriters busy
Indicates the number of times that a log buffer needed to be written but no idle logwriter in any DBMS server could be found to write it. This causes a log wait until a logwriter thread is available.
The statistics All logwriters busy, Max write queue len, and Max write queue cnt represent activity of the log writers.
The “All logwriters busy” count should be a small fraction of overall log waits (10% or less). A high count indicates a lack of logwriter threads relative to the number of log buffers. If increasing the number of logwriter threads does not help, look for a disk or controller bottleneck writing to the transaction log.
Max write queue len
Indicates the maximum number of log buffers queued for a physical log write.
The “Max write queue len” value is usually equal to the number of log buffers, unless the system is configured with more log buffers than necessary.
Max write queue cnt
Indicates the number of times the log write queue length reached “Max write queue len.”
The “Max write queue cnt” value indicates how busy the logging system is. A high value in conjunction with significant numbers of log split waits or log free waits may indicate a lack of log buffers, or a physical disk I/O problem with the transaction log.
Log Waits By Type
Shows log wait totals categorized by the type of event that caused the wait:
Force
Indicates the number of times a thread was stalled waiting for a log force operation to complete.
Free Buffer
Indicates the number of times all the log buffers are either in force mode or unavailable for writing. One log buffer is written to at a time. If free buffer waits are frequent, then increasing the number of log buffers may be the solution. Remember that an increase in the number of buffers requires (number_of_log_buffers * log_buffer_size) requires more memory on the host system.
Split Buffer
Indicates the number of times a thread was stalled waiting for a log split operation to complete.
Log Header I/O
Indicates the number of times a thread was stalled waiting for a log header I/O operation to complete.
Ckpdb Stall
Indicates the number of times a thread was stalled waiting for a ckpdb (checkpoint a database) operation to complete.
Opendb
Indicates the number of times a thread was stalled waiting for an open database operation to complete.
BCP Stall
Indicates the number of times a thread was stalled waiting for Begin Consistency Point information to be written to disk. This is a very brief stall performed at the start of a consistency point.
Logfull Stall
Indicates the number of times a thread was stalled waiting for a LOGFULL condition to clear. All logging system writes are stalled for users until the condition is cleared. This is most often seen from the user’s viewpoint as a “hung” system. Always check logstat for the status in the header block. If this value is LOGFULL then this is a stall condition.
Lastbuf
Indicates the number of times a thread was stalled waiting for its COMMIT record to be written.
Forced I/O
Indicates the number of times a thread was stalled waiting for a forced I/O operation to complete.
Event
Indicates the number of waits for internal non-I/O logging operations.
Mini transaction
Indicates the number of waits for internal transactions to complete.
Logfull Commit
Indicates the number of times a thread was stalled in conjunction with SET SESSION WITH ON_LOGFULL = COMMIT protocols.