onstat -g ckp command: Print checkpoint history and configuration recommendations

Use the onstat -g ckp command to print checkpoint history and show configuration recommendations if a suboptimal configuration is detected.

Figure 1: Syntax:

1  onstat   -g ckp

Example output

Figure 2: onstat -g ckp command output
Auto Checkpoints=On   RTO_SERVER_RESTART=60 seconds   Estimated recovery time 7 seconds
                                                           Critical Sections        
         Clock                        Total  Flush  Block  #      Ckpt  Wait  Long  #Dirty
Interval Time     Trigger  LSN        Time   Time   Time   Waits  Time  Time  Time  Buffers
1        18:41:36 Startup  1:f8       0.0    0.0    0.0    0      0.0   0.0   0.0   4    
2        18:41:49 Admin    1:11c12cc  0.3    0.2    0.0    1      0.0   0.0   0.0   2884 
3        18:42:21 Llog     8:188      2.3    2.0    2.0    1      0.0   2.0   2.0   14438 
4        18:42:44*User     10:19c018  0.0    0.0    0.0    1      0.0   0.0   0.0   39  
5        18:46:21 RTO      13:188     54.8   54.2   0.0    30     0.6   0.4   0.6   68232 
	   
           Physical Log    Logical Log
  Dskflu   Total   Avg      Total   Avg
  /Sec     Pages   /Sec     Pages  /Sec
  4        3       0        1       0
  2884     1966    163      4549    379
  7388     318     10       65442   2181
  39       536     21       20412   816
  1259     210757  1033     150118  735
Max Plog   Max Llog    Max Dskflush    Avg Dskflush   Avg Dirty   Blocked
pages/sec  pages/sec   Time            pages/sec      pages/sec   Time
8796       6581        54              43975          2314         0

Output description

Auto Checkpoints
Indicates if the AUTO_CKPTS configuration parameter is on or off
RTO_SERVER_RESTART
Displays the RTO time in seconds. Zero (0) means that RTO is off.
Estimated recovery time ## seconds
Indicates the estimated recovery time if the data server stops responding. This value appears only if RTO_SERVER_RESTART is active.
Interval
Checkpoint interval ID
Clock Time
Clock time when checkpoint occurred.
Trigger
Event that triggered the checkpoint. An asterisk (*) indicates that the checkpoint that was requested was a transaction-blocking checkpoint.
Trigger name Description
Admin Administrator-related tasks. For example:
  • Create, drop, or rename a dbspace
  • Add or drop a chunk
  • Add or drop a log file
  • Change physical log size or location
  • After "shrink" operation on partition
  • Turn on or off mirroring
Backup Back up related operations. For example:
  • Fake backup
  • Start of an archive
  • After the completion of a physical restore
CDR ER subsystem is started for the first time, or is restarted after all of the replication participants were removed.
CKPTINTVL When the checkpoint interval expires. The checkpoint interval is the value that is specified for the CKPTINTVL parameter in the onconfig file.
HA High availability. For example:
  • A new RSS or SDS node is added to a High Availability cluster
  • A secondary server is promoted to a primary server
  • The physical log file is low on a secondary server
HDR High-Availability Data Replication. For example:
  • The mode of the server is changed
  • The start of the first transfer after HDR is set up
  • There is the potential for a physical log overflow on primary or secondary servers
IPL Trigger checkpoint to reduce physical log usage on the secondary server. Index page logging can cause foreground writes and heavy physical log usage on secondary servers.
Lightscan Before the look aside is turned off on partitions.
Llog Running out of logical log resources.
LongTX Long Transaction. If a long transaction was found but not stopped, a checkpoint is initiated to stop the transaction. During rollback, a checkpoint is initiated in the rollback phase if a checkpoint has not already happened after long transaction was aborted.
Misc Miscellaneous events. For example:
  • A dbspace or chunk is being brought down because of I/O errors
  • During rollback when the addition of the chunk is being undone: for example, when removing the chunk.
Plog Physical log has one of the following conditions:
  • Physical log is 75% full
  • The amount of physical log used plus the number of dirty partitions is more than 90% of physical log size
Restore Pt Restore Point. Checkpoints at the start and end of a restore point. The restore point is (used by conversion guard) CONVERSION_GUARD configuration parameter is enabled and a temporary directory is specified in the RESTORE_POINT_DIR configuration parameter.
Recovery During a restore, at the start of a fast recovery.
Reorg At the start of online index build.
RTO Maintaining the Recovery Time Objective (RTO) policy. During normal operations, when the restart time after a crash might exceed the value that is set for the RTO_SERVER_RESTART configuration parameter.
Stamp Wrap Checkpoint timestamp. If the new checkpoint timestamp appears to be before the last written checkpoint, then the timestamp is advanced out of interval between checkpoints. Another checkpoint is triggered.
Startup At the startup of the database server.
Uncompress Uncompress commands that are issued on a table or partition. This applies only for checkpoints on tables or databases that are not logged.
User A checkpoint request is submitted by the user.
LSN
Logical log position where checkpoint is recorded
Total Time
Total checkpoint duration, in seconds, from request time to checkpoint completion
Flush Time
Time, in seconds, to flush buffer pools
Block Time
Time a transaction was blocked, in seconds, by a checkpoint that was triggered by a scarcity of some needed resource. For example, running out of physical log, or wrap-around of the logical log.
# Waits
Number of transactions that are blocked waiting for checkpoint
Ckpt Time
Time, in seconds, for all transactions to recognize a requested checkpoint
Wait Time
Average time, in seconds, that transactions waited for checkpoint
Long Time
Longest amount of time, in seconds, a transaction waited for checkpoint
# Dirty Buffers
Number of dirty buffers that are flushed to disk during checkpoint
Dskflu/sec
Number of buffers that are flushed per second
Physical Log Total Pages
Total number of pages that are physically logged during checkpoint interval
Physical Log Avg/Sec
Average rate of physical log activity during checkpoint interval
Logical Log Total Pages
Total number of pages that are logically logged during checkpoint interval
Logical Log Avg/Sec
Average rate of logical log activity during checkpoint interval
Max Plog pages/sec
Maximum rate of physical log activity during checkpoint interval
Max Llog pages/sec
Maximum rate of logical log activity during checkpoint interval
Max Dskflush Time
Maximum time, in seconds, to flush buffer pools to disk
Avg Dskflush pages/sec
Average rate buffer pools are flushed to disk
Avg Dirty pages/sec
Average rate of dirty pages between checkpoints
Blocked Time
Longest blocked time, in seconds, since the database server was last started

Performance advisory messages

If the HCL OneDB™ data server detects a configuration that is less than optimal, a performance advisory message with tuning recommendations appears below the checkpoint history. This performance advisory message also appears in the message log. Following are examples of performance advisory messages:

Physical log is too small for bufferpool size. System performance may be 
less than optimal.
Increase physical log size to at least %ldKb

Physical log is too small for optimal performance.
Increase the physical log size to at least $ldKb.

Logical log space is too small for optimal performance.
Increase the total size of the logial log space to at least %ld Kb.

Transaction blocking has taken place. The physical log is too small.
Please increase the size of the physical log to %ldKb

Transaction blocking has taken place. The logical log space is too small.
Please increase the size of the logical log space to %ldKb