Description of the DB2 buffer pool optimization and diagnosis Solution

The following article describes how to diagnose and optimize the DB2 buffer pool problem. At the same time, this article also provides background knowledge about the diagnosis and optimization of the DB2 buffer pool problem, the following describes the suggestions.

Pool problems, buffer, diagnose pool problems, buffer, and Diagnose

Background

The buffer pool is a storage area in the memory. It is used to temporarily read and modify the database pages that contain table rows or index items ). The purpose of the DB2 buffer pool is to improve the performance of the database system. Accessing data from the memory is much faster than accessing data from the disk.

Therefore, the fewer times the database manager needs to read or write data to the disk from the disk, the better the performance. Configuration of one or more buffer pools is the most important aspect of optimization because most data of applications connected to the database does not include large objects and long field data) all operations are performed in the DB2 buffer pool.

By default, the application uses the buffer pool ibmdefabp bp, which is created when the database is created. When the NPAGES value of the buffer pool in the SYSCAT. BUFFERPOOLS directory table is-1, the DB2 database configuration parameter BUFFPAGE controls the buffer pool size. Otherwise, the BUFFPAGE parameter is ignored and the buffer pool is created using the number of pages specified by the NPAGES parameter.

Suggestions

For applications that only use one buffer pool, change NPAGES to-1 so that the BUFFPAGE can control the size of the buffer pool. This makes it easier to update and report the buffer pool size and other DB2 database configuration parameters.

Make sure that you can use the BUFFPAGE parameter in the database configuration to control the buffer pool size and set this parameter to an appropriate value. This parameter is set to a reasonable shard based on the database size and application nature, which is safe. Generally, the default value of this parameter is very small and may not meet the requirements. Consider the following situations:

At first, if your machine has enough memory, set the BUFFPAGE to 40000 pages (160 MB), or equal to 10% of the total machine memory.

For large OLTP databases, you can leave as much memory as possible for the DB2 buffer pool while maintaining system stability. First, try to use 1.6 GB memory, and then try to use more memory.

How to change this parameter

Run the following script:

Verify directory Value

Enable Database Configuration Parameter BUFFPAGE

Update the BUFFPAGE value of all databases.

 
 

  
  
DB2 -v connect to DB_NAME  
  
  
DB2 -v select * from syscat.bufferpools  
  
  
DB2 -v alter bufferpool IBMDEFAULTBP size -1  
  
  
DB2 -v connect reset  
  
  
DB2 -v update db cfg for dbname using BUFFPAGE bigger_value  
  
  
DB2 -v terminate 
 
 

Study procedure

To determine whether the buffer pool size of the database is determined by the BUFFPAGE parameter, run:

 
 

  
  
DB2 -v connect to DB_NAME  
  
  
DB2 -v SELECT * from SYSCAT.BUFFERPOOLS  
  
  
DB2 -v connect reset  
  
  
DB2 -v terminate 
 
 

Check results. If each buffer pool has an NPAGES Value of "-1", the buffer pool size is controlled by the BUFFPAGE parameter in the database configuration.

To determine whether the buffer pool size is large enough, collect snapshots of the database and/or DB2 buffer pool when running the application. Similar to the following script to provide you with the required information:

 
 

  
  
DB2 -v update monitor switches using bufferpool on  
  
  
DB2 -v get monitor switches  
  
  
DB2 -v reset monitor all  
  
  
-- run your application --  
  
  
DB2 -v get snapshot for all databases > snap.out  
  
  
DB2 -v get snapshot for dbm >> snap.out  
  
  
DB2 -v get snapshot for all bufferpools >> snap.out  
  
  
DB2 -v reset monitor all  
  
  
DB2 -v terminate  
 
 

Make sure that you issue "DB2-v get snapshot" before disconnecting the database ". When the last application is disconnected from the database, the database stops running and all snapshot statistics are lost. To ensure that a connection keeps the database running properly, use one of the following methods:

Keep a separate connection in the snapshot collection window.

Use the DB2 activate database command.

In the snapshot output of the database snapshot or buffer pool snapshot, find the following "logical reads" and "physical reads" to calculate the buffer pool hit rate, which helps you tune the buffer pool:

 
 

  
  
-- Related lines from a sample of bufferpool snapshots --  
  
  
Buffer pool data logical reads = 702033 
  
  
Buffer pool data physical reads = 0 
  
  
Buffer pool data writes = 414 
  
  
Buffer pool index logical reads = 168255 
  
  
Buffer pool index physical reads = 0 
 
 

Buffer Pool hit rate indicates the percentage of time when the database manager can process page requests without loading the page from the disk, that is, the page is already in the DB2 buffer pool. The higher the buffer pool hit rate, the lower the disk I/O frequency. Calculate the buffer pool hit rate as follows:

 
 

  
  
(1 - ((buffer pool data physical reads + buffer pool index physical reads) /  
  
  
(buffer pool data logical reads + pool index logical reads))  
  
  
) * 100% 
 
 

This calculation takes into account all the page indexes and data cached by the buffer pool at high speed ). Ideally, this ratio should exceed 95% and be as close to 100% as possible. To increase the buffer pool hit rate, try the following methods:

Increase the buffer pool size.

Allocate multiple buffer pools. If possible, assign a DB2 buffer pool to the tablespace of each frequently accessed large table and a buffer pool to a small table, then try to use a buffer pool of different sizes to check which combination will provide the best performance.

If the allocated memory does not help improve performance, avoid allocating too much memory to the buffer pool. The size of the buffer pool should be determined based on the snapshot information taken from the test environment.