Dear all,


Please give the solution for below given query.

I have DB2 UDB v8.1.1.80 Fixpack 8 on AIX 5.3


My database is hitting 100% to Bufferpool. Shall I add/create a new bufferpool to my database or not ?

In case add/create a new bufferpool to database. How to add the bufferpoll to database.

Thanks,

Mohan Reddy

100% buffer pool hit ratio would be an extremely good thing because all requests for data pages could be answered by the buffer pool.

Could you describe you situation a bit more and which concerns you have?

Hi,

Thanks for your replay. I have HRMS application is on aix server seperate server and DB2 UDB on aix seperate server. When my users are connected through application server it is responcing very slow and some times it is hangs the clients browser. That time in my database server bufferpool is hitting 100%. My idea is to increate the size of the bufferpool, then the clients can get the fast responce.

Even i was run the TOPAS on my db server it is showing 90 - 95% ushage. I saw the my quest control dignosys the bufferpool and index bufferpool is hitting 100%.

Please help me out whether i can add/create the new bufferpool or not?

Thanks,

Mohan Reddy

Please explain what exactly "bufferpool is hitting 100%" means. I guess it is not what is commonly known as "buffer pool hit ratio". For that, a 100% hit ratio is superb and means you have basically an in-memory database. In that case, you would have a completely different problem to solve and search at the wrong place.

A buffer pool is simply a cache. All requests to data pages go against the buffer pool(s). If the page is in the buffer pool, you have a cache hit - otherwise, you have a cache miss. Since it is usually not possible to size the buffer pools big enough to hold all the data (simply because you don't have enough physical memory), it is necessary that the buffer pool manager reads in requested data from disk. Likewise, data pages not used for a while have to be evicted from the cache/buffer pool and written back to disk. If a page request can be satisfied from the buffer pool without reading it from disk, you have a "logical read". If the buffer pool manager has to read the data from disk, we talk about a "physical read". You want to have as few physical reads as possible.

So generally, it is better to have big buffer pools because you will have less physical reads. However, buffer pool needs to be backed by physical memory and not virtual one. Because with virtual memory, you'd run into paging/swapping and then you loose all benefits from the memory cache again. In such situations it would be better to reduce the memory usage, i.e. reduce sort heaps, buffer pools, ...

Based on your scarce description, I have no idea what your problem is. If "bufferpool and index bufferpool is hitting 100%" means that all frames/slots in the buffer pool are used, this is not necessarily a bad thing. Of course, you can change buffer pools using the ALTER BUFFERPOOL statement and change their size, or you can create new buffer pools (see CREATE BUFFERPOOL statement) and assign tablespaces to those buffer pools.

My recommendation would be that you run the DB2 Configuration Advisor. It asks you about 10 simple questions and gives you a good base-level configuration of your system. Why am I suggesting that? My guess is that no serious system performance tuning was applied so far. Thus, changing something at one end (buffer pools) may help but still leave you in an insatisfactory situation. Also, you may change something in a way that has a negative impact on other parts of the overall system. For example, you increase buffer pools and use much of the physical memory, leaving nothing for other processes like web servers.