Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

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

599 views
Skip to first unread message

Toni Arte

unread,
Jun 16, 2010, 8:37:42 AM6/16/10
to
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

unread,
Jun 16, 2010, 9:08:39 AM6/16/10
to Toni Arte, inform...@iiug.org
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.



--
Toni
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

fandelau

unread,
Jun 30, 2010, 4:51:54 PM6/30/10
to
> > 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

Art Kagel

unread,
Jun 30, 2010, 10:30:20 PM6/30/10
to fandelau, inform...@iiug.org
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 (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.



Art Kagel

unread,
Jul 11, 2010, 10:56:41 AM7/11/10
to inform...@iiug.org
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 (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 Fri, Jul 9, 2010 at 7:49 PM, Ramon Rey <fand...@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....@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 (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 30, 2010 at 4:51 PM, fandelau <fand...@gmail.com> wrote:
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list




--
====================================
"Any man who can drive safely while kissing a
pretty girl is simply not giving the kiss the
attention it deserves."
Albert Einstein
====================================
Ramón Rey
ifmx...@yahoo.com
fand...@hotmail.com
====================================

0 new messages