Efficient I/O managment | DB2 objects management


Memory usage


+

Search Tips   |   Advanced Search

DB2 associates memory for the database through the use of bufferpool objects. A bufferpool has a page size associated with it and is linked to one or more tablespaces. Thus, if tablespaces of different page sizes are created, then bufferpools corresponding to the different page sizes are required.

While you can create multiple bufferpools having the same page size, IBM recommends that only one bufferpool per page size be created, for the most efficient usage of memory on the database server.

The question is always how much memory to assign to the bufferpools. For DB2 32-bit implementations, there is a limit, based on the operating system, that can be available for bufferpools. This ranges from a maximum of 1.5 GB on AIX platforms to 3.3 GB on Solaris.

Assuming a dedicated database server, a general rule of thumb is to allocate a large proportion of memory available on the server, about 75% to 80%, but not exceed the platform limits.

Note that for 64-bit implementations of DB2, the limits are significantly increased. In this case, the bufferpool hit ratio would need to be monitored to determine the optimal setting for the bufferpools. You can also monitor the hit ratio for 32-bit implementation using database snapshots using the following command:

db2 get snapshot for database on <dbalias>

The output generated will contain some statistics on bufferpool logical and physical reads:

Buffer pool data logical reads             = DLR
Buffer pool data physical reads            = DPR
...
Buffer pool index logical reads            = ILR
Buffer pool index physical reads           = IPR

In this output, DLR, DPR, ILR, and IPR will have actual values. The hit ratio can be computed using the following formula:

(1 - (( DPR + IPR) / (DLR + ILR))) * 100%

The size of the bufferpool can be changed using the ALTER BUFFERPOOL command, or the BUFFPAGE parameter if the size of the bufferpool is set to -1.