#!/bin/sh
#
#  $Id: mysqlard.server.sh 40 2006-01-20 20:26:11Z dewitge $
#	
#  MySQL Activity Report
#  mysqlard Start script for the MySQL Activity Report Daemon
#
#  Copyright 2004 Gert Dewit <gert.dewit@sos.be>
#
# chkconfig: - 79 11
# description: MySQL Activity Report Daemon
# processname: mysqlard
# config: /usr/local/etc/mysqlard.cnf
# pidfile: /var/run/mysqlard.pid

. /etc/rc.subr

# Source the config script
if [ -z "$datadir" ] ; then
	echo Loading defaultsettings
	. /usr/local/etc/mysqlard.cnf
fi

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

step=${step:="60"}
minsamples=${minsamples:="720"}
datadir=${datadir:="/usr/local/www/mysqlard"}
pidfile=/var/run/mysqlard.pid
RRDTOOL=${RRDTOOL:="/usr/local/bin/rrdtool"}
MYSQLARD=${MYSQLARD:="/usr/local/sbin/mysqlard"}
MYSQLUSER=${MYSQLUSER:="mysqlar"}
MYSQLHOST=${MYSQLHOST:=""}
########################################################################

# 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"}
slavefile=${slavefile:="${datadir}/slave.rrd"}

slaveopt=
if [ -n "$slave" ]; then
  slaveopt=-L
fi

if [ -n "$MYSQLHOST" ] ; then
  MYSQLHOST=--host=$MYSQLHOST
fi

# functions
avgsamps () {
  avg=$(($1 / $2))
  if [ ${avg} -lt 1 ]
  then
    avg=1
  fi
  echo ${avg}
}
archives () {
  arch=$(($1 / $2))
  if [ $(($2 * ${arch} * $3)) -lt $4 ]
  then
    arch=$((${arch} + 1))
  fi
  echo ${arch}
}

# calculated values
heartbeat=$((${step} * 2))
hoursamps=$((3600 / ${step}))
houravgs=$(avgsamps ${hoursamps} ${minsamples})
hourarchives=$(archives ${hoursamps} ${houravgs} ${step} 3600)
daysamps=$((86400 / ${step}))
dayavgs=$(avgsamps ${daysamps} ${minsamples})
dayarchives=$(archives ${daysamps} ${dayavgs} ${step} 86400)
weeksamps=$((604800 / ${step}))
weekavgs=$(avgsamps ${weeksamps} ${minsamples})
weekarchives=$(archives ${weeksamps} ${weekavgs} ${step} 604800)
monthsamps=$((2678400 / ${step}))
monthavgs=$(avgsamps ${monthsamps} ${minsamples})
montharchives=$(archives ${monthsamps} ${monthavgs} ${step} 2678400)
yearsamps=$((31622400 / ${step}))
yearavgs=$(avgsamps ${yearsamps} ${minsamples})
yeararchives=$(archives ${yearsamps} ${yearavgs} ${step} 31622400)

# calculated RRA's
hourrra="RRA:AVERAGE:0.5:${houravgs}:${hourarchives}"
dayrra="RRA:AVERAGE:0.5:${dayavgs}:${dayarchives}"
weekrra="RRA:AVERAGE:0.5:${weekavgs}:${weekarchives}"
monthrra="RRA:AVERAGE:0.5:${monthavgs}:${montharchives}"
yearrra="RRA:AVERAGE:0.5:${yearavgs}:${yeararchives}"
allrras="${hourrra} ${dayrra} ${weekrra} ${monthrra} ${yearrra}"

initrrd () {
if [ ! -f ${confile} ]
then
  ${RRDTOOL} create ${confile} --step ${step} \
  DS:threads_connected:GAUGE:${heartbeat}:0:U \
  DS:max_connections:GAUGE:${heartbeat}:0:U \
  ${allrras}
fi

if [ ! -f ${tabfile} ]
then
  ${RRDTOOL} create ${tabfile} --step ${step} \
  DS:open_tables:GAUGE:${heartbeat}:0:U \
  DS:table_cache:GAUGE:${heartbeat}:0:U \
  DS:created_tmp_tables:COUNTER:${heartbeat}:0:U \
  DS:created_tmp_disk_t:COUNTER:${heartbeat}:0:U \
  ${allrras}
fi

if [ ! -f ${keyfile} ]
then
  ${RRDTOOL} create ${keyfile} --step ${step} \
  DS:key_reads:COUNTER:${heartbeat}:0:U \
  DS:key_read_requests:COUNTER:${heartbeat}:0:U \
  DS:key_buffer_size:GAUGE:${heartbeat}:0:U \
  DS:key_blocks_used:GAUGE:${heartbeat}:0:U \
  DS:select_full_join:COUNTER:${heartbeat}:0:U \
  DS:select_range_check:COUNTER:${heartbeat}:0:U \
  DS:handler_read_key:COUNTER:${heartbeat}:0:U \
  DS:handler_read_rnd:COUNTER:${heartbeat}:0:U \
  DS:slow_queries:COUNTER:${heartbeat}:0:U \
  ${allrras}
fi

if [ ! -f ${queryfile} ]
then
  ${RRDTOOL} create ${queryfile} --step ${step} \
  DS:questions:COUNTER:${heartbeat}:0:U \
  DS:com_select:COUNTER:${heartbeat}:0:U \
  DS:com_insert:COUNTER:${heartbeat}:0:U \
  DS:com_update:COUNTER:${heartbeat}:0:U \
  DS:com_delete:COUNTER:${heartbeat}:0:U \
  ${allrras}
fi

if [ ! -f ${slavefile} ]
then
  ${RRDTOOL} create ${slavefile} --step ${step} \
  DS:read_master_log_pos:COUNTER:${heartbeat}:0:U \
  DS:exec_master_log_pos:COUNTER:${heartbeat}:0:U \
  ${allrras}
fi
}

status() {
if [ -f $pidfile ]
then
  echo $0 is running. PID `cat ${pidfile}`
else
  echo $0 is not running.
fi

}

case "$1" in
	start)
		initrrd
		${MYSQLARD} --step=${step} --datadir=${datadir} --user=${MYSQLUSER}
$MYSQLHOST --pidfile=${pidfile} ${slaveopt}
	;;
	stop)
		kill `cat ${pidfile}`
		rm -f ${pidfile}
	;;
	initrrd)
		initrrd
	;;
	status)
		status
	;;
	*)
		echo "Usage: $0 {start|stop|status|initrrd}"
		exit 1
esac

exit $?