Re: SMI queries to calculate the buffer wait ratio, the buffer turnover rate and the readahead utilization
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 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-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
Unfortunately, and as far as I know, newratios.ksh (either Art's or
David Kleppinger's version) wont account for 2 or more buffer sizes...
I'm still looking for a BTR calculation for 2 or more buffer sizes...
my instance has both 2k and 16k buffer sizes and I got a BTR of
1706.68/hr which sounds a bit off the scale.
Art? David?
Cheers!
Almost Darth Fandelau
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 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-l...@iiug.org
> > >http://www.iiug.org/mailman/listinfo/informix-list
>
> Unfortunately, and as far as I know, newratios.ksh (either Art's or
> David Kleppinger's version) wont account for 2 or more buffer sizes...
> I'm still looking for a BTR calculation for 2 or more buffer sizes...
> my instance has both 2k and 16k buffer sizes and I got a BTR of
> 1706.68/hr which sounds a bit off the scale.
> Art? David?
> Cheers!
>
> Almost Darth Fandelau
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>