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.

Physical Logging
Buffer bufused  bufsize  numpages   numwrits   pages/io
  P-2  53       64       2283374    35990      63.44
      phybegin         physize    phypos     phyused    %used
      2:53             2047947    296185     5034       0.25

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        0x41      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

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

Buffer Waiting

Heading	Description	Format	See Also
Buffer	Which buffer, typically L1, L2, L3	Str
ioproc	Contains the rstcb address of the thread assigned to to the work	Int
flags	0x01 I/O operation active or about to be 0x40 add current page to log pages used tally	Hex
Unknown

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 2: Used to prevent log overwrites during Level 0 backups G Staged for Enterprise Replication R Not needed by the currently active archive W Records are currently being flushed to this log X Still needed by an active archive Z Will be cleared before use 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.

Archive Flags (John Lengyel)

Let's say you start a whole system backup when the current log is unique ID 44. Let's also say you have some open transactions, the oldest of which began work in log 41 and wrote some records there. This means that if you were to restore this archive and you elected not to roll forward any logs, the first thing the server would do before going into quiescent mode is roll back the transactions that were open at the time of the archive checkpoint, which means it would need to have logs 41 through 44 on disk.

When we start the archive we know we need those logs, so after archiving the reserve pages from rootdbs the next thing we send to the archive stream is what we call a "log snapshot"--the needed pages from logs 41 through 44. We don't need any log older than 41 so we don't bother including it on the archive. And the archive doesn't need any logs beyond 44. In fact it doesn't need any log pages beyond the one in log 44 that contains the archive checkpoint. So this log snapshot will include only logs 41 through that specific page in log 44.

Until those needed logs are written to the archive we can't allow them to be freed and potentially overwritten, so we use a flag on each log file structure to distinguish between log group 41-44 and all the other logs, which can be freed if necessary because the archive doesn't care about them.

If a log file in the onstat -l output has the 'X' flag that means there's an archive that has just started and the log hasn't yet been written to that archive.

If a log file has the 'R' flag it means there's an archive going but that log is not needed for this archive, either because it never was to begin with or it's already been written to the stream. As each log file is archived you'll see its flag change from 'X' to 'R'.

This is all unrelated to logical log backups. The log snapshot I've been referring to is included in the archive itself. Neither the 'X' flag nor the 'R' flag says anything about whether a log has been backed up in the usual way.