Re: lock table overflow
How do I get BR and BTR?
This is the onstat -p output:
Informix Dynamic Server Version 7.31.TD2 -- On-Line -- Up 06:56:56 --
37120 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
5787892 7443911 69016835 91.61 35540 52447 368971 90.37
isamtot open start read write rewrite delete commit
rollbk
19552342 561605 2665827 11353618 79866 58637 2207 16541 0
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 28 4155.83 1772.55 78 158
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
735373 36 217446454 0 0 17 1055 36247
ixda-RA idx-RA da-RA RA-pgsused lchwaits
741504 1437 4454388 5156644 1387
sending to informix-list
On Thu, 21 Aug 2003 17:00:08 -0400, Bill Hamilton wrote:
> How do I get BR and BTR?
<SNIP>
The ratios are:
BR = (bufwaits / (bufwrits + pagreads)) * 100.00
BTR = ((bufwrits + pagreads) / BUFFERS) / <time since stats were zero'd>
Here is an update to a script Marat Kotik posted to calculate them for you:
Art S. Kagel
<FILE: ratios.ksh>
#
# Written by Marat Kotik - Contributed 7/26/2000
# Enhanced by Art S. Kagel
#
typeset -i BufUsed
typeset -i BUFFERS
BUFFERS=`awk '$1 == "BUFFERS"{print $2; exit;}' $INFORMIXDIR/etc/$ONCONFIG`
onstat -p
onstat -p | awk '
BEGIN { print ""; }
/[a-zA-Z]/ {
for(i=1; i<=NF; i++){
name[i] = $i;
}
}
/[0-9]/ {
for(i=1; i<=NF; i++){
content[name[i]] = $i;
}
}
END {
ixdaRA = content["ixda-RA"];
idxRA = content["idx-RA"];
daRA = content["da-RA"];
RApgsused = content["RA-pgsused"];
print "ReadAhead Utilization (UR): ", (RApgsused / (ixdaRA + idxRA + daRA)) * 100, "%";
bufwaits = content["bufwaits"];
bufwrits = content["bufwrits"];
pagreads = content["pagreads"];
print "Bufwaits Ratio(BR): ", ((bufwaits/(pagreads + bufwrits)) * 100), "%";
}'
BufUsed=`onstat -p | awk '
BEGIN { print ""; }
/[a-zA-Z]/ {
for(i=1; i<=NF; i++){
name[i] = $i;
}
}
/[0-9]/ {
for(i=1; i<=NF; i++){
content[name[i]] = $i;
}
}
END {
bufwrits = content["bufwrits"];
pagreads = content["pagreads"];
print bufwrits + pagreads
}'`
dbaccess sysmaster - <<EOF >/dev/null 2>&1
unload to '/tmp/ratios.$$.cleared'
select sh_curtime - sh_pfclrtime from sysshmvals;EOF
cnt=0
for itm in `awk -F'|' '{ hrs=$1/3600; mins=($1 % 3600)/60; secs=($1 % 60);
print hrs, mins, secs;}' /tmp/ratios.$$.cleared`; do
tim[$cnt]=$itm
cnt=$(( cnt + 1 ))
done
typeset -i tim[0]
typeset -i tim[1]
typeset -i tim[2]
echo "scale=4" >/tmp/ratios.$$.bc;
echo "(($BufUsed/$BUFFERS)/(${tim[0]}+(${tim[1]}/60)+(${tim[2]}/3600)))" >>/tmp/ratios.$$.bc;
echo -n 'Average Buffer Turnover Rate (BTR): ';
BTR=`bc </tmp/ratios.$$.bc`;
if [[ $BTR == \\.* ]]; then BTR=0$BTR; fi
echo "$BTR/HR";
echo ' ';
awk -F'|' '{ hrs=$1/3600; mins=($1 % 3600)/60; secs=($1 % 60);
printf "Stats reset %02.2d:%02.2d:%02.2d ago.\\n", hrs, mins, secs;}' </tmp/ratios.$$.cleared
echo "\\n----------------------------------------------------------\\n";
echo "The UR should ideally be VERY near 100% - the higher the better.";
echo "The BR should be below 7% - the lower the better.";
echo "The BTR should be small. BTR=6.0000 implies all buffers are overwritten "
echo "every 10 minutes! "
echo "Check all ratios over peak periods and long term."
echo "Check CDI archives for more info.\\n";
rm -f /tmp/ratios.$$.cleared /tmp/ratios.$$.bc
<END FILE>