Using the pdbufls command to collect statistical information

Purpose: This information is collected to determine whether 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 hit rate (HIT) is 80% or higher.

Actions to be taken

Global buffer pool hit rate is below 80%: Increase the value of the pdbuffer operand's -n option (number of global buffers).

Global buffer pool hit rate is 80% or better: There is no problem; however, to improve the hit rate even further, increase the number of global buffer sectors.

(2) Check the update requests hit rate (HIT (UPD))

Purpose: This information is collected in order to determine how appropriate the number of global buffer sectors and the deferred write processing executed internally by HiRDB are.; 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 hit 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:

Check that the update requests hit rate is not too low
Check that the reference buffer hit rate is not too low even if the update requests hit rate is high.

The update requests hit rate is obtained with the following formula:

Update requests hit rate (%) = (update GET requests hits count [Figure]

update GETs count) [Figure]

100

Actions to be taken

When the update requests hit rate is low

Increase the value of the pdbuffer operand's -n option (number of global buffers).
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.
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 hit rate is high: There is no problem.

(3) Check the reference requests hit rate (HIT (REF))

Purpose: This information is collected in order to determine how appropriate the reference requests hit rate is. 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 hit rate is close to 80%. The reference requests hit rate is obtained with the following formula:; Reference requests hit rate (%) = (reference requests hits count reference GETs count) 100

Actions to be taken

When the reference requests hit rate is 80% or lower

Increase the value of the pdbuffer operand's -n option (number of global buffers).
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.
If the reference requests hit rate is too low and the reference page flush count is too high for the update requests hit rate, specify pd_dbbuff_lru_option=MIX.
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 hit rate is higher than 80%: There is no problem.

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

Purpose: This information is collected in order to determine how appropriate the update buffer flushes count is. 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 might 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

Increase the value of the pdbuffer operand's -n option (number of global buffers).
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.
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 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.

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

Purpose: This information is collected in order to determine how appropriate the reference buffer flushes count is. If, for the number of operations, there is a high value for the number of times a free buffer is created to read new pages after the content of the referenced buffer is flushed (the reference buffer flushes count), the number of I/O operations increases, adversely affecting 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 might 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 hit rate is low and the reference buffer flushes count is high

The number of update buffers might 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 hit 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 hit 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 determine how appropriate the number of disk I/O operations is. 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

If many RDAREAs use the same global buffer and each RDAREA is accessed frequently, input operations might occur frequently due to a low buffer hit rate.
If the reference requests hit rate is low, the page input operation might 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

If greater importance is placed on the update buffer hit rate than on the reference requests hit rate, the corresponding global buffer might be requested for update processing more frequently.
If the update GETs count is greater than the reference GETs count, adverse effects might be caused by update buffer flushing, as described in (4) above.

Actions to be taken

When the reference requests hit rate is low and the number of input operations is high

Increase the value of the pdbuffer operand's -n option (number of global buffers).
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.
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).
Specify pd_dbbuff_lru_option=MIX.

When there are many output operations

pd_dbsync_point=commit specified
There is no problem if the update request hit rate is high. If it is low, increase the value of the pdbuffer operand's -n option (number of global buffers).
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 determine how appropriate the global buffer pool allocations to RDAREAs are. 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

If the buffer lock-release waits count is high for the total amount of processing, there might be many users using the same global buffer pool, resulting in high buffer access workload.
If the buffer lock-release waits count is high for the total amount of processing, multiple tables might be defined for the same global buffer pool, resulting in concentration of buffer accesses.

Actions to be taken

If multiple tables are stored in a single RDAREA, divide and store these tables in multiple RDAREAs and allocate each RDAREA to a separate global buffer.
If multiple RDAREAs are allocated to one global buffer pool, reduce the number of RDAREAs allocated to that global buffer pool.
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 insufficient buffer count (INSB)

Purpose: This information is collected in order to determine how appropriate the buffer sector count of the global buffer pool is. If all global buffers available to read the pages to be accessed are in use, HiRDB invalidates the transaction. Therefore, if the buffer sector count is small, some transactions might not be executed if a large number of transactions must be executed within a short time period.

Evaluating the analysis results

If the following condition is applicable, you can assume that the buffer sector count is small:

The buffer shortage occurrence count is not 0.

Actions to be taken

Increase the value of the pdbuffer operand's -n option (number of buffers).
For RDAREAs with concentrated I/O operations, do not have multiple RDAREAs share a single global buffer. Instead, allocate a dedicated global buffer pool to each RDAREA.

(9) Check the prefetch input pages count (PRRED) and prefetch hit rate (PRHIT)

Purpose: This information is collected in order to determine how appropriate it is to use 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 hit rate is 80% or less, the data or index storage pages for the table being retrieval might be fragmented.

Actions to be taken: Reorganize the table being retrieved. If the hit 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).

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

Purpose: This information is collected in order to determine how appropriate it is to use 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.

23.1.1 Using the pdbufls command to collect statistical information

(1) Check the global buffer pool hit rate (HIT)

(2) Check the update requests hit rate (HIT (UPD))

(3) Check the reference requests hit rate (HIT (REF))

(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 insufficient buffer count (INSB)

(9) Check the prefetch input pages count (PRRED) and prefetch hit rate (PRHIT)

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