The MySQL Graph Collection - Version 2.0!

Templates, scripts for templates, scripts and requests for templates.

Moderators: Moderators, Developers

Post Reply
Author
Message
vad3r
Posts: 2
Joined: Fri Nov 03, 2006 3:04 pm

#106 Post by vad3r » Mon Nov 06, 2006 5:56 am

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

User avatar
gandalf
Developer
Posts: 22375
Joined: Thu Dec 02, 2004 2:46 am
Location: Muenster, Germany
Contact:

#107 Post by gandalf » Mon Nov 06, 2006 2:08 pm

vad3r wrote: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
Posts: 5
Joined: Tue Jan 09, 2007 6:05 pm

only traffic have no result. it's always zero.

#108 Post by mild » Thu Jan 11, 2007 12:29 am

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

[[email protected] 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
Cacti User
Posts: 206
Joined: Tue Jan 09, 2007 8:28 am
Location: London, UK

#109 Post by msw1970 » Thu Jan 11, 2007 10:12 am

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

User avatar
gandalf
Developer
Posts: 22375
Joined: Thu Dec 02, 2004 2:46 am
Location: Muenster, Germany
Contact:

#110 Post by gandalf » Thu Jan 11, 2007 12:20 pm

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

msw1970
Cacti User
Posts: 206
Joined: Tue Jan 09, 2007 8:28 am
Location: London, UK

#111 Post by msw1970 » Thu Jan 11, 2007 3:06 pm

gandalf wrote: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
Cacti User
Posts: 103
Joined: Fri Aug 18, 2006 11:22 am
Location: MSK, RU

#112 Post by airwalk » Fri Jan 12, 2007 6:25 am

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
Posts: 19
Joined: Tue Jul 12, 2005 1:49 pm
Location: Argentina
Contact:

No Graph Display

#113 Post by schef4711 » Thu Feb 01, 2007 1:21 am

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
Posts: 9
Joined: Mon Oct 03, 2005 11:48 am

Re: No Graph Display

#114 Post by joe.garrett » Sat Feb 03, 2007 9:17 pm

schef4711 wrote: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
Posts: 7
Joined: Wed Apr 04, 2007 5:16 am

#115 Post by jezdikm » Wed Apr 04, 2007 7:31 am

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
Posts: 7
Joined: Wed Apr 04, 2007 5:16 am

#116 Post by jezdikm » Wed Apr 04, 2007 7:46 am

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
Posts: 1
Joined: Mon Apr 09, 2007 2:54 pm

#117 Post by razzle dazzle » Mon Apr 09, 2007 3:07 pm

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

The MySQL version I use is > 5.0.2, and the "SHOW STATUS" command seems to have changed from that version on:
mysql.com wrote: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 ! :D

maw
Posts: 34
Joined: Wed Mar 07, 2007 2:26 am
Location: Vancouver, Canada

#118 Post by maw » Wed Apr 11, 2007 4:18 pm

Thank you Razzle and Joe for your documented fixes.

Something I noticed from the README file... in the usage section:
"GRANT PROCESS ON * TO [email protected]'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
:: FreeBSD6.2 - Apache 1.3.37+mod_ssl - PHP 5.2.1 - MySQL 5.0 ::

Panther_1
Posts: 28
Joined: Tue May 08, 2007 8:29 am
Location: NRW, Germany
Contact:

new Version? still in development?

#119 Post by Panther_1 » Tue May 08, 2007 8:40 am

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
Posts: 1
Joined: Tue Mar 13, 2007 4:22 pm

traffic graph blank

#120 Post by tone77 » Wed Jun 13, 2007 3:43 pm

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?
Attachments
graph_image.php.png
graph_image.php.png (26.99 KiB) Viewed 11193 times

Post Reply