Skip site navigation (1)Skip section navigation (2)
Date:      Tue, 1 Aug 2006 12:39:20 -0700
From:      "Greg Albrecht" <gregoryba@gmail.com>
To:        FreeBSD-gnats-submit@FreeBSD.org
Subject:   ports/101217: new port: databases/mysqlard A tool to collect and graph database statistics from mysql
Message-ID:  <39ed86f90608011239i5e61432am7e36a93f9d6b1cdf@mail.gmail.com>
Resent-Message-ID: <200608011940.k71JeFvO049513@freefall.freebsd.org>

next in thread | raw e-mail | index | archive | help

>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@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@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@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@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



Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?39ed86f90608011239i5e61432am7e36a93f9d6b1cdf>