[Info-ingres] 4k Cache

[Paul White]

Hi Steve,

I run DM420 each hour on all my machines and find no performance hit.

I have a daily report, just tracking FIX CALLS, HITS and GREADIOS. You can imagine there is a lot of variation through the day. eg: Business hours transactions Vs Nightly batch updates and data warehouse type rebuilds.

DM420 Summary
Tue Aug 10 23:59:01 2021  8K     15147542269     14270336064        81203000
Tue Aug 10 23:59:01 2021 16K          199351          163232               0
Wed Aug 11 23:59:01 2021  2K      2288674635      2145159221        14345379
Wed Aug 11 23:59:01 2021  4K    138029033284    116068917854      7373573391
Wed Aug 11 23:59:01 2021  8K     15230093493     14344747905        84427376
Wed Aug 11 23:59:01 2021 16K          199821          163420               0
Thu Aug 12 23:59:01 2021  2K      2297930809      2153676516        14526936
Thu Aug 12 23:59:01 2021  4K    138675202115    116598765877      7414216733
Thu Aug 12 23:59:01 2021  8K     15304264245     14415316317        84572496
Thu Aug 12 23:59:01 2021 16K          200131          163544               0
Fri Aug 13 23:59:01 2021  2K      2318102537      2172851157        14666209
Fri Aug 13 23:59:01 2021  4K    139303943428    117126194520      7446085449
Fri Aug 13 23:59:01 2021  8K     15387181712     14493897241        84776536
Fri Aug 13 23:59:01 2021 16K          200586          163726               0
Sat Aug 14 23:59:01 2021  2K      2320799021      2175289002        14666209
etc


The hourly summary is stored in /tmp looks like this
Tue Aug 10 05:59:01 2021  4K    136895848951    115119235768      7313740965
Tue Aug 10 05:59:01 2021  8K     15084022752     14211247981        80677416
Tue Aug 10 05:59:01 2021 16K          199126          163142               0
Tue Aug 10 06:59:01 2021  2K      2275300590      2133421181        13842732
Tue Aug 10 06:59:01 2021  4K    136926793703    115146579337      7313836100
Tue Aug 10 06:59:01 2021  8K     15085432932     14212554643        80679795
Tue Aug 10 06:59:01 2021 16K          199126          163142               0
Tue Aug 10 07:59:01 2021  2K      2275535207      2133631420        13842732
Tue Aug 10 07:59:01 2021  4K    136953509680    115169631080      7314479057
...

If there is unusual activity I can go back to the full log to 
investigate time of day. DM420.log is in ingres/files saved for later 
investigations. I take the hourly outputs and paste them into Excel to 
calculate differences and make some pretty graphs.

See attached file dm420.txt with two scripts embedded you might be 
useful. Please excuse primary school shell programming.

run_dm420_summ runs daily.  This pulls out the last 122 lines of just the 11.59pm event.

echo "DM420 Summary" >> $daylog
run_dm420_summ -l 122 >> $daylog

dm420 runs hourly. It assumes II_DBMS_LOG is enabled. I reset it once a week to keep the log manageable

ingprenv...

II_DBMS_LOG=/home/ingres/IngresII/ingres/files/ii_dbms210905000001.%p.log

run_dm420 grabs the last xxx lines of the most recently modified ii_dbms*.log

You may choose to modify this line...

tail -280 $logname |

to make sure you can capture the whole event depending on the number of caches you have active.

Paul

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210910/6fca1b09/attachment.html>
-------------- next part --------------
/home/utils/run_dm420

#!/bin/sh
. /home/utils/ingres_env

sql iidbdb << eof >/dev/null
set trace point dm420 \g
commit \g
\q
eof

logname=`ls -1t $II_SYSTEM/ingres/files/ii_dbms*.log|head -1`
tail -280 $logname |
nawk '
BEGIN   {p=0}
/trace point dm420/     {p=1}
p==1    {print}
' > $II_SYSTEM/ingres/files/dm420.tmp

cat $II_SYSTEM/ingres/files/dm420.tmp >> $II_SYSTEM/ingres/files/dm420.log


/home/utils/run_dm420_summ
:
# Two files generated in /tmp
#
# dm420summ1.$$ hourly extract
# dm420summ2.$$ daily extract based on 23:59
#
# 16jun2016 PW use II_SYSTEM. options are mandatory now. Add option to get last X rows. stdout.
#
usage()
{
echo run_dm420_summ [-M|-m recipient] [-l numlines] [-f dm420log]
echo ""
echo -M default email address is shift7, default numlines is all
echo default dm420log is $II_SYSTEM/ingres/files/dm420.log
}

if [ "II_SYSTEM" = "" ] ; then
        . /home/utils/default_env
        . /home/utils/default_ingres
fi

mailit=0
mailto=shift7
tail=cat
dm420log=$II_SYSTEM/ingres/files/dm420.log
tmp1=/tmp/dm420summ1.$$
tmp2=/tmp/dm420summ2.$$

while getopts Mm:l:f: c
do
    case $c in
    m)      mailit=1;mailto=$OPTARG;;
    M)      mailit=1;;
    l)      tail="tail -$OPTARG";;
    f)      dm420log=$OPTARG;;
    \?)     usage
            exit 2;;
    esac
done

cat $dm420log | nawk '
BEGIN   {dt=""
        cache=""}

/iidbdb/        {dt=substr($0,2,24) ;next}
/Summary Statistics/    {
        cache=substr($0,24,3)
        next
        }

/FIX CALLS/ {
        if ( cache != "" ) {
                getline; fix=substr($0,10,15); hit=substr($0,31,15);
                getline
                getline
                getline
                getline
                gread=substr($0,94,15)
                print dt " " cache " " fix " " hit " " gread
                cache=""
        }
}
' > $tmp1
grep "23:59" $tmp1 > $tmp2

if [ "$mailit" = "1" ] ; then
        (
#       cat dm420summ.tmp | gzip -c | uuencode dm420summ.txt.gz
        $tail $tmp2
        )| mailx -sdm420 $mailto
else
        $tail $tmp2
fi