Db2 DPF Pools Test

A buffer pool is an area of memory into which database pages are read, modified, and held during processing.

Buffer pools improve database performance. If a needed page of data is already in the buffer pool, that page is accessed faster than if that page had to be read directly from disk. The database manager has agents whose tasks are to retrieve data pages from disk and place them in the buffer pool (prefetchers), and to write modified data pages from the buffer pool back to disk (page cleaners).

The reading and writing of data pages to and from disk is called disk input/output (I/O). Avoiding the wait associated with disk I/O is the primary way to improve the performance of the database. How you create the buffer pool, and configure the database manager and the agents associated with the buffer pool, controls the performance of the database. Through SQL and configuration parameters, you can control the size of the buffer pool, the number of prefetchers and page cleaners that move data pages into and out of the buffer pool, the size of the data pages, and the number of data pages that can be moved at one time.

In Figure 3, we showed bufferpools split across the different partitions. Interpreting this figure for buffer pools, is different than for the other objects, because the data cached in the bufferpools is not partitioned as the figure may imply. What is actually happening is that buffer pools in a DPF environment can be tailored to the different partitions. Using the CREATE BUFFERPOOL statement with the DATABASE PARTITION GROUP clause, you can associate a bufferpool to a given partition group. What this means is that you have the flexibility to define the buffer pool to the specific partitions defined in the partition group. In addition, the size of the buffer pool on each partition in the partition group can be different if desired.

The statistics reported by the Db2 Pools test help administrators analyze the usage of the buffer pools, and provides them with useful pointers to fine-tune the configuration of the buffer pools.

Target of the test : A DB2 database server with DPF enabled

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every logical partition of each database on the DB2 database server that is currently active

Configurable parameters for the test
TEST PERIOD – How often should the test be executed Host – The IP address of the DB2 server Port – The port number through which the DB2 server communicates. The default port is 50000. user - Specify the name of the user who has any of the following privileges to the specified DATABASE: SYSADM or SYSCTRL or SYSMAINT or SYSMON. You can create a separate user on the OS hosting the DB2 server for this purpose, and assign any of the aforesaid privileges to that user. The steps for the same are detailed in the Creating a Special User for Monitoring DB2. password - Enter the password of the specified USER in the PASSWORD text box. confirm password – Confirm the password by retyping it here. database - Specify the name of the database on the monitored DB2 server to be used by this test

Measurements made by the test
Measurement	Description	Measurement Unit	Interpretation
Buffer Pool Hit Ratio:	Indicates the percentage of the pages requested that is readily available in the memory of this logical partition without doing disk I/O.	Percent	This measure is an overall indicator of how well the buffer pool is performing. Lower values indicate that more physical I/O is happening than logical. Since physical I/O costs more, maintaing higher buffer hit ratio is desired. Typically a hit ratio over 90% shows that the buffer pool is performing well. If this measure is consistently low, increase the size of the buffer pool by increasing BUFFPAGE configuration value.
Buffer Pool Hit Ratio (Data):	Indicates the percentage of the data pages requested that is readily available in the memory of this logical partition without doing disk I/O.	Percent	This measure is an indicator of how well the buffer pool is performing for the data page requests. Lower values indicate that more physical I/O is happening than logical. Since physical I/O costs more, maintaing higher buffer hit ratio is desired. Typically a hit ratio over 90% shows that the buffer pool is performing well. If this measure is consistently low, increase the size of the buffer pool by increasing BUFFPAGE configuration value.
Buffer Pool Hit Ratio (Index):	Indicates the percentage of the index pages requested that is readily available in the memory of this logical partition without doing disk I/O.	Percent	This measure is an overall indicator of how well the buffer pool is performing. Lower values indicate that more physical I/O is happening than logical. Since physical I/O costs more, maintaing higher buffer hit ratio is desired. Typically a hit ratio over 90% shows that the buffer pool is performing well. If this ratio is really low and the overall ratio is relatively high, then break the index(s) out into their own tablespace/buffer pool.
Pre Fetch Ratio:	The ratio of asynchronous reads to synchronous reads. The value indicates how effectively the DB2 database manager is populating the buffer pools through the use of prefetchers.	Percent	High value indicates more asynchronous I/O is happening than synchronous. The value can be used to tune the num_ioservers configuration parameter.
Percent Log Cleans:	Indicates the percentage of times a page cleaner was invoked because the logging space used had reached a predefined criterion for the database logical partition.	Percent	If this value is high (say > 40%), this could mean that page cleaners are constantly being utilized to clean the log and aren’t available for other page cleaning activities, hampering performance. On the other hand, if the value is low, (say < 10%) then the page cleaners aren’t being triggered as often for this activity. this means that they would be more available for the other types of page cleaning activities, which is great for buffer pool performance.
Percent Dirty Page Cleans:	Indicates the percentage of times a page cleaner was invoked because the buffer pool defined for this logical partition had reached the dirty page threshold criterion for the database.	Percent	The threshold is set by the chngpgs_thresh configuration parameter. It is a percentage applied to the buffer pool size. When the number of dirty pages in the pool exceeds this value, the cleaners are triggered. If this value is set too low, pages might be written out too early, requiring them to be read back in. If set too high, then too many pages may accumulate, requiring users to write out pages synchronously.
Percent Victim Cleans:	Indicates the percentage of times the page cleaner(s) were triggered to oust a victim page from the buffer pool. A victim page is a clean or dirty page in the buffer pool that is removed simply because DB2 needs to make room for incoming pages. If a victim page is a dirty page then the information must be written out to disk. Any page that is removed will most likely cause more physical I/O to occur in order to retrieve it again at later time when DB2 is ready to use it.	Percent	If the ratio is higher then the above two then that is typically a good indicator that the buffer pool needs to be larger since there never seems to be enough room for new pages to be brought in. This could also be a sign that dirty pages are staying in the buffer pool too long which could mean that the changed pages threshold (CHNGPGS_THRESH) is set too high. Even the SOFTMAX parameter could be set too high and too much of the changed pages that are logged are not getting flushed out to make way for new pages. If this ratio is low, it may indicate that you have defined too many page cleaners. If your chngpgs_thresh is set too low, you may be writing out pages that you will dirty later. Aggressive cleaning defeats one purpose of the buffer pool, that is to defer writing to the last possible moment.
Catalog Cache Hit Ratio:	Indicates the percentage of time the requested information for table descriptor or authorization was readily available in catalog cache without requiring to perform disk I/O.	Percent	The catalog cache is referenced whenever a table, view, or alias name is processed during the compilation of an SQL statement. If the ratio is greater than 80%, then the catalog cache is performing well. A smaller value indicates that the catalog cache size should be increased by tuning the parameter CATALOGCACHE_SZ in the database configuration. The value may be low immediately following the first connection to the database. The execution of Data Definition Language (DDL) SQL statements involving a table,view, or alias will evict the table descriptor information for that object from the catalog cache causing it to be re-inserted on the next reference. Therefore, the heavy use of DDLs may also increase the value of the measure.
Package cache hit ratio:	The package and section information required for the execution of dynamic and static SQL statements are placed in the package cache as required. This information is required whenever a dynamic or static statement is being executed. The ratio indicates the effectiveness of package cache hit ratio.	Percent	If the hit ratio is high (more than 80%), the cache is performing well. A smaller ratio may indicate that the package cache size (pckcachesz) should be increased.