 | |
SMI queries to calculate the buffer wait ratio, the buffer turnoverrate and the readahead utilization
comp.databases.informix comp.databases.informix
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
SMI queries to calculate the buffer wait ratio, the buffer turnoverrate and the readahead utilization -
06-16-2010
, 08:37 AM
|
| ' %' from systables where tabid = 1 " | dbaccess sysmaster --- |
These metrics are explained for example here:
http://www.mofeel.net/246-comp-datab...rmix/1151.aspx
--
Toni
#2
| |||
| |||
|
|
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-datab...rmix/1151.aspx -- Toni _______________________________________________ Informix-list mailing list Informix-list (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
#3
| |||
| |||
|
|
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... (AT) iiug (DOT) 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, orby 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... (AT) iki (DOT) 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-datab...rmix/1151.aspx -- Toni _______________________________________________ Informix-list mailing list Informix-l... (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
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
#4
| |||
| |||
|
|
On Jun 16, 6:08 am, Art Kagel <art.ka... (AT) gmail (DOT) 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... (AT) iiug (DOT) 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... (AT) iki (DOT) 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-datab...rmix/1151.aspx -- Toni _______________________________________________ Informix-list mailing list Informix-l... (AT) iiug (DOT) 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 (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
#5
| |||
| |||
|
|
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, justa 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.0000 00,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 (AT) gmail (DOT) 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 (AT) iiug (DOT) 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 (AT) gmail (DOT) com> wrote: On Jun 16, 6:08 am, Art Kagel <art.ka... (AT) gmail (DOT) 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... (AT) iiug (DOT) 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... (AT) iki (DOT) 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-datab...rmix/1151.aspx -- Toni _______________________________________________ Informix-list mailing list Informix-l... (AT) iiug (DOT) 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 (AT) iiug (DOT) 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_dba (AT) yahoo (DOT) com fandelau (AT) hotmail (DOT) com ==================================== |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.