High Latency While Processing Large Transactions Using Temporary Files

Issue

Latency is high or increasing on the Capture (source) or Integrate (target) side, and the disk utilization is increasing on the machine where you have created the HVR_TMP directory.

On the Capture side, a latency increase occurs, though errors don't appear, and additional entries don't appear in the Capture job's log file.

On the Integrate side, the following warning may appear at the beginning of cycles:

W_JT0E00: Sorting used x temporary file due to memory limit. Consider defining action Environment with parameter Name=HVR_SORT_BYTE_LIMIT Value=541M to avoid spilling (if this memory is available). Current spill limit is 512M.

Alternatively, the job may fail with the following error:

F_JG240A: Write of x bytes to file failed. Error: No space left on device.

Environment

• HVR 6
• Any OS and file system with HVR 6 installed

Resolution

To resolve this issue, we recommend improving performance on the source or target side by processing large files with maximum memory utilization rather than depending on temporary files. This can be achieved by defining the environment variables described below.

Capture

To resolve issues affecting Capture side, do the following:

1. Click Channels.
2. Click on the affected channel's name.
3. Click View actions.
4. Click on the Add Action button.
5. Choose the Environment action.
6. In the Environment action dialog,
   • Set the action scope to SOURCE location.
   • Set the Name parameter to HVR_TEMP_SPILL.
   • Set the Value parameter to the amount of RAM you want the target machine to utilize based on its resources before creating temporary files. For example, 1G or 500M.

Integrate

To resolve issues affecting Integrate side, do the following:

1. Click Channels.
2. Click on the affected channel's name.
3. Click View actions.
4. Click on the Add Action button.
5. Choose the Environment action.
6. In the Environment action dialog,
   • Set the action scope to TARGET location.
   • Set the Name parameter to HVR_SORT_BYTE_LIMIT.
   • Set the Value parameter to the amount of RAM you want the target machine to utilize based on its resources before creating temporary files. For example, 1G or 500M.

Once you have saved the action, perform Activate Replication with the Jobs component selected using only the location where you have set the Environment action defined.

If you are not using an HVR Agent on the source or target side, you must consider the available resources on the HVR hub server machine.

Cause

If large transactions occur on a source database, for example, from bad batch jobs, it can generate excessively large transaction log files. These large files pose challenges for the Capture job due to their immense size, causing increased latency during processing. The Capture job is required to parse through the entire transaction log file, even if only a few or no replicated tables are affected by the changes. Similar latency issues may arise when transactions affect tables with large object (LOB) columns.

If the HVR hub routes a large transaction to the target location, the Integrate job will also require more resources and time to process it. As a result, latency can increase on the target side. Using memory instead of temporary files can massively improve the speed required to process these files.