21.1.2 Using the statistics analysis utility to collect statistical information
(1) Check the input waits count (WAITR) and output waits count (WAITW)
- Purpose
- This information is collected in order to evaluate the validity of global buffer allocations to RDAREAs. If contention results because of multiple requests to access the same page, data I/O operations are placed in wait status for the following reasons:
- Requested data must be read from the HiRDB file, because the appropriate page cannot be found in the global buffer pool; this results in an input wait.
- Even when the appropriate page is found in the global buffer pool, but contention occurs between a referencing request and an updating request during a HiRDB file output operation, the HiRDB file is placed in output completion wait status.
- Evaluating the analysis results
- When the input waits count is high
- Input waits may occur frequently for the following reasons:
- I/O operations concentrate because HiRDB files in one or more RDAREAs are allocated on the same disk.
- If multiple tables are stored in one RDAREA, many processing requests are issued for that RDAREA.
- When the output waits count is high
- Output waits may occur frequently for the following reasons:
- Deferred write processing on the appropriate page and HiRDB file output processing result in contention.
- The number of update buffers increases in the global buffer pool, because a large amount of global buffer pool space is used for data load processing by the database load utility or for reload processing by the database reorganization utility.
- Actions to be taken
- If multiple tables are stored in one RDAREA, allocate the RDAREAs to each of the applicable global buffer pools.
- If a table contains a large amount of data, consider dividing the table or index into multiple partitions.
- Distribute HiRDB files in one or more RDAREAs onto multiple disks.
- If a large amount of data is being loaded by the database load utility or is being reloaded by the database reorganization utility, increase the number of batch output pages (-n option). Note that the database reorganization utility is applicable to a table whose data attribute is FIX.
- When performing deferred write processing (when the pd_dbsync_point operand is specified as sync or is omitted) while the output waits count is high, decrease the value of the deferred write trigger request rate (pd_dbbuff_rate_updpage operand).
(2) Check the maximum concurrent request buffer sectors count (MAXB) and the buffer shortages count (BFINS)
- Purpose
- This information is collected in order to evaluate the validity of the number of buffer sectors in a global buffer pool. If the entire global buffer used for reading pages is unavailable, HiRDB cancels a transaction that requests a new page; this has an adverse effect on transaction throughput.
- Evaluating the analysis results
- If any of the following conditions is true, the number of buffer sectors is too low:
- The maximum concurrent request buffer sectors count is close to or equal to the global buffer sectors count, and the buffer shortages count is 0 (this does not apply when the reference or update buffer hits rate is high; maximum performance seems to be achieved when the reference or update buffer hits rate is high).
- The maximum concurrent request buffer sectors count is close to or equal to the global buffer sectors count, and the buffer shortages count is high.
- The buffer shortages count is not 0.
- Actions to be taken
- Increase the value of the pdbuffer operand's -n option (number of global buffers).
- If I/O operations concentrate on a particular RDAREA, allocate a dedicated global buffer pool instead of sharing the same global buffer pool among multiple RDAREAs.
(3) Check the number of synchronization point dump pages (SYNCW)
- Purpose
- This information is collected in order to evaluate the validity of the synchronization point dump output interval. Normally, all updated pages are output from a global buffer pool to the database during a synchronization point dump, which has an adverse effect on transaction performance. To prevent this, HiRDB executes internal processing (pre-sync processing and deferred write processing) and reduces the number of pages to be output during synchronization point dumps. Therefore, tuning the global buffer has an effect on the synchronization point dump output interval.
- Evaluating the analysis results
- Check that the number of pages output during a synchronization point dump is no greater than 50% of the global buffer sectors.
- Actions to be taken
- Increase the value of the pdbuffer operand's -w option (page rate of output by deferred write processing).
(4) Check the lock-release contention rate (SLEPR) in global buffer lock processing
- Purpose
- The purpose is to determine how much contention there is for locking of global buffers. If the contention rate is high, this will be one factor in the increased probability of global buffer lock-waiting and the resulting reduced performance.
- Evaluating the analysis results
- If the lock-release contention rate (SLEPR) in global buffer lock processing is greater than 1, the contention for global buffer lock processing can be considered to be high.
- Actions to be taken
- Check the value of the pd_dbbuff_lock_release_detect operand, and take one of the following measures:
When pd_dbbuff_lock_release_detect=interval- Do the following to reduce the CPU usage to below 70% and within the allowed operating range:
Increase the value of the pd_dbbuff_lock_spn_count operand.
Decrease the value of the pd_dbbuff_lock_interval operand.
- Review the method of allocation of global buffers.
Allocate dedicated global buffers to an RDAREA that stores tables that are accessed frequently. If dedicated global buffers are already allocated, either row partition the tables and allocate dedicated global buffers to each RDAREA or store tables with a high frequency of accesses in different RDAREAs, and allocate dedicated global buffers to those RDAREAs.
When pd_dbbuff_lock_release_detect=pipe (default value)- Specify interval for the pd_dbbuff_lock_release_detect operand. Then execute step 1 above.
- Execute step 2 above.
(5) Check the average value for the sleep processing execution count in buffer lock processing (SLEPA)
- Purpose
- The purpose is to determine whether or not a delay in the global buffer lock processing is causing an extremely long processing time for some jobs during concurrent job execution.
- Evaluating the analysis results
- If the following conditions are satisfied, it may be possible to reduce the global buffer lock processing time:
- The average value for the sleep processing execution count in buffer lock processing (SLEPA) is at least 1 but less than 2.
- There is no difference in the global buffer lock processing time between cases in which sleep processing is executed once or more, and cases in which sleep processing is never executed.
- Actions to be taken
- Check the value of the pd_dbbuff_lock_release_detect operand, and take one of the following measures:
When no transaction stops responding during synchronization point processing- Specify interval in the pd_dbbuff_lock_release_detect operand.
- When the CPU usage is below 70% and within the allowed operating range, do the following:
- Specify 10 in the pd_dbbuff_lock_interval operand.
- Specify 100 in the pd_dbbuff_lock_spn_count operand.
If the value of SLPA remains 2 or greater after these settings have been specified, increase the pd_dbbuff_lock_spn_count operand value until the value of SLPA becomes less than 2.
- Review the method of allocating global buffers.
Allocate dedicated global buffers to an RDAREA that stores tables that are accessed frequently. If dedicated global buffers are already allocated, either row partition the tables and allocate dedicated global buffers to each RDAREA, or store tables with a high frequency of accesses in different RDAREAs and allocate dedicated global buffers to those RDAREAs.
When transactions stop responding during synchronization point processing- See (6) Check the buffer pool lock time during synchronization point processing (SYNCL).
(6) Check the buffer pool lock time during synchronization point processing (SYNCL)
- Purpose
- If a transaction stops responding during synchronization point processing, there may be contention for locking the global buffer between search processing in the buffer subject to synchronization point processing, and buffer access by the transaction. If there is contention, buffer access by a transaction is placed on hold during the buffer pool lock time during synchronization point processing (SYNCL).
- Evaluating the analysis results
- If a transaction stops responding during synchronization point processing, determine whether or not the buffer pool lock time during synchronization point processing (SYNCL) is too long for the lock release wait time.
- Actions to be taken
- Reduce the pd_dbsync_lck_release_count operand value, so as to adjust the buffer pool lock time during synchronization point processing (SYNCL) to be within the allowable limits of the lock release wait time.
(7) Check the take-over count of database write processing by the reference request hit during synchronization point processing (ALTRW)
- Purpose
- If a referencing transaction stops responding during synchronization point processing, delayed output processing may be taking place at an extension of transaction at the time of the reference request hit on the buffer that is subject to synchronization point processing.
- Evaluating the analysis results
- If a referencing transaction stops responding during synchronization point processing, check to see if this is caused by the take-over count of database write processing by the reference request hit during synchronization point processing (ALTRW).
- Actions to be taken
- By specifying Y in the pd_dbsync_altwrite_skip operand, you can suppress database write processing by the reference request hit during synchronization point processing. However, this increases the synchronization point acquisition processing time. Hitachi recommends that you use the facility for parallel writes in deferred write processing to distribute the write processing workload. For details about the facility for parallel writes in deferred write processing, see the manual HiRDB Version 8 Installation and Design Guide.