DB2 Buffer pools and Its Best Practices

In DB2, Buffer Pools definitely a way to improve performance. In this post, I have explained eight key points on how buffer pools works.

#1 One Buffer Pool for One Table Space

The most important reason to use more than one user table space is to manage buffer utilization. A table space can be associated with only one buffer pool, but one buffer pool can be used for more than one table space.

#2 Size of Buffer Pools

The goal of buffer pool tuning is to help DB2 make the best possible use of the memory available for buffers.

The overall buffer size has a significant effect on DB2 performance, because a large number of pages can significantly reduce I/O, which is the most time-consuming operation.

However, if the total buffer size is too large, and there is not enough storage to allocate them, a minimum system buffer pool for each page size is allocated, and performance is sharply reduced.

#3 How to Calculate Buffer Pool Size

To calculate the maximum buffer size, DB2 considers all other storage utilization, the operating system, and any other applications.

Once the total available size is determined, this area can be divided into different buffer pools to improve utilization. If there are table spaces with different page sizes, there must be at least one buffer pool per page size.

#4 How many buffer Pools you need – how to decide

Having more than one buffer pool can preserve data in the buffers. For example, you might have a database with many very-frequently used small tables, which would normally be in the buffer in their entirety to be accessible very quickly.

You might also have a query that runs against a very large table that uses the same buffer pool and involves reading more pages than the total buffer size.

When this query runs, the pages from the small, very frequently used tables are lost, making it necessary to re-read them when they are needed again.

#5 How Buffer Pool Work for Small Tables

If the small tables have their own buffer pool, thereby making it necessary for them to have their own table space, their pages cannot be overwritten by the large query.

This can lead to better overall system performance, albeit at the price of a small negative effect on the large query.

Often tuning is a trade-off between different functions of a system to achieve an overall performance gain.

It is essential to prioritize functions and keep total throughput and usage in mind while making adjustments to the performance of a system.

#6 When You can Modify Buffer Pools

With DB2 Version 8 and higher, you can change buffer pool sizes without shutting down the database.

#7 How to use Alter Buffer Pool

The ALTER BUFFERPOOL statement with the IMMEDIATE option takes effect right away, unless there is not enough reserved space in the database-shared memory to allocate new space.

#8 When you need to modify Buffer Pools

This feature can be used to tune database performance according to periodic changes in use, such as switching from daytime interactive use to nighttime batch work.

DB2 Version 9.1 and higher enables fully automated size management of a bufferpool. DB2 self-tuning memory manager (STMM) controls this automation process.

Related Posts