Analyzing DB2 buffer pool performance

Edit online

You must analyze the server performance for varying size of DB2® buffer pool to determine the optimum DB2 buffer pool size for your server.

Procedure

Run the following command to turn on buffer pool monitoring:

db2 update database manager configuration using DFT_MON_BUFPOOL ON
db2stop
db2start

Run the following command to connect to the database:
```
db2 connect to ldapdb2
```
where, ldapdb2 is the database instance.
Start the workload to be analyzed.
Reset the monitor data.
```
db2 resetmonitor all
```
Obtain the statistics with the workload in progress.
Take a snapshot of the buffer pool statistics and process the output. Run the following command to take a snapshot of the current buffer pool statistics:
```
db2 get snapshot for bufferpools on ldapdb2
```

Run the following command to take a snapshot and report the read times:

db2 get snapshot for bufferpools on idsdb | awk ’{ 
if($1=="Bufferpool" && $2=="name"){print  $0} 
if (index($0,"pool read time")){print "\t"$0} 
}’

Run the following command to take a snapshot and report the number of logical and physical reads:

db2 get snapshot for bufferpools on idsdb | awk ’{ 
if($1=="Bufferpool" && $2=="name"){print$0} 
if (index($0,"cal reads")){print "\t"$0} 
}’ | grep-vtemp

Run the following command to take a snapshot and report miss ratios:

db2 get snapshot for bufferpools on idsdb | grep -v temporary | 
awk ’{ if($1=="Bufferpool" && $2=="name"){print$0} 
if (index($0,"logical  reads")){l=$NF;getline;p=$NF; 
if (l==0){r=0}else{r=p/l};print "\tMiss ratio: "$3""r} 
}’

Tune the buffer pool sizes such that the IBMDEFAULTBP buffer pool meets the following conditions:
- A low read time.
- A low number of reads.
- A low miss ratio.
- Must not exceed system physical memory size.
- Must not reduce the LDAPBP size to less than 2075 pages of 32 KB.
A higher miss ratio indicates that there are a higher number of physical reads and a lower number of cache hits.

Allocate the remaining physical memory to the LDAPBP with the following criteria:

With file system cache turned off

LDAPBP size = ( total physical memory - 1 GB(for DS and DB2) 
- (IBMDEFAULTBP size) * 4096 ) / 32768

With file system cache turned on

LDAPBP size = ( total physical memory - 1.75GB (for DS,
 DB2, and file system caching) - (IBMDEFAULTBP size) * 4096 ) / 32768