dbawiki

free -m
             total       used       free     shared    buffers     cached
Mem:           499        214        284          0          2        169

  -  Total
free -m | grep Mem | awk '{print $2}'
499

  -  Used
free -m | grep Mem | awk '{print $3}'
65

rrdtool create /root/mem.rrd --step 60 DS:mem:GAUGE:120:0:499 RRA:MAX:0.5:1:31622400

  - !/bin/bash

  -  update data
mem_used=`free -m | grep Mem | awk '{print $3}'`
/usr/bin/rrdtool update /root/mem.rrd --template mem N:$mem_used

  -  create graph
/usr/bin/rrdtool graph /var/www/html/mem.png   \\
    -w 500 -h 150 -a PNG                       \\
    --slope-mode                               \\
    --start -3600 --end now                    \\
    --font DEFAULT:7:                          \\
    --title "Memory used (last hour)"          \\
    --watermark "`date`"                       \\
    --x-grid MINUTE:5:MINUTE:10:MINUTE:10:0:%R \\
    --alt-y-grid                               \\
    --rigid                                    \\
    --lower-limit=0                            \\
    --color BACK#363636                        \\
    --color CANVAS#000000                      \\
    --color GRID#999999                        \\
    --color MGRID#B5B5B5                       \\
    --color FONT#CCCCCC                        \\
    DEF:memory=/root/mem.rrd:mem:MAX           \\
    AREA:memory#FFD700                         \\
    LINE1:memory#FFD700:Memory used (MB)       \\
    GPRINT:memory:LAST:Last\\: %5.2lf           \\
    GPRINT:memory:AVERAGE:Avg\\: %5.2lf         \\
    GPRINT:memory:MAX:Max\\: %5.2lf             \\
    GPRINT:memory:MIN:Min\\: %5.2lf

crontab -l
  * /5 * * * * /root/update_and_create_graph.sh

  - !/usr/bin/ksh
  -  ==============================================================================
  -  Name         : dbamon_grapher.ksh
  -  Description  : Creates and updates rrdtool databases
  - 
  -  Parameters   : none
  - 
  -  Notes        : Only as 'up-to-date' as the latest files sent over
  - 
  -  Modification History
  -  ====================
  -  When      Who               What
  -  ========= ================= ==================================================
  -  06-DEC-13 Stuart Barkley    Created
  -  ==============================================================================

CWD=`dirname $0`

  -  name of the file to process
COLLECTOR_DIR="/var/www/dbdc.be.ibm.com/dba"
COLLECTOR_FILES=${COLLECTOR_DIR}/"2dbamon_spool_stats*"
RRDTOOL="/usr/bin/rrdtool"
RRDDIR="/var/www/dbdc.be.ibm.com/dba"
PNGDIR="/var/www/dbdc.be.ibm.com/html/images"

  -  =======================================
  -  thats it, nothing to change below here!
  -  =======================================


function cre_export_duration {
    rrd_dir=$1
    rrdname=$2
    $RRDTOOL create ${rrd_dir}/${rrdname}                 \\
        --start 1384000000                                \\
        -s 86400                                          \\
        DS:started:GAUGE:100000:1300000000:2000000000     \\
        DS:stopped:GAUGE:100000:1300000000:2000000000     \\
        RRA:AVERAGE:0.5:1:720
    if [[ $? -ne 0 ]]; then
        echo "cre_export_duration: failed to create ${rrd_dir}/${rrdname}"
        return 1
    fi
  -     echo "cre_export_duration: created ${rrd_dir}/${rrdname}"
    return 0
}



rm -f $RRDDIR/*rrd




  -  create the rrd databases
  -  ------------------------
for statfile in `ls -t ${COLLECTOR_FILES}`
do
    IFS="|"
    exec 0<$statfile
    while read host db type started stopped
    do
        [[ "$stopped" == "" ]] && continue
        rrdfile="export_duration_${host}_${db}.rrd"
        ls ${RRDDIR}/${rrdfile} >/dev/null 2>&1
        if [[ $? -ne 0 ]]; then
            cre_export_duration "$RRDDIR" "$rrdfile"
        fi
    done
done


  -  ---------------------------------------------
  -  update the rrd databases with the latest data
  -  ---------------------------------------------
for statfile in `ls -t ${COLLECTOR_FILES}`
do
  - echo "statfile: $statfile"
    cat $statfile | sort -nk4 | while IFS=\\| read host db type started stopped
    do
        [[ "$stopped" == "" ]] && continue
        rrdfile="export_duration_${host}_${db}.rrd"
        $RRDTOOL update ${RRDDIR}/${rrdfile} ${started}:${started}:${stopped}
        [[ $? -ne 0 ]] && echo "nok: $?"
    done
done


  - rrdtool fetch /var/www/dbdc.be.ibm.com/dba/export_duration_uaccdba22_ENDQ1.rrd AVERAGE --start "-1week"


  -          --color BACK#996 \\    # the bit where the title is written
  -          --color CANVAS#996 \\  # the background to the graph area
  -          --color GRID#0000FF \\ # the grid lines (graph paper)
  -          --color MGRID#fffffc \\
  -          --color FONT#CCCCCC \\


for statfile in `ls -t ${COLLECTOR_FILES}`
do
    awk -F'|' '{print $1"|"$2}' $statfile | sort | uniq | while IFS=\\| read host db
    do
echo "host:$host;db:$db"
        [[ "$db" == "" ]] && continue
        $RRDTOOL graph ${PNGDIR}/export_duration_${host}_${db}.png                 \\
         -a PNG                                                                    \\
         -s "-14days"                                                              \\
         --slope-mode                                                              \\
         --title="Export duration for ${db} on ${host}"                            \\
         --vertical-label Minutes                                                  \\
         --alt-y-grid \\
         --rigid   \\
         --color BACK#ffffd0    \\
         --color CANVAS#ffffd0   \\
         --color GRID#0000FF       \\
         --color MGRID#996      \\
         --color FONT#000      \\
         "DEF:started=${RRDDIR}/export_duration_${host}_${db}.rrd:started:AVERAGE" \\
         "DEF:stopped=${RRDDIR}/export_duration_${host}_${db}.rrd:stopped:AVERAGE" \\
         "CDEF:duration=stopped,started,-,60,/"                                    \\
         'AREA:duration#996:Duration'
    done
done

rrdtool info <database>
eg:
rrdtool info export_duration_wdcrhbp05_ARISPPMP.rrd

rrdtool fetch <database> <consolidation function> --start <start time in epoch seconds>
eg:
rrdtool fetch export_duration_wdcrhbp05_ARISPPMP.rrd AVERAGE --start now-100000

Most of these parameters are the same as those used to graph the data

rrdtool xport --json -s now-1month DEF:rmantime=dbamon_rman-duration_SOL_solax025_BI4REFP.rrd:rmantime:MAX CDEF:mins=rmantime,60,/ XPORT:mins

or

rrdtool xport -s now-1month DEF:rmantime=dbamon_rman-duration_SOL_solax025_BI4REFP.rrd:rmantime:MAX CDEF:mins=rmantime,60,/ XPORT:mins > xport.xml

and trim the results to bare data

cat xport.xml | grep '<row>' | grep -v NaN | sed -e 's/ //g' -e 's/<row><t>//' -e 's/<\\/t><v>/ /' -e 's/<\\/v><\\/row>//'

This function is not (yet) available and so the best work-around is:

• Use RRDTool Dump to export RRD file to XML
• Open the XML file, find and edit the bad data
• Restore the RRD file using RRDTool Restore

rrdtool dump filename.rrd [[filename.xml]] [[--header|-h {none,xsd,dtd}]] [[--no-header]] [[--daemon address]] > filename.xml
eg:
rrdtool dump export_duration_wdcrhbp05_ARISPPMP.rrd dumped.xml

and

rrdtool restore filename.xml filename.rrd [[--range-check|-r]] [[--force|-f]]
eg:
rrdtool restore dumped.xml export_duration_wdcrhbp05_ARISPPMP.rrd -f

• http://oss.oetiker.ch/rrdtool/
• Original page
• Creating an initial RRD
• Better explanations of RRA