DB2 - Bufferpools

Db2 - Buffer Pools

This chapter introduces you to Buffer Pools, a critical component for database performance tuning in Db2.

Introduction to Buffer Pools

A buffer pool is a dedicated portion of the database server’s main memory (RAM) allocated by the Db2 database manager. Its primary purpose is to act as a high-speed cache for table and index data pages that are read from or written to disk. By keeping frequently accessed data in memory, Db2 significantly reduces slow disk I/O operations, leading to substantial performance gains.

Every database is created with a default buffer pool named IBMDEFAULTBP. When data is requested, Db2 first checks if the data page is in the buffer pool. If it is (a ‘hit’), the data is retrieved directly from memory. If not (a ‘miss’), the page is read from disk into the buffer pool first, then provided to the application. Pages that are modified in the buffer pool but not yet written back to disk are known as ‘dirty pages’. These are flushed to disk periodically by database agents.

The Relationship Between Tablespaces and Buffer Pools

A crucial rule in Db2 is that every tablespace must be associated with a specific buffer pool. This association is a strict one-to-one mapping in terms of page size: a tablespace can only be associated with a buffer pool that has the exact same page size. For example, a tablespace created with an 8 KB page size must use a buffer pool also defined with an 8 KB page size. However, a single buffer pool can serve multiple tablespaces (as long as their page sizes match).

Strategically creating multiple buffer pools allows for fine-grained performance tuning. You can isolate different types of data, such as separating index data from table data or high-transaction OLTP data from large-scan reporting data, into different buffer pools to optimize memory usage and cache efficiency.

Modern Buffer Pool Management: Self-Tuning Memory Manager (STMM)

Modern Db2 versions strongly advocate for using the Self-Tuning Memory Manager (STMM). When a buffer pool’s size is set to AUTOMATIC, STMM dynamically adjusts its size based on the current workload. It intelligently distributes available memory among various consumers (like buffer pools, sort heaps, etc.) to optimize overall database performance. For most scenarios, enabling STMM is the recommended best practice, simplifying administration and often yielding better results than manual tuning.

Querying Buffer Pool Information

To list all buffer pools in the current database and their configurations, you can query the system catalog view SYSCAT.BUFFERPOOLS.

SELECT
    BPNAME,
    BUFFERPOOLID,
    PAGESIZE,
    NPAGES
FROM SYSCAT.BUFFERPOOLS;

To monitor the effectiveness of your buffer pools, you can use monitoring functions like MON_GET_BUFFERPOOL. This example calculates the hit ratio, which indicates the percentage of page requests satisfied from memory.

SELECT
    BP_NAME,
    POOL_DATA_L_READS,
    POOL_DATA_P_READS,
    CASE WHEN POOL_DATA_L_READS = 0 THEN 0 ELSE
        DEC(100 * (1 - (FLOAT(POOL_DATA_P_READS) / FLOAT(POOL_DATA_L_READS))), 5, 2)
    END AS HIT_RATIO_PERCENT
FROM TABLE(MON_GET_BUFFERPOOL('',-2));

A high hit ratio (typically > 95%) is desirable and indicates the buffer pool is effectively caching data.

Creating a Buffer Pool

When creating a buffer pool, you must specify a unique name and a page size. The page size must match the tablespaces you intend to assign to it. Available page sizes are 4 KB, 8 KB, 16 KB, and 32 KB.

Syntax:

CREATE BUFFERPOOL <bufferpool_name>
IMMEDIATE -- Or DEFERRED
SIZE <number_of_pages> -- For manual sizing, or SIZE AUTOMATIC for STMM
PAGESIZE <page_size_in_bytes>; -- e.g., 8192 for 8 KB

Example: Create a new 8 KB buffer pool named BP_DATA_8K with its size managed automatically by STMM.

CREATE BUFFERPOOL BP_DATA_8K
IMMEDIATE
SIZE AUTOMATIC
PAGESIZE 8192;

Output:

DB20000I  The SQL command completed successfully.

Dropping a Buffer Pool

You can only drop a buffer pool if no tablespaces are currently assigned to it. The default buffer pool, IBMDEFAULTBP, cannot be dropped.

Syntax:

DROP BUFFERPOOL <bufferpool_name>;

Example: Drop the BP_DATA_8K buffer pool.

DROP BUFFERPOOL BP_DATA_8K;

Output:

DB20000I  The SQL command completed successfully.