MySQL Host Template -- Updated on 2006-08-10

[You4eea]

I followed the readme step by step and everything went fine until I got to the device part.
I did the following:
Click the device link and choose teMysqlHost template; clicked create and get a list of 21 varibles to monitor, but no way to create graphs. I just have a red "X" to the far right of them.

SysSpecs:
FreeBSD 4.10
Cacti 8.6h

Any ideas?

[karlski]

Ok, I did everything the README said to get teMysql running properly, but have hit some apparently minor snags.

We run replication on our Mysql servers -does the teMysql still work with those? When I did the GRANT command I had to slightly change it because the README either had a typo, or the command simply broke replication:

GRANT PROCESS ON * TO cacti@'mybox.mydomain.com' IDENTIFIED by 'cactipasswd';
GRANT SUPER ON * TO cacti@%'mybox.mydomain.com' IDENTIFIED BY 'cactipasswd';

I had to change them to: (removed the % and change * to *.*)

GRANT PROCESS ON *.* TO cacti@'cactimachine' IDENTIFIED by 'cactipasswd';
GRANT SUPER ON *.* TO cacti@'cactimachine' IDENTIFIED BY 'cactipasswd';

SNMP was set once I configured /etc/snmp/snmpd.conf to have the correct community settings, and my 'device'e.g. my server is now shown as "up" in the devices tab of Cacti.

I also did an snmpwalk just to make sure from the cacti machine to the target server.

Now, when I imported the XML, I accidentally imported all of them, the step-to-five-minutes and the 1 minute interval XML. Was this a bad idea, is this why i'm not getting graphs?

I also did a change to the DB where I logged in to mysql on the cacti box, and entered:

UPDATE poller_item SET rrd_step=60;

Was this how to set the polling to go once every minute?

I also logged into cacti server and cd'd to /var/www/html/scripts and noticed the teMysql .php files for memcache and mysql perms did not match the other scripts, so I matched them by doing a chown apache:apache to them, now they look like the others, and have same exec perms as well via chmod.

I still do not have everything work, -can someone advise me what I did wrong?

I really beleive it must be the 1 minute polling, and that my database UPDATE poller command was useless. Should I be editing something esle, or dump my db and or how do I get rid of the XML that does 1 minute and just use the step to 5 minutes since both of them got imported and might conflict?

-karlski

[scio]

It seems the username/password support in Cacti is causing others woes. Believe me, it caused me woes, too.

Another work-around is to not enter any username/password at all, make no changes to the XML files (as Gleam suggests), but instead to modify the PHP stats gathering script like this:

Code: Select all

if ($_SERVER["argc"] == 3 || $_SERVER["argc"] == 5 || ($_SERVER["argv"][1] == "status" && $_SERVER["argc"] == 6)) {

    $host     = $_SERVER["argv"][2];
    $username = $_SERVER["argv"][3];
    $password = $_SERVER["argv"][4];

    $username = "cacti";
    $password = "yourcactipassword";

What this'll do is let a 3-argument call succeed, then it'll just hardcode username/password to be cacti/yourcactipassword. This is actually what I'm doing at Digg now because I started using a browser !Firefox, and it wouldn't let me enter the username/password so easily anymore.

It'd be super-cool if some PHP guru could suggest how to elegently make the script take "--username=XXX --password=XXX --host=XXX" sort of calling convention. If I ever have to digg into this too much, I'll just rewrite it in Perl.

Hmm. The more I ponder this, the more I think I should just make this the default script behaviour. Anyone have opinions on this matter?

Okay I read the documentation that came with the script and followed it exactly the first problem I ran into was that when adding the MySQL graphs to a device I would not get prompted for a username, password, or hostname. As seen in the post before me this was a common issue.. The first fix I tried was to change all the accept nulls from yes to no, but this did not work for me...then I tried changing my code like the above code which did not work either because I don't believe a username, password, or hostname is being passed to the script....?

I am curious to know how the arguments (username, password, and hostname) are being passed to the script from cacti that will probably really help me understand what exactly is going on here.

It is a great template though, this is really needed there are a few minor bugs but that is expected of course, thanks again for making this awesome template and if you could provide me with any more information that help me debug my issue...also re writing your script in perl sounds like a great deal I'd love to help if you need it Perl rocks! it rocks php soxs.

[maxos75]

Hi,
I've changed php.ini to 32M, make a "service httpd restart" but i still can't importing the xml file, anyone has an idea?

[feron]

Does anyone have a mirror of the latest version of the file? Femalia.net has been down for a few days now and I can't find the file anywhere else.

Thanks

[vad3r]

Very nice work. I would love to use it for all my MySQL servers but there's one problem: On some systems i run multiple instances and therefore it would be great to make the port configurable. Is there a easy way to add this feature??

Thx

vad3r

[kaiserbeto]

Is it just me or does this template take up a crap load of system resources.

My monitoring server has dual xeon cpu and 6 gb of ram ...
This happens ONLY when the host using this mysql template is enabled ...

See my 'top' output below ... any thoughts?

12198 apache 19 0 1939m 1.7g 432 R 2.3 29.2 0:58.05 rrdtool
12189 apache 20 0 1901m 1.7g 364 R 9.6 28.0 0:53.74 rrdtool
12207 apache 18 0 794m 621m 432 R 30.8 10.2 0:36.31 rrdtool
12209 apache 18 0 639m 552m 364 R 23.5 9.1 0:30.51 rrdtool
12206 apache 19 0 533m 436m 432 R 13.2 7.2 0:28.20 rrdtool
12214 apache 19 0 477m 381m 432 R 7.9 6.3 0:25.17 rrdtool
12226 apache 18 0 425m 312m 432 R 28.1 5.1 0:23.14 rrdtool

[statsfreak]

Ignore the message (admin: delete )

[statsfreak]

Ignore the message (admin: delete )

[statsfreak]

Is it just me or does this template take up a crap load of system resources

I've just installed the the Mysql template on my server and also experienced some huge performance problems almost the same as on your top output.

After deleting some InnoDB graphs at least the Buffers Pages mem and InsBuffer graphs my system was responsive again and can enjoy the great graphs from this template.

See http://www.satscan.net/cactiexport/serv ... net_7.html for my MySQL graphs

Eventually do a ps -ax | grep rrdtool to see where cacti is hanging and disable the graphs in Cacti where rrdtool is hanging.

Regards, Iwan van Rienen

[statsfreak]

After deleting some InnoDB graphs (at least the

Sorry about the double postings, got some error messages while submitting this post

[Coyote]

Is it just me or does this template take up a crap load of system resources.

My monitoring server has dual xeon cpu and 6 gb of ram ...
This happens ONLY when the host using this mysql template is enabled ...

See my 'top' output below ... any thoughts?

12198 apache 19 0 1939m 1.7g 432 R 2.3 29.2 0:58.05 rrdtool
12189 apache 20 0 1901m 1.7g 364 R 9.6 28.0 0:53.74 rrdtool
12207 apache 18 0 794m 621m 432 R 30.8 10.2 0:36.31 rrdtool
12209 apache 18 0 639m 552m 364 R 23.5 9.1 0:30.51 rrdtool
12206 apache 19 0 533m 436m 432 R 13.2 7.2 0:28.20 rrdtool
12214 apache 19 0 477m 381m 432 R 7.9 6.3 0:25.17 rrdtool
12226 apache 18 0 425m 312m 432 R 28.1 5.1 0:23.14 rrdtool

i had the same problem.
i narrowed it to be caused by the logarithmic graph.
i changed them to normal graph, and they now graph perfectly!

[kaiserbeto]

genius! it works. thanks and thanks again

[mmuse]

rrdtool would eat up all the available memory and max out CPU when trying to generate "InnoDB Buffers Pages/Mem" and "InnoDB InsBuffer". I have to eventually kill the rrdtool proc to bring the server back.

Under Cacti Management > Graph Management (in debug mode), the rrdtool command seems to be fine.

/usr/local/rrdtool-1.2.15/bin/rrdtool graph - \
--imgformat=PNG \
--start=-86400 \
--end=-60 \
--title="sql02 - teMySQL - InnoDB InsBuffer" \
--base=1000 \
--height=150 \
--width=600 \
--alt-autoscale-max \
--lower-limit=1 \
--logarithmic \
--vertical-label="" \
--slope-mode \
DEF:a="/var/www/html/cacti/rra/sql02_insbuffer_merged_300.rrd":insbuffer_inserts:AVERAGE \
DEF:b="/var/www/html/cacti/rra/sql02_insbuffer_merged_300.rrd":insbuffer_merged:AVERAGE \
DEF:c="/var/www/html/cacti/rra/sql02_insbuffer_merged_300.rrd":insbuffer_merges:AVERAGE \
AREA:a#CAF100:"Inserts" \
LINE1:a#6EA100:"" \
LINE1:b#4444FF:"Merged" \
LINE1:c#862F2F:"Merges"

---

RRD are getting the data:

[root@ rra]# /usr/local/rrdtool-1.2.15/bin/rrdtool fetch sql02_insbuffer_merged_300.rrd AVERAGE -r 60 -s -10m

insbuffer_merged insbuffer_merges insbuffer_inserts

1170981960: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982020: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982080: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982140: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982200: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982260: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982320: 1.6101694915e-02 1.6101694915e-02 1.6101694915e-02
1170982380: 5.6497175141e-04 5.6497175141e-04 5.6497175141e-04
1170982440: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982500: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00

Help please ~

[mmuse]

rrdtool would eat up all the available memory and max out CPU when trying to generate "InnoDB Buffers Pages/Mem" and "InnoDB InsBuffer". I have to eventually kill the rrdtool proc to bring the server back.

Under Cacti Management > Graph Management (in debug mode), the rrdtool command seems to be fine.

/usr/local/rrdtool-1.2.15/bin/rrdtool graph - \
--imgformat=PNG \
--start=-86400 \
--end=-60 \
--title="sql02 - teMySQL - InnoDB InsBuffer" \
--base=1000 \
--height=150 \
--width=600 \
--alt-autoscale-max \
--lower-limit=1 \
--logarithmic \
--vertical-label="" \
--slope-mode \
DEF:a="/var/www/html/cacti/rra/sql02_insbuffer_merged_300.rrd":insbuffer_inserts:AVERAGE \
DEF:b="/var/www/html/cacti/rra/sql02_insbuffer_merged_300.rrd":insbuffer_merged:AVERAGE \
DEF:c="/var/www/html/cacti/rra/sql02_insbuffer_merged_300.rrd":insbuffer_merges:AVERAGE \
AREA:a#CAF100:"Inserts" \
LINE1:a#6EA100:"" \
LINE1:b#4444FF:"Merged" \
LINE1:c#862F2F:"Merges"

---

RRD are getting the data:

[root@ rra]# /usr/local/rrdtool-1.2.15/bin/rrdtool fetch sql02_insbuffer_merged_300.rrd AVERAGE -r 60 -s -10m

insbuffer_merged insbuffer_merges insbuffer_inserts

1170981960: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982020: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982080: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982140: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982200: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982260: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982320: 1.6101694915e-02 1.6101694915e-02 1.6101694915e-02
1170982380: 5.6497175141e-04 5.6497175141e-04 5.6497175141e-04
1170982440: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00
1170982500: 0.0000000000e+00 0.0000000000e+00 0.0000000000e+00

Help please ~

Nevermind, just find out that if I unchecked Logarithmic, rrdtool can graph without any problem.