Progress Buffer Cache Test

The buffer pool is used to keep copies of popular database blocks in memory to minimize disk I/O. There are a number of complex data structures involved, but the most important are the buffers themselves and the buffer headers. Each buffer contains a copy of one database block and is the same size as a database block. For each buffer, there is a buffer header that describes what is in the buffer and some information about it (updated or not, which database block it is, whether the buffer is locked or not, and so forth). The buffer headers are around 150 bytes long. There are two different buffer pools in the progress database. They are:

• Primary buffer pool
• Alternate buffer pool

Whenever a user requests data from the database, for the very first time, the data is fetched from the disk and stored in the buffer pool. Subsequent requests for the same data are catered from the buffer pool. Sometimes, the buffer pool may not be able to cater the data due to unavailability of the data, buffer overhead etc leading to an increase in disk I/O and a decrease in the efficiency of the buffer pool. This may lead to over-utilization of the disk. Therefore, it is essential to monitor the buffer pools of the Progress database server. The Progress Buffer Cache test helps administrators in this regard!

This test auto-discovers the buffer pools in the Progress database server and for each buffer pool, this test reports how well the read/write operations were performed on the database block? In addition, this test also reports the rate at which the read/write operations were performed on the disk. Using this test administrators can figure out how well the buffer pools are utilized and optimize the buffer pools so that the disk I/O is reduced. The query response time to retrieve the data can also be improved considerably.

Target of the test : A Progress Database server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every buffer cache of the target Progress database server that is to be monitored

Configurable parameters for the test

Parameter	Description
Test Period	How often should the test be executed
Host	The IP address of the Progress database server.
Port	The port number on which the database server is listening. By default, this is NULL.
Database Name	Specify the name of the Progress database instance that is to be monitored.
Username	In order to monitor a Progress database instance, a special database user account has to be created in every Progress database instance that requires monitoring. This special user needs to be granted a set of privileges. To know how to create the database user and grant the required privileges, refer to Pre-Requisites for monitoring the Progress database. Specify the name of such a user in this text box.
Password	The password of the specified Username.
Confirm Password	Confirm the password by retyping it here.

Measurements made by the test

Measurement	Description	Measurement Unit	Interpretation
Logical reads	Indicates the rate at which read operations were performed on the database block of this buffer pool during the last measurement period.	Reads/sec	A high value is desired for these measures. A low value is indicative of ineffective cache usage, typically caused by improper cache size.
Logical writes	Indicates the rate at which write operations were performed on the database block of this buffer pool during the last measurement period.	Writes/sec
Physical reads	Indicates the rate at which the client requests to this buffer pool directly executed on and read the database blocks from the physical disk.	Reads/Sec	Physical database reads can cause processing overheads to escalate, thereby affecting database performance. These transactions therefore have to be minimized. A high value could indicate that direct disk accesses are occurring too frequently. This in turn implies poor cache usage. You may consider resizing your cache to accommodate more number of queries, so that direct disk reads are reduced.
Physical writes	Indicates the rate at which the database blocks were directly written to the disk instead of this buffer pool.	Writes/Sec
Hit ratio	Indicates the percentage of database blocks that were served from this buffer pool (i.e., without requiring a read from the disk).	Percent	A high value is desired for this measure. A sudden/gradual decrease in the value of this measure is a cause of concern as this may lead to an abnormal increase in the disk I/O.