How to Monitor Replication Latency

Question

How can I monitor replication latency?

Environment

HVR 5

Answer

NOTE: An alternative way to monitor replication latency is to use HVR Insights graphical user interface.

Replication latency can be divided into the following parts:

• Capture latency
• Integrate latency

You can monitor the replication latency using the following:

• HVR graphical user interface (HVR GUI)
• HVR Statistics
• HVR Maint
• HVR log files

HVR GUI

You can use the HVR GUI to interactively monitor the replication latency. For each job, the job table displays several relevant columns including the following:

• Latency
• Capture Start: The start of the oldest open transaction
• Capture Emit: The time stamp of the latest record emitted from the capture job
• Router Rows: The number of rows waiting to be integrated
• Router Timestamp: The oldest time stamp for the records waiting to be integrated

HVR Statistics

HVR Statistics is a utility that analyses the HVR log files and provides replication statistics. This includes minimum and maximum values for Capture and Integrate latency. You can run it interactively on the command line (using the hvrstatistics command) or by using the HVR GUI. Alternatively, you can use the HVR Statistics channel, which periodically (for example, hourly) runs hvrstatistics as an agent and loads the resulting data into a database table. For more information, see our Hvrstatistics documentation.

HVR Maint

You can use the HVR Maint utility to monitor and maintain HVR. For more information, see our Hvrmaint documentation.

This utility can send email or SNMP alerts if an error occurs or if a certain latency limit has been reached. It can provide the thresholds for a specific location, job, channel, or for the whole system.

The following is an example of HVR Maint's latency output:

---------------------------Latencies over 10min limit---------------------------
2h 44m latency for ch-integ-tgt
---------------------------------All latencies----------------------------------
1s latency for ch-cap-src
2h 44m latency for ch-integ-tgt
---------------------------------End of Latency--------------------------

HVR log files

Both Capture and Integrate jobs provide the latency numbers in their output (log files).

Example of capture latency:

hvr_demo15-cap-db02: Scanned 304 transactions (5205440 bytes) from between 4 seconds and 2 seconds ago containing 1074 rows (150 del, 300 ins, 312 upd) for 6 tables in 0.69 seconds.

Example of integrate latency:

hvr_demo15-integ-db01: Integrated 127 changes from between 15 seconds and 13 seconds ago for 'dm15_tab2a' (25 del, 50 ins, 52 upd).
hvr_demo15-integ-db01: Integrated 202 changes from between 15 seconds and 14 seconds ago for 'dm15_tab12b' (25 del, 50 ins, 127 upd).
hvr_demo15-integ-db01: Integrated 127 changes from between 15 seconds and 14 seconds ago for 'dm15_tab6' (25 del, 50 ins, 52 upd).
hvr_demo15-integ-db01: Integrated 202 changes from between 15 seconds and 13 seconds ago for 'dm15_tab12a' (25 del, 50 ins, 127 upd).
hvr_demo15-integ-db01: Integrated 127 changes from between 15 seconds and 13 seconds ago for 'dm15_tab2b' (25 del, 50 ins, 52 upd).
hvr_demo15-integ-db01: Integrated 127 changes from between 15 seconds and 13 seconds ago for 'dm15_tab4' (25 del, 50 ins, 52 upd).
hvr_demo15-integ-db01: Integrate cycle used 10 transactions for 282 capture transactions from between 15 seconds and 13 seconds ago and took 1.71 seconds.

Capture latency from .cap_state file

With log-based capture, you can monitor the capture latency for each job in it’s *.cap_state file. This file is located in $HVR_CONFIG/router/<hub>/<channel>/loc_<cap>/. It has a binary format but you can print it as an XML file using hvrrouterview:

$hvrrouterview hubdb hvr_demo15 hvr_config/router/hubdb/hvr_demo15/loc_db02/55f198c5_b913b-55f198c6.cap_state
   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE hvr_private SYSTEM "lib/hvr_private.dtd">
   <hvr_private version="1.0">
       <file name="hvr_config/router/hubdb/hvr_demo15/loc_db02/55f198c5_b913b-55f198c6.cap_state"/> <!-- revealed 2015-09-10 16:50:45 -->
       <list>
            <!-- scan_load --> <int>1441896606</int> <!-- 2015-09-10 16:50:06 -->
            <!-- scan_start_tstamp --> <int>1441896645</int> <!-- 2015-09-10 16:50:45 -->
            <!-- scan_start_addr --> <str>0x003cba.000044e8.0010</str>
            <!-- emit_seq --> <str>25325752877057</str>
            <!-- scan_start_seq --> <str>25325752745985</str>
            <!-- emit_tstamp --> <int>1441896646</int> <!-- 2015-09-10 16:50:46 -->
            <!-- emit_addr --> <str>0x003cba.000044ed.01dc</str>
       </list>
   </hvr_private>

Integrate latency from state table

The integrate job maintains the Integrate Receive Timestamp table of the target database. The timestamp in the trailer_cap_begin column of this table provides you the replication latency. For example, if the timestamp is 09:05 AM, it means that all the changes made to the source database for this channel before this timestamp have been integrated into this location.

There is a separate table for each channel.