Re: DB Tuning...

Art S. Kagel wrote: > Tuning is pretty good. Here are your metrics: > > BR = (24306 / (32278143 + 622031)) * 100.00 = .0700 ! Excellent > BTR = (((32278143 + 622031) / 100000) / 61.817) = 5.3221 ! On target > RAU = (221553/(120432+12182+90586)) * 100.00 = 99.2600 ! Just a bit low Where do these metrics come from and what are the ideal values for BR and BTR? A quick pointer to the documentation would be great. Ben.

On Fri, 30 Jul 2004 06:20:11 -0400, Ben Thompson wrote: > Art S. Kagel wrote: > >> Tuning is pretty good. Here are your metrics: >> >> BR = (24306 / (32278143 + 622031)) * 100.00 = .0700 ! Excellent BTR = >> (((32278143 + 622031) / 100000) / 61.817) = 5.3221 ! On target RAU = >> (221553/(120432+12182+90586)) * 100.00 = 99.2600 ! Just a bit low > > Where do these metrics come from and what are the ideal values for BR and > BTR? A quick pointer to the documentation would be great. Hi Ben. The documentation is many years of posts here on CDI. These three metrics were developed by verious denizens of this newsgroup over the years. I'll take credit for BR & BTR, don't remember who proposed RAU. There is a script, ratios.ksh, to automatically calculate and display these by parsing values from onstat -p and some sysmaster queries. It is available for download from the IIUG Software Repository. As to what are they: BR == Bufwaits Ratio - A measure of the percentage of IO requests that require a buffer or LRU latch that have had to wait for other sessions' latches to be released. BR values less than 7.00 indicate a system with little latch contention. Values between 7.00 and 9.99 indicate a system that users are perceiving as sluggish. Values over 10.00 are in death mode. Solution: more buffers if other indicators show buffers may be low (see BTR) and, mostly, more LRU queues to reduce LRU contention. If LRUS is maxed out there is a tunable, LRUPOLICY, that can adjust how sessions select an LRU which can also reduce contention. BTR == Buffer Turnover Rate - It is an estimate of the number of times the entire buffer cache is being replaced per unit of time (usually per hour). Values less than 10.00 per hour are acceptable. Higher values MAY indicate that increasing BUFFERS will reduce cache thrashing and improve cache percentages. It may also indicate excessive sequential scan activity. If you run onstat -P and the 'other' column value for partnum zero (0) is a non-trivial value (ie in the thousands rather than a few) then more buffers is unlikely to help, but look at the partnums for particular tables that are hitting the buffers heavily and see what you can do. Also in this case look at the 'seqscans' versus 'open' values to see if a large percentage of your queries are performing sequential scans and stressing the IO subsystems. Note this is an interpretive value and must be considered with other data. A high BTR may just indicate that you are running reports that scan through the entire database but only require a few pages at a time in which case there is no problem and more buffers will not change anything. You can invert this frequency value and get BTP (Buffer Turnover Period), showing that your buffers are turning over every 0.25 hours (with a BTR of 4) but I like BTR. RAU == ReadAhead Utilization == This is a direct measure of the percentage of readahead pages actually accessed. It should be as close to 100.00% as possible (and I mean 99.97 not 98.99). Low values may mean adjustments are needed to RA_PAGES or RA_THRESHOLD. Reducing RA_THRESHOLD has the most dramatic positive effect without reducing available readahead pages. If your disk farm is fast (and especially if you are using a modern disk array or SAN with large cache and readahead of its own - see my posts on the subject) you can live with VERY low RA_THRESHOLD despite the recommendations in the manuals which do not take disk array cache and array readahead into account. Formulae: BR = (bufwaits / (bufwrits + pagreads)) * 100.00 BTR = (((bufwrits + pagreads) / BUFFERS) / elapsed) RAU = (RA-pgsused / (ixda-RA + idx-RA + da-RA)) * 100.00 Above elapsed is time since onstat -z was last run or since startup if not run since startup. BUFFERS is the setting from the ONCONFIG file or an SMI query against sysconfig. All other values are from the onstat -p output or the equivalent SMI queries. Art S. Kagel

Hi Art , Can I recommend that the explinations you have put for BTR and RA be put into the FAQ at the IIUG org. The metrices are a great help to all Informix users and the more people that know about the better. Cheers Traveller2003

Thanks for the info. Ben.

On Mon, 02 Aug 2004 08:36:22 -0400, Traveller2003 wrote: Submitted. -- Art > Hi Art , > > Can I recommend that the explinations you have put for BTR and RA be put > into the FAQ at the IIUG org. The metrices are a great help to all Informix > users and the more people that know about the better. > > > Cheers > > Traveller2003