DB2 9.7. 8GB RAM on server. Currently BP has 500 MB (number of pages*pagesize) Database has issue with increased IOPS. So we think maybe BP should be increased. How to estimate needed size for BP? Is there some recommendation? Maybe I would like to put it on 1GB. What can be negative effect? Is it always greater better for BP?


You can't "estimate" the size of a buffer pool, because the answer is, "it depends".

Try using the AUTOCONFIGURE command to get started, and enable STMM. Together these will go a long way towards getting your database running more efficiently.

I would also recommend you spend some time reading about DB2 Performance Tuning. There is a ton of information available on the web, starting from the entire Performance Tuning section of the manual.

| improve this answer | |
  • robertsdb2blog.blogspot.com/2010/09/… I found here that " IF YOU HAVE BIG MEMORY, YOU SHOULD HAVE BIG DB2 BUFFER POOLS." I run Configuration Advisor and it gives me the value 32678 so it is 1 GB. My though is maybe I should increase it more (to 1.5 GB) What can be negative effect except I will have less memory on the server but currently I have 2,3 GB which are not used so I do not believe I will have some memory heap dump or similar. – Dejan Feb 25 '14 at 11:44
  • I marked this an answered I am only interested in your opinion if it should be ok if I extend this to 1.5 GB? – Dejan Feb 26 '14 at 10:24
  • Dejan, I have no way of knowing whether increasing the buffer pool size to 1.5 Gb will have any effect on performance. If you show that there are 2.3 Gb free then you can certainly try it. However, if you follow my recommendation of enabling the Self Tuning Memory Manager (STMM) will adjust the size of the buffer pool (and other memory consumers), DB2 will work to optimize the various pool sizes for you. – Ian Bjorhovde Feb 26 '14 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.