Buffer Pool

A buffer pool is an area of main memory that has been allocated by the database manager for the purpose of caching table and index data as it is read from disk. Every DB2® database must have a buffer pool.

Additional buffer pools can be created, dropped, and modified, using the CREATE BUFFERPOOL, DROP BUFFERPOOL, and ALTER BUFFERPOOL statements.
  
How buffer pools are used

When a row of data in a table is first accessed, the database manager places the page that contains that data into a buffer pool. Pages stay in the buffer pool until the database is shut down or until the space occupied by the page is required by another page.

Pages in the buffer pool can be either in-use or not, and they can be dirty or clean:

• In-use pages are currently being read or updated. To maintain data consistency, the database manager only allows one agent to be updating a given page in a buffer pool at one time. If a page is being updated, it is being accessed exclusively by one agent. If it is being read, it may be read by multiple agents simultaneously.
• "Dirty" pages contain data that has been changed but has not yet been written to disk.
• After a changed page is written to disk, it is clean and might remain in the buffer pool.

     A large part of tuning a database involves setting the configuration parameters that control the movement of data into the buffer pool and the writing of data from the buffer out to disk. If not needed by a recent agent, the page space can be used for new page requests from new applications. Database manager performance is degraded by extra disk I/O.

     You can use the snapshot monitor to calculate the buffer pool hit ratio, which can help you tune your buffer pools.

Creating buffer pools

      Use the CREATE BUFFERPOOL statement to define a new buffer pool to be used by the database manager. 

Example of a basic CREATE BUFFERPOOL statement is:

    CREATE BUFFERPOOL <buffer pool name> 
      PAGESIZE 4096

      The buffer pool may be come active immediately if there is sufficient memory available. By default new buffer pools are created using the IMMEDIATE keyword, and on most platforms, the database manager will be able to acquire more memory. The expected return is successful memory allocation. Only in cases where the database manager is unable to allocate the extra memory will it return a warning condition stating that the buffer pool could not be started, and this is done on the subsequent database startup. For immediate requests, you do not need to restart the database. When this statement is committed, the buffer pool is reflected in the system catalog tables, but the buffer pool does not become active until the next time the database is started. For more information about this statement, including other options, see the "CREATE BUFFERPOOL statement".

 

       If you issue a CREATE BUFFERPOOL DEFERRED, the buffer pool is not immediately activated; instead, it is created at the next database startup. Until the database is restarted, any new table spaces will use an existing buffer pool, even if that table space is created to explicitly use the deferred buffer pool.

There needs to be enough real memory on the machine for the total of all the buffer pools that you have created. The operating system also needs some memory to operate.

To create a buffer pool using the command line, do the following:

1. Get the list of buffer pool names that already exist in the database by issuing the following SQL statement:

   SELECT BPNAME FROM SYSCAT.BUFFERPOOLS

2. Choose a buffer pool name that is not currently found in the result list.
3. Determine the characteristics of the buffer pool you are going to create.
4. Ensure that you have the correct authorization ID to run the CREATE BUFFERPOOL statement.
5. Issue the CREATE BUFFERPOOL statement.

     On partitioned databases, you can also define the buffer pool to be created differently, including different sizes, on each database partition. The default ALL DBPARTITIONNUMS clause indicates that this buffer pool will be created on all database partitions in the database.

      In the following example, the optional DATABASE PARTITION GROUP clause identifies the database partition group or groups to which the buffer pool definition applies:

 

    CREATE BUFFERPOOL <buffer pool name> 
      PAGESIZE 4096
      DATABASE PARTITION GROUP <db partition group name>

 

      If this parameter is specified, the buffer pool will only be created on database partitions in these database partition groups. Each database partition group must currently exist in the database. If the DATABASE PARTITION GROUP clause is not specified, this buffer pool will be created on all database partitions (and on any database partitions that are subsequently added to the database).

 

---

Designing buffer pools

The sizes of all buffer pools can have a major impact on the performance of your database.

Before you create a new buffer pool, resolve the following items:

• What buffer pool name do you want to use?
• Whether the buffer pool is to be created immediately or following the next time that the database is deactivated and reactivated?
• Whether the buffer pool should exist for all database partitions, or for a subset of the database partitions?
• What page size you want for the buffer pool? See Buffer pool page sizes below.
• Whether the buffer pool will be a fixed size, or whether the database manager will automatically adjust the size of the buffer pool in response to your workload? It is suggested that you allow the database manager to tune your buffer pool automatically by leaving the SIZE parameter unspecified during buffer pool creation. For details, see the SIZE parameter of the "CREATE BUFFERPOOL statement" and Buffer pool memory considerations.
• Whether you want to reserve a portion of the buffer pool for block based I/O? For details, see: "Block-based buffer pools for improved sequential prefetching".

Relationship between table spaces and buffer pools

     When designing buffer pools, you need to understand the relationship between table spaces and buffer pools. Each table space is associated with a specific buffer pool. IBMDEFAULTBP is the default buffer pool. The database manager also allocates these system buffer pools: IBMSYSTEMBP4K, IBMSYSTEMBP8K, IBMSYSTEMBP16K, and IBMSYSTEMBP32K (formerly known as the "hidden buffer pools"). To associate another buffer pool with a table space, the buffer pool must exist and the two must have the same page size. The association is defined when the table space is created (using the CREATE TABLESPACE statement), but it can be changed at a later time (using the ALTER TABLESPACE statement).

     Having more than one buffer pool allows you to configure the memory used by the database to improve overall performance. For example, if you have a table space with one or more large (larger than available memory) tables that are accessed randomly by users, the size of the buffer pool can be limited, because caching the data pages might not be beneficial. The table space for an online transaction application might be associated with a larger buffer pool, so that the data pages used by the application can be cached longer, resulting in faster response times. Care must be taken in configuring new buffer pools.

Buffer pool page sizes

     The page size for the default buffer pool is set when you use the CREATE DATABASE command. This default represents the default page size for all future CREATE BUFFERPOOL and CREATE TABLESPACE statements. If you do not specify the page size when creating the database, the default page size is 4 KB.

 

Note: If you have determined that a page size of 8 KB, 16 KB, or 32 KB is required by your database, you must have at least one bufferpool of the matching page size defined and associated with table space in your database.

 

      However, you might need a buffer pool that has different characteristics than the system buffer pool. You can create new buffer pools for the database manager to use. You may have to restart the database for table space and bufferpool changes to take effect. The page sizes that you specify for your table spaces should determine the page sizes that you choose for your buffer pools. The choice of page size used for a buffer pool is important because you cannot alter the page size after you create a buffer pool.