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

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

Moderators: Moderators, Developers

Post Reply
Author
Message
philovivero
Posts: 37
Joined: Mon Dec 12, 2005 2:27 pm
Location: San Francisco, USA

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

#1 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:

Image

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).
Last edited by philovivero on Thu Aug 10, 2006 4:28 pm, edited 6 times in total.
power to the soul
belief to the people

JJX
Cacti User
Posts: 402
Joined: Thu Oct 06, 2005 5:03 am

#2 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!
cacti rulez!

philovivero
Posts: 37
Joined: Mon Dec 12, 2005 2:27 pm
Location: San Francisco, USA

#3 Post by philovivero » Mon Dec 19, 2005 11:53 pm

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.
power to the soul
belief to the people

JJX
Cacti User
Posts: 402
Joined: Thu Oct 06, 2005 5:03 am

#4 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 ;)
cacti rulez!

User avatar
dust2k
Cacti User
Posts: 101
Joined: Sun Dec 04, 2005 3:14 am

#5 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
Cacti User
Posts: 402
Joined: Thu Oct 06, 2005 5:03 am

#6 Post by JJX » Wed Dec 21, 2005 12:47 am

read the readme ;)

its step-by-step
cacti rulez!

RunningWave
Posts: 6
Joined: Tue Dec 20, 2005 10:44 pm

#7 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.

User avatar
dust2k
Cacti User
Posts: 101
Joined: Sun Dec 04, 2005 3:14 am

#8 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
Posts: 37
Joined: Mon Dec 12, 2005 2:27 pm
Location: San Francisco, USA

#9 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.
power to the soul
belief to the people

philovivero
Posts: 37
Joined: Mon Dec 12, 2005 2:27 pm
Location: San Francisco, USA

#10 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>".
power to the soul
belief to the people

philovivero
Posts: 37
Joined: Mon Dec 12, 2005 2:27 pm
Location: San Francisco, USA

MySQL Graphs including InnoDB

#11 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.
Last edited by philovivero on Sun Dec 25, 2005 5:37 pm, edited 2 times in total.
power to the soul
belief to the people

philovivero
Posts: 37
Joined: Mon Dec 12, 2005 2:27 pm
Location: San Francisco, USA

#12 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.
power to the soul
belief to the people

JJX
Cacti User
Posts: 402
Joined: Thu Oct 06, 2005 5:03 am

#13 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 ]
Attachments
maybe_problem.gif
maybe_problem.gif (19.5 KiB) Viewed 116674 times
cacti rulez!

JJX
Cacti User
Posts: 402
Joined: Thu Oct 06, 2005 5:03 am

#14 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
cacti rulez!

philovivero
Posts: 37
Joined: Mon Dec 12, 2005 2:27 pm
Location: San Francisco, USA

JJX's Odd-looking Graph

#15 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.
power to the soul
belief to the people

Post Reply