Bufferpool for 2K pagesize

Hello, i was just wondering why read and write caches are showing 0% utilization even though we allocated 20mb for this default bufferpool to one of our informix instances. And buffer turnover ration is around 950 for 2K page size. we have not seen this behavior in other instances. any thoughts? Buffer pool page size: 2048 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 370890048 11717845887 65062319 0.00 16570381 53216838 263163 0.00 bufwrits_sinceckpt bufwaits ovbuff flushes 122 41040 0 6588 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 85097 21Mb Bufferpool Segments id segment size # buffs 0 0x63fdb000 21Mb 10000 ------------------------------ Buffer pool page size: 4096 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 59049252948 124367598324 824278522253 92.84 54837845 736895388 824176506 93.35 bufwrits_sinceckpt bufwaits ovbuff flushes 922307 705551109 0 6132 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -nan 8594404 1024Mb Bufferpool Segments id segment size # buffs 0 0x655ca000 1024Mb 250000 ------------------------------ Cherry ------------------------------ ------------------------------

While it seems that you have plenty of activity in the 2K bufferpool, the 21 MB is just too tiny for any significant caching, and things are being swapped out way too quickly. When a read or write is performed, for something that was in the cache, the page has already been pushed out of the buffers by something else. The Buffer Turnover of 950 confirms that. You should be looking for a much lower number, preferably in the single digits. Look at increasing the size of that 2K bufferpool. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Hi, The bufferpool is too small for the workload on this server. The workload needs to access more pages that go through that bufferpool. Either A) Different sql B) More data needs to be accessed for the sql B) Could be 1) More rows are returned for the sql 2) Data that is returned could be spread across more pages 3) Query plans could be different meaned more rows/page need to be read to return the same data. Use syssesprof.bufreads to see what sessions are doing the most reads and how that compares to another server without the issue. Actually it would be good to be able to see sysesprof reads/writes broken down by bufferpools/ https://ibm-data-and-ai.ideas.ibm.com/ideas/INFX-I-568 Raise for this! Regards, David. ------------------------------ David Williams ------------------------------

Hello Mike, I think it should at least show some number instead of 0. i see only default BUFFERPOOL is available in onconfig file. should i explicitly mention BUFFERPOOL for 2K as below in onconfig file? BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50.00,lru_max_dirty=60.50 BUFFERPOOL size=2K,buffers=10000,lrus=8,lru_min_dirty=50.00,lru_max_dirty=60.50 ------------------------------ Cherry ------------------------------

Hello David, 2K bufferpool is only used for root, logical log and physical log dbspaces only. We have bigger databases(with higher loadd) are running fine with 90% cache in our environment with same setting. ------------------------------ Cherry ------------------------------

Hi, I would always explictly configure bufferpools. Also keep the number of buffers different that the default as that is usually way too low and also means you cannot tell if you got a default bufferpool by mistake! Regards, David. ------------------------------ David Williams ------------------------------

The "default" bufferpool is what will be used if you haven't explicitly specified a bufferpool for the page size for which you have dbspaces configured. While technically in your example the 2K bufferpool would not be needed because it is exactly the same as the default, it is good practice to define a bufferpool explicitly for each pagesize. Then you can configure it as you need to. 20 MB is very small for a bufferpool. I understand that you may have the same in other instances, but perhaps you have less running against the tables in the rootdbs, or you have more tables in this rootdbs (sysadmin database maybe?), or there are frequent scans in this instance and not the other? What is your buffer turnover in those other instances...is it less than the (very high) 950 you have here? ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Cherry: So, your cache hit rate is NOT zero, but rather it is just less than 0.1%. A BTR of 950/hour means that over the course of any given minute those 10,000 pages are replaced 15 times or about every 4 seconds on average. Since the buffer pool is SO small, it is unlikely that any pages stay around to be accessed more than a few times before they are swapped out for other data. So, how to configure your buffer pool? First, I would note that if this system is insert heavy for the 2K dbspaces, then the BTR at 950 may be inflated a bit and I would recommend using the BTR3 calculation to perform the analysis below instead of BTR. Best case, if the 2K dbspaces are not insert heavy then the BTR3 will be almost exactly the same as the BTR, so no worries switching which you use. Basically I no longer look at the original BTR at all. So, an ideal BTR is under 6 turns per hour, so 950 turns is 158X ideal that indicates that you MAY need to increase the size of that cache from 10,000 pages to 1,580,000 pages. Unfortunately that ignores the fact that much of a properly sized buffer pool is populated with relatively static data like small lookup tables and the "tops" of index trees. It also ignores how much active data you really have that might need to be cached which may be far less that 3GB. So, I would start with 790,000 as a working number then try to estimate the size of your normal and peak active working set of data from the 2K dbspaces. Finally it ignores how much memory you can afford to add to the server without either running our of physical memory completely or of starving other critical processes. Your starting number will be the smallest of those values. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Cherry, Perhaps on this server there is another object in rootdbs? oncheck -peotherwise onstat -B to a file every 5 or 10 or 15 minutes and from the oncheck -pe output see which objects are those pages. Regards, David. Regards, David. ------------------------------ David Williams ------------------------------