Section 8.5. Buffer Pools

8.5. Buffer Pools

The database buffer pools are the area where all of the work in a database really happens. All regular data and index keys are read, scanned, updated, inserted, and deleted within the database buffer pools.

The database buffer pool area is a piece of real memory that is used by DB2 to temporarily store (cache) the regular data and index pages when they are read from disk to be scanned or modified. The buffer pool area improves the performance of the database, since the pages can be accessed much more quickly from memory than from disk.

When you connect to a database, the database buffer pools (along with the lock list, database heap, and so on) are allocated in memory. When you (and anyone else using the database) disconnect all applications from the database, this memory is freed back to the operating system.

If your applications frequently connect to and disconnect from the database, you should consider activating the database (using the ACTIVATE DATABASE command) so that the buffer pools and all database-related memory remain allocated. This eliminates the overhead of allocating all of this memory each time an application connects to the database. When you then want to "close" the database, use the DEACTIVATE DATABASE command.

8.5.1. Creating Buffer Pools

When you create a database, DB2 creates a default buffer pool named IBMDEFAULTBP. On Linux and Windows the default buffer pool is 250 pages or 1MB, while on UNIX the default buffer pool is 1,000 pages or 4MB. You cannot drop the default buffer pool, but you can change its size using the ALTER BUFFERPOOL statement.

You can also create additional buffer pools if your data and workloads can benefit from isolating the different database objects into their own separate work areas. You can use the CREATE BUFFERPOOL statement to create a new buffer pool and can specify the following information with this statement:

• The name of the buffer pool. This name cannot already be used within the database and cannot begin with the characters SYS or IBM.

• Whether to create the buffer pool immediately or wait until the database is stopped and restarted.

  - If you tell DB2 to create the buffer pool immediately but there is not enough memory available, DB2 will instead create it deferred.

  - The default option is immediate.

• The database partition group on which to create the buffer pool. If you do not specify a database partition group, the buffer pool will be created on all partitions.

• The page size used for the buffer pool. The default page size is 4K or 4096 bytes.

• The size of the buffer pool, specified in the number of pages.

  - In a partitioned database, this will be the default size for all database partitions where the buffer pool exists.

  - The EXCEPT ON DBPARTITIONNUMS clause allows the buffer pool to have different sizes on the different database partitions.

• The number of pages to be created in the block-based area of the buffer pool. This area cannot be more than 98 percent of the total buffer pool size. Specifying a value of 0 will disable block I/O for the buffer pool.

• The number of pages within a given block in the block-based area of the buffer pool. The block size must be between 2 and 256 pages; the default value is 32 pages.

• Whether buffer pool victim pages can be copied to extended storage, a secondary cache between the buffer pools, and disk. Extended storage is more efficient than retrieving data from disk but less efficient than retrieving data from the buffer pool, so this is not applicable to 64-bit environments.

NOTE

The page size and buffer pool name cannot be altered once it has been defined for a buffer pool.

Enabling block-based I/O by setting NUMBLOCKPAGES to a value greater than zero can help performance for applications that perform a lot of sequential prefetching.

NOTE

NUMBLOCKPAGES does not need to be set to allow prefetching to occur.

The following are a couple limitations that you need to be aware of.

• You cannot specify both a block-based area in the buffer pool and tell it to use extended storage.

• You cannot specify a block-based area in the buffer pool if the buffer pool is set up to support Address Windowing Extensions (AWE) on Windows. If you tell the buffer pool to use both AWE support and block-based I/O, then block-based I/O support will be disabled automatically.

To examine buffer pools in more detail, let's look at a few examples. Consider a database that is used for an online Web-based ordering application. Performance is fine most of the time, but once a week management runs some reports that cause the system to slow down. In examining the reports you notice that they are large, multiple joins that create a large temporary table. To isolate the creation of the temporary table from overwhelming the buffer pool, you can create a 10,000-page buffer pool dedicated to the temporary table space as follows:

 CREATE BUFFERPOOL tempbp SIZE 10000 

You then need to tell the table space to use the buffer pool:

 ALTER TABLESPACE tempspace1 BUFFERPOOL tempbp 

As mentioned earlier, the default page size for a database is 4K. If you want to create a table that is more than 4005 bytes, you need to create a table space with a larger page size. But before you can create this table space, you need to create a buffer pool with the same page size. For this example, assume that a 16K page size is best for this table.

 CREATE BUFFERPOOL bp16k SIZE 100000 PAGESIZE 16K 

You can then create the table space as follows:

 CREATE TABLESPACE tspc16k PAGESIZE 16K BUFFERPOOL bp16k 

If you know that this table will be scanned a lot and that it would benefit from sequential prefetch, you could set aside a portion of the buffer pool for block-based I/O as follows:

 CREATE BUFFERPOOL bp16k     SIZE 100000     PAGESIZE 16K     NUMBLOCKPAGES 24000     BLOCKSIZE 256 

If you specify a block size that is larger than 98 percent of the buffer pool size, you will get the following error.

[View full width]
 SQL20150N The number of block pages for a buffer pool is too large for the size of the  buffer pool. SQLSTATE=54052 

NOTE

If you are using block-based I/O, you should ensure that the block size you set is set based on the table space's extent size.

If you are creating a buffer pool in a multi-partition database, and the table space you are creating the buffer pool for is in a database partition group that is not defined on all database partitions, you can specify in which partition group the buffer pool will be created:

 CREATE BUFFERPOOL bp16k     SIZE 100000     PAGESIZE 16K     NUMBLOCKPAGES 24000     BLOCKSIZE 256     DATABASEPARTITIONGROUP pg16k 

If you are creating a buffer pool in a multi-partition database and you want the buffer pool to be sized larger on some database partitions, you can specify these partitions and their sizes as follows:

 CREATE BUFFERPOOL bp16k     SIZE 100000     PAGESIZE 16K     NUMBLOCKPAGES 24000     BLOCKSIZE 256     EXCEPT ON DBPARTITIONNUMS 1,2,3 SIZE 200000 

In this case you can also use the commands:

 CREATE BUFFERPOOL bp16k     SIZE 100000     PAGESIZE 16K     NUMBLOCKPAGES 24000     BLOCKSIZE 256     EXCEPT ON DBPARTITIONNUMS 1 TO 3 SIZE 200000 

or

 CREATE BUFFERPOOL bp16k     SIZE 100000     PAGESIZE 16K     NUMBLOCKPAGES 24000     BLOCKSIZE 256     EXCEPT ON DBPARTITIONNUM 1 SIZE 200000     EXCEPT ON DBPARTITIONNUM 2 SIZE 200000     EXCEPT ON DBPARTITIONNUM 3 SIZE 200000 

8.5.2. Maximizing Buffer Pool Size on Windows

When working with Windows 2000, the total addressable memory can be up to 64GB. Therefore, the maximum buffer pool sizes that can be created on Windows equals 64GB minus the memory used by the operating system and other DB2 memory allocations, assuming that the server is dedicated to DB2. The support for large memory addressing on Windows is provided by AWE. Through AWE, Windows 2000 Advanced Server provides support for up to 8GB of memory addressing, while the Windows 2000 Data Center Server provides support for up to 64GB of memory.

For the complete details on how to use AWE with DB2, refer to Chapter 15, The DB2 Memory Model.

8.5.3. Altering Buffer Pools

You can change some of the attributes of a buffer pool using the ALTER BUFFERPOOL statement. DB2 allows you to change the following:

• Size

• Database partition group

• Block-based area

• Block size

• Enabling or disabling extended storage

Given a buffer pool created with the statement:

 CREATE BUFFERPOOL bp16k     SIZE 100000     PAGESIZE 16K     NUMBLOCKPAGES 24000     BLOCKSIZE 256     DATABASEPARTITIONGROUP PG16K 

to make the buffer pool twice as large, you would use the statement:

 ALTER BUFFERPOOL bp16k     SIZE 200000 

Notice that since you did not want to change the block-based area size or the block size, you did not specify these options in the ALTER BUFFERPOOL statement.

To make the size of the block-based area 32,000 pages instead of 24,000 pages, you would use the statement:

 ALTER BUFFERPOOL bp16k     NUMBLOCKPAGES 32000 

To allocate this buffer pool also on the partitions in the database partition group pg1234, use the statement:

 ALTER BUFFERPOOL bp16k     DATABASEPARTITIONGROUP pg1234 

8.5.4. Dropping Buffer Pools

You will not be able to drop any buffer pools that are associated with a table space. Before you can drop the buffer pool you will need to associate the table space with a different buffer pool using the ALTER TABLESPACE statement.

Once there are no longer any table spaces associated with the buffer pool, you can drop the buffer pool using the DROP BUFFERPOOL statement. This will release the memory back to the operating system for use by other DB2-related buffers and heaps, or for other application memory requests.

To drop the buffer pool BP16K, use the statement:

 DROP BUFFERPOOL bp16k