|
|
|
Permission required: Installation owner.
The logstat utility displays logging status. Logstat output has the following sections:
Note: Logstat functions are included in the forms-based Interactive Performance Monitor (ipm) utility. Also, you can use Visual DBA to monitor log information. See Visual DBA online help.
For more information on the information displayed by the logstat command, see the Interactive Performance Monitor User Guide.
The logstat command has the following format:
logstat [-help]
Displays command options online.
|
|
|
The Logging System Summary on the logstat output provides an overall view of the logging system. The information indicates how well the logging system is tuned.
The summary contains the following informational fields (described in the order in which they appear):
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.
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.
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.
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.
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.
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.
Indicates the number of writes from the database buffer into the log buffers. These are memory-to-memory writes.
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.
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).
Indicates the number of times a log split operation is delayed due to no free log buffers on the free queue. 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.
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 log free waits is 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) more memory on the host system.
Indicates the number of times any writes to the log buffers are stalled due to either CP events (consistency point writing) or LOGFULL events occur. All logging system writes are stalled for users until these conditions are 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.
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.
Indicates the number of times a thread was stalled waiting for a LOGFULL condition to clear.
Indicates the number of times that multiple transactions participate in a log buffer flush to the log file.
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.
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.
Indicates the number of times a log file write actually occurs because of the timer expiration. As explained above, this will occur only if the buffer has not completely filled before this timer expires.
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.
Indicates the number of bytes written to the log file
Indicates the number of physical reads of the primary log file
Indicates the number of physical reads of the dual log file
Indicates the number of times a write of the primary log file completes successfully
Indicates the number of times a file write of the dual log file completes successfully
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.
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.
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.
|
|
|
The Current Log File Header gives quantitative information on the logging system, such as the size of the log file, log buffers, and CP interval.
Consistency Points (CP)—In the Current log file header section is a group of numbers preceded by the label CP. These numbers, like the numbers following Begin and End, are in three groups. The middle group refers to the block marking the last consistency point. This consistency point contains a list of all open transactions and open databases at that time.
In the sample output shown here, the block marking the consistency point is 1873. CPs shorten the recovery window after a system goes down. Instead of reading from BOF to EOF, the last CP is read and recovery begins from there.
----Current log file header----------------------------------------------------
Block size: 4096 Block count: 2048 Buffer count: 4
CP interval: 102 Logfull interval: 1945 Abort interval: 1536
Last Transaction Id: 00002D5B2D5BFA03
Begin: <760996814:1702:2304> CP: <760996814:1873:3592> End: <760996815:107:20>
Percentage of log file in use or reserved: 30
Log file blocks reserved by recovery system: 180
Archive Window: <760996814,1991,3508>..<760996815,107,220>
Previous CP: <760996814,1702,2304>
Status: ONLINE,ARCHIVE,CPFLUSH
Active Log(s): LOG_FILE
The Current Log File Header section has the following fields:
Indicates the size of the log buffer and log file blocks in bytes. The log file is organized as a series of blocks that are laid down in a circular fashion and used for on line backup.
Indicates the size of the log file in blocks
Indicates the number of log file buffers. All processes connected to the logging system share the buffers.
Indicates the number of blocks between consistency points. CPs may also be caused by other events, such as archiver PURGEs and online checkpoints.
Indicates the number of log file blocks used before LOG_FULL is signaled
Indicates the number of log file blocks that must be used before a FORCE_ABORT is signaled
Indicates the ID of the last transaction to write a log record
Indicates the log addresses of the beginning of the log file, the last consistency point, and the end of the log file
Indicates the percentage of the log file that has either been used or is reserved for use by the recovery system
Indicates the number of log file blocks reserved for transaction recovery operations. Space reserved by a transaction is freed when the transaction commits normally, or it is used to write compensation log records during transaction abort processing.
Indicates the segment of the log file that can be examined by the archiver for journal or dump processing
Indicates the current logging system status. Status can be one or more of the following values:
ACP_SHUTDOWN—the archiver is preparing to shut down. (This indicates that an rcpconfig command with the shutdown option has been issued.)
ARCHIVE—the archiver process is archiving journaled transactions to the journal files.
BCPSTALL—the logging system is requesting the recovery process to start writing a begin consistency point.
CKP_SBACKUP—the logging system marks the start of online backup. It marks this block as the online backup start block (SB). Ckpdb starts backing up the database.
CLOSEDB—the logging system is in the process of closing a database.
CPFLUSH—DBMS Servers are flushing their modified pages to disk, that is, a consistency point is being taken.
CPNEEDED—the logging system is about to take a consistency point.
CPWAKEUP—the logging system is synchronizing the fast-commit threads.
DISABLE_DUAL_LOGGING—the logging system is in the process of disabling dual logging.
DUAL_LOGGING—dual logging is enabled. (Note that this does not mean that both primary and dual logs are active. For active logs look at the Active Log(s) field.)
ECP—the logging system is requesting that the recovery process start writing an end consistency point.
ECPDONE—the logging system has taken an end consistency point. This status flag is present most of the time while the logging system is functioning normally.
FORCE_ABORT—the force-abort-limit has been reached; the oldest open transaction will be aborted.
IMM_SHUTDOWN—the logging system has been told to shut down immediately. (This is displayed when the user invokes rcpconfig with the imm_shutdown option.) Note that the logging system does not perform any housekeeping as part of the shutdown process. The recovery process then becomes responsible for backing out any uncommitted transactions left in the log file once the logging system has been restarted.
LOGFULL—the log file is full. The system administrator should determine the cause of this and increase the log file size. A warning indicator is also displayed.
MAN_ABORT—the logging system has been requested to manually abort a distributed transaction.
MAN_COMMIT—the logging system has been requested to manually commit a distributed transaction.
ONLINE—the logging system is on line. The logging and recovery systems are operating OK.
OPENDB—the logging system is in the process of opening a database.
PURGEDB—a database has been closed by the last user who had it open; the archiver is archiving transactions that belong to this database.
RCP_RECOVER—the recovery process is recovering transactions from a runaway DBMS.
RECOVER—the logging system has requested the recovery process to perform recovery.
START_ARCHIVER—this important status indicates that the archiver has stopped and must be restarted by the DBA. This is not done automatically. If the DBA does not do it, the log file will eventually fill up, reaching the LOG_FILE_FULL limit, and cause the system to stall.
START_SHUTDOWN—the logging system is shutting down. As part of the shutdown process, the logging system commits to disk all the committed transactions and backs out any uncommitted ones. The archiver also journals all the committed transactions for tables with journaling enabled.
Displays log files that are active.
|
|
|
The List of Active Processes provides information on processes currently active in the logging system. This section has the following fields:
Indicates the internal logging system ID for a process
Indicates the process ID
Indicates the type of the active process. The type field can be:
FCT—a DBMS Server running Fast Commit
SLAVE—a DBMS Server not running Fast Commit
ARCHIV—the archiver process
MASTER—the recovery process
Indicates the number of times the server opened a database. The recovery process and archiver each have their own database opened at all times, while the server is the process that opens the databases.
Indicates the number of writes this process has performed in the logging system
Indicates the number of times this process requested that a log buffer be forced to disk
Indicates the number of times any transaction in this process needed to wait for a logging system-related reason
Indicates the number of transactions started by this process
Indicates the number of transactions ended by this process
|
|
|
The "List of active databases" provides statistical information on all active databases in the logging system. You can use the information in this section and the List of active transactions section to determine which databases are open and active. Before shutting down an installation, you should ensure that all databases are closed. Knowing which databases are open and active allows you to determine whom to notify of the impending shutdown.
This section has the following fields:
Identifies the logging system ID number of an active database
Identifies the name of the database and of the DBA.
Indicates the current state of the database.
Indicates the number of transactions currently active in the database
Indicates the number of transactions started in this database
Indicates the number of transactions ended in this database
Indicates the number of reads that the logging system performed on behalf of this database
Indicates the number of writes that the logging system performed on behalf of this database
Indicates the number of times that the logging system had to force out the log buffer on behalf of this database
Indicates the number of times that the logging system had to wait for a log buffer on behalf of this database
Indicates the physical location of this database in the file system
Indicates the active journal window on this database. If no journaling is active on the database, the window has boundaries <0,0,0>. .<0,0,0>.
Indicates the log file end-of-file (EOF) address when a database backup is started. This address is used during online backup processing.
|
|
|
The List of active transactions provides statistical information on each active transaction. The fields in this section are described below:
Identifies the transaction ID used by the logging system
Identifies the transaction. The transaction ID is used by both the logging and locking systems. The ID is useful when you want to follow a transaction from lockstat to logstat output.
Identifies the ID of the database. This ID is the same as the ID in the List of active databases section of the logstat output.
Indicates the ID of the process currently working on this transaction. This field corresponds to the internal logging system ID in the List of active processes section of logstat.
Currently not used
Indicates the user session ID that owns this transaction. This is the same ID used in iimonitor output. Use this ID to locate the user and the terminal that initiated the transaction.
Indicates the log file address of the first record associated with this transaction
Indicates the log file address of the last record associated with this transaction
Indicates the first consistency point address taken that concerns this transaction
Indicates the number of log buffer writes because of this transaction
Indicates the number of times this transaction had to wait for a log buffer in order to write a log record that spanned multiple buffers
Indicates the number of times the log buffer was flushed. The force conditions are commented in more detail under the Log forces field in the Summary section above.
Indicates the number of times this transaction had to wait for a logging system-related event
Indicates the number of log blocks reserved by this transaction for recovery operations
Indicates the status of this transaction. This field can take the following values:
ACTIVE—this transaction has written a number of records to the log file.
INACTIVE—this transaction is in the retrieve mode and has not written any records to the log file.
PROTECT—this transaction is a user transaction (as opposed to an internal system transaction) and will be recovered in the event of a server or system failure.
JOURNAL—this transaction must be journaled. This flag indicates that the transaction should be archived.
Indicates why the transaction is waiting. Wait Reason can have the following values:
(not waiting): The transaction is not waiting.
FORCE—waiting for a log force
FREE—waiting for a free log buffer
SPLIT—waiting for a log split completion
HDRIO—waiting for log header I/O completion
CKPDB—waiting for a ckpdb completion
OPENDB—waiting for an open database completion
BCPSTALL—waiting for BCP log write to complete
LOGFULL—waiting because of LOGFULL condition
FREEBUF—waiting for a free buffer
LASTBUF—waiting for the last buffer in the transaction to be written
BUFIO—waiting for a log buffer to be freed
EVENT—waiting for a log event
ABSOLUTE_LOGFULL—waiting at the absolute end of a LOGFULL condition
Indicates the owner of this transaction. User can have the following values:
logfile_I/O_thread—log file read/write thread
group_commit_thread—group commit thread
buffer_manager—the buffer manager
log_reader_transaction—log file read/write thread
recovery_thread—the DMFRCP recovery thread
consistency_pt_thread—the consistency point thread
consistency_point_timer—the consistency point timer thread
write_behind—the write behind thread
security audit thread—in C2 enabled systems only, the security audit thread
username—user session
|
|
|
To determine which databases are currently active, follow these steps:
Note: The first entry listed is always owned by $ingres.
For example, in the sample output shown here, the second database shown is testdb owned by test. The ID for this database is 00280005. In the List of active transactions, the transaction listed belongs to Database: 00280005. The status of this database (testdb) is ACTIVE,PROTECT,JOURNAL. If an installation shutdown was pending, you can inform the testdb's owner, test, of the impending shutdown.
----List of active databases-------------------------------------------
Id: FFFF0001 Database: ($recovery,$ingres) Status: NOTDB,ACTIVE
Tx_cnt: 13 Begin: 16 End: 1 Read: 0 Write: 217 Force: 583 Wait: 1707
Location: None
Journal Window: <0,0,0>..<0,0,0>
Start Backup Location: <0,0,0> (0,0)
Id: 00280005 Database: (testdb,test) Status: JOURNAL,FAST_COMMIT,ACTIVE
Tx_cnt: 1 Begin: 17 End: 16 Read: 0 Write: 4690 Force: 6 Wait: 681
Location: /devsrc/65sun4/install/test/ingres/data/default/testdb
Journal Window: <760996814,981,2800>..<760996814,1300,3184>
Start Backup Location: <0,0,0> (0,0)
----List of active transactions------------------------------------
[... transaction information deleted ...]
Tx_id: 295D001D Tran_id: 00002D5B2D5BF9F7 Database: 00280005
Process: 00010012 Dis_tran_id: <0,0> Session: 0093C000
First: <760996814,1160,3100> Last: <760996814,1300,3184>
Cp: <760996814,881,948>
Write: 463 Split: 107 Force: 0 Wait: 109 Reserved: 140
Status: ACTIVE,PROTECT,JOURNAL
Wait Reason: (not waiting)
User: <test>
|
|
|
To determine how close your installation is to the FORCE-ABORT-LIMIT, use the information from the Current log file header section in the logstat output. Four statistics are relevant: the Abort interval, the Block count, the Begin, and the End.
The number appearing after Abort interval is the number of blocks in the log file that must be filled before the FORCE-ABORT-LIMIT is reached. The Block count refers to the total number of blocks in the log file. Begin refers to the block marking the log file's Beginning of File (BOF), and End refers to the block marking the log file's End of File (EOF). The numbers following Begin and End are divided into three groups separated by colons. The middle group is the most relevant. For example, in the sample output, block 778 marks the beginning of the log file.
To calculate how close the installation is to the FORCE-ABORT-LIMIT, follow these steps:
Because the log file is a circular file, the block marking the file's beginning can have a higher number than the block marking the file's end (see the Second Log File Header).
Consequently, there are two ways to determine the number of blocks in use:
1299 - 778 = 521
(2048 - 1702) + 107 = 453
For example, in the sample output shown here, 521 blocks are in use and the Abort interval is 1536, so the number of blocks still available is:
1536 - 521 = 1015
----Current log file header-----------------------------------
Block size: 4096 Block count: 2048 Buffer count: 4
CP interval: 102 Logfull interval: 1945 Abort interval: 1536
Last Transaction Id: 00002D5B2D5BFA03
Begin: <760996814:1702:2304> CP: <760996814:1873:3592>
End: <760996815:107:20>
Percentage of log file in use or reserved: 30
Log file blocks reserved by recovery system: 180
Archive Window: <760996814,1991,3508>..<760996815,107,220>
Previous CP: <760996814,1702,2304>
Status: ONLINE,ARCHIVE,CPFLUSH
Active Log(s): LOG_FILE