The MySQL Activity Report package is a tool to help MySQL database

administrators to collect several database parameters and variables.
These collected values can be used for server monitoring or
performance tuning purposes.

WWW: http://gert.sos.be/en/projects/mysqlar/

PR:		ports/101217
Submitted by:	Greg Albrecht <gregoryba at gmail.com>

1 files changed, 184 insertions, 0 deletions

diff --git a/databases/mysqlard/files/mysqlard.sh.in b/databases/mysqlard/files/mysqlard.sh.in
new file mode 100644
index 000000000000..6db84303808e
--- /dev/null
+++ b/databases/mysqlard/files/mysqlard.sh.in
@@ -0,0 +1,184 @@
+#!/usr/local/bin/bash
+#
+#  $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: %%PREFIX%%/etc/mysqlard.cnf
+# pidfile: /var/run/mysqlard.pid
+
+. %%RC_SUBR%%
+
+# Source the config script
+if [[ "$datadir" == "" ]] ; then
+	echo Loading defaultsettings
+	. %%PREFIX%%/etc/mysqlard.cnf
+fi
+
+########################################################################
+# Options, change them in %%PREFIX%%/etc/mysqlard.cnf or you can override
+# them here, but that's not recommended. Read the comments in
+# %%PREFIX%%/etc/mysqlard.cnf if you want to know what you're changing.
+
+step=${step:="60"}
+minsamples=${minsamples:="720"}
+datadir=${datadir:="%%PREFIX%%/www/mysqlard"}
+pidfile=/var/run/mysqlard.pid
+RRDTOOL=${RRDTOOL:="%%PREFIX%%/bin/rrdtool"}
+MYSQLARD=${MYSQLARD:="%%PREFIX%%/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 [[ "$slave" != "" ]]; then
+  slaveopt=-L
+fi
+
+if [[ "$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 $?