Re: SMI queries to calculate the buffer wait ratio, the buffer turnover rate and the readahead utilization

Hm, I thought that I'd updated IIUG long ago. The source I have here works fine, I'll upload an update today. Sent you my local copy of the script, if it doesn't work with the version of the stored procedure you have, let me know and I'll send you that as well. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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, Jul 9, 2010 at 7:49 PM, Ramon Rey <fandelau@gmail.com> wrote: > Art, > > Here is the output of the newratios.ksh script as ran on my system, along > with other output I think can be useful. > Please let me know what am I doing wrong, because not only the numbers seem > to be way off, I'm not getting the breakdown for 16k and 2k buffers, just a > system wide result. > Do let me know if you need any other piece of information > Thanks! > > Ramon. > > > $> who am i > informix pts/2 Jul 9 16:34 (myserver) > > $> dbaccess sysmaster ratios.sql > > Database selected. > > > Routine dropped. > > > Routine created. > > > Database closed. > > $> INFORMIXSERVER=rmt_dbms10 newratios.ksh > > Metric Ratio Report Summary For All Caches > > ReadAhead Utilization: 100.000000% > Bufwaits Ratio: 1.070000% > Buffer Turnover Rate: 2087.38/hr > Used Buffer Turnover Rate: 52.10/hr > > > ---------------------------------------------------------- > > 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. > > $> which newratios.ksh > ./newratios.ksh > $> onstat - > > IBM Informix Dynamic Server Version 11.10.FC3 -- On-Line -- Up 36 days > 05:27:54 -- 10799104 Kbytes > > $> uname -a> SunOS irsadb1 5.10 Generic_141444-09 sun4v sparc SUNW,T5240 > $> onstat -c |grep "^BUFFER" > BUFFERPOOL > size=2K,buffers=10240,lrus=8,lru_min_dirty=70.000000,lru_max_dirty=80.000000 > BUFFERPOOL > size=16K,buffers=400000,lrus=128,lru_min_dirty=70.000000,lru_max_dirty=80.000000 > $> onstat -g buf> > IBM Informix Dynamic Server Version 11.10.FC3 -- On-Line -- Up 36 days > 05:35:33 -- 10799104 Kbytes> > Profile > > Buffer pool page size: 2048 > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits > %cached > 233549 1267613 61551488 99.62 734892 1479212 14105105 > 94.79 > > bufwrits_sinceckpt bufwaits ovbuff flushes > 6 1382 0 1008 > > Fg Writes LRU Writes Avg. LRU Time Chunk Writes > 0 0 NaN 75269 > > Buffer pool page size: 16384 > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits > %cached > 971642638 18293394920 12974074403 92.51 4136654 33324056 266461063 > 98.45 > > bufwrits_sinceckpt bufwaits ovbuff flushes > 7033 197852131 0 1013 > > Fg Writes LRU Writes Avg. LRU Time Chunk Writes > 0 0 NaN 694352 > > Fast Cache Stats > gets hits %hits puts > 600654452 599243198 99.77 119806969 > > $> > > > > > On Wed, Jun 30, 2010 at 7:30 PM, Art Kagel <art.kagel@gmail.com> wrote: > >> Sure it does! Newratios.ksh prints out the total metrics and then >> recalculates them for each buffer page size cache. >> >> Art >> >> Art S. Kagel >> Advanced DataTools (www.advancedatatools.com) >> IIUG Board of Directors (art@iiug.org) >> >> 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 Wed, Jun 30, 2010 at 4:51 PM, fandelau <fandelau@gmail.com> wrote: >> >>> On Jun 16, 6:08 am, Art Kagel <art.ka...@gmail.com> wrote: >>> > Just go to the IIUG Software Repository (www.iiug.org/software) and >>> download >>> > my package ratios.shr_ak. You will find the script newratios.ksh and >>> an SQL >>> > file ratios.sql. Run the ratios.sql script in the sysmaster database >>> where >>> > it will install a stored procedure. Then you can just run the >>> newratios.ksh >>> > to get a report of these metrics for the total engine and separately >>> for >>> > each pagesize cache. >>> > >>> > Art >>> > >>> > Art S. Kagel >>> > Advanced DataTools (www.advancedatatools.com) >>> > IIUG Board of Directors (a...@iiug.org) >>> > >>> > 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 Wed, Jun 16, 2010 at 8:37 AM, Toni Arte <toni.a...@iki.fi> wrote: >>> > > Hi all, >>> > >>> > > During my recent performance studies I came across these metrics. As >>> I >>> > > found it quite difficult to calculate these from the output of >>> 'onstat >>> > > -p', I thought there must be a better way. I finally ended up with >>> > > these SQL queries to calculate the metrics. >>> > >>> > > Bufwaits ratio: >>> > > --- >>> > > echo "select 'bufwaits ratio: ' || trunc(100 * (select value from >>> > > sysprofile where name='buffwts')/((select value from sysprofile where >>> > > name='bufwrites')+(select value from sysprofile where >>> > > name='pagreads')),2) || ' %' from systables where tabid = 1 " | >>> > > dbaccess sysmaster >>> > > --- >>> > >>> > > Buffer turnover rate: >>> > > --- >>> > > echo "select 'buffer turnover rate: ' || trunc(3600 * ((((select >>> value >>> > > from sysprofile where name='bufwrites')+(select value from sysprofile >>> > > where name='pagreads'))/(select cf_effective from sysconfig where >>> > > cf_name='BUFFERS'))/((select