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

philovivero · Post by **philovivero** » Sun Dec 18, 2005 8:43 pm

EDIT: 2006.02.01:

This is a tarball containing a host XML template for MySQL stats. Almost all the MySQL stats you could possibly want. Pretty thumbnail. Oooh. So pretty:

Download URL is here: http://www.faemalia.net/mysqlUtils/.

. InnoDB graphs were added 2005-12-23.
. MySQL 5.0 support was added 2006-02-01.
. Graphs are logarithmic. If you don't like that, edit each graph template and uncheck the logarithmic checkbox.
. As of 2005-12-25, all data input methods are DERIVE, not COUNTER, with minimum value of 0. This way when you restart your MySQL, you won't get those ugly spikes that make your graphs useless.
. Took out most Y-axis labels. They're not accurate on almost any graph, and on the ones they are, everyone already knows what the Y-axis means anyway.

Download the file, untar it ("tar -zxvf" on Linux, read this thread for all the various Win32 programs that can deal with it). Read the README included. It's got an important note, especially if you're using polling interval of 300 seconds (you probably are).

JJX · Post by **JJX** » Mon Dec 19, 2005 7:10 am

http://www.faemalia.net/mysqlUtils/Live ... gPaper.pdf
link isnt working

looks really nice - i will try these now!

philovivero · Post by **philovivero** » Mon Dec 19, 2005 11:53 pm

JJX wrote:http://www.faemalia.net/mysqlUtils/Live ... gPaper.pdf
link isnt working.

Oh, sorry. Fixed it. It's just a copy of the paper available on the MySQL site about LiveJournal's back-end architecture.

Let me know if you have any input on the Cacti MySQL graphs. I'm excited to see how they work for others. Every time I look at them, I think of something I could improve.

JJX · Post by **JJX** » Tue Dec 20, 2005 2:32 am

works really nice!

i installed to 2 pcs and both worked fine

something u can add to the readme:

to make it work you have to remove the snmp community string or it will not work!!!

when i had snmp community string ("public") the host was detected as down and nothing worked.
when i removed it everything worked asap

great work

dust2k · Post by **dust2k** » Wed Dec 21, 2005 12:08 am

how to install this template ? uncompressed file looks like a binary file instead of XML file.

JJX · Post by **JJX** » Wed Dec 21, 2005 12:47 am

read the readme

its step-by-step

RunningWave · Post by **RunningWave** » Wed Dec 21, 2005 3:10 am

Hi,

This template is cool, There are few things which I think could be imporved

- The graphs are missing "max" "average" "current" data for the counters above them which are quite helpful.

- The axis are marked as 1.0e+1 etc (in my case) which is kind of ugly. I have not yet found a way to fix it.

- Innodb data is mixing (yes I know it is inconvenient to dig it from SHOW INNODB STATUS)

- If somehow mysql user info is to be entered only once per host adding it for each graph is inconvenient.

dust2k · Post by **dust2k** » Wed Dec 21, 2005 3:42 am

JJX wrote:read the readme

its step-by-step

Where is readme ? uncompress file is only one file call :teMySQL-cacti-20051218

philovivero · Post by **philovivero** » Fri Dec 23, 2005 2:39 pm

RunningWave wrote:- The graphs are missing "max" "average" "current" data for the counters above them which are quite helpful.

I tried to have those in there, but for some reason, when I put them in, I get no values. Since it's a lot of work to put them in for no values, I didn't put them in. Maybe it's a bug, maybe I did something stupid. Please feel free to add them to one or two graphs, and if it works for you, I'm sure we'd all really appreciate your help. :)

- The axis are marked as 1.0e+1 etc (in my case) which is kind of ugly. I have not yet found a way to fix it.

Edit each graph template and uncheck the "logarithmic" checkbox.

- Innodb data is mixing (yes I know it is inconvenient to dig it from SHOW INNODB STATUS)

Peter Zaitsev, the MySQL tuning guru, has sent me a patch to mysql_stats.php that parses out the SHOW INNODB STATUS. Now it's on my list of things to do to create graphs for all this data. If you download the current XML file, you'll notice data input methods for the various InnoDB stats! Yay Peter!

- If somehow mysql user info is to be entered only once per host adding it for each graph is inconvenient.

It's a limitation of Cacti. I want to edit the mysql_stats.php to have a default username/password, then if your Cacti monitoring username/password is the same everywhere in your cluster, you could just not enter that information and it'd work.

But yes, I find it very annoying that for every host I add (tens) I have to type the same two values over and over. Thank goodness for Firefox and its autocomplete. On the second host and beyond, I just type <downarrow><downarrow><tab> over and over 30 times until I get down to the bottom.

philovivero · Post by **philovivero** » Fri Dec 23, 2005 2:41 pm

dust2k wrote:how to install this template ? uncompressed file looks like a binary file instead of XML file.

If you uncompress the file, you have a tar archive. Do "tar -xvf <filename>" or, before uncompressing, do "tar -zxvf <filename>".

philovivero · Post by **philovivero** » Fri Dec 23, 2005 5:30 pm

Again, thanks to Peter Zaitsev, we have InnoDB graphing support!

[EDIT!]Removed info. See thread root post by me for all current info.[/EDIT]

Anyone who knows how to get the Current/Average/Max values onto the graph, PLEASE DO SO! Stick them on, and email me the modified XML or whatever. I'll host the file on my website.

Enjoy.

philovivero · Post by **philovivero** » Fri Dec 23, 2005 5:35 pm

JJX wrote:something u can add to the readme:
to make it work you have to remove the snmp community string or it will not work!!!

when i had snmp community string ("public") the host was detected as down and nothing worked. when i removed it everything worked asap ;)

That isn't necessarily true. It depends on the snmpd.conf of each of your hosts to graph. For example, our snmpd.conf files have an "rocommunity" argument that isn't public, but something else entirely (I think for security purposes).

I think by default, the "public" snmp community is what most snmpd.conf will specify...

Not sure this belongs in the README of my MySQL graphs, though. Perhaps it would be more of a Cacti FAQ.

JJX · Post by **JJX** » Sat Dec 24, 2005 4:46 pm

philovivero wrote:
JJX wrote:something u can add to the readme:
to make it work you have to remove the snmp community string or it will not work!!!

when i had snmp community string ("public") the host was detected as down and nothing worked. when i removed it everything worked asap
That isn't necessarily true. It depends on the snmpd.conf of each of your hosts to graph. For example, our snmpd.conf files have an "rocommunity" argument that isn't public, but something else entirely (I think for security purposes).

I think by default, the "public" snmp community is what most snmpd.conf will specify...

Not sure this belongs in the README of my MySQL graphs, though. Perhaps it would be more of a Cacti FAQ.

- also i have a differenet community set.
if i set any value as community in cacti mysql host seems down and no graph is being graphed!

- using the first version (only) mysql graphs are corrupted (or they should look like that?). any idea why happen this?
[ check attached image ]

JJX · Post by **JJX** » Sat Dec 24, 2005 6:09 pm

Using the new template i have these data input methods created:

Data Input Method
[success] teMySQL - Replication [update]
[success] teMySQL - Volatile Queries [update]
[success] teMySQL - Volatile Handler [update]
[success] teMySQL - Select Queries [update]
[success] teMySQL - Select Handler [update]
[success] teMySQL - Index Usage [update]
[success] teMySQL - Query Cache [update]
[success] teMySQL - Network Usage [update]
[success] teMySQL - Sorts [update]
[success] teMySQL - Locking and Slow [update]
[success] teMySQL - Temporary Objects [update]
[success] teMySQL - Threads/Abends [update]
[success] Get SNMP Data [update]
[success] MySQL - Command statistics [update]
[success] MySQL - Handler statistics [update]
[success] teMySQL - InnoDB Row Operations [update]
[success] teMySQL - InnoDB Buffers [update]
[success] teMySQL - InnoDB Locks [update]
[success] teMySQL - InnoDB File I/O [update]

as u can see cpu & load avergae data input methods are missing (no graphs are created at all)
Also the rest graphs has no data

(before adding the new template i deleted the old one - the word "update" is because i added the same template twice to check what happens)

running the sctipts manually i get correct data

philovivero · Post by **philovivero** » Sun Dec 25, 2005 5:40 pm

That is actually the way it's supposed to look. Since it's a logarithmic graph, and 0's are not plotted (I'd love a special case where 0 was plotted at the same distance below 1e+00 as 1e+0 is plotted below 1e+01), then you get missing graph chunks wherever the result was 0.

So that's actually right.

I don't know how I could fix this. Any pointers welcome.

Cacti

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

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

MySQL Graphs including InnoDB

JJX's Odd-looking Graph

Who is online