Optimize your Tablespaces and Bufferpools-3: Bufferpool Optimization

Hi all...

It has rather been quite a busy quarter for me... Office has kept me quite busy and was quite pre-occupied with my job... But here I am back with all the stuffs I wanted to share...
We have already seen how to wisely choose optimum value for various tablespace related parameters and how to decide on tablespace sizing... Now we will proceed to the last thread in this discussion and see how one can optimize bufferpools so that it can match the expectations set by tablespace optimiztions (that we have learned in pervious two posts)...

TO start with as a rule of thumb you should have a separate bufferpool for temporary tablespace and if you have different user temp tablespace(which I strongly recommend) you should have a diff bufferpool for that too... If you do not do this, you will see a lot of sort overflows or low bufferpool utilization in snapshots or high read/write operations @ disk level...

Also you should ideally have different bufferpools for index and data tablespaces... This is help you avoid situations where an index tree forced to flushed out of bufferpool to make space for a table read operation...

More importantly you should have different bufferpools for tablespaces storing transactional/operational (which is generally huge in volumes and cardinality) tables and a separate one for tablespaces which store config data tables... As config data is generally queried again and again e.g. every transaction you do in a bank will fetch transaction code (Cash Deposite or withdrwal etc are transaction codes) for your transaction... If you tablespace storing the table for transaction codes available in system is mapped to same bufferpool which is also mapped to tablepace storing table which logs all the transaction (e.g. your cash withdrawal activity and your friends credit card payment activity etc)... In such a scenario when a user does an enquiry on transactions it will flush the transaction code data it will have to read gain for a new transaction... If a transaction enquiry is frequent activity it will screw up cause of this faulty bufferpool design...
You can keep a watch on bufferpool utilization with sysibmadm.snapbp snapshot view... Ideally I will say table-bufferpool utilization should be above 90% (but 85% might be acceptable in certain cases)... For index bufferpools if it is below 95% then you need to worry, ideally it should be arround 97%...


Generally Even in an OLTP system, transactional data is read sequentially when working with a report... This helps making best of I/O cycles (obviously this decision should be based on queries you have in you system)... In such cases you have an option to specify NUMPAGEBLOCK in bufferpool which means the number of page-blocks that would be kept aside specially for block data... you can keep a watch on this utilization of specification with pages_from_block_ios, unread_prefetch_pages, pool_async_data_read_reqs and prefetch_wait_time monitor elements... Not to mention that it will also help in sequential prefetching...

Unlike other performance improvement techniques e.g. indexes and table-partitioning etc. a need for which could be easily guessed over a period of monitoring, a flaw in tablespace design or bufferpool allocation can not be so easily caught at times... The snapshots or anyother monitor data might not be indicative or directly pointing at it... I will suggest thinking about these well in advance... Many people think that this is more of a production call... But a production-DBA knows less than you (the development DBA, involved in table design) about the number of tables, volume of data and nature of queries...

I shall some time try to sum-up on how to use various monitoring techniques to keep a track of parameters set for your bufferpool/tablespaces...