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>