21.1.1 Using the pdbufls command to collect statistical information

Organization of this subsection
(1) Check the global buffer pool's hits rate (HIT)
(2) Check the update requests hits rate (HIT)
(3) Check the reference requests hits rate (HIT)
(4) Check the update buffer flushes count (UPFLS)
(5) Check the reference buffer flushes count (RFFLS)
(6) Check the real READs count (READ) and real WRITEs count (WRITE)
(7) Check the buffer lock-release waits count (WAITL)
(8) Check the prefetch input pages count (PRRED), prefetch hit pages count (PRHIT), and prefetch hits rate (HIT)
(9) Check the prefetch buffer shortages count (PRINS)

(1) Check the global buffer pool's hits rate (HIT)

Purpose
This information is collected to determine whether or not the number of buffer sectors in a global buffer pool is appropriate. This is the most important item for tuning a global buffer pool.
Evaluating the analysis results
Check to see if the global buffer pool hits rate (HIT) is 80% or higher.
Actions to be taken
Global buffer pool hits rate is below 80%
Increase the value of the pdbuffer operand's -n option (number of global buffers).
Global buffer pool hits rate is 80% or better
There is no problem; however, to improve the hits rate even further, increase the number of global buffer sectors.
RDAREAs storing tables with no buffering effects should be combined into a single RDAREA; otherwise, more memory may be used than is necessary.

(2) Check the update requests hits rate (HIT)

Purpose
This information is collected in order to evaluate the validity of the number of global buffer sectors and the deferred write processing that is executed internally by HiRDB.
When a data insertion, updating, or deletion request is issued for a database, the corresponding data is updated in a global buffer pool. Therefore, the ratio of the number of times requested data was found in a global buffer pool to the total number of update GET requests has an effect on throughput.
Evaluating the analysis results
Evaluate the update requests hits rate with respect to the total number of issued INSERT, UPDATE, and DELETE statements, as reported in the UAP information output by the statistics analysis utility:
  1. Check that the update requests hits rate is not too low
  2. Check that the reference buffer hits rate is not too low even if the update requests hits rate is high.
The update requests hits rate is obtained with the following formula:
Update requests hits rate (%) = (update GET requests hits count[Figure] update GETs count)[Figure] 100
Actions to be taken
When the update requests hits rate is low
  1. Increase the value of the pdbuffer operand's -n option (number of global buffers).
  2. Re-evaluate the value specified for the pdbuffer operand. If multiple RDAREAs are allocated to one global buffer, separate the RDAREAs for tables to be updated from the RDAREAs for tables to be referenced, and allocate a global buffer to each type of RDAREA. If one RDAREA is allocated to one global buffer, store indexes and tables in separate RDAREAs and allocate a global buffer dedicated to indexes. Or, row-partition the table.
  3. When performing deferred write processing (when the pd_dbsync_point operand is specified as sync or it is omitted), decrease the value of the pdbuffer operand's -w option (percentage of pages output in deferred write processing).
When the update requests hits rate is high
There is no problem; however, to improve the hits rate even further, increase the number of global buffer sectors.
RDAREAs storing tables with no buffering effects should be combined into a single RDAREA; otherwise, more memory may be used than is necessary.

(3) Check the reference requests hits rate (HIT)

Purpose
This information is collected in order to evaluate the validity of the reference requests hits rate. If needed data is not found in the global buffer, the system retrieves database data from the RDAREA and sets it in the buffer. If the number of times requested data is found in the global buffer pool is small, the number of I/O operations increases, affecting adversely the transaction processing performance.
Evaluating the analysis results
Determine whether the reference requests hits rate is close to 80%. The reference requests hits rate is obtained with the following formula:
Reference requests hits rate (%) = (reference requests hits count[Figure] reference GETs count)[Figure] 100
Actions to be taken
When the reference requests hits rate is 80% or lower
  1. Increase the value of the pdbuffer operand's -n option (number of global buffers).
  2. Re-evaluate the value specified for the pdbuffer operand. If multiple RDAREAs are allocated to one global buffer, allocate one global buffer to a frequently accessed table. If one RDAREA is allocated to one global buffer, row-partition the table.
  3. If the reference requests hits rate is too low and the reference page flush count is too high for the update requests hits rate, specify pd_dbbuff_lru_option=MIX.
  4. When performing deferred write processing (when the pd_dbsync_point operand is specified as sync or it is omitted), decrease the value of the pdbuffer operand's -w option (percentage of pages output in deferred write processing).
When the reference requests hits rate is higher than 80%
There is no problem; however, to improve the hits rate even further, increase the number of global buffer sectors. RDAREAs storing tables with no buffering effects should be combined into a single RDAREA; otherwise, more memory may be used than is necessary.

(4) Check the update buffer flushes count (UPFLS)

Purpose
This information is collected in order to evaluate the validity of the update buffer flushes count. If the number of times a free buffer is created to read new pages after writing an updated buffer into a HiRDB file (update buffer flushes count) is high for the number of operations, the number of I/O operations increases, adversely affecting the transaction processing performance.
Evaluating the analysis results
The number of updated global buffer sectors may have increased along with the update buffer flushes count and the buffers count for reloading the same data (reference GETs count) for one of the following reasons:
  • The number of global buffer pool sectors is small
  • The ratio of the number of updating processes to the number of operations is high
Determine whether the update buffer flushes count is high or low for the number of operations.
Actions to be taken
When the update buffer flushes count is high
  1. Increase the value of the pdbuffer operand's -n option (number of global buffers).
  2. Re-evaluate the value specified for the pdbuffer operand. If multiple RDAREAs are allocated to one global buffer, separate the RDAREAs for tables to be updated from the RDAREAs for tables to be referenced, and allocate one global buffer to each type of RDAREA. If one RDAREA is allocated to one global buffer, store indexes and tables in separate RDAREAs and allocate a global buffer dedicated to indexes. Or, row-partition the tables.
  3. When performing deferred write processing (when the pd_dbsync_point operand is specified as sync or it is omitted), decrease the value of the pdbuffer operand's -w option (percentage of pages output in deferred write processing).
  4. When performing deferred write processing (when the pd_dbsync_point operand is specified as sync or it is omitted), decrease the value of the deferred write trigger request rate (pd_dbbuff_rate_updpage operand).
When the update buffer flushes count is low
There is no problem; however, to improve the hits rate even further, increase the number of global buffer sectors.
RDAREAs storing tables with no buffering effects should be combined into a single RDAREA; otherwise, more memory may be used than is necessary.

(5) Check the reference buffer flushes count (RFFLS)

Purpose
This information is collected in order to evaluate the validity of the reference buffer flushes count. If the number of times a free buffer is created to read new pages after writing a reference buffer into a HiRDB file (reference buffer flushes count) is high for the number of operations, the number of I/O operations increases, affecting adversely the transaction processing performance.
Evaluating the analysis results
When the reference buffer flushes count is too high for the number of operations
The number of reference buffers may have increased for one of the following reasons:
  • The number of global buffer pool sectors is small.
  • The ratio of the number of referencing processes to the number of operations is high.
As a result, buffer invalidation is occurring frequently in memory, or the same data is being fetched frequently in the buffer.
When the reference requests hits rate is low and the reference buffer flushes count is high
The number of update buffers may have increased for one of the following reasons:
  • The number of global buffer pool sectors is small.
  • The ratio of the number of update processes to the total number of processes is high (applicable when pd_dbbuff_lru_option=SEPARATE is specified or this operand is omitted).
As the amount of buffer space to be updated increases, the reference requests hits rate decreases. As a result, buffer invalidation occurs frequently in memory, or the same data is fetched frequently in the buffer.
Actions to be taken
When the reference buffer flushes count is high
If the reference requests hits rate is high, there is no problem. If it is low, the following actions can be taken:
  • Increase the value of the pdbuffer operand's -n option (number of global buffers).
  • Specify pd_dbbuff_lru_option=MIX.
When the reference buffer flushes count is low
There is no problem.

(6) Check the real READs count (READ) and real WRITEs count (WRITE)

Purpose
This information is collected in order to evaluate the validity of the number of disk I/O operations. If the number of disk I/O operations (HiRDB file I/O operations) is high for the total amount of processing, transaction processing performance and throughput are affected adversely. The number of I/O operations can be reduced efficiently by evaluating this information together with the information for each RDAREA that is provided as the HiRDB file information related to database accesses.
Evaluating the analysis results
When there are many input operations
  1. If many RDAREAs use the same global buffer and each RDAREA is accessed frequently, input operations may occur frequently due to a low buffer hits rate.
  2. If the reference requests hits rate is low, the page input operation may occur frequently due to the following factors:
  • The number of global buffer pool sectors is small (applicable when pd_dbbuff_lru_option=MIX is specified).
  • The ratio of the number of updating processes to the total amount of processing is too high for the reference buffer (applicable when pd_dbbuff_lru_option=SEPARATE is specified or this operand is omitted).
When there are many output operations
  1. If greater importance is placed on the update buffer hits rate than on the reference requests hits rate, the corresponding global buffer may be requested for update processing more frequently.
  2. If the update GETs count is greater than the reference GETs count, adverse effects may be caused by update buffer flushing, as described in (4) above.
Actions to be taken
When the reference requests hits rate is low and the number of input operations is high
  1. Increase the value of the pdbuffer operand's -n option (number of global buffers).
  2. Re-evaluate the value specified for the pdbuffer operand. If multiple RDAREAs are allocated to one global buffer, allocate one global buffer to a frequently accessed table. If the same global buffer pool is allocated to an index and table, allocate a global buffer pool dedicated to the index. If one RDAREA is allocated to one global buffer, row-partition the table.
  3. When performing deferred write processing (when the pd_dbsync_point operand is specified as sync or it is omitted), increase the value of the pdbuffer operand's -w option (percentage of pages output in deferred write processing).
  4. Specify pd_dbbuff_lru_option=MIX.
When there are many output operations
  1. pd_dbsync_point=commit specified
    There is no problem if the update request hits rate is high. If it is low, increase the value of the pdbuffer operand's -n option (number of global buffers).
  2. pd_dbsync_point=sync specified or this operand omitted
  • Increase the value of the pdbuffer operand's -n option (number of global buffers).
  • When performing deferred write processing (when the pd_dbsync_point operand is specified as sync or it is omitted), increase the value of the pdbuffer operand's -w option (percentage of pages output in deferred write processing). Also increase the synchronization point interval.

(7) Check the buffer lock-release waits count (WAITL)

Purpose
This information is collected in order to evaluate the validity of the global buffer pool allocations to RDAREAs. If the number of concurrently executed transactions increases, contention occurs on accesses to the same global buffer pool, resulting in lock-release wait status. When the number of buffer lock-release waits increases, transaction processing time also increases, and the global buffer pool allocations should be reevaluated.
Evaluating the analysis results
  1. If the buffer lock-release waits count is high for the total amount of processing, there may be many users using the same global buffer pool, resulting in high buffer access workload.
  2. If the buffer lock-release waits count is high for the total amount of processing, multiple tables may be defined for the same global buffer pool, resulting in concentration of buffer accesses.
Actions to be taken
  1. If multiple tables are stored in one RDAREA, allocate the storage RDAREA to each of the applicable global buffer pools.
  2. If multiple RDAREAs are allocated to one global buffer pool, reduce the number of RDAREAs allocated to that global buffer pool.
  3. If a table and index are both stored in the same RDAREA, store them in separate RDAREAs, and allocate a global buffer dedicated to the index.

(8) Check the prefetch input pages count (PRRED), prefetch hit pages count (PRHIT), and prefetch hits rate (HIT)

Purpose
This information is collected in order to evaluate the validity of the prefetch facility. In the case of retrieval of all items or retrieval by range, processing time can be reduced by using the prefetch facility. When a large amount of data is to be retrieved, the prefetch facility can influence the processing time.
Evaluating the analysis results
If the prefetch hits rate is 80% or less, the data or index storage pages for the table being retrieval may be fragmented.
Actions to be taken
Reorganize the table being retrieved. If the hits rate is extremely low but the table cannot be reorganized, do not use the prefetch facility (either omit the pdbuffer -m operand or specify 0 in the -m operand).

(9) Check the prefetch buffer shortages count (PRINS)

Purpose
This information is collected in order to evaluate the validity of the prefetch facility. If a shortage occurs in the buffer for prefetch processing, a user who is unable to execute a batch input operation cannot take advantage of prefetch processing.
Evaluating the analysis results
It is no problem if the prefetch buffer shortages count is 0. If it is other than 0, it can be considered that the maximum number of simultaneous prefetches is greater than the value specified in the pdbuffer operand's -m option.
Actions to be taken
Increase the value of the pdbuffer operand's -m option (maximum number of simultaneous prefetches). Also, reevaluate the memory requirements, because if this value is increased, the shared memory for global buffer pools also increases.