UpdateÑ The MySQL Graph Collection - Version 2.5.0!

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

Moderators: Moderators, Developers

Author
Message
scottevil
Posts: 24
Joined: Tue Nov 28, 2006 6:44 am
Location: Akron
Contact:

UpdateÑ The MySQL Graph Collection - Version 2.5.0!

#1 Post by scottevil » Wed Apr 06, 2011 2:25 pm

Just updated the code to include the query cache hits template!!! Thanks Kyle and

All, Otto is no longer has time to support this template, so I inherited it! Let me know of any changes that might need done. I fixed a few problems with the graphs and now everything works again on 0.8.7g. I also plan to add a port option and some documentation on how to use this template with the cacit CLI.

The code is now here if anybody wants to help with it:
http://code.google.com/p/crunchtools/so ... ysql_stats

Best Regards
Scott M
Attachments
graph_mysql_stats.zip
(35.2 KiB) Downloaded 1374 times
mysql_stats.gif
mysql_stats.gif (71.22 KiB) Viewed 16017 times
Last edited by scottevil on Wed May 30, 2012 2:09 pm, edited 2 times in total.
Scott McCarty
Solutions Architect/Open Source Evangelist
Twitter: @fatherlinux
URL: http://crunchtools.com

tosage
Cacti User
Posts: 154
Joined: Wed Jul 28, 2010 5:05 am
Location: France

Re: The MySQL Graph Collection - Version 2.0.1!

#2 Post by tosage » Sat Apr 30, 2011 9:56 am

Thanks for the share and to continue the work in this template :)
These files have been exported to a recent version of cacti, more recent than 0.8.7e

Can you convert it ?
Cacti Version - 0.8.8a
Plugin Architecture - 3.1
Poller Type - spine
Server Info - Linux
Web Server - Apache/2.2.22 (Ubuntu)
PHP - 5.3.10-1ubuntu3.6 with Suhosin-Patch (cli)
MySQL - 5.5.29-0ubuntu0.12.04.2
RRDTool - 1.4.7

scottevil
Posts: 24
Joined: Tue Nov 28, 2006 6:44 am
Location: Akron
Contact:

Re: The MySQL Graph Collection - Version 2.0.1!

#3 Post by scottevil » Sat Apr 30, 2011 5:34 pm

Oh, that is a problem now. Do you know if there is any easier way to convert them, than to install an older version of cacti and export them from that? I think I could take the originals, patch them and export them in an older version, but not sure if that is the only path.

Best Regards
Scott M
Scott McCarty
Solutions Architect/Open Source Evangelist
Twitter: @fatherlinux
URL: http://crunchtools.com

noname
Cacti Guru User
Posts: 1566
Joined: Thu Aug 05, 2010 2:04 am
Location: Japan

Re: The MySQL Graph Collection - Version 2.0.1!

#4 Post by noname » Mon May 02, 2011 2:03 am

Original templates seemed to be exported from Cacti 0.8.7g.
Let's try replacing hash code to your version,
e.g. "hash_xx0021" -> "hash_xx0019".

See also: http://docs.cacti.net/howto:determine_cacti_template_version

For example, converting for 0.8.7e by traditional(?) sed method:
% sed -i.bak -e 's/\([<\/*|>|\|]\)hash_\(..\)0021/\1hash_\20019/g' graph_mysql_stats/*.xml
or, more simply:
% sed -i.bak -e 's/hash_\(..\)0021/hash_\10019/g' graph_mysql_stats/*.xml
Import results:
(when imported modified 'cacti_graph_template_mysql_command_statistics.xml')
Image

But I'm not sure whether if this converted templates will work (or not) at older version of Cacti.

tosage
Cacti User
Posts: 154
Joined: Wed Jul 28, 2010 5:05 am
Location: France

Re: The MySQL Graph Collection - Version 2.0.1!

#5 Post by tosage » Mon May 02, 2011 6:51 am

Whaou great, thanks for the tips :wink:
I test it !
Cacti Version - 0.8.8a
Plugin Architecture - 3.1
Poller Type - spine
Server Info - Linux
Web Server - Apache/2.2.22 (Ubuntu)
PHP - 5.3.10-1ubuntu3.6 with Suhosin-Patch (cli)
MySQL - 5.5.29-0ubuntu0.12.04.2
RRDTool - 1.4.7

Kedare
Posts: 2
Joined: Sun May 08, 2011 8:27 am

Re: The MySQL Graph Collection - Version 2.0.1!

#6 Post by Kedare » Sun May 08, 2011 8:31 am

Hello,

I tried to create the graphs for my MySQL Server, but only the Query Cache is shown correctly, all the other graphs are not displayed (the image is missing).

I tried to run the script by hand, and it works fine :

Code: Select all

/usr/bin/php -q /var/www/cacti/scripts/mysql_stats.php cache XxX.com cacti_stats XxXx
PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php5/20060613+lfs/mhash.so' - /usr/lib/php5/20060613+lfs/mhash.so: cannot open shared object file: No such file or directory in Unknown on line 0
PHP Warning:  Cannot load module 'pdo_sqlite' because required module 'pdo' is not loaded in Unknown on line 0
used:17728 available:67091136

/usr/bin/php -q /var/www/cacti/scripts/mysql_stats.php traffic XxX cacti_stats XxXx
PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php5/20060613+lfs/mhash.so' - /usr/lib/php5/20060613+lfs/mhash.so: cannot open shared object file: No such file or directory in Unknown on line 0
PHP Warning:  Cannot load module 'pdo_sqlite' because required module 'pdo' is not loaded in Unknown on line 0
in:85 out:73
But, only the Query Cache graph is shown... Do you have an idea of what can be the problem ?

Thank you

scottevil
Posts: 24
Joined: Tue Nov 28, 2006 6:44 am
Location: Akron
Contact:

Re: The MySQL Graph Collection - Version 2.0.1!

#7 Post by scottevil » Sun May 08, 2011 10:34 am

What version are you running? Do you know if you are getting data in the RRD files?

Best Regards
Kedare wrote:Hello,

I tried to create the graphs for my MySQL Server, but only the Query Cache is shown correctly, all the other graphs are not displayed (the image is missing).

I tried to run the script by hand, and it works fine :

Code: Select all

/usr/bin/php -q /var/www/cacti/scripts/mysql_stats.php cache XxX.com cacti_stats XxXx
PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php5/20060613+lfs/mhash.so' - /usr/lib/php5/20060613+lfs/mhash.so: cannot open shared object file: No such file or directory in Unknown on line 0
PHP Warning:  Cannot load module 'pdo_sqlite' because required module 'pdo' is not loaded in Unknown on line 0
used:17728 available:67091136

/usr/bin/php -q /var/www/cacti/scripts/mysql_stats.php traffic XxX cacti_stats XxXx
PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php5/20060613+lfs/mhash.so' - /usr/lib/php5/20060613+lfs/mhash.so: cannot open shared object file: No such file or directory in Unknown on line 0
PHP Warning:  Cannot load module 'pdo_sqlite' because required module 'pdo' is not loaded in Unknown on line 0
in:85 out:73
But, only the Query Cache graph is shown... Do you have an idea of what can be the problem ?

Thank you
Scott McCarty
Solutions Architect/Open Source Evangelist
Twitter: @fatherlinux
URL: http://crunchtools.com

Kedare
Posts: 2
Joined: Sun May 08, 2011 8:27 am

Re: The MySQL Graph Collection - Version 2.0.1!

#8 Post by Kedare » Sun May 08, 2011 2:35 pm

I'm running Version 0.8.7e. (I can't upgrade for now)

How can I know if the RDD files are getting data ?

scottevil
Posts: 24
Joined: Tue Nov 28, 2006 6:44 am
Location: Akron
Contact:

Re: The MySQL Graph Collection - Version 2.0.1!

#9 Post by scottevil » Sun May 08, 2011 3:15 pm

That is probably your problem, let me try and install 0.8.7e first and translate this thing, so that it will work with older versions. I will let you know when I am done.
Kedare wrote:I'm running Version 0.8.7e. (I can't upgrade for now)

How can I know if the RDD files are getting data ?
Scott McCarty
Solutions Architect/Open Source Evangelist
Twitter: @fatherlinux
URL: http://crunchtools.com

User avatar
jt555
Cacti User
Posts: 54
Joined: Sat Nov 17, 2007 3:37 pm
Location: Atlanta, Ga

Re: The MySQL Graph Collection - Version 2.0.1!

#10 Post by jt555 » Wed May 18, 2011 9:38 am

This works fine on my local server but fails when connecting to a remote server.

I can connect to the remote via cli:
mysql -h remoteip -user=dbuser --password=dbpw
and run the same commands the script runs:
SHOW STATUS; and SHOW VARIABLES;.

But I cannot connect the remote host using the php script:
$ /usr/bin/php /var/www/html/scripts/mysql_stats.php status remoteip dbuser dbpw Connections
Error: MySQL connect failed. Check MySQL parameters (host/login/password)

I know that my parameters are correct.

select version(); returns:
local MySQL version: 5.0.77
remote MySQL version: 5.0.54a-enterprise-log

Anybody have any idea how to get this to work?

noname
Cacti Guru User
Posts: 1566
Joined: Thu Aug 05, 2010 2:04 am
Location: Japan

Re: The MySQL Graph Collection - Version 2.0.1!

#11 Post by noname » Wed May 18, 2011 10:58 am

When performed this command, what do you see?

Code: Select all

$ /usr/bin/php -r 'mysql_connect("remoteip", "dbuser", "dbpw");'
If nothing returned (and nothing recorded in your php error log), perhaps it succeeded.
If not so, hmm..

User avatar
jt555
Cacti User
Posts: 54
Joined: Sat Nov 17, 2007 3:37 pm
Location: Atlanta, Ga

Re: The MySQL Graph Collection - Version 2.0.1!

#12 Post by jt555 » Wed May 18, 2011 11:40 am

noname wrote:When performed this command, what do you see?

Code: Select all

$ /usr/bin/php -r 'mysql_connect("remoteip", "dbuser", "dbpw");'
If nothing returned (and nothing recorded in your php error log), perhaps it succeeded.
If not so, hmm..
This is what was returned:
PHP Warning: Module 'mysql' already loaded in Unknown on line 0


I can't find the php error log.
'find / -name php.errors' returned nothing.

noname
Cacti Guru User
Posts: 1566
Joined: Thu Aug 05, 2010 2:04 am
Location: Japan

Re: The MySQL Graph Collection - Version 2.0.1!

#13 Post by noname » Wed May 18, 2011 7:49 pm

jt555 wrote:PHP Warning: Module 'mysql' already loaded in Unknown on line 0
If the following line exists in your php.ini, then comment out.

Code: Select all

extension=mysql.so --> ;extension=mysql.so
After that, restart Apache (and Mysql).

Probably your PHP binary have already linked MySQL library.

Code: Select all

% ldd /usr/bin/php | grep mysql
        libmysqlclient.so.15 =>  /usr/lib/libmysqlclient.so.15

User avatar
jt555
Cacti User
Posts: 54
Joined: Sat Nov 17, 2007 3:37 pm
Location: Atlanta, Ga

Re: The MySQL Graph Collection - Version 2.0.1!

#14 Post by jt555 » Fri May 20, 2011 8:52 am

noname wrote:
jt555 wrote:PHP Warning: Module 'mysql' already loaded in Unknown on line 0
If the following line exists in your php.ini, then comment out.

Code: Select all

extension=mysql.so --> ;extension=mysql.so
After that, restart Apache (and Mysql).

Probably your PHP binary have already linked MySQL library.

Code: Select all

% ldd /usr/bin/php | grep mysql
        libmysqlclient.so.15 =>  /usr/lib/libmysqlclient.so.15

extension=mysql.so is not in my php.ini.
ldd /usr/bin/php | grep mysql returned nothing.

User avatar
jt555
Cacti User
Posts: 54
Joined: Sat Nov 17, 2007 3:37 pm
Location: Atlanta, Ga

Re: The MySQL Graph Collection - Version 2.0.1!

#15 Post by jt555 » Fri May 20, 2011 9:32 am

extension=mysql.so is not in my php.ini.
ldd /usr/bin/php | grep mysql returned nothing.
It works now without making any changes other than a MySQL restart. I think it was a problem with our MySQL server all along.
Thanks for your help.

Post Reply