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.ksh
Metric 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------------------------------
Original Message:
Sent: Tue December 22, 2020 10:55 PM
From: AMIT PATEL
Subject: 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