[hobbit] mysql monitor
Dominique Frise
Dominique.Frise at unil.ch
Thu Jun 1 12:20:59 CEST 2006
thomas.seglard.enata at cnp.fr wrote:
> Hi,
>
> I've done all of that and I didn't get it to work ! Is there another thing to do ? I'm looking at the man-page (hobbitd_rrd) but it's the same
> example ! I put the line NCV_mysql="Uptime:NONE,Queriespersecondavg:GAUGE"
> into hobbitserver.cfg but what can I do to have the corresponding graph
> appear in "trends" ?
> Thanks a lot for your help !
>
> Thomas
>
>
> "Ralph Mitchell" <ralphmitchell at gmail.com> a écrit sur 31/05/2006 07:25:11
> :
>
>
>>Looks like I've got these:
>>
>>in hobbitgraph.cfg:
>> [mysqlslow]
>> TITLE MySQL Slow Queries
>> YAXIS Slow Queries
>> DEF:slow=mysql.rrd:Slowqueries:AVERAGE
>> LINE2:slow#00CCCC:Slow Queries
>> COMMENT:\n
>> GPRINT:slow:LAST: \: %5.1lf (cur)
>> GPRINT:slow:MAX: \: %5.1lf (max)
>> GPRINT:slow:MIN: \: %5.1lf (min)
>> GPRINT:slow:AVERAGE: \: %5.1lf (avg)\n
>>
>> [mysqlthread]
>> TITLE MySQL Active Threads
>> YAXIS Threads
>> DEF:threads=mysql.rrd:Threads:AVERAGE
>> AREA:threads#00CCCC:Threads
>> COMMENT:\n
>> GPRINT:threads:LAST: \: %5.1lf (cur)
>> GPRINT:threads:MAX: \: %5.1lf (max)
>> GPRINT:threads:MIN: \: %5.1lf (min)
>> GPRINT:threads:AVERAGE: \: %5.1lf (avg)\n
>>
>>in hobbitserver.cfg:
>> TEST2RRD="cpu=la,disk...mysql=ncv"
>> GRAPHS="la,disk...mysql:mysqlslow,mysql:mysqlthread"
>> NCV_mysql="Uptime:NONE,Queriespersecondavg:GAUGE"
>>
>>The status line from one host reads:
>> Uptime: 5421958 Threads: 1 Questions: 1051219 Slow queries:
>>577 Opens: 80325 Flush tables: 189 Open tables: 63
>>
>>The Uptime value is discarded and the rest go into the mysql.rrd.
>>Only the "Slow queries" and "Thread" graphs show on the Trends page.
>>
>>It may not be optimal, but I am getting graphs... :)
>>
>>Ralph Mitchell
>
>
>>On 5/30/06, Jason Chambers <Jason.Chambers at geosoft.com > wrote:
>>No Rush! :D
>>
>>Jason Chambers
>>IT Helpdesk Support
>>[image supprimée] eosoft Inc.
>>85 Richmond St. West - 8th Floor
>>Toronto, Ontario, Canada
>>M5H 2C9
>>Tel: 416-369-0111 x344
>>Fax: 416-369-9599
>>www.geosoft.com
>>
>>
>>From: Ralph Mitchell [mailto:ralphmitchell at gmail.com]
>>Sent: Monday, May 29, 2006 8:47 PM
>>
>>To: hobbit at hswn.dk
>>Subject: Re: [hobbit] mysql monitor
>>
>>I don't have immediate access to the hobbit server - it's at work,
>>I'/m at home... Tomorrow night, OK?
>>
>>Ralph Mitchell
>
>
>>On 5/29/06, Jason Chambers <Jason.Chambers at geosoft.com> wrote:
>>I finally figured out that I made a typo for my hobbitlaunch.cfg
>>file. Can you help me get the graphing working too? I never done it
>>before, and mostly relied on the server being able to do that.
>>
>>Jason Chambers
>>IT Helpdesk Support
>>eosoft Inc.
>>85 Richmond St. West - 8th Floor
>>Toronto, Ontario, Canada
>>M5H 2C9
>>Tel: 416-369-0111 x344
>>Fax: 416-369-9599
>>www.geosoft.com
>>
>>
>>From: Ralph Mitchell [mailto:ralphmitchell at gmail.com]
>>Sent: Saturday, May 27, 2006 1:12 AM
>>To: hobbit at hswn.dk
>>Subject: Re: [hobbit] mysql monitor
>>
>>Hmm... I just downloaded the current bb-mysqlstatus.tar.gz from
>
> deadcat.net
>
>>, diffed bb-mysqlstat.sh against the one I have running on a hobbit
>>server, and the only change I made was to replace VALIDMYSQLUSER &
>>VALIDMYSQLPASSWORD.
>>
>>The status page shows:
>>Sat May 27 00:05:21 CDT 2006 - Mysql OK
>>Uptime: 286 Threads: 1 Questions: 2916 Slow queries: 0 Opens: 37
>>Flush tables: 1 Open tables: 31 Queries per second avg: 10.196
>
>
>>This is on a Sun E450 running Gentoo, linux kernel 2.6.16, MySQL-4.1.19.
>>
>>Ralph Mitchell
>>On 5/26/06, Jason Chambers <Jason.Chambers at geosoft.com> wrote:
>>Hi,
>>
>>Does anyone have a mysql monitor working for hobbit client? I suck
>>at reprogramming the one on deadcat.net
>>
>>Jason Chambers
>>IT Helpdesk Support
>>eosoft Inc.
>>85 Richmond St. West - 8th Floor
>>Toronto, Ontario, Canada
>>M5H 2C9
>>Tel: 416-369-0111 x344
>>Fax: 416-369-9599
>>www.geosoft.com
>>
>>
>>
>>
>>
>>
>>
>
>
>
> Ce message (et toutes ses pieces jointes eventuelles) est confidentiel et etabli a l'intention exclusive de ses destinataires.
> Toute utilisation de ce message non conforme a sa destination, toute diffusion ou toute publication, totale ou partielle, est
> interdite, sauf autorisation expresse.
> L'internet ne permettant pas d'assurer l'integrite de ce message, CNP Assurances et ses filiales declinent toute responsabilite
> au titre de ce message, s'il a ete altere, deforme ou falsifie.
>
> *****
>
> This message and any attachments (the "message") are confidential and intended solely for the addressees.
> Any unauthorised use or dissemination is prohibited.
> E-mails are susceptible to alteration.
> Neither CNP Assurances nor any of its subsidiaries or affiliates shall be liable for the message if altered, changed or falsified.
>
>
> To unsubscribe from the hobbit list, send an e-mail to
> hobbit-unsubscribe at hswn.dk
>
>
>
Hi Thomas,
We have the mysql monitoring working since yesterday and yes, this was painfull
to set up for us too ;-)
There is a good reference "How to" (howtograph.html) in the help dir. of your
Hobbit installation.
Here below the necessary configuration steps using snapshot of 31 mai.
1. Modify bb-mysqlstat.sh
-------------------------
The status $MYSQLADMIN returned by the original script is not suitable for the
NCV module. You have to modify it to return Name:value pairs each on a single
line. After extracting the values from $MYSQLADMIN (awk), we send this to the
Hobbit server:
$BB $BBDISP "status $MACHINE.$TEST $COLOR `date` - $STATUS
$UPTIME
$THREADS
$QUESTIONS
$SLOWQUERIES
$OPENS
$FLUSHTABLES
$OPENTABLES
$QUERIESPERSECONDAVG
"
2. Get clientlaunch (hobbitlaunch if your on the server) to run the script
---------------------------------------------------------------------------
[mysql]
ENVFILE $HOBBITCLIENTHOME/etc/hobbitclient.cfg
CMD $HOBBITCLIENTHOME/ext/bb-mysqlstat.sh
LOGFILE $HOBBITCLIENTHOME/logs/bb-bb-mysqlstat.log
INTERVAL 5m
3. Check that the script data arrives in Hobbit
------------------------------------------------
After a few minutes, a mysql column should appear on your Hobbit view of this host.
4. Arrange for the data to be collected into an RRD file
--------------------------------------------------------
Modify the TEST2RRD for mysql and set the RRD datatype for the NCV module used
for mysql in the NCV_mysql variable.
TEST2RRD="cpu=la,disk,<...lots more stuff...>,hobbitd,mysql=ncv
NCV_mysql="*:GAUGE"
5. Check that the RRD collects data
-----------------------------------
You can use "rrdtool dump ~hobbit/data/rrd/HOSTNAME/mysql.rrd" or "rrdtool
fetch ~hobbit/data/rrd/HOSTANME/mysql.rrd AVERAGE" to see what collected.
$ rrdtool fetch /soft/pub/BB/hobbit/data/rrd/deca/mysql.rrd AVERAGE
Uptime Threads Questions
Slowqueries Opens Flushtables
Opentables Queriespersecondavg
1149067800: NaN NaN NaN NaN NaN NaN NaN NaN
...
...
1149153300: 1.7361796133e+06 1.2386666667e+01 1.3905225373e+07 2.7000000000e+01
8.3400000000e+02 1.0000000000e+00 1.2100000000e+02 8.0089200000e+00
1149153600: 1.7364796000e+06 1.2200000000e+01 1.3911530100e+07 2.7000000000e+01
8.3400000000e+02 1.0000000000e+00 1.2100000000e+02 8.0113000000e+00
1149153900: 1.7367795867e+06 1.3826666667e+01 1.3915204080e+07 2.7000000000e+01
8.3400000000e+02 1.0000000000e+00 1.2100000000e+02 8.0122933333e+00
1149154200: NaN NaN NaN NaN NaN NaN NaN NaN
1149154500: NaN NaN NaN NaN NaN NaN NaN NaN
6. Set up a graph definition
----------------------------
If your are interested in tracking the active threads, cut and paste the
[mysqlthread] section of hobbitgraph.cfg and give it the [mysql] tag.
Refresh the status page in your browser, and the graph should show up.
7. Add the graph to the collection of graphs on the trends column
-----------------------------------------------------------------
If you want the graph included with the other graphs on the trends column, you
must add it to the GRAPHS setting in the ~hobbit/server/etc/hobbitserver.cfg file.
GRAPHS="la,disk,<... lots more ...>,bbproxy,hobbitd,mysql"
Save the file, and when you click on the trends column you should see the mysql
graph at the bottom of the page.
8 Optional: add more mysql graphs to the trends column
-------------------------------------------------------
In bb-hosts, you can set TRENDS for displaying -here beside mysql- other
mysql-graphs defined in hobbitgraph.cfg.
Example definitions in hobbitgraph.cfg:
[mysqlslow]
DEF:slow=mysql.rrd:Slowqueries:AVERAGE
TITLE MySQL Slow Queries
YAXIS #
LINE2:slow#FF0000:Slow Queries
GPRINT:slow:LAST: \: %5.1lf (cur)
GPRINT:slow:MAX: \: %5.1lf (max)
GPRINT:slow:MIN: \: %5.1lf (min)
GPRINT:slow:AVERAGE: \: %5.1lf (avg)\n
[mysqlqueries]
DEF:quer=mysql.rrd:Queriespersecondavg:AVERAGE
TITLE MySQL Queries Per Second Average
YAXIS #
LINE2:quer#0000FF:Queries Per Second Average
GPRINT:quer:LAST: \: %5.1lf (cur)
GPRINT:quer:MAX: \: %5.1lf (max)
GPRINT:quer:MIN: \: %5.1lf (min)
GPRINT:quer:AVERAGE: \: %5.1lf (avg)\n
Example bb-hosts entry:
1.2.3.4 HOSTNAME # TRENDS:*,mysql:mysql|mysqlslow|mysqlqueries
Hope this helps.
Dominique
UNIL - University of Lausanne
More information about the Xymon
mailing list