The MySQL Graph Collection - Version 2.0!

[jbreault]

Is there somebody that can add to the script mysql port support ? we have about 16 mysql db running on different port and since cacti add by ping if host is alive before to try to graph, we cant use anymore HOST:PORT as host...


thanks

[lucky]

great work.
it works!

i would like to monitor the response time (for INSERT, SELECT, DELETE and UPDATE) of a database at my provider. i've there only one database and have no supersuser privileges on the database server.

is ist possible with some modification on this script?

THX

[lcguy229]

I have sample problem in "invalid y-axis error". Thanks for sharing your experience.

Jon.

Hello,

I've a problem since the upgrade from Cacti 0.8.6h and 0.8.6i to 0.8.6j-r1 that some Mysql-Graphs don't work again.

Also some new added Graphs (like command) doesn't work and I don't know why. If I will start the mysqli_stats.php manually I will get back a line like delete:854670 insert:2874455 select:6750599 update:312939 but thats all.

System : Gentoo
Cacti : 0.8.6j-r1
Mysql : 4.1.22-r1
Php : 4.4.4-r8

The Traffic-Graph still works fine. The new QueryCache will display a empty graph, 2 other works before but nothing now and the new onces don't display nothing (no empty graph too).

Have anyone a idea what can be happen ??

bye alex

I am having the same issue, which I just fixed. I noticed and invalid y-axis error when debug mode was turned on. This post turned me onto the solution (or workaround) :
http://forums.cacti.net/about12327-0-asc-15.html

To fix, go to GRAPH TEMPLATES and select the MySQL template of the graph that is not working. Scroll down and remove the text in "Unit Grid Value" and click save. The graphs should work.

I believe a bug fix in the latest version of Cacti made this problem show up. The original MySQL templates should be updated.

[Florian___]

I have sample problem in "invalid y-axis error". Thanks for sharing your experience.

Jon.

Same problem

with
System : Debian
Cacti : 0.8.6j
Mysql : 5.0.45
Php : 5.2

[gandalf]

Please visit the Graph Template. Make sure, Unit Grid Value and the Exponent value are empty and retry
Reinhard

[Florian___]

Ok, thanks, it *works* I didn't have this message anymore, it was the Unit Grid Value to set to blank.

But now, i have a new error message :

Code: Select all

   	 RRDTool Command:

/usr/bin/rrdtool graph - \
--imgformat=PNG \
--start=-86400 \
--end=-300 \
--title="mysql fresh 3310 - MySQL - Connections" \
--base=1000 \
--height=120 \
--width=500 \
--alt-autoscale-max \
--lower-limit=0 \
--vertical-label="connections" \
--slope-mode \
DEF:a="/home/sites/****/*****/admin/cacti-0.8.6j/rra/mysql_fresh_3310_connections_175.rrd":connections:AVERAGE \
AREA:a#4668E4:"Connections"  \
GPRINT:a:MIN:"Min\:%8.2lf%s"  \
GPRINT:a:MAX:"Max\:%8.2lf%s"  \
GPRINT:a:LAST:"Current\:%8.2lf%s" 

RRDTool Says:

ERROR: opening '/home/sites/****/*****/admin/cacti-0.8.6j/rra/mysql_fresh_3310_connections_175.rrd': No such file or directory

Any ideas ?
I'll check later in the forum


Thanks for your qucik answer

[gandalf]

The rrd file will not be created if any of the following matches
- target not reachable
- downed host detection says target is not reachable
- no value found
Please try second link of my signature for more insight
Reinhard

[paulbeard206]

It seems to get everything else just fine.

CACTID: Version 0.8.6i startingview /usr/local/share/cacti/scripts/mysql_stats.php
CACTID: Host[18] DS[264] SCRIPT: /usr/local/bin/php -q /usr/local/share/cacti/scripts/mysql_stats.php cache 192.168.2.25 cactiuser cactiuser, output: used:2872080 available:1322224
CACTID: Host[18] DS[260] SCRIPT: /usr/local/bin/php -q /usr/local/share/cacti/scripts/mysql_stats.php command 192.168.2.25 cactiuser cactiuser, output: change_db:0 delete:0 insert:0 select:0 update:0
CACTID: Host[18] DS[262] SCRIPT: /usr/local/bin/php -q /usr/local/share/cacti/scripts/mysql_stats.php handler 192.168.2.25 cactiuser cactiuser, output: delete:0 read_first:0 read_key:0 read_next:0 read_prev:0 read_rnd:0 read_rnd_next:0 update:0 write:0
CACTID: Host[18] DS[261] SCRIPT: /usr/local/bin/php -q /usr/local/share/cacti/scripts/mysql_stats.php status 192.168.2.25 cactiuser cactiuser Connections, output: 9337
CACTID: Host[18] DS[263] SCRIPT: /usr/local/bin/php -q /usr/local/share/cacti/scripts/mysql_stats.php status 192.168.2.25 cactiuser cactiuser Questions, output: 288704
CACTID: Host[18] DS[266] SCRIPT: /usr/local/bin/php -q /usr/local/share/cacti/scripts/mysql_stats.php traffic 192.168.2.25 cactiuser cactiuser, output: in:83 out:72

It's Mysql 5.1, if that matters.

Update: it looks like the handler query option is gone in MySQL 5.1.

[utdrmac]

how can I add this script to the machines snmpd so that it responds to a private OID? We don't have networking turned on for our mysql servers so this script will have to live locally on each mysql server and be queried using OIDs.

[xorl]

I have all the graphs working, just they will not populate with data. I see in the cacti log they pass the information, but, all the graphs just show "nan" where the numerical output should be.

[khufure]

Nan means it's not getting data. You should try to run the command from a command prompt. Try to connect via mysql client to the host using user/pass and I think it's "show innodb status". (Exact syntax is in the perl script).

[aboyz]

Hi,

Anyone know how to add in your very own query? I want to monitor this. How would I go doing this. Can someone give me an example>?

select count(*) from whos_online WHERE customer_id = 0" -s -s oscom

total number of guests currently on the site

Any help will be appreiicated.!!
thanks

[devym]

The script isn't fetching any data (for example in the command mode it retrieves all 0) and I am using Mysql 5.0.x.

I have looked into the script and I have discovered that the query is wrong.
In mysql 5.x the two queries should be:

Code: Select all

"SHOW GLOBAL STATUS;" instead of "SHOW STATUS;"
"SHOW GLOBAL VARIABLES" instead of "SHOW VARIABLES;"

The GLOBAL modifier with the STATUS command is introduced in MySql 5 and allow to show global status instead of session status.

Hoping to be useful...

[dbuchner]

Someone please jog my memory.

I have got the graphs working - have had for more than a year by now. Just added a brand new host and only Connections, Query Cache, Questions and Threads are working.

Command & Handler Stats as well as Traffic are not showing any data. The RRD has been created.

A manual walk for the said stats returns the data as expected - also the other 7 hosts for which I have been graphing mysql stats for over a year now are not having any issues.

Code: Select all

root@web1> /usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php cache IP cacti cact1 used:0 available:0

root@web1> /usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php traffic IP cacti cact1 in:79 out:77

The error log shows:

Code: Select all

3/20/2008 09:25:05 PM - CACTID: Poller[0] Host[28] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php cache <USED TO BE IP> cacti cact1'
03/20/2008 09:25:05 PM - CACTID: Poller[0] Host[28] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php command <USED TO BE IP> cacti cact1'
03/20/2008 09:25:05 PM - CACTID: Poller[0] Host[29] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php cache <USED TO BE IP> cacti cact1'
03/20/2008 09:25:05 PM - CACTID: Poller[0] Host[29] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php command <USED TO BE IP> cacti cact1'
03/20/2008 09:25:06 PM - CACTID: Poller[0] Host[29] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php handler <USED TO BE IP> cacti cact1'
03/20/2008 09:25:06 PM - CACTID: Poller[0] Host[28] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php handler <USED TO BE IP> cacti cact1'
03/20/2008 09:25:06 PM - CACTID: Poller[0] Host[28] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php status <USED TO BE IP> cacti cact1 Connections'
03/20/2008 09:25:06 PM - CACTID: Poller[0] Host[29] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php status <USED TO BE IP> cacti cact1 Connections'
03/20/2008 09:25:06 PM - CACTID: Poller[0] Host[29] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php status <USED TO BE IP> cacti cact1 Questions'
03/20/2008 09:25:06 PM - CACTID: Poller[0] Host[28] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php status <USED TO BE IP> cacti cact1 Questions'
03/20/2008 09:25:06 PM - CACTID: Poller[0] Host[28] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php thread <USED TO BE IP> cacti cact1'
03/20/2008 09:25:06 PM - CACTID: Poller[0] Host[28] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php traffic <USED TO BE IP> cacti cact1'
03/20/2008 09:25:06 PM - CACTID: Poller[0] Host[29] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php thread <USED TO BE IP> cacti cact1'
03/20/2008 09:25:06 PM - CACTID: Poller[0] Host[29] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php traffic <USED TO BE IP> cacti cact1'
03/20/2008 09:25:07 PM - CACTID: Poller[0] Host[23] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php cache <USED TO BE IP> cacti cact1'
03/20/2008 09:25:07 PM - CACTID: Poller[0] Host[23] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php command <USED TO BE IP> cacti cact1'
03/20/2008 09:25:07 PM - CACTID: Poller[0] Host[23] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php handler <USED TO BE IP> cacti cact1'
03/20/2008 09:25:07 PM - CACTID: Poller[0] Host[23] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php status <USED TO BE IP> cacti cact1 Connections'
03/20/2008 09:25:07 PM - CACTID: Poller[0] Host[23] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php status <USED TO BE IP> cacti cact1 Questions'
03/20/2008 09:25:07 PM - CACTID: Poller[0] Host[23] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php thread <USED TO BE IP> cacti cact1'
03/20/2008 09:25:07 PM - CACTID: Poller[0] Host[23] ERROR: Empty result <USED TO BE IP>: '/usr/local/bin/php -q /home/servers/public_html/graphs/scripts/mysql_stats.php traffic <USED TO BE IP> cacti cact1'

[Aurelgadjo]

Hi,

I've many graphes who does not work.

The message is same :

RRDTool Command:

/usr/bin/rrdtool graph - \
--imgformat=PNG \
--start=-86400 \
--end=-60 \
--title="Home - Saber - MySQL - Questions" \
--base=1000 \
--height=120 \
--width=500 \
--alt-autoscale-max \
--lower-limit=0 \
--y-grid=questions \
--vertical-label="questions" \
--slope-mode \
--font TITLE:12: \
--font AXIS:8: \
--font LEGEND:10: \
--font UNIT:8: \
DEF:a="/var/www/cacti.home.aurelgadjo.fr/rra/home_-_saber_questions_52.rrd":questions:AVERAGE \
DEF:b="/var/www/cacti.home.aurelgadjo.fr/rra/home_-_saber_questions_52.rrd":questions:MAX \
AREA:a#FF7D00FF:"Questions" \
GPRINTMIN:"Min\:%8.2lf %s" \
GPRINTMAX:"Max\:%8.2lf %s" \
GPRINTLAST:"Current\:%8.2lf %s"

RRDTool Says:

ERROR: invalid y-grid format

Any idea ?