Categories: Alternative - Computer - Microsoft - Miscellaneous - Recreational - Science - Society - Talk - Other - Search


Computer Discussion: SMI Queries To Calculate The Buffer Wait Ratio, The Buffer Turnoverrate And The Readahead UtilizationGroup: comp.databases.informix
Discussion: SMI Queries To Calculate The Buffer Wait Ratio, The Buffer Turnoverrate And The Readahead Utilization
Add this discussion to your Favorites
Posts: 5

Page: 1   (First | Last)

Toni Arte
Wed, 16 Jun 2010 05:37:42 -0700 (PDT)
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


Art Kagel
Jun 16, 2010 - 09:08:39 am EST
--0003255578bad0ff700489256c6e
Content-Type: text/plain; charset=ISO-8859-1

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 [No Spam] 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 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 [No Spam] iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>

--0003255578bad0ff700489256c6e
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Just go to the IIUG Software Repository (www.iiug.org/software) and download my package ratios.shr_ak.=A0=
You will find the script newratios.ksh and an SQL file ratios.sql.=A0 Run =
the ratios.sql script in the sysmaster database where it will install a sto=
red procedure.=A0 Then you can just run the newratios.ksh to get a report o=
f these metrics for the total engine and separately for each pagesize cache=
.

ArtArt S. KagelAdvanced DataTools (www.advancedatatools.com)IIUG Boa=
rd of Directors (art [No Spam] iiug.org)D=
isclaimer: 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. =A0Neither do those opinions reflect those of other individua=
ls affiliated with any entity with which I am affiliated nor those of the e=
ntities themselves.


On Wed, Jun 16, 2010 at 8:37 AM, Toni Ar=
te <toni.arte [No Spam] 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 f=
rom
sysprofile where name=3D'buffwts')/((select value from sysprofile w=
here
name=3D'bufwrites')+(select value from sysprofile where
name=3D'pagreads')),2) || ' %' from systables where tabid =
=3D 1 " |
dbaccess sysmaster
---

Buffer turnover rate:
---
echo "select 'buffer turnover rate: ' || trunc(3600 * ((((sele=
ct value
from sysprofile where name=3D'bufwrites')+(select value from syspro=
file
where name=3D'pagreads'))/(select cf_effective from sysconfig where=

cf_name=3D'BUFFERS'))/((select dbinfo('utc_current')-sh_pfc=
lrtime from
sysshmvals))),2) from systables where tabid =3D 1 " | dbaccess sysmast=
er
---

Readahead utilization:
---
echo "select 'readahead utilization: ' || trunc(100 * (select =
value
from sysprofile where name=3D'rapgs_used')/((select value from
sysprofile where name=3D'btradata')+(select value from sysprofile w=
here
name=3D'btraidx')+(select value from sysprofile where name=3D'd=
pra')),2)
|| ' %' from systables where tabid =3D 1 " | dbaccess sysmaste=
r
---

These metrics are explained for example here:
http://www.mofeel.net/246-comp-databases-informix/1151.aspx
--
Toni
_______________________________________________
Informix-list mailing list
Informix-list [No Spam] iiug.org
http://www.iiug.org/mailman/listinfo/informix-list


--0003255578bad0ff700489256c6e--



Fandelau
Jun 30, 2010 - 04:51:54 pm EST
On Jun 16, 6:08=A0am, Art Kagel wrote:
> Just go to the IIUG Software Repository (www.iiug.org/software) and downl=
oad
> my package ratios.shr_ak. =A0You will find the script newratios.ksh and a=
n SQL
> file ratios.sql. =A0Run the ratios.sql script in the sysmaster database w=
here
> it will install a stored procedure. =A0Then you can just run the newratio=
s.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... [No Spam] 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 othe=
r
> organization with which I am associated either explicitly, implicitly, or=
by
> inference. =A0Neither do those opinions reflect those of other individual=
s
> 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 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=3D'buffwts')/((select value from sysprofile where
> > name=3D'bufwrites')+(select value from sysprofile where
> > name=3D'pagreads')),2) || ' %' from systables where tabid =3D 1 " |
> > dbaccess sysmaster
> > ---
>
> > Buffer turnover rate:
> > ---
> > echo "select 'buffer turnover rate: ' || trunc(3600 * ((((select value
> > from sysprofile where name=3D'bufwrites')+(select value from sysprofile
> > where name=3D'pagreads'))/(select cf_effective from sysconfig where
> > cf_name=3D'BUFFERS'))/((select dbinfo('utc_current')-sh_pfclrtime from
> > sysshmvals))),2) from systables where tabid =3D 1 " | dbaccess sysmaste=
r
> > ---
>
> > Readahead utilization:
> > ---
> > echo "select 'readahead utilization: ' || trunc(100 * (select value
> > from sysprofile where name=3D'rapgs_used')/((select value from
> > sysprofile where name=3D'btradata')+(select value from sysprofile where
> > name=3D'btraidx')+(select value from sysprofile where name=3D'dpra')),2=
)
> > || ' %' from systables where tabid =3D 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... [No Spam] 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



Art Kagel
Jun 30, 2010 - 10:30:20 pm EST
--001485f6ca5ca08d4b048a4a419f
Content-Type: text/plain; charset=ISO-8859-1

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 [No Spam] 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 wrote:

> On Jun 16, 6:08 am, Art Kagel 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... [No Spam] 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 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... [No Spam] 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 [No Spam] iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>

--001485f6ca5ca08d4b048a4a419f
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Sure it does!=A0 Newratios.ksh prints out the total metrics and then recalc=
ulates them for each buffer page size cache.ArtArt S. KagelAdvanced DataTools (www.advancedatatools.com)

IIUG Board of Directors (art [No Spam] iiug.org)Disclaimer: Please keep in mind that my own opinions are my own opin=
ions and do not reflect on my employer, Advanced DataTools, the IIUG, nor a=
ny other organization with which I am associated either explicitly, implici=
tly, or by inference. =A0Neither do those opinions reflect those of other i=
ndividuals affiliated with any entity with which I am affiliated nor those =
of the entities themselves.


On Wed, Jun 30, 2010 at 4:51 PM, fandela=
u <fandelau [No Spam] gmai=
l.com> wrote:

On Jun 16, 6:08=A0am, Art Kagel <art.ka... [No Spam] gmail.com> wrote:
> Just go to the IIUG Software Repository (www.iiug.org/software) and download
> my package ratios.shr_ak. =A0You will find the script newratios.ksh an=
d an SQL
> file ratios.sql. =A0Run the ratios.sql script in the sysmaster databas=
e where
> it will install a stored procedure. =A0Then you can just run the newra=
tios.ksh
> to get a report of these metrics for the total engine and separately f=
or
> each pagesize cache.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (a... [No Spam] i=
iug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinio=
ns and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any o=
ther
> organization with which I am associated either explicitly, implicitly,=
or by
> inference. =A0Neither do those opinions reflect those of other individ=
uals
> affiliated with any entity with which I am affiliated nor those of the=

> entities themselves.
>
>
>
> On Wed, Jun 16, 2010 at 8:37 A=
M, Toni Arte <toni.a... [No Spam] iki.fi&g=
t; 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 &#=
39;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 * (sele=
ct value from
> > sysprofile where name=3D'buffwts')/((select value from sy=
sprofile where
> > name=3D'bufwrites')+(select value from sysprofile where
> > name=3D'pagreads')),2) || ' %' from systables whe=
re tabid =3D 1 " |
> > dbaccess sysmaster
> > ---
>
> > Buffer turnover rate:
> > ---
> > echo "select 'buffer turnover rate: ' || trunc(3600 =
* ((((select value
> > from sysprofile where name=3D'bufwrites')+(select value f=
rom sysprofile
> > where name=3D'pagreads'))/(select cf_effective from sysco=
nfig where
> > cf_name=3D'BUFFERS'))/((select dbinfo('utc_current=
9;)-sh_pfclrtime from
> > sysshmvals))),2) from systables where tabid =3D 1 " | dbacce=
ss sysmaster
> > ---
>
> > Readahead utilization:
> > ---
> > echo "select 'readahead utilization: ' || trunc(100 =
* (select value
> > from sysprofile where name=3D'rapgs_used')/((select value=
from
> > sysprofile where name=3D'btradata')+(select value from sy=
sprofile where
> > name=3D'btraidx')+(select value from sysprofile where nam=
e=3D'dpra')),2)
> > || ' %' from systables where tabid =3D 1 " | dbacces=
s sysmaster
> > ---
>
> > These metrics are explained for example here:
> >http://www.mofeel.net/246-comp-databases-informix/1=
151.aspx
> > --
> > Toni
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l.=
.. [No Spam] iiug.org
> >http://www.iiug.org/mailman/listinfo/infor=
mix-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 [No Spam] iiug.org
http://www.iiug.org/mailman/listinfo/informix-list


--001485f6ca5ca08d4b048a4a419f--



Art Kagel
Jul 11, 2010 - 10:56:41 am EST
--000e0cd5cec2326607048b1dd963
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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, i=
f
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 [No Spam] iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions an=
d
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or b=
y
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 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 se=
em
> 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=3Drmt_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 p*****e 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 day=
s
> 05:27:54 -- 10799104 Kbytes
>
> $> uname -a
> SunOS irsadb1 5.10 Generic_141444-09 sun4v sparc SUNW,T5240
> $> onstat -c |grep "^BUFFER"
> BUFFERPOOL
> size=3D2K,buffers=3D10240,lrus=3D8,lru_min_dirty=3D70.000000,lru_max_dir=
ty=3D80.000000
> BUFFERPOOL
> size=3D16K,buffers=3D400000,lrus=3D128,lru_min_dirty=3D70.000000,lru_max=
_dirty=3D80.000000
> $> onstat -g buf
>
> IBM Informix Dynamic Server Version 11.10.FC3 -- On-Line -- Up 36 day=
s
> 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 26646106=
3
> 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 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 [No Spam] 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, implici=
tly,
>> or by inference. Neither do those opinions reflect those of other
>> individuals affiliated with any entity with which I am affiliated nor th=
ose
>> of the entities themselves.
>>
>>
>>
>> On Wed, Jun 30, 2010 at 4:51 PM, fandelau wrote:
>>
>>> On Jun 16, 6:08 am, Art Kagel 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... [No Spam] 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 th=
e
>>> > entities themselves.
>>> >
>>> >
>>> >
>>> > On Wed, Jun 16, 2010 at 8:37 AM, Toni Arte wrote:
>>> > > Hi all,
>>> >
>>> > > During my recent performance studies I came across these metrics. A=
s
>>> 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=3D'buffwts')/((select value from sysprofile w=
here
>>> > > name=3D'bufwrites')+(select value from sysprofile where
>>> > > name=3D'pagreads')),2) || ' %' from systables where tabid =3D 1 " |
>>> > > dbaccess sysmaster
>>> > > ---
>>> >
>>> > > Buffer turnover rate:
>>> > > ---
>>> > > echo "select 'buffer turnover rate: ' || trunc(3600 * ((((select
>>> value
>>> > > from sysprofile where name=3D'bufwrites')+(select value from syspro=
file
>>> > > where name=3D'pagreads'))/(select cf_effective from sysconfig where
>>> > > cf_name=3D'BUFFERS'))/((select dbinfo('utc_current')-sh_pfclrtime f=
rom
>>> > > sysshmvals))),2) from systables where tabid =3D 1 " | dbaccess
>>> sysmaster
>>> > > ---
>>> >
>>> > > Readahead utilization:
>>> > > ---
>>> > > echo "select 'readahead utilization: ' || trunc(100 * (select value
>>> > > from sysprofile where name=3D'rapgs_used')/((select value from
>>> > > sysprofile where name=3D'btradata')+(select value from sysprofile w=
here
>>> > > name=3D'btraidx')+(select value from sysprofile where name=3D'dpra'=
)),2)
>>> > > || ' %' from systables where tabid =3D 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... [No Spam] 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 [No Spam] iiug.org
>>> http://www.iiug.org/mailman/listinfo/informix-list
>>>
>>
>>
>
>
> --
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> "Any man who can drive safely while kissing a
> pretty g**l is simply not giving the kiss the
> attention it deserves."
> Albert Einstein
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Ramón Rey
> ifmx_dba [No Spam] yahoo.com
> fandelau [No Spam] hotmail.com
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>

--000e0cd5cec2326607048b1dd963
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hm, I thought that I'd updated IIUG long ago.=A0 The source I have here=
works fine, I'll upload an update today.=A0 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.

ArtArt S. KagelAdvanced DataTools (www.advancedatatools.com)IIUG Boa=
rd of Directors (art [No Spam] iiug.org)D=
isclaimer: 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.=A0 Neither do those opinions reflect those of other individua=
ls affiliated with any entity with which I am affiliated nor those of the e=
ntities themselves.


On Fri, Jul 9, 2010 at 7:49 PM, Ramon Re=
y <fandelau [No Spam] gmai=
l.com> wrote:

Art,Here is the output of the newratios.ksh script as r=
an on my system, along with other output I think can be useful.=A0Please let me know what am I doing wrong, because not only the numbers se=
em 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 informationTh=
anks!Ramon.$> who am iinformix =A0 pts/2 =A0 =A0 =A0 =A0Jul =A09=
16:34 =A0 =A0(myserver)


$> dbaccess sysmaster ratios.sqlDatabase selected.Routine dropped.Routine created.


Database closed.$> INFORMIXSERVER=3Drmt_dbms10 newratios.ksh=
Metric Ratio Report Summary For All Caches=A0=A0 =
=A0 =A0 =A0ReadAhead Utilization: =A0 =A0 =A0100.000000%


=A0=A0 =A0 =A0 =A0Bufwaits Ratio: =A0 =A0 =A0 =A0 =A0 =A0 1.070000%=A0=A0 =A0 =A0 =A0Buffer Turnover Rate: =A0 =A0 =A0 2087.38/hr=A0=A0 =A0 =A0 =A0Used Buffer Turnover Rate: =A0 52.10/hr----------------------------------------------=
------------


The RAU should ideally be VERY near 100% - the higher t=
he better.The BR should be below 7% - the lower the better.The BTR and UBTR should ideally be less than 10. =A0UBTR 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. =A0U=
BTR wasa unsuccessful attempt a more accurate p*****e the nature=
of the=A0


cache churning which the BTR reports.Check C=
DI archives or the Informix FAQ for more info.$&g=
t; which newratios.ksh./newratios.ksh$> onstat -


IBM Informix Dynamic Server Version 11.10.FC3 =A0 =A0 -=
- On-Line -- Up 36 days 05:27:54 -- 10799104 Kbytes$> uname -a=A0SunOS irsadb1 5.10 Generic_141444-09 sun4v sp=
arc SUNW,T5240


$> onstat -c |grep "^BUFFER"BUFFERPOOL =A0 =A0=
=A0size=3D2K,buffers=3D10240,lrus=3D8,lru_min_dirty=3D70.000000,lru_max_di=
rty=3D80.000000BUFFERPOOL =A0 =A0 =A0size=3D16K,buffers=3D400000=
,lrus=3D128,lru_min_dirty=3D70.000000,lru_max_dirty=3D80.000000


$> onstat -g bufIBM Informix Dynamic Serv=
er Version 11.10.FC3 =A0 =A0 -- On-Line -- Up 36 days 05:35:33 -- 10799104 =
KbytesProfileBuffer poo=
l page size: 2048


dskreads =A0 pagreads =A0 bufreads =A0 %cached dskwrits =A0 pagwrits =
=A0 bufwrits =A0 %cached233549 =A0 =A0 1267613 =A0 =A061551488 =
=A0 99.62 =A0 734892 =A0 =A0 1479212 =A0 =A014105105 =A0 =A094.79=A0bufwrits_sinceckpt =A0bufwaits =A0 ovbuff =A0 =A0 flushe=
s =A0=A0


6 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 1382 =A0 =A0 =A0 0 =A0 =A0 =A0 =
=A0 =A01008 =A0 =A0 =A0Fg Writes =A0 =A0 LRU Writ=
es =A0 =A0Avg. LRU Time Chunk Writes=A00 =A0 =A0 =A0 =A0 =A0 =A0=
0 =A0 =A0 =A0 =A0 =A0 =A0 NaN =A0 =A0 =A0 =A0 =A0 75269 =A0 =A0 =A0 =A0


Buffer pool page size: 16384dskreads =A0 pagreads =A0 bufre=
ads =A0 %cached dskwrits =A0 pagwrits =A0 bufwrits =A0 %cached97=
1642638 =A018293394920 12974074403 92.51 =A0 4136654 =A0 =A033324056 =A0 26=
6461063 =A0 98.45=A0


bufwrits_sinceckpt =A0bufwaits =A0 ovbuff =A0 =A0 flush=
es =A0=A07033 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0197852131 =A00 =A0 =
=A0 =A0 =A0 =A01013 =A0 =A0 =A0Fg Writes =A0 =A0 =
LRU Writes =A0 =A0Avg. LRU Time Chunk Writes=A0


0 =A0 =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 =A0 =A0 =A0 NaN =A0 =A0 =A0 =
=A0 =A0 694352 =A0 =A0 =A0=A0Fast Cache Statsgets =A0 =A0 =A0 hits =A0 =A0 =A0 %hits =A0 puts =A0 =A0 =A0600654452 =A0599243198 =A099.77 =A0 119806969=A0


$>On Wed, Jun 30, 2010 at =
7:30 PM, Art Kagel <art.kagel [No Spam] gmail.com> wrote:


Sure it does!=A0 =
Newratios.ksh prints out the total metrics and then recalculates them for e=
ach buffer page size cache.


ArtArt S. KagelAdvanced DataTools (www.advancedatatools.co=
m)

IIUG Board of Directors (=
art [No Spam] iiug.org)Disclaimer: Please keep in mind that my own opinio=
ns are my own opinions and do not reflect on my employer, Advanced DataTool=
s, the IIUG, nor any other organization with which I am associated either e=
xplicitly, implicitly, or by inference. =A0Neither do those opinions reflec=
t those of other individuals affiliated with any entity with which I am aff=
iliated nor those of the entities themselves.





On Wed, Jun 30, 2010 at 4:51 PM, fandela=
u <fandelau [No Spam] gmail.com> wrote:




On Jun 16, 6:08=A0am, Art Kagel <art.ka... [No Spam] gmail.com> wrote:
> Just go to the IIUG Software Repository (www.iiug.org/software) and download
> my package ratios.shr_ak. =A0You will find the script newratios.ksh an=
d an SQL
> file ratios.sql. =A0Run the ratios.sql script in the sysmaster databas=
e where
> it will install a stored procedure. =A0Then you can just run the newra=
tios.ksh
> to get a report of these metrics for the total engine and separately f=
or
> each pagesize cache.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (a... [No Spam] iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinio=
ns and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any o=
ther
> organization with which I am associated either explicitly, implicitly,=
or by
> inference. =A0Neither do those opinions reflect those of other individ=
uals
> 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... [No Spam] 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 &#=
39;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 * (sele=
ct value from
> > sysprofile where name=3D'buffwts')/((select value from sy=
sprofile where
> > name=3D'bufwrites')+(select value from sysprofile where
> > name=3D'pagreads')),2) || ' %' from systables whe=
re tabid =3D 1 " |
> > dbaccess sysmaster
> > ---
>
> > Buffer turnover rate:
> > ---
> > echo "select 'buffer turnover rate: ' || trunc(3600 =
* ((((select value
> > from sysprofile where name=3D'bufwrites')+(select value f=
rom sysprofile
> > where name=3D'pagreads'))/(select cf_effective from sysco=
nfig where
> > cf_name=3D'BUFFERS'))/((select dbinfo('utc_current=
9;)-sh_pfclrtime from
> > sysshmvals))),2) from systables where tabid =3D 1 " | dbacce=
ss sysmaster
> > ---
>
> > Readahead utilization:
> > ---
> > echo "select 'readahead utilization: ' || trunc(100 =
* (select value
> > from sysprofile where name=3D'rapgs_used')/((select value=
from
> > sysprofile where name=3D'btradata')+(select value from sy=
sprofile where
> > name=3D'btraidx')+(select value from sysprofile where nam=
e=3D'dpra')),2)
> > || ' %' from systables where tabid =3D 1 " | dbacces=
s sysmaster
> > ---
>
> > These metrics are explained for example here:
> >http://www.mofeel.net/246-comp-databases-informix/1=
151.aspx
> > --
> > Toni
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l... [No Spam] 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 [No Spam] i=
iug.org
http://www.iiug.org/mailman/listinfo/informix-list

-- =3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D"Any man who can drive safely while kissin=
g a pretty g**l is simply not giving the kiss the attention it dese=
rves."


Albert Einstein=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DRamón Reyifmx_dba [No Spam] yahoo.comfandelau [No Spam] hot=
mail.com

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D




--000e0cd5cec2326607048b1dd963--



Page: 1   (First | Last)


Search Tags:
newratios.ksh
advanced data tools "ratios.sql"
Calculating buffer to buffer turnover
dbinfo("utc_current") example
how to calculate buffer turn over rate
informix faq buffer turn over rate
informix how to calculate buffer turn over rate
informix Read Ahead Utilization
informix+UBTR+ratio
kagel ubtr
select dbinfo('utc_current')
smi queries to calculate the buffer wait ratio, the



@ 2014 UsenetMessages.com | Privacy | Try: AnswerDB