The MySQL Graph Collection - Version 2.0!

[vad3r]

Is there a easy way to add the port to the XML's? If have several servers with 2 or more MySQL instances running.

Thanks

vad3r

[gandalf]

Is there a easy way to add the port to the XML's? If have several servers with 2 or more MySQL instances running.

Thanks

vad3r

The way to do so would be using <arg_prepend>, AFAIK. But I did not try it on my own. Please let me know if you've got it
Reinhard

[mild]

i installed that fellow instruction.
and change settings as debug mode.

another xml is working fine, but only traffic xml had problem.

Code: Select all

01/11/2007 02:10:02 PM - CMDPHP: Poller[0] Host[2] DS[37] CMD: /usr/sbin/php -q /home/mild/public_html/cacti/scripts/mysql_stats.php traffic 222.122.166.40 uid pwd, output: in:78 out:67
01/11/2007 02:10:02 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "insert into poller_output (local_data_id, rrd_name, time, output) values (37, '', '2007-01-11 14:10:02', 'in:78 out:67')"
01/11/2007 02:10:02 PM - CMDPHP: Poller[0] Time: 0.9999 s, Theads: N/A, Hosts: 2
01/11/2007 02:10:02 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "insert into poller_time (poller_id, start_time, end_time) values (0, NOW(), NOW())"
01/11/2007 02:10:03 PM - CMDPHP: Poller[0] DEBUG: SQL Assoc: "select poller_id,end_time from poller_time where poller_id = 0"
01/11/2007 02:10:03 PM - CMDPHP: Poller[0] DEBUG: SQL Assoc: "select  poller_output.output,  poller_output.time,  poller_output.local_data_id,  poller_item.rrd_path,  poller_item.rrd_name,  poller_item.rrd_num  from (poller_output,poller_item)  where (poller_output.local_data_id=poller_item.local_data_id and poller_output.rrd_name=poller_item.rrd_name)  limit 10000"
01/11/2007 02:10:03 PM - CMDPHP: Poller[0] DEBUG: SQL Assoc: "select     data_template_rrd.data_source_name,     data_input_fields.data_name     from (data_template_rrd,data_input_fields)     where data_template_rrd.data_input_field_id=data_input_fields.id     and data_template_rrd.local_data_id=37"
01/11/2007 02:10:03 PM - POLLER: Poller[0] Parsed MULTI output field 'in:78' [map in->traffic_in]
01/11/2007 02:10:03 PM - POLLER: Poller[0] Parsed MULTI output field 'out:67' [map out->traffic_out]
01/11/2007 02:10:03 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "delete from poller_output where local_data_id='37' and rrd_name='' and time='2007-01-11 14:10:02'"
01/11/2007 02:10:03 PM - POLLER: Poller[0]  [b]CACTI2RRD: /usr/bin/rrdtool update /home/mild/public_html/cacti/rra/a3box_db_server_traffic_out_37.rrd --template traffic_in:traffic_out 1168524602:78:67
[/b]

well.. i tried manually like this

update /home/mild/public_html/cacti/rra/a3box_db_server_traffic_out_37.rrd --template traffic_in:traffic_out 1168524602:78:67

then it's work and make output as 78:67

but.
when i fetch data from rrd directly,

then

Code: Select all

[root@mp3-list log]# rrdtool fetch /home/mild/public_html/cacti/rra/a3box_db_server_traffic_out_37.rrd AVERAGE


1168522800: 0.0000000000e+00 0.0000000000e+00
1168523100: 0.0000000000e+00 0.0000000000e+00
1168523400: 0.0000000000e+00 0.0000000000e+00
1168523700: 0.0000000000e+00 0.0000000000e+00
1168524000: 0.0000000000e+00 0.0000000000e+00
1168524300: 0.0000000000e+00 0.0000000000e+00
1168524600: 0.0000000000e+00 0.0000000000e+00
1168524900: 0.0000000000e+00 0.0000000000e+00
1168525200: nan nan

the result is always zero.

i confirmed 78:67 value and field name . but useless

how can i fix that ?

[msw1970]

Hi

Having a bit of trouble with these graphs. When I use the host localhost for these, everything works fine. If I try to query another host using SNMP I get nan's.

I don't believe it's an SNMP problem on the remote box as I can get Apache stats from that one using SNMP.

Any ideas??

[gandalf]

You need to pay attention to provide the cactiuser as a valid mysql user on the remote host with the correct permissions
Reinhard

[msw1970]

You need to pay attention to provide the cactiuser as a valid mysql user on the remote host with the correct permissions
Reinhard

I've created the cactiuser on the remote host and granted it all privileges on the db I want to query. Still no luck.

[airwalk]

I'm using mysql on non-standard port so I had to modify mysql_connect to the following:

"if (@mysql_connect('localhost:3307', $username, $password))"

If I run mysql_stat from cmd I always receive "zero" results. here's the output from cmd:

"change_db:0 delete:0 insert:0 select:0 update:0"

No other messages. I connect to mysql as a root.

What could be wrong?

[schef4711]

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

[joe.garrett]

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.

[jezdikm]

Hi,
fist thank you for this script.

I have problem (as some others) with displaying graphs. I can run mysql_stats.php from CLI and it return correct values. There is no error in poller log. (data are inserted into DB).

The whole problem is with RRDTOOL graph generation.
Here is the command:

Code: Select all

/usr/bin/rrdtool graph - \
--imgformat=PNG \
--start=-86400 \
--end=-300 \
--title="Poppy - MySQL - Command statistics" \
--base=1000 \
--height=120 \
--width=500 \
--alt-autoscale-max \
--lower-limit=0 \
--y-grid=access units \
--vertical-label="access units" \
--slope-mode \
....

Here is the output:

Code: Select all

ERROR: invalid y-grid format

Any idea where is the problem? I mean what to change and where.

Cacti: 0.8.6j
rrdtool: 1.2.18
script downloaded from first page of this thread

Thanks in advance,
Milan

[jezdikm]

I found a solution (empty graph - -y-grid option problem).
In Graph Templates -> MySQL graphs -> remove any text from "Unit Grid Value" input box and than it is working.

Why is this text in here ? I don't know ...

[razzle dazzle]

I just spent a couple of hours looking why the "mysql command statistics" and "mysql traffic" graphs stayed empty and finally got it working.

The MySQL version I use is > 5.0.2, and the "SHOW STATUS" command seems to have changed from that version on:

Before MySQL 5.0.2, SHOW STATUS returned global status values. Because the default as of 5.0.2 is to return session values, this is incompatible with previous versions. To issue a SHOW STATUS statement that will retrieve global status values for all versions of MySQL, write it like this:
SHOW /*!50002 GLOBAL */ STATUS;

So if you have MySQL 5.0.2 or later, you can fix your graphs by doing the following:

- Open the file <cacti-install-dir>/site/scripts/mysql_stats.php

- Search for the text "$result_stat = @mysql_query("SHOW STATUS");" (around line 19)

- On that line, replace the text SHOW STATUS by SHOW /*!50002 GLOBAL */ STATUS


Please see this url for more information.


Hope this helps !

[maw]

Thank you Razzle and Joe for your documented fixes.

Something I noticed from the README file... in the usage section:
"GRANT PROCESS ON * TO cactiuser@'cactihost.com' IDENTIFIED by 'cactipasswd';"

it should read:
GRANT PROCESS ON *.* TO 'cactiuser'@'cactihost.com' IDENTIFIED by 'cactipasswd';

Where, cactiuser/cactipasswd is the user specified user/pass.
Where, cactihost.com is localhost or FQDN

[Panther_1]

hy,

is this Script /template still in development? Will be there a new Version with fixes for the show globals and the - -y-grid ?

Nice would be also a version which graphs the replication delay.


ruben

[tone77]

I just installed the scripts and xml templates. QueryCache is working great, but the traffic graph is blank and thread graph isn't even created.

I am using Cacti 0.8.6j

from the command line traffic works great
php -n mysql_stats.php traffic <host> <user> <pw>
in:96 out:85

debug mode: RRDTool says: OK

cli threads:
php -n mysql_stats.php traffic <host> <user> <pw>
connected:41 running:3 cached:29


any ideas?