Allocate Memory Optimally to the Bufferpool and Sortheap for DB2 Databases on Itanium-Based Systems

Submit New Article

February 28, 2009 11:00 PM PST


Challenge

Allocate memory to the bufferpool and sortheap for high performance of IBM DB2* databases on Itanium®-based systems. DB2 allocates memory mainly on the logical-node level for bufferpool(s), sortheap, and other uses.

Solution

Size the bufferpool and sortheap to use most of the available physical memory (>90%). Consider a benchmark trial where the bufferpool was allocated 375,000 pages and sortheap was allocated 75,000 pages. The following table summarizes memory usage:

The peak of committed memory is 29,182,062,720 bytes, or about 27.18GB, in which 27GB was used by DB2 or 6.8GB per logical node. This data demonstrates the advantage of 64-bit environments; in a 32-bit system, each logical node can only use up to 3GB.

Starting in DB2 Version 8.1.4, you can set the size for bufferpool memory allocations using the DB2_ALLOCATION_SIZE registry variable. Setting this variable to a higher value means that it will require fewer allocations to reach the desired amount of memory that is allocated to a bufferpool.

When working with the sortheap, you should consider the following:

  • Appropriate indexes can minimize the use of the sortheap.
  • Hash join buffers and dynamic bitmaps (used for index ANDing and Star Joins) use sortheap memory. Increase the size of this parameter when these techniques are used.
  • Increase the size of this parameter when frequent large sorts are required.
  • When increasing the value of this parameter, you should examine whether the sheapthres parameter in the database manager configuration file also needs to be adjusted.
  • The sortheap size is used by the optimizer in determining access paths. You should consider rebinding applications (using the REBIND command) after changing this parameter.

 

Sources

This KnowledgeBase item contains text drawn verbatim from the following sources:

Tuning IBM DB2* databases for Intel® Itanium®-based systems*

IBM DB2* Information Management Software Information Center entry "Setting buffer pool memory allocation size*"

IBM DB2* Information Management Software Information Center entry "Sort Heap Size configuration parameter – sortheap*"