Increasing BUFFERS

Hello All, Please let me know, if need to increase BUFFR parameter , then How should I calculate how many buffer I need to increse? And how it referenced with SHARED MEMORY? If Increasing BUFFER parameter, then need to change any of the SHARED MEMORY parameter also? Kindly assist. Thanks Amit ------------------------------ AMIT PATEL ------------------------------ #Informix

Hello Amit, It is difficult to determine exactly what value to use for BUFFERS. Consider the factors that will increase the virtual segment area (lock, session memory, sort memory, etc.) and set it within the physical memory size excluding the memory used by processes other than oninit. In my case, the criteria for increasing the buffer are such as disk i/o, bufwait ratio, buffer turnover rate, and buffer cahe hit ratio. If you have enough memory, how about setting it as large as possible? And when configuring buffers, you don't necessarily need to modify other onconfig parameters, but you may need to modify physical log size, cleaners, lru_min, lru_max, checkpoint interval, etc. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Amit: Get my ratios.shr_ak package, install the ratios.sql stored procedure in sysmaster, then run the newratios.ksh script to get the set of metrics that I use to evaluate a server's state quickly. Look at the BTR #3 value for each bufferpool and for the total (see the report from my system below. You want to see BTR#3 values less than 10.0, better less than 7.0, best even less. If that value is in the accectable range for all page size bufferpools then you are fine as far as the cache sizing is concerned. If it is too high for one or more pagesizes, then you should increase the cache for those pagesize bufferpools. How much? You'd have to guess. Ideally the entire normal working set of data should fit in cache. If you can estimate that, then you have your answer. If you cannot, you can a) calculate by what percentage the BTR3 value is above 6.0 and increase the cache by that percentage and reevaluate. One technique that I use is to watch the onstat -P output over time to determine how many new pages are swapped into the bufferpool over a set period (I will usually use 60mins/BTR3), normalize that to an hour, and increase the bufferpool for the pagesize you are evaluating by that number of buffers and again reevaluate. I have a script for this, if you want it, email me directly. My address is in the output below. On your other question, the bufferpools are independent of the other shared memory settings. However, if increasing the cache will oversubscribe memory then you may have to decrease SHMVIRTSIZE to free up memory for the additional cache. Otherwise, if you have enough memory to make the increase safely, then don't worry about it. $ newratios.kshMetric Ratio Report For 2K Cache Bufwaits Ratio: 2.550000% Buffer Turnover Rate: 3.94/hr Used Buffer Turnover Rate: 3.94/hr Experimental BTR #2: 0.42/hr Experimental BTR #3: 1.49/hr Metric Ratio Report For 8K Cache Bufwaits Ratio: 17.700000% Buffer Turnover Rate: 5.79/hr Used Buffer Turnover Rate: 5.79/hr Experimental BTR #2: 0.43/hr Experimental BTR #3: 1.54/hr Metric Ratio Report Summary For All Caches ReadAhead Utilization: 98.870000% Bufwaits Ratio: 23.140000% Buffer Turnover Rate: 4.56/hr Used Buffer Turnover Rate: 4.56/hr Experimental BTR #2: 0.42/hr Experimental BTR #3: 1.50/hr Lock Wait Ratio: 0.01000% Sequential Scan Ratio: 11.82000% Statistics reset at: 2020-12-21 11:19:35 Elapsed time since reset: 46:04:31 ------------------------------ The RAU should ideally be VERY near 100% - the higher the better. The BR should be below 7% - the lower the better. The BTR and UBTR should ideally be less than 10. UBTR is the same calculation as BTR but removes any unused buffers from the calculation If BTR and UBTR differ, your buffer cache may be too large. UBTR was a unsuccessful attempt a more accurate picture the nature of the cache churning which the BTR reports. Check CDI archives or the Informix FAQ for more info. Baseline values for Experimental BTR metrics #2 & #3 have not yet been determined. Any data you can provide back to me that will help to determine the acceptable and troublesome value ranges will be greatly appreciated. Please send data to: art.kagel@gmail.com . ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------ ------------------------------