Buffer Pool Size (buffpage)

Configuration Type

Database

Parameter Type

Configurable

Default [Range]

UNIX 32-bit platforms

1 000 [ 2 -- 524 288 ]

UNIX 64-bit platforms

1 000 [ 2 -- 2 147 483 647 ]

OS/2 and Windows NT

250 [ 2 -- 524 288 ]

Unit of Measure

Pages

When Allocated

When the first application connects to the database

When Freed

When last application disconnects from the database

Related Parameters

• Changed Pages Threshold (chngpgs_thresh)
• Database Heap (dbheap)
• Number of Asynchronous Page Cleaners (num_iocleaners)

Each database has at least one buffer pool (IBMDEFAULTBP, which is created when the database is created), and can have more. All buffer pools reside in global memory, which is available to all applications using the database. The memory is allocated on the machine where the database is located. If the buffer pools are large enough to keep the required data in memory, less disk activity will occur. Conversely, if the buffer pools are not large enough, the overall performance of the database can be severely curtailed and the database manager can become I/O-bound as a result of a high amount of disk activity (I/O) required to process the data your application requires.

The buffpage parameter controls the size of a buffer pool when the CREATE BUFFERPOOL or ALTER BUFFERPOOL statement was run with NPAGES -1; otherwise, the buffpage parameter is ignored and the buffer pool will be created with the number of pages specified by the NPAGES parameter.

To determine whether the buffpage parameter is active for a buffer pool, do a:

  SELECT * from SYSCAT.BUFFERPOOLS.

Each buffer pool that has an NPAGES value of -1 uses buffpage.

There is a trade-off between the buffer pool size and the memory allocations of other system users. Memory requirements of database servers are so important on multi-user high transaction rate servers, that database servers and file or communication servers are often separated and reside on different machines.

If your queries access nicknames, consider increasing the buffer pool size when:

• The optimizer decides that most or all operations are completed locally. When a query is processed, the optimizer will usually push down operations to the data source where possible. As an example, a GROUP BY operator is usually evaluated at the data source. It is possible, however, that materializing the table at DB2 and performing an operation locally is the least cost route. This situation could occur if the DB2 server workstation is more powerful than the data source workstation.
• Sort operations must be completed locally. Queries containing nicknames are sorted according to the DB2 collating sequence. If a data source does not have the same collating sequence, all sort operations are performed locally.

All buffer pools are allocated when the first application connects to the database, or when the database is explicitly activated. As an application requests data out of the database, pages containing that data are transferred to one of the buffer pools from disk. (Note that database data is stored in pages within the tables on the disk.) Pages are not written back to disk until the page is changed and one of the following occurs:

• All applications disconnect from the database
• The database is explicitly deactivated
• The database quiesces (that is, all connected applications have committed)
• Its space is required for another page that needs to be read into the buffer pool
• A page cleaner is available (num_iocleaners) and is activated by the database manager.

Recommendations:

• Instead of using the buffpage configuration parameter, you can use the CREATE BUFFERPOOL and ALTER BUFFERPOOL SQL statements to create and change buffer pools and their sizes.
• The size of the buffer pool is used by the optimizer in determining access plans. You should consider rebinding applications (using the REBIND PACKAGE command) after changing this parameter.
• Because the sizes of all the buffer pools can have a major impact on performance, you should consider the following factors to ensure that excessive page swapping does not occur:
  • The amount of installed memory on your machine.
  • The memory required by other applications running concurrently with the database manager on the same machine.

  Page swapping results when there is not enough memory to hold the page that is being accessed. The result is that the page is written ("swapped") to temporary disk storage to make room for the other page. When the page on the temporary disk storage is needed, it is "swapped back" into memory.

• You may wish to allocate as much as 75% of the machine's memory to the database buffer pools when you have the following:
  • Multiple users
  • A machine used only as a database server
  • A large amount of repeated access to the same data and index pages
  • One database on the machine.
• For every buffer pool page allocated, some space is used in the database heap for internal control structures.

  If the total size of the buffer pool (or buffer pools) is increased, you may also need to increase dbheap.

• If the data source collating sequence matches the DB2 collating sequence, ensure that the server option collating_sequence is set to indicate so.