9.5.3  Buffer pools

The default size for buffer pools is very small: only 250 pages (~ 1 MB) for 

Windows and 1000 pages (~ 4 MB) for Linux and UNIX platforms. The overall 

buffer size has a great effect on DB2 UDB performance since it can significantly 

reduce I/O, which is the most time consuming operation. We recommend to 

increase the default values. However, the total buffer pool size should not be set 

too high, because there might be not enough memory to allocate them. To 

calculate the maximum buffer size, all other DB2 memory related parameters like 

database heap, the agent's memory, storage for locks, as well as the operating 

system and any other applications should be considered.

Initially, set the total size of buffer pools to 10% to 20% of available memory. You 

can monitor the system later and correct it. DB2 version 8 allows changing buffer 

pool sizes without shutting down the database. The 

ALTER BUFFERPOOL

 statement 

with the 

IMMEDIATE

 option will take effect right away, except when there is not 

enough reserved space in the database shared memory to allocate new space. 

This feature can be used to tune database performance according to periodical 

changes in use, for example, switching from daytime interactive use to nighttime 

batch work.

Once the total available size is determined, this area can be divided into different 

buffer pools to improve utilization. Having more than one buffer pool can preserve 

data in the buffers. For example, let us suppose that a database has many very 

frequently used small tables, which would normally be in the buffer in their 

entirety, and thus would be accessible very fast. Now let us suppose that there is 

a query that runs against a very large table, which uses the same buffer pool and 

involves reading more pages than the total buffer size. When this query runs, the 

pages from the small, very frequently used tables will be lost, making it 

necessary to re read them when they are needed again.

At the start you can create additional buffer pools for caching data and leave the 

IBMDEFAULTBP

 for system catalogs. Creating an extra buffer pool for system 

temporary data also can be valuable for the system performance, especially in an 

OLTP environment where the temporary objects are relatively small. Isolated 

temporary buffer pools are not influenced by the current workload, so it should 

take less time to find free pages for temporary structures, and it is likely that the 

modified pages will not be swapped out to disk. In a warehousing environment, 

the operation on temporary table spaces are considerably more intensive, so the 

buffer pools should be larger, or combined with other buffer pools if there is not 

enough memory in the system (one pool for caching data and temporary 

operations).

Example 9 32 shows how to create buffer pools assuming that an additional table 

space 

DATASPACE

 for storing data and indexes was already created and that there 

 Chapter 9. Testing and tuning 

301