how many of BUFFERS size is reasonable?

BUFFERS too small , read cache will be poor! So increase it , will improve the performance. But It bother me that : How many is suitable and reasonble ? How can I tell that BUFFERS size is too large ? Will too much more BUUFER hurt the system performance ?

To know if your cache is too big run onstat -P and look at the first line of the report which is for partnum zero (0). In the "other" column you should see a zero or very low buffer count. If the count is significant, most of those buffers are unused and you can probably reduce the cache by that number unless you know that there are larger periodic tasks that will need those buffers later. If the cache is too small, the Buffer Turnover Rate (BTR) metric: (((pagreads + bufwrites) / #buffers) / <fractional hours since stats were zero'd out>) will be high. Ideally you should be turning the cache less often than once every six minutes which works out to a BTR of 10.00 or less. You can also watch the onstat -P output over time to see how many pages are being replaced or shifted from partnum to partnum over each six minute period. That will tell you approximately how many more buffers you can likely take advantage of. How to estimate a good starting cache size? Ideally you want your application's complete common working set of data to reside in memory all day long. For some applications you can estimate that pretty well. For example, if you know that a typical day will produce 10,000 new invoiced and modify 2,000 older ones affecting 9,000 customers, 60,000 inventory items from 500 vendors residing in 120 warehouses you can make that calculation, add in some overhead for index pages. Don't forget to allow for weekly and monthly management reports which will span more data - although the data for those reports will not have to reside in the cache for long, you don't want these reports to chase all of your operational data out of the cache, so you will have to allow some extra cache to prevent periodic batch reports from trashing OLTP performance (unless you are using the Informix Warehouse Accelerator or a secondary server to run those reports of course). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jan 31, 2013 at 11:05 PM, joe <joeclhua@gmail.com> wrote: > BUFFERS too small , read cache will be poor! > > So increase it , will improve the performance. > > But It bother me that : > > How many is suitable and reasonble ? > > How can I tell that BUFFERS size is too large ? > > Will too much more BUUFER hurt the system performance ? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Hi Joe, Another thing to consider is the output of onstat -R, which will show you how many buffers are low or high priority. I don't know the the exact algorithm used by Informix, but essentially the more frequently access buffers are high priority. Certain types of pages, such as index node pages, are more likely to get a high priority. When a large portion of the buffer cache is high priority I have seen improvement when adding buffers. HTH, Jason

Hi Art, I always thought that once a page is read in, it stays in the cache until that buffer is required for another page. This means that eventually pagenum 0 is used. Unless of course, you have less data that buffers. But that must be rare. Also trying to understand your equation. Why do you include bufwrits there? Cheers, Jason

Jason: High priority buffers are bitmaps and index pages. The rest are data pages which all have the same priority. The priority algorithm that was developed for version 7.30 & 9.20 in part to support RESIDENT tables was a dismal failure and was ripped on in versin 7.31/9.21. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Feb 1, 2013 at 8:54 AM, Jason Harris <jgh@jgharris.com> wrote: > Hi Joe, > > Another thing to consider is the output of onstat -R, which will show you > how many buffers are low or high priority. > > I don't know the the exact algorithm used by Informix, but essentially the > more frequently access buffers are high priority. Certain types of pages, > such as index node pages, are more likely to get a high priority. > > When a large portion of the buffer cache is high priority I have seen > improvement when adding buffers. > > HTH, > > Jason > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Art, All I can find is this in v10 new features: Improved Priority Management for the Buffer Manager Buffers are now divided into two classes: HIGH priority for frequently accessed buffers, and LOW priority for infrequently accessed buffers. Priority classification is dynamic, based on observed access frequencies of the buffers. The CPU usage of the buffer manager is reduced, thus improving performance. Can't find documentation where it changed after that. What are you reading from? Is it just me or is the documentation containing less of this specific internal info, or it is going somewhere else? Jason

Hmm, dunno. Last I was told was index high, data low. I'll have to check it out, unless some IBM lurker wants to comment. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Feb 1, 2013 at 9:54 AM, Jason Harris <jgh@jgharris.com> wrote: > Art, > > All I can find is this in v10 new features: > Improved Priority Management for the Buffer Manager > Buffers are now divided into two classes: HIGH priority for frequently > accessed buffers, and LOW priority for infrequently accessed buffers. > Priority classification is dynamic, based on observed access frequencies of > the buffers. The CPU usage of the buffer manager is reduced, thus improving > performance. > > Can't find documentation where it changed after that. What are you reading > from? > > Is it just me or is the documentation containing less of this specific > internal info, or it is going somewhere else? > > Jason > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Jason: Here's the reasoning behind the BTR: - Pages read into cache from disk can be replacing least recently used pages so pagreads - Creating new pages for inserts can also replace the least recently used pages so bufwrites - Divide by #buffers to determine the number of times the cavhe MIGHT have ben replaced - or a subset of the cache more heavily thrashed (to use financial market speak). - Divide by time since the stats were zero'd (or since startup) - I use hours as a good period of examination. Voila! BTR. Now, I am working on other metrics for this because I have sites I monitor where the BTR is WAY high but performance is just fine. I'm figuring that this is because the sites are heavily weighted towards inserts of data that is not accessed again for a long time so it quickly gets flushed out by other inserts without affecting the data that needs to remain static in the cache. My package, ratios.shr_ak, reports the metrics I use including two new versions of BTR - named BTR2 & BTR3 for now. What I am slowly figuring out is that the BTR2 which is: BTR2 = (((pagreads + fgwrites + lruwrites + chunkwrites) / usedbuffs) / stathrs) Replaces the bufwrites with actual writes to disk and may eliminate the effects of very high insert rates. I am finding that at some of those sites I mentioned while the BTR is very high (in the hundreds or even thousands per hour), the BTR2 is below or just around 10.00 turns per hour. At sites where BTR is a better predictor than these unusual sites, BTR and BTR2 are nearly identical. So, if BTR and BTR2 agree and are both OK then the cache is probably sufficient. Where BTR is high but BTR2 is OK, the cache is probably OK, but a small increase may help somewhat. I may eventually stop using BTR in favor of BTR2, but for now I'm still evaluating. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Feb 1, 2013 at 9:01 AM, Jason Harris <jgh@jgharris.com> wrote: > Hi Art, > > I always thought that once a page is read in, it stays in the cache until > that buffer is required for another page. This means that eventually > pagenum 0 is used. Unless of course, you have less data that buffers. But > that must be rare. > > Also trying to understand your equation. Why do you include bufwrits there? > > Cheers, > > Jason > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Hi Art, Thanks for that very detailed explanation. I see where you are heading. I was thinking about cases where the same page gets updated multiple times, which could increase that figure, but increasing the buffer cache would not really help. And lots of rows inserted per page could do the same thing. Good luck finding that magic formula. It may require more "Art" than science! :) Thanks, Jason

I agree, that's where I was trying to go with BTR2 & BTR3 and the BTR2 looks like the winner. I'll know in a week or so. Tuning about 15 servers with BTR2 in mind over the next week or two. I'll see how that goes. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Feb 1, 2013 at 10:41 AM, Jason Harris <jgh@jgharris.com> wrote: > Hi Art, > > Thanks for that very detailed explanation. I see where you are heading. > > I was thinking about cases where the same page gets updated multiple > times, which could increase that figure, but increasing the buffer cache > would not really help. And lots of rows inserted per page could do the same > thing. > > Good luck finding that magic formula. It may require more "Art" than > science! :) > > Thanks, > > Jason > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >