#!/bin/bash
#
#  $Id: mysqlar_graph.sh 40 2006-01-20 20:26:11Z dewitge $
#	
#  MySQL Activity Report
#  Script to generate graphics for the MySQL Activity Report
#
#  Copyright 2004 Gert Dewit <gert.dewit@sos.be>

config=${config:="/etc/mysqlard/mysqlard.cnf"}
# Source the config script
. ${config}

########################################################################
# Options, you can override them by setting them before running this
# script. eg daily=1 start='2004-01-01 00:00'  imgdir=/archive mysqlar_graph
hourly=${hourly:=0}
daily=${daily:=0}
weekly=${weekly:=0}
monthly=${monthly:=0}
yearly=${yearly:=0}
start=${start:=""}
ext=${ext:=""}

########################################################################
# Settings, change them in /etc/mysqlard/mysqlard.cnf or you can override
# them here, but that's not recommended. Read the comments in
# /etc/mysqlard/mysqlard.cnf if you want to know what you're changing.

datadir=${datadir:="/var/lib/mysqlard"}
imgdir=${imgdir:="${datadir}"}
RRDTOOL=${RRDTOOL:="/usr/bin/rrdtool"}
graphwidth=${graphwidth:="400"}
graphheight=${graphheight:="100"}
graphformat=${graphformat:="PNG"}
backcol=${backcol:="#FFFFFF"}
canvascol=${canvascol:="#FFFFFF"}
shadeacol=${shadeacol:="#FFFFFF"}
shadebcol=${shadebcol:="#FFFFFF"}
gridcol=${gridcol:="#000000"}
mgridcol=${mgridcol:="#000000"}
fontcol=${fontcol:="#000000"}
framecol=${framecol:="#000000"}
arrowcol=${arrowcol:="#FF0000"}
########################################################################
goodcol="#AAFFAA"
badcol="#FF6666"
datacol="#0000AA"

# don't change anything below this line, but heck, I can't stop you ;-)
confile=${confile:="${datadir}/connections.rrd"}
tabfile=${tabfile:="${datadir}/table_cache.rrd"}
keyfile=${keyfile:="${datadir}/key_cache.rrd"}
queryfile=${queryfile:="${datadir}/queries.rrd"}
graphopts="--no-minor --width ${graphwidth} --height ${graphheight} --imgformat ${graphformat} --color BACK${backcol} --color CANVAS${canvascol} --color SHADEA${shadeacol} --color SHADEB${shadebcol} --color GRID${gridcol} --color MGRID${mgridcol} --color FONT${fontcol} --color FRAME${framecol} --color ARROW${arrowcol}"

if [ "x${start}" != "x" ]
then
  if [ "x${archivedir}" != "x" ]
  then
    imgdir=${archivedir}
  fi
  hour_date=${start}
  hour_start=$(date -d"${start}" "+%s")
  hour_end=$((${hour_start} + 3600))
  day_date=$(date -d"${start}" "+%A %F")
  day_start=${hour_start}
  day_end=$((${day_start} + 3600 * 24))
  week_date=$(date -d"${start}" "+week nr. %V %Y")
  week_start=${hour_start}
  week_end=$((${week_start} + 3600 * 24 * 7))
# find first and last day of the month
  year=$(date -d"${start}" "+%Y")
  month=$(date -d"${start}" "+%m")
  if [ ${month} -gt 11 ]
  then
    nextmonth=01
    nextyear=$((${year} + 1))
  else
    nextmonth=$((10#${month} + 1))
    nextyear=${year}
  fi
  month_date=$(date -d"${start}" "+%B %Y")
  month_start=$(date -d"${year}-${month}-01" "+%s")
  month_end=$(date -d"${nextyear}-${nextmonth}-01" "+%s")
  year_date=$(date -d"${start}" "+%Y")
  year_start=$(date -d"${year}-01-01" "+%s")
  nextyear=$((${year} + 1))
  year_end=$(date -d"${nextyear}-01-01" "+%s")
else
  hour_date=$(date +"%F %R")
  hour_end=$(date +%s)
  hour_start=$((${hour_end} - 3600))
  day_date=$(date +"%A %F")
  day_end=${hour_end}
  day_start=$((${day_end} - 3600 * 24))
  week_date=$(date "+week nr. %V %Y")
  week_end=${hour_end}
  week_start=$((${week_end} - 3600 * 24 * 7))
  month_date=$(date "+%B %Y")
  month_end=${hour_end}
  month_start=$((${month_end} - 3600 * 24 * 28))
  year_date=$(date "+%Y")
  year_end=${hour_end}
  year_start=$((${year_end} - 3600 * 24 * 365))
fi

hour_limits="--start ${hour_start} --end ${hour_end}"
day_limits="--start ${day_start} --end ${day_end}"
week_limits="--start ${week_start} --end ${week_end}"
month_limits="--start $((${month_start} - 3600 * 24)) --end ${month_end} --x-grid DAY:1:WEEK:1:DAY:1:0:%e"
year_limits="--start ${year_start} --end ${year_end}"

if [ -f ${confile} ]
then
  title="Connections"
  consettings="${graphopts} --units-exponent 0 DEF:con=${confile}:threads_connected:AVERAGE DEF:mcon=${confile}:max_connections:AVERAGE AREA:mcon${goodcol}:max_connections AREA:con${datacol}:threads_connected"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/con-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${consettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/con-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${consettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/con-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${consettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/con-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${consettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/con-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${consettings}
  fi
fi

if [ -f ${tabfile} ]
then
  title="Table Cache"
  tabsettings="${graphopts} --units-exponent 0 DEF:tab=${tabfile}:open_tables:AVERAGE DEF:mtab=${tabfile}:table_cache:AVERAGE AREA:mtab${goodcol}:table_cache AREA:tab${datacol}:open_tables"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/tab-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${tabsettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/tab-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${tabsettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/tab-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${tabsettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/tab-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${tabsettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/tab-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${tabsettings}
  fi
  title="Temporary Tables"
  tmptabsettings="${graphopts} DEF:tmptab=${tabfile}:created_tmp_tables:AVERAGE DEF:tmptabdisk=${tabfile}:created_tmp_disk_t:AVERAGE LINE1:tmptab${goodcol}:created_tmp_tables LINE1:tmptabdisk${badcol}:created_tmp_disk_tables"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/tmptab-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${tmptabsettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/tmptab-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${tmptabsettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/tmptab-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${tmptabsettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/tmptab-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${tmptabsettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/tmptab-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${tmptabsettings}
  fi
fi

if [ -f ${keyfile} ]
then
  title="Key Cache"
  keysettings="${graphopts} DEF:key=${keyfile}:key_reads:AVERAGE DEF:keyreq=${keyfile}:key_read_requests:AVERAGE CDEF:keyrate=key,keyreq,/ HRULE:0.001${goodcol} HRULE:0.01${badcol} LINE2:keyrate${datacol}:key_read_requests/key_reads"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/key-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${keysettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/key-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${keysettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/key-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${keysettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/key-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${keysettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/key-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${keysettings}
  fi
  title="Key Buffer"
  keybufsettings="${graphopts} --base 1024 DEF:keybuf=${keyfile}:key_blocks_used:AVERAGE DEF:mkeybuf=${keyfile}:key_buffer_size:AVERAGE AREA:mkeybuf${goodcol}:key_buffer_size AREA:keybuf${datacol}:key_blocks_used"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/keybuf-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${keybufsettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/keybuf-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${keybufsettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/keybuf-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${keybufsettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/keybuf-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${keybufsettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/keybuf-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${keybufsettings}
  fi
  title="Full Joins"
  joinsettings="${graphopts} DEF:join=${keyfile}:select_full_join:AVERAGE LINE1:join${badcol}:select_full_join"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/join-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${joinsettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/join-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${joinsettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/join-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${joinsettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/join-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${joinsettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/join-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${joinsettings}
  fi
  title="Range Checks"
  rangesettings="${graphopts} DEF:range=${keyfile}:select_range_check:AVERAGE LINE1:range${badcol}:select_range_check"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/range-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${rangesettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/range-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${rangesettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/range-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${rangesettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/range-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${rangesettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/range-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${rangesettings}
  fi
  title="Reads"
  readsettings="${graphopts} DEF:readkey=${keyfile}:handler_read_key:AVERAGE DEF:readrnd=${keyfile}:handler_read_rnd:AVERAGE LINE1:readkey${goodcol}:handler_read_key LINE1:readrnd${badcol}:handler_read_rnd"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/read-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${readsettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/read-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${readsettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/read-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${readsettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/read-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${readsettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/read-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${readsettings}
  fi
  title="Slow Queries"
  slowsettings="${graphopts} DEF:slow=${keyfile}:slow_queries:AVERAGE LINE1:slow${badcol}:slow_queries"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slow-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${slowsettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slow-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${slowsettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slow-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${slowsettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slow-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${slowsettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slow-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${slowsettings}
  fi
fi

if [ -f ${queryfile} ]
then
  title="Questions"
  querysettings="${graphopts} --units-exponent 0 --vertical-label q/min DEF:query=${queryfile}:questions:AVERAGE DEF:select=${queryfile}:com_select:AVERAGE DEF:insert=${queryfile}:com_insert:AVERAGE DEF:update=${queryfile}:com_update:AVERAGE DEF:delete=${queryfile}:com_delete:AVERAGE CDEF:queries=query,60,* CDEF:selects=select,60,* CDEF:inserts=insert,60,* CDEF:updates=update,60,* CDEF:deletes=delete,60,* LINE1:queries${datacol}:questions LINE1:selects#00FF00:selects LINE1:inserts#FF0000:inserts LINE1:updates#FF00FF:updates LINE1:deletes#00FFFF:deletes"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/queries-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${querysettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/queries-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${querysettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/queries-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${querysettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/queries-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${querysettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/queries-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${querysettings}
  fi
fi

if [ -f ${slavefile} ]
then
  title="Slave Updates"
  querysettings="${graphopts} DEF:read=${slavefile}:read_master_log_pos:AVERAGE DEF:exec=${slavefile}:exec_master_log_pos:AVERAGE LINE1:read${datacol}:read LINE1:exec#00FF00:exec"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slave-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${querysettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slave-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${querysettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slave-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${querysettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slave-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${querysettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slave-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${querysettings}
  fi

  title="Slave Tracking"
  querysettings="${graphopts} DEF:read=${slavefile}:read_master_log_pos:AVERAGE DEF:exec=${slavefile}:exec_master_log_pos:AVERAGE CDEF:tracking=exec,read,- LINE1:tracking${datacol}:tracking"
  if [ ${hourly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slavec-hour${ext}.png ${hour_limits} --title "${title} : ${hour_date}" ${querysettings}
  fi
  if [ ${daily} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slavec-day${ext}.png ${day_limits} --title "${title} : ${day_date}" ${querysettings}
  fi
  if [ ${weekly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slavec-week${ext}.png ${week_limits} --title "${title} : ${week_date}" ${querysettings}
  fi
  if [ ${monthly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slavec-month${ext}.png ${month_limits} --title "${title} : ${month_date}" ${querysettings}
  fi
  if [ ${yearly} -ne 0 ]
  then
    ${RRDTOOL} graph ${imgdir}/slavec-year${ext}.png ${year_limits} --title "${title} : ${year_date}" ${querysettings}
  fi
fi

