Expert advice needed on Buffer management

Hi gang, I received this below from the architecture Dept. trying to simulate Production like performance in QC and development envs. Assuming performance testing would be equal in all those environments if buffers are adjusted to a certain value. Please advise! My question to you guys. 1- Can Buffer management be tuned mathematically as he stated without examining the onstat -p, -B, -b and -X and #' of LRU's to determine performance issues Thanks! " here is the architect reasoning below " Please send feedback , I need to reply ASAP to them. I have looked at Q buffer configuration vs, Production buffer configuration. Q 40000 buffers 256 peak session count ~80 average session count P 200000 buffers 2816 peak session count ~2300 average session count Using the average count this gives 500 buffers per session (buffers is actually a pool this number is for relative comparisons of pool size) 87 buffers per session As to table size I just looked at claim. Q claim has 1000+ records P claim has 95000+ records To bring the two environments closer to per session pooling possibilities, lower Q buffer pool to 7000. Also add data to CIS as was done to UWS. The equivalent buffer change should be done in all of our development and QC environments. sending to informix-list

jpierrot@chubb.com wrote: > Hi gang, His per-session buffer pool requirement calculation is completely bogus since most sessions will be sharing the use of the same pages of data and which pages are shared and which are uniquely used by a single session will differ from server to server as different rows will be placed on pages with each other almost at random. The ONLY calculation that is valid for a 10000 foot view of predicting the required size of the buffer cache without live stats is the size of the working set of rows divided by the number of rows per page which is itself an average which will need to be adjusted over time by monitoring onstat -P for the partnum(s) of the table(s) in question and by monitoring the basic metrics that indicate whether the buffer cache is performing at reasonable levels (BR, RAU, & BTR). By working set I mean: can you predict the number of rows that will be actively accessed by all related applications and ad hoc users during a reasonable portion of each day? Same during peak period? If so, then that is the size of the working set for that set of apps. You will have to do the same for all of the applications running on the server concurrently. Thee concurrency issue means that if jobA only runs in the AM and jobB runs only at night and their working sets are disjoint, then you need to calculate the daytime and nighttime working sets independently and only have to allow for the larger of the two, not the total of both. Anyway the point is that his idea to calculate the working set is a good one, but calculating it per session in invalid. On the flip side, you are correct, the easiest way to determine the working set, once the data is online and the applications are active, is to monitor onstat -P over time and keep track of the maximum number of records for thepartnums belonging to the table(s) in question (including all fragments, indexes and index fragments). One caviat: If the current buffer pool is too small then there may be a situation where two or more tables are thrashing the pool in which case you have to determine the max values for all of those (by watching over time as the counts for each partnum wax and wane when you would expect steady working set levels for each partnum) and add them all up to guess how much to increase the pool. If the pool is too large you will notice that there are many buffers in the 'other' column for partnum zero (0) which means they are unused and that count can safely be deducted from BUFFERS. Art S. Kagel > I received this below from the architecture Dept. trying to simulate > Production like performance in QC and development envs. Assuming > performance testing would be equal in all those environments if buffers are > adjusted to a certain value. Please advise! > My question to you guys. > 1- Can Buffer management be tuned mathematically as he stated without > examining the onstat -p, -B, -b and -X and #' of LRU's to determine > performance issues > > Thanks! > > > " here is the architect reasoning below " Please send feedback , I need to > reply ASAP to them. > > > I have looked at Q buffer configuration vs, Production buffer > configuration. > > Q 40000 buffers 256 peak session count ~80 average session count > P 200000 buffers 2816 peak session count ~2300 average session count > > Using the average count this gives > > 500 buffers per session (buffers is actually a pool this number is for > relative comparisons of pool size) > 87 buffers per session > > As to table size I just looked at claim. > > Q claim has 1000+ records > P claim has 95000+ records > > To bring the two environments closer to per session pooling possibilities, > lower Q buffer pool to 7000. Also add data to CIS as was done to UWS. > > The equivalent buffer change should be done in all of our development and > QC environments. > > sending to informix-list