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

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 dbinfo('utc_current')-sh_pfclrtime from sysshmvals))),2) from systables where tabid = 1 " | dbaccess sysmaster --- Readahead utilization: --- echo "select 'readahead utilization: ' || trunc(100 * (select value from sysprofile where name='rapgs_used')/((select value from sysprofile where name='btradata')+(select value from sysprofile where name='btraidx')+(select value from sysprofile where name='dpra')),2) || ' %' from systables where tabid = 1 " | dbaccess sysmaster --- These metrics are explained for example here: http://www.mofeel.net/246-comp-databases-informix/1151.aspx -- Toni

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 (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 16, 2010 at 8:37 AM, Toni Arte <toni.arte@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 dbinfo('utc_current')-sh_pfclrtime from > sysshmvals))),2) from systables where tabid = 1 " | dbaccess sysmaster > --- > > Readahead utilization: > --- > echo "select 'readahead utilization: ' || trunc(100 * (select value > from sysprofile where name='rapgs_used')/((select value from > sysprofile where name='btradata')+(select value from sysprofile where > name='btraidx')+(select value from sysprofile where name='dpra')),2) > || ' %' from systables where tabid = 1 " | dbaccess sysmaster > --- > > These metrics are explained for example here: > http://www.mofeel.net/246-comp-databases-informix/1151.aspx > -- > Toni > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >