DB2 Purescale Bufferpools Test

In DB2 pureScale, buffer pools play a crucial role in managing memory and optimizing performance across the clustered database environment.

A buffer pool is a region of memory used to cache data pages read from disk. When a query accesses data, DB2 first checks the buffer pool to see if the data is already in memory—this avoids costly disk I/O operations.

In pureScale, buffer pools are managed independently on each member node, but coordinated through the Cluster Caching Facility (CF) to maintain consistency.

Monitoring the DB2 pureScale is crucial for ensuring Improved Performance, Scalability, and Data Consistency.

The DB2 Purescale Bufferpools Test continuously monitors the Purescale Bufferpools , and reports the metrics like -Data physical reads, Index physical reads, XDA physical reads, Sync writes percentage and Total writes etc.

Target of the test : A DB2 Purescale database cluster.

Agent deploying the test : An internal agent

Outputs of the test : One set of results for the target DB2 Purescale cluster being monitored

Configurable parameters for the test
Parameter	Description
Test Period	How often should the test be executed.
Host	Specify the IP address of the DB2 server in this text box.
Port	Specify the port at which the target host is listening. The default port is 50000.
Username, Password	To monitor Db2 Purescale database server, the eG agent should be configured with the credentials of a user with any of the following privileges SYSADM or SYSCTRL or SYSMAINT or SYSMON. Specify the credentials of such a user in the User and Password text boxes. Confirm the Password by retyping it in the Confirm Password text box.
Database	The test uses a database on the monitored Db2 Purescale. Specify the name of the database in the Database text box.
Include DB	Specify the list of databases that you wish to monitor in a comma-seperated list in the Include DB text box. By default this is set to ALL.
Exclude DB	Specify the list of databases that you wish to exclude from monitoring in a comma-seperated list in the Exclude DB text box. By default this is set to NONE.
SSL	If the target database server monitored is SSL-enabled, then set the SSL flag to Yes. If not, then set the SSL flag to No.
Include Available Nodes	In the Include Available Nodes text box, provide a comma-separated list of all the available nodes to be included for monitoring. This way, the test monitor and collect metrics from all the available nodes in the cluster. By default, this parameter is set to none. The format of this configuration is: HOSTNAME:PORT, for example, 172.16.8.136:3306,172.16.8.139:3306
Confirm Password	Confirm the Password by retyping it in the Confirm Password text box.

Measurements made by the test
Measurement	Description	Measurement Unit	Interpretation
Data physical reads	Indicates the number of physical data reads from bufferpool per second.	Reads/Second	High number of physical reads depict good working condition of bufferpool.
Data hit ratio percentage	Indicates the percentage of data reads from bufferpool which are successful	Percent	If the hit ratio is high it means the buffers are working well. High value is expected.
Index physical reads	Indicates the number of physical index reads from bufferpool per second.	Reads/Second	High number of physical reads depict good working condition of bufferpool.
Index hit ratio percentage	Indicates the percentage of index reads from bufferpool which are successful	Percent	If the hit ratio is high it means the buffers are working well. High value is expected.
XDA physical reads	Indicates the number of physical XDA reads from bufferpool per second.	Reads/Second	High number of physical reads depict good working condition of bufferpool.
XDA hit ratio percentage	Indicates the percentage of XDA reads from bufferpool which are successful.	Percent
COT physical reads	Indicates the number of physical COT reads from bufferpool per second.	Reads/Second	High number of physical reads depict good working condition of bufferpool.
COT hit ratio percentage	Indicates the percentage of COT reads from bufferpool which are successful.	Percent
Total physical reads	Indicates the total number of physical data, index, COT and XDA reads from bufferpool per second.	Reads/Second	High number of physical reads depict good working condition of bufferpool.
Average physical read time	Indicates the average time taken by reads.	Seconds	Average physical read time should be optimal an should ideally remain stredy, a upward or downward trend need to investigated.
Prefetch ratio percentage	Indicates the percentage of prefetch from bufferpool out of total reads.	Percent
Asynchronous not read percentage	Indicates the percentage of asynchronous not reads from bufferpool out of total reads.	Percent
Total writes	Indicates the total number of writes to the bufferpool.	Writes/Second
Average write time	Indicates the average time taken by writes.	Seconds
Sync writes percentage	Indicates the percentage of sync writes.	Percent
GBP data hit ratio percentage	Indicates the percentage of GBP data successful reads out of total GBP reads from buffer pool.	Percent	If the hit ratio is high it means the buffers are working well. High value is expected.
GBP index hit ratio percentage	Indicates the percentage of GBP index successful reads out of total GBP reads from buffer pool.	Percent	If the hit ratio is high it means the buffers are working well. High value is expected.
GBP XDA hit ratio percentage	Indicates the percentage of GBP XDA successful reads out of total GBP reads from buffer pool.	Percent	If the hit ratio is high it means the buffers are working well. High value is expected.
GBP COT hit ratio percentage	Indicates the percentage of GBP COT successful reads out of total GBP reads from buffer pool.	Percent	If the hit ratio is high it means the buffers are working well. High value is expected.
Average synchronous read time	Indicates the average time for synchronous reads,	Seconds
Average asynchronous read time	Indicates the average time for asynchronous reads,	Seconds
Average synchronous write time	Indicates the average time for synchronous writes.	Seconds
Average asynchronous write time	Indicates the average time for asynchronous writes.	Seconds