Increasing DB2 buffer pools

Just a small tip on DB2 memory allocation. It’s very well described in a number of articles, like this or in IBM DB2 official guide on Troubleshooting and Tuning Database Performance. What I want to describe here is how to increase buffer pools, probably one of the most important tuning parameters and very basic at the same time. The issue you can run into is when you increase buffer pool size you get an error SQL20189W:

The buffer pool operation (CREATE/ALTER) will not take effect until the next database startup due to insufficient memory.

It is not just a warning which suggests you to reboot. In fact, after a reboot your buffer pools won’t activate due to insufficient memory and database will work using small system buffers which will drastically decrease performance.

The reason why it happens is global memory cap which is configured in instance Configuration Parameters and called INSTANCE_MEMORY. It’s a total amount of memory which this instance can use for its operations. In order to have bigger buffer pools you must also increase this parameter. After that, SQL20189W goes away and you can tweak buffer pool memory on-the-fly. To check that change has happened use:

db2mtrk -d -v

and look for the line like

Buffer Pool Heap (1) is of size 3343450112 bytes

Advertisements

Tags: , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: