onstat -g laq command: Print log apply queues

Use the onstat -g laq command to print information about log recovery apply queues.

Use the onstat -g laq command to print information about log recovery apply queues. This includes logical log recovery on secondary servers as well as logical restore or logical recovery part of fast recovery. Log records from logical logs are assigned to replay worker threads according to the tablespace ID (partnum) associated with them; a subset of log records will be applied by the replay master thread.

For instance, in a high-availability cluster, the primary server sends log records to one or more secondary servers over the network. Each secondary server continuously replays the transaction logs from the primary server to ensure that data is replicated on the secondary server. Each tblspace on the primary server is assigned a queue on the secondary server in which to receive log records. A replay thread applies the log records stored in the queue to the secondary server. The log records are applied in the order in which they were received.

You use the onstat -g laq command to monitor the performance of the log apply queues, on a secondary server or during any other form of log recovery. Use this command if you suspect that the primary server performance is slowed because logs are not replaying quickly enough on the secondary server, or to monitor the progress made during logical restore. The Avg Depth (average depth) column indicates the average number of log records in the queue(Queue Size) incurred whenever putting a new log record on a queue. The Current/Last LSN column specifies the log record a replay thread currently is active on, or the last one it was replaying, with the Partval column typically specifying the tablespace ID this log record refers to. Transaction pointer and ID shown for a replay thread indicate a log record currently being applied.

When used in repeat mode, using -r [<seconds>][.<fraction>] option, an overall log record apply rate is calculated and shown.

On a secondary server, the transaction latency measured, in full seconds, with each end-of-transaction(COMMIT, ROLLBACK) log record as difference between local apply time and primary server's EoT time, is shown.

The onstat -g laq command is valid only when some form of logical log recovery is going on, otherwise only an onstat header is printed.

Figure 1: Syntax:

Example output

Figure 2: onstat -g laq -r .3 command output from a remote standalone secondary server


Log Apply Info:
Thread          Queue        Total      Avg
                 Size       Queued    Depth   Current/Last LSN    Partval Txp      (Txid)
wreplay_1           1       938310    19.66     14087,0x482ec      100540 0x450a8c28 (29)
wreplay_2           0       782865    12.91     14087,0x48184
wreplay_3           3       937766    19.86     14087,0x45598      100542 0x450a8c28 (29)
wreplay_4           2       529755    14.43     14087,0x483bc      100543 0x450a8c28 (29)
wreplay_5           6       389432    10.93     14087,0x46500      10054e 0x450a8c28 (29)
wreplay_6           0       789238    10.90     14087,0x4318c
wreplay_7           0      1317820    20.26     14087,0x440b0
wreplay_8           0       991836    12.29     14086,0xb3d8
wreplay_9           0       851854    19.60     14086,0xb52c
wreplay_10          1       913434     9.42     14087,0x4849c          1d 0x450a8c28 (29)
mreplay                     689544              14087,0x4849c
Total:             13      9131854   150.26  Avg:    15.03

Secondary Apply Queue:         Total Buffers:12 Size:1024K Free Buffers:11
Log Recovery Queue:            Total Buffers:12 Size:16K Free Buffers:10
Log Page Queue:                Total Buffers:512 Size:2K Free Buffers:512
Log Record Queue:              Total Buffers:50 Size:16K Free Buffers:42

Transaction Latency: 1 seconds
Apply rate:  30213.33 recs/sec - 9064 new recs in 300ms

Output description

Thread: The name of the apply thread for a given log record queue.
Queue Size: The number of log records queued for a given apply thread.
Total Queued: The total number of queued log records for a given apply thread.
Avg Depth: The average number of log records in the queue at the time a queue insert operation occurred.
Secondary Apply Queue: The secondary apply queue receives log buffers from the primary server. The values displayed represent the total number of buffers allocated to receiving log buffer records(SEC_DR_BUFS), the size of the buffers(LOGBUFF), and the number of currently unused buffers.
Log Recovery Queue: The log recovery queue receives output from the secondary apply queue. The log buffers are converted to a format compatible with the ontape utility. The values displayed represent the total number of stream buffers in the recovery queue, the size of the stream buffers(LTAPEBLK), and the number of unused buffers.
Log Page Queue: The log page queue receives output from the log recovery queue. The values displayed represent the total number of log pages in the queue, the size of the queue, and the number of unused buffers.
Log Record Queue: The log record queue receives output from the log page queue. The log pages are divided into individual log records. The values displayed represent the total number of log records in the recovery queue, the size of the queue, and the number of unused buffers.
Transaction Latency: Time difference between last replayed transaction commit time at primary server and local server. For this to be accurate, operating system time must match between primary and secondary servers.
Apply rate: Number of log records replayed per second. Apply rate is only shown with -r option.