ports/101217: new port: databases/mysqlard A tool to collect and graph database statistics from mysql
Greg Albrecht
gregoryba at gmail.com
Tue Aug 1 19:40:34 UTC 2006
>Number: 101217
>Category: ports
>Synopsis: new port: databases/mysqlard A tool to collect and
>Confidential: no
>Severity: non-critical
>Priority: low
>Responsible: freebsd-ports-bugs
>State: open
>Quarter:
>Keywords:
>Date-Required:
>Class: change-request
>Submitter-Id: current-users
>Arrival-Date: Tue Aug 01 19:40:15 GMT 2006
>Closed-Date:
>Last-Modified:
>Originator: greg albrecht
>Release: FreeBSD 5.4-RELEASE-p7 i386
>Organization:
schematic
>Environment:
System: FreeBSD littlerock.schematic.com 5.4-RELEASE-p7 FreeBSD
5.4-RELEASE-p7 #1: Tue Oct 4 10:21:31 PDT 2005
gba at littlerock.schematic.com:/usr/obj/usr/src/
sys/LITTLEROCK i386
>Description:
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/
>How-To-Repeat:
<code/input/activities to reproduce the problem (multiple lines)>
>Fix:
# This is a shell archive. Save it in a file, remove anything before
# this line, and then unpack it by entering "sh file". Note, it may
# create directories; files and directories will be owned by you and
# have default permissions.
#
# This archive contains:
#
# mysqlard
# mysqlard/Makefile
# mysqlard/pkg-descr
# mysqlard/distinfo
# mysqlard/pkg-plist
# mysqlard/files
# mysqlard/files/mysqlard.sh.in
# mysqlard/files/patch-ab
# mysqlard/files/patch-af
# mysqlard/files/patch-ae
# mysqlard/files/patch-ad
# mysqlard/files/patch-ac
# mysqlard/files/patch-aa
# mysqlard/pkg-message
#
echo c - mysqlard
mkdir -p mysqlard > /dev/null 2>&1
echo x - mysqlard/Makefile
sed 's/^X//' >mysqlard/Makefile << 'END-of-mysqlard/Makefile'
X# New ports collection makefile for: mysqlard
X# Date created: 31 July 2006
X# Whom: gba
X#
X# $FreeBSD$
X#
X
XPORTNAME= mysqlard
XPORTVERSION= 1.0.0
XCATEGORIES= databases www
XMASTER_SITES= http://gert.sos.be/downloads/mysqlar/
X
XMAINTAINER= gregoryba at gmail.com
XCOMMENT= A tool to collect and graph database statistics from mysql
X
XRUN_DEPENDS= rrdtool:${PORTSDIR}/net/rrdtool
X
XUSE_MYSQL= yes
XUSE_APACHE= 1.3+
X
XMYSQLARD_DIR?= ${PREFIX}/www/mysqlard
X
XUSE_RC_SUBR= mysqlard.sh
X
XMAN1 = mysqlar_graph.1
XMAN8 = mysqlard.8
X
XGNU_CONFIGURE= yes
XCONFIGURE_TARGET= --build=${MACHINE_ARCH}-portbld-freebsd${OSREL}
XCONFIGURE_ARGS= --prefix="${PREFIX}" --with-rrd="${PREFIX}"
--datadir="${PREFIX}/www" --sysconfdir="${PREFIX}/etc"
X
Xpost-install:
X @${MV} ${PREFIX}/www/mysqlard/mysqlard.cnf ${PREFIX}/etc
X @${CAT} ${PKGMESSAGE}
X
X.include <bsd.port.mk>
END-of-mysqlard/Makefile
echo x - mysqlard/pkg-descr
sed 's/^X//' >mysqlard/pkg-descr << 'END-of-mysqlard/pkg-descr'
XThe MySQL Activity Report package is a tool to help MySQL database
Xadministrators to collect several database parameters and variables.
XThese collected values can be used for server monitoring or
Xperformance tuning purposes.
X
XWWW: http://gert.sos.be/en/projects/mysqlar/
END-of-mysqlard/pkg-descr
echo x - mysqlard/distinfo
sed 's/^X//' >mysqlard/distinfo << 'END-of-mysqlard/distinfo'
XMD5 (mysqlard-1.0.0.tar.gz) = 693ef6f36ca232131b22db7063cae940
XSHA256 (mysqlard-1.0.0.tar.gz) =
07f333110c8fbb0174a534570884471139b00c96950f56a55fdbbd4683aef7f4
XSIZE (mysqlard-1.0.0.tar.gz) = 109656
END-of-mysqlard/distinfo
echo x - mysqlard/pkg-plist
sed 's/^X//' >mysqlard/pkg-plist << 'END-of-mysqlard/pkg-plist'
Xbin/mysqlar_graph
Xetc/mysqlard.cnf
Xsbin/mysqlard
Xwww/mysqlard/mysql.gif
Xwww/mysqlard/mysqlar.daily
Xwww/mysqlard/mysqlar.monthly
Xwww/mysqlard/mysqlar.php
Xwww/mysqlard/mysqlar.weekly
Xwww/mysqlard/mysqlard.server
Xwww/mysqlard/rrdtool.gif
Xwww/mysqlard/sos.gif
Xwww/mysqlard/style.css
X at dirrm www/mysqlard
END-of-mysqlard/pkg-plist
echo c - mysqlard/files
mkdir -p mysqlard/files > /dev/null 2>&1
echo x - mysqlard/files/mysqlard.sh.in
sed 's/^X//' >mysqlard/files/mysqlard.sh.in <<
'END-of-mysqlard/files/mysqlard.sh.in'
X#!/usr/local/bin/bash
X#
X# $Id: mysqlard.server.sh 40 2006-01-20 20:26:11Z dewitge $
X#
X# MySQL Activity Report
X# mysqlard Start script for the MySQL Activity Report Daemon
X#
X# Copyright 2004 Gert Dewit <gert.dewit at sos.be>
X#
X# chkconfig: - 79 11
X# description: MySQL Activity Report Daemon
X# processname: mysqlard
X# config: %%PREFIX%%/etc/mysqlard.cnf
X# pidfile: /var/run/mysqlard.pid
X
X. %%RC_SUBR%%
X
X# Source the config script
Xif [[ "$datadir" == "" ]] ; then
X echo Loading defaultsettings
X . %%PREFIX%%/etc/mysqlard.cnf
Xfi
X
X########################################################################
X# Options, change them in %%PREFIX%%/etc/mysqlard.cnf or you can override
X# them here, but that's not recommended. Read the comments in
X# %%PREFIX%%/etc/mysqlard.cnf if you want to know what you're changing.
X
Xstep=${step:="60"}
Xminsamples=${minsamples:="720"}
Xdatadir=${datadir:="%%PREFIX%%/www/mysqlard"}
Xpidfile=${pidfile:="/var/run/mysqlard.pid"}
XRRDTOOL=${RRDTOOL:="%%PREFIX%%/bin/rrdtool"}
XMYSQLARD=${MYSQLARD:="%%PREFIX%%/sbin/mysqlard"}
XMYSQLUSER=${MYSQLUSER:="mysqlar"}
XMYSQLHOST=${MYSQLHOST:=""}
X########################################################################
X
X# don't change anything below this line, but heck, I can't stop you ;-)
Xconfile=${confile:="${datadir}/connections.rrd"}
Xtabfile=${tabfile:="${datadir}/table_cache.rrd"}
Xkeyfile=${keyfile:="${datadir}/key_cache.rrd"}
Xqueryfile=${queryfile:="${datadir}/queries.rrd"}
Xslavefile=${slavefile:="${datadir}/slave.rrd"}
X
Xslaveopt=
Xif [[ "$slave" != "" ]]; then
X slaveopt=-L
Xfi
X
Xif [[ "$MYSQLHOST" != "" ]] ; then
X MYSQLHOST=--host=$MYSQLHOST
Xfi
X
X# functions
Xavgsamps () {
X avg=$(($1 / $2))
X if [ ${avg} -lt 1 ]
X then
X avg=1
X fi
X echo ${avg}
X}
Xarchives () {
X arch=$(($1 / $2))
X if [[ $(($2 * ${arch} * $3)) -lt $4 ]]
X then
X arch=$((${arch} + 1))
X fi
X echo ${arch}
X}
X
X# calculated values
Xheartbeat=$((${step} * 2))
Xhoursamps=$((3600 / ${step}))
Xhouravgs=$(avgsamps ${hoursamps} ${minsamples})
Xhourarchives=$(archives ${hoursamps} ${houravgs} ${step} 3600)
Xdaysamps=$((86400 / ${step}))
Xdayavgs=$(avgsamps ${daysamps} ${minsamples})
Xdayarchives=$(archives ${daysamps} ${dayavgs} ${step} 86400)
Xweeksamps=$((604800 / ${step}))
Xweekavgs=$(avgsamps ${weeksamps} ${minsamples})
Xweekarchives=$(archives ${weeksamps} ${weekavgs} ${step} 604800)
Xmonthsamps=$((2678400 / ${step}))
Xmonthavgs=$(avgsamps ${monthsamps} ${minsamples})
Xmontharchives=$(archives ${monthsamps} ${monthavgs} ${step} 2678400)
Xyearsamps=$((31622400 / ${step}))
Xyearavgs=$(avgsamps ${yearsamps} ${minsamples})
Xyeararchives=$(archives ${yearsamps} ${yearavgs} ${step} 31622400)
X
X# calculated RRA's
Xhourrra="RRA:AVERAGE:0.5:${houravgs}:${hourarchives}"
Xdayrra="RRA:AVERAGE:0.5:${dayavgs}:${dayarchives}"
Xweekrra="RRA:AVERAGE:0.5:${weekavgs}:${weekarchives}"
Xmonthrra="RRA:AVERAGE:0.5:${monthavgs}:${montharchives}"
Xyearrra="RRA:AVERAGE:0.5:${yearavgs}:${yeararchives}"
Xallrras="${hourrra} ${dayrra} ${weekrra} ${monthrra} ${yearrra}"
X
Xinitrrd () {
Xif [ ! -f ${confile} ]
Xthen
X ${RRDTOOL} create ${confile} --step ${step} \
X DS:threads_connected:GAUGE:${heartbeat}:0:U \
X DS:max_connections:GAUGE:${heartbeat}:0:U \
X ${allrras}
Xfi
X
Xif [ ! -f ${tabfile} ]
Xthen
X ${RRDTOOL} create ${tabfile} --step ${step} \
X DS:open_tables:GAUGE:${heartbeat}:0:U \
X DS:table_cache:GAUGE:${heartbeat}:0:U \
X DS:created_tmp_tables:COUNTER:${heartbeat}:0:U \
X DS:created_tmp_disk_t:COUNTER:${heartbeat}:0:U \
X ${allrras}
Xfi
X
Xif [ ! -f ${keyfile} ]
Xthen
X ${RRDTOOL} create ${keyfile} --step ${step} \
X DS:key_reads:COUNTER:${heartbeat}:0:U \
X DS:key_read_requests:COUNTER:${heartbeat}:0:U \
X DS:key_buffer_size:GAUGE:${heartbeat}:0:U \
X DS:key_blocks_used:GAUGE:${heartbeat}:0:U \
X DS:select_full_join:COUNTER:${heartbeat}:0:U \
X DS:select_range_check:COUNTER:${heartbeat}:0:U \
X DS:handler_read_key:COUNTER:${heartbeat}:0:U \
X DS:handler_read_rnd:COUNTER:${heartbeat}:0:U \
X DS:slow_queries:COUNTER:${heartbeat}:0:U \
X ${allrras}
Xfi
X
Xif [ ! -f ${queryfile} ]
Xthen
X ${RRDTOOL} create ${queryfile} --step ${step} \
X DS:questions:COUNTER:${heartbeat}:0:U \
X DS:com_select:COUNTER:${heartbeat}:0:U \
X DS:com_insert:COUNTER:${heartbeat}:0:U \
X DS:com_update:COUNTER:${heartbeat}:0:U \
X DS:com_delete:COUNTER:${heartbeat}:0:U \
X ${allrras}
Xfi
X
Xif [ ! -f ${slavefile} ]
Xthen
X ${RRDTOOL} create ${slavefile} --step ${step} \
X DS:read_master_log_pos:COUNTER:${heartbeat}:0:U \
X DS:exec_master_log_pos:COUNTER:${heartbeat}:0:U \
X ${allrras}
Xfi
X}
X
Xstatus() {
Xif [ -f $pidfile ]
Xthen
X echo $0 is running. PID `cat ${pidfile}`
Xelse
X echo $0 is not running.
Xfi
X
X}
X
Xcase "$1" in
X start)
X initrrd
X ${MYSQLARD} --step=${step} --datadir=${datadir} --user=${MYSQLUSER}
$MYSQLHOST --pidfile=${pidfile} ${slaveopt}
X ;;
X stop)
X kill `cat ${pidfile}`
X rm -f ${pidfile}
X ;;
X initrrd)
X initrrd
X ;;
X status)
X status
X ;;
X *)
X echo "Usage: $0 {start|stop|status|initrrd}"
X exit 1
Xesac
X
Xexit $?
END-of-mysqlard/files/mysqlard.sh.in
echo x - mysqlard/files/patch-ab
sed 's/^X//' >mysqlard/files/patch-ab << 'END-of-mysqlard/files/patch-ab'
X--- src/mysqlar.monthly.sh.orig Tue Aug 1 11:23:19 2006
X+++ src/mysqlar.monthly.sh Tue Aug 1 11:10:54 2006
X@@ -1,4 +1,4 @@
X-#!/bin/bash
X+#!/usr/local/bin/bash
X #
X # $Id: mysqlar.monthly.sh 35 2004-03-10 15:48:41Z dewitge $
X #
END-of-mysqlard/files/patch-ab
echo x - mysqlard/files/patch-af
sed 's/^X//' >mysqlard/files/patch-af << 'END-of-mysqlard/files/patch-af'
X--- src/mysqlar.php.sh.orig Tue Aug 1 11:23:19 2006
X+++ src/mysqlar.php.sh Tue Aug 1 11:13:32 2006
X@@ -15,7 +15,7 @@
X * and has only usage permissions, no grants on any databases or tables.
X */
X $sqlhost = "localhost";
X- $sqluser = "";
X+ $sqluser = "mysqlar";
X $sqlpassword = "";
X
X /* sort a matrix using column as key */
END-of-mysqlard/files/patch-af
echo x - mysqlard/files/patch-ae
sed 's/^X//' >mysqlard/files/patch-ae << 'END-of-mysqlard/files/patch-ae'
X--- src/mysqlar.daily.sh.orig Tue Aug 1 11:23:19 2006
X+++ src/mysqlar.daily.sh Tue Aug 1 11:10:47 2006
X@@ -1,4 +1,4 @@
X-#!/bin/bash
X+#!/usr/local/bin/bash
X #
X # $Id: mysqlar.daily.sh 35 2004-03-10 15:48:41Z dewitge $
X #
END-of-mysqlard/files/patch-ae
echo x - mysqlard/files/patch-ad
sed 's/^X//' >mysqlard/files/patch-ad << 'END-of-mysqlard/files/patch-ad'
X--- src/mysqlard.cnf.sh.orig Tue Aug 1 11:23:19 2006
X+++ src/mysqlard.cnf.sh Tue Aug 1 11:11:22 2006
X@@ -1,4 +1,4 @@
X-#!/bin/bash
X+#!/usr/local/bin/bash
X # $Id: mysqlard.cnf.sh 40 2006-01-20 20:26:11Z dewitge $
X #
X # MySQL Activity Report
END-of-mysqlard/files/patch-ad
echo x - mysqlard/files/patch-ac
sed 's/^X//' >mysqlard/files/patch-ac << 'END-of-mysqlard/files/patch-ac'
X--- src/mysqlar.weekly.sh.orig Tue Aug 1 11:23:19 2006
X+++ src/mysqlar.weekly.sh Tue Aug 1 11:11:09 2006
X@@ -1,4 +1,4 @@
X-#!/bin/bash
X+#!/usr/local/bin/bash
X #
X # $Id: mysqlar.weekly.sh 35 2004-03-10 15:48:41Z dewitge $
X #
END-of-mysqlard/files/patch-ac
echo x - mysqlard/files/patch-aa
sed 's/^X//' >mysqlard/files/patch-aa << 'END-of-mysqlard/files/patch-aa'
X--- src/mysqlard.server.sh.orig Tue Aug 1 11:23:19 2006
X+++ src/mysqlard.server.sh Tue Aug 1 11:11:29 2006
X@@ -1,4 +1,4 @@
X-#!/bin/bash
X+#!/usr/local/bin/bash
X #
X # $Id: mysqlard.server.sh 40 2006-01-20 20:26:11Z dewitge $
X #
END-of-mysqlard/files/patch-aa
echo x - mysqlard/pkg-message
sed 's/^X//' >mysqlard/pkg-message << 'END-of-mysqlard/pkg-message'
X
XMake sure to add crontabs for mysqard and start the mysqlard daemon:
X${PREFIX}/etc/rc.d/mysqlard.sh
X
Xand add this line to your crontab:
X*/5 * * * * root hourly=1 daily=1
weekly=1 monthly=1 ${PREFIX}/bin/mysqlar_graph > /dev/null
X
END-of-mysqlard/pkg-message
exit
>Release-Note:
>Audit-Trail:
>Unformatted:
graph database statistics from mysql
More information about the freebsd-ports-bugs
mailing list