Using the statistics analysis utility to collect statistical information

Purpose: This information is collected in order to determine whether the buffer sector count of the global buffer pool is appropriate. This is the most important item to be aware of when tuning the global buffer pool.

Evaluating the analysis results: Check whether the global buffer pool hit rate (GBHIT) is 80% or higher.

Actions to be taken

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

When the global buffer pool hit rate is 80% or higher: There is no problem. To improve the hit rate further, increase the number of global buffer sectors.

(2) 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, 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 writing an updated buffer into a HiRDB file (update buffer flushes count), the number of disk I/O operations increases, adversely affecting 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 table.
When deferred write processing is being performed (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).
When deferred write processing is being performed (when the pd_dbsync_point operand is specified as sync or it is omitted), decrease the percentage of deferred write trigger requests (pd_dbbuff_rate_updpage operand).

When the update buffer flushes count is low: There is no problem.

(3) 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 flushing the referenced buffer (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 number of operations 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 is occurring frequently in memory, or the same data is being 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. However, 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.

(4) 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 number of processing operations, transaction processing performance and throughput are adversely affected. 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

If 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 when this operand is omitted).

If 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 (2) Check the update buffer flushes count (UPFLS).

Actions to be taken

If 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.
If deferred write processing is being performed (if the pd_dbsync_point operand is specified as sync or if 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.

If there are many output operations

If pd_dbsync_point=commit is 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).
If pd_dbsync_point=sync is specified, or this operand is omitted

Increase the value of the pdbuffer operand's -n option (number of global buffers).
If deferred write processing is being performed (if the pd_dbsync_point operand is specified as sync, or if 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.

(5) Check the output waits count (WAITW)

Purpose

This information is collected in order to determine how appropriate the global buffer allocations to RDAREAs are. 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:

If update request contention occurs when data is being output to HiRDB files, HiRDB file output completion wait occurs.

Evaluating the analysis results

Output waits might 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 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).

(6) 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 when accesses are made 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, so you must re-evaluate the global buffer pool allocations.

Evaluating the analysis results

If the buffer lock-release waits count is high for the number of processing operations, there might be many users using the same global buffer pool, resulting in high buffer access workload.
Also, if the buffer lock-release waits count is high for the number of processing operations, another explanation might be that multiple tables are defined for the same global buffer pool, resulting in a 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.

(7) Check the maximum concurrent request buffer sectors count (MAXB) and the buffer shortages count (BFINS)

Purpose: This information is collected in order to determine how appropriate the number of buffer sectors in a 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 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 hit rate is high; maximum performance seems to be achieved when the reference or update buffer hit 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.

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

Purpose: This information is collected in order to determine how appropriate is to use the prefetch facility. When you retrieve all items or retrieve items by range, processing time can be reduced by using the prefetch facility. When a large amount of data needs to be retrieved, the prefetch facility can also reduce 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 retrieved 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 -m option of the pdbuffer operand or specify 0 in the -m option).

(9) 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: There is no problem if the prefetch buffer shortages count is 0. If it is other than 0, it can be concluded 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, re-evaluate the memory requirements, because if this value is increased, the shared memory for global buffer pools also increases.

(10) Check the number of synchronization point dump pages (SYNCW)

Purpose: This information is collected in order to determine how appropriate the synchronization point dump output interval is. 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).

(11) 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: 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.

(12) Check the average value for the sleep processing execution count in buffer lock processing (SLEPA)

Purpose: The purpose is to determine whether 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 might 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: 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.

(13) 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 might 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. We recommend 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 HiRDB Version 9 Installation and Design Guide.

23.1.2 Using the statistics analysis utility to collect statistical information

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

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

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

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

(5) Check the output waits count (WAITW)

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

(7) Check the maximum concurrent request buffer sectors count (MAXB) and the buffer shortages count (BFINS)

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

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

(10) Check the number of synchronization point dump pages (SYNCW)

(11) Check the lock-release contention rate (SLEPR) in global buffer lock processing

(12) Check the average value for the sleep processing execution count in buffer lock processing (SLEPA)

(13) Check the take-over count of database write processing by the reference request hit during synchronization point processing (ALTRW)