21.3.3 How to reduce the synchronization point processing time

There are three ways to reduce the synchronization point processing time:

Organization of this subsection
(1) Reducing the deferred write trigger interval
(2) Increasing the update page output rate during deferred write trigger
(3) Increasing the number of parallel WRITE processes during deferred write processing

(1) Reducing the deferred write trigger interval

Reduce the deferred write trigger interval and the number of update buffers during synchronization point processing. If the number of update buffers decreases, the time required for applying the updated pages during synchronization point processing can be reduced, thereby reducing the synchronization point processing time.

The following describes how to tune the deferred write trigger interval.

Procedure
  1. Acquire statistical information about the global buffer and the deferred write processing.
  2. From the execution results in the global buffer statistical information, identify the global buffer whose synchronization point output pages count (SYNCW) is high.
  3. Use the following operands to specify the deferred write trigger start conditions:
    [Figure]pd_dbbuff_rate_updpage operand
    [Figure]-y option in the pdbuffer operand
    Check the global buffer statistical information and change the operand values in such a manner that the following condition is satisfied:
    TRGUP (number of update buffers that become output triggers during deferred write triggers) < SYNCW (number of synchronization point dump pages)/2
    If the value of TRGUP is too small, the number of write operations increases and the transaction performance may be adversely affected. If this happens, restore the original operand values.
  4. Check to see if the synchronization point processing time is within 10% of the synchronization point acquisition interval. If it is greater than 10%, repeat steps 1 through 3 until it is within 10%.

To determine the synchronization point processing time, check the execution time (DWTOTAL) in the statistical information about deferred write processing.

Obtain the synchronization point collection interval from the difference between the output times of the following messages:

(2) Increasing the update page output rate during deferred write trigger

Increase the update page output rate during deferred write processing (increase the number of update pages to be applied during deferred write trigger processing), and reduce the number of update buffers during synchronization point processing. If the number of update buffers decreases, the time required for applying the updated pages during synchronization point processing can be reduced, thereby reducing the synchronization point processing time.

The following describes how to tune the update page output rate during deferred write trigger.

Procedure
  1. Acquire statistical information about the deferred write processing.
  2. Increase the -w option value in the pdbuffer operand.
    If the -w option value is too large, the number of write operations increases and the transaction performance may be affected adversely. If this happens, restore the original operand value.
  3. Check to see if the synchronization point processing time is within 10% of the synchronization point acquisition interval. If it is greater than 10%, repeat steps 1 and 2 until it is within 10%.

Obtain the synchronization point collection interval from the difference between the output times of the following messages:

(3) Increasing the number of parallel WRITE processes during deferred write processing

Increase the number of parallel WRITE processes during deferred write processing to reduce the deferred write processing time. The following describes how to tune the number of parallel WRITE processes during deferred write processing.

Procedure
  1. Acquire statistical information about the deferred write processing.
  2. Increase the pd_dfw_awt_process operand value to increase the number of parallel WRITE processes during deferred write processing.
    Note that if the pd_dfw_awt_process operand value increases, the number of processes also increases, resulting in an increase in the CPU workload. Therefore, use a facility such as an OS function to monitor the CPU workload.
  3. Check to see if the synchronization point processing time is within 10% of the synchronization point acquisition interval. If it is greater than 10%, repeat steps 1 and 2 until it is within 10%.

To determine the synchronization point processing time, check the execution time (DWTOTAL) in the statistical information about deferred write processing.

Obtain the synchronization point collection interval from the difference between the output times of the following messages:

If increasing the number of parallel WRITE processes during deferred write processing does not reduce the parallel WRITE time (DWPARA, DWPARAM), possible causes are as follows: