How do I graphing mysql statistics in Cacti?


Graphing mysql statistics in Cacti

The HOWTO below will guide you through creating statistics graphs for MySQL usage in Cacti.

 

We have modified the authentication to use the standard mtop user which was already on our system.

I saved this script as /usr/local/bin/cacti-mysqlstats.php and set it executable with:

chmod a+rx /usr/local/bin/cacti-mysqlstats.php

Here is a local cached copy of the script from: http://www.cacti.net/additional_scripts.php [Gets statistics from MySQL server]
#!/usr/bin/php -q
<?

/* sql_stat – Gets statistics from MySQL server

Takes one argument (Variable name) and returns it’s value.
Good replacement for the sql.php script.

some interesting variables:

Questions – queries executed since last restart.
Threads_running – running queries now
Threads_connected – connected clients
.
.
For more info RTFM or mail me:

Martin Spasov (mspasov@techno-link.com)
This script is distributed under GPL.

*/

$var = $argv[1];
mysql_connect(“localhost”, “mysqltop”) or die (“0″);
$res = mysql_query(“SHOW STATUS”) or die(“0″);
while ($a = mysql_fetch_row($res)) {
$stat[$a[0]] = $a[1];
}
printf(“%s”, $stat[$var]);
mysql_close();
?>

Data Input Method

To graph the data we need an input method

  1. Click – Data Input Methods (left hand menu)

To graph Queries

  1. Click – Add (top right hand corner)
  2. Should now show “Data Input Methods [new]“
  3. Set: Name -> Mysql – “Queries”
  4. Set: Input Type -> Query/Command
  5. Set: Input String -> “/usr/local/bin/cacti-mysqlstats.php Queries”
  6. Click – Create
  7. Click Output Fields -> Add
  8. Set: Field [Output] -> “queries”
  9. Set: Friendly Name -> “Queries”
  10. Click – Create
  11. Click – Save

Data Templates

  1. Click – Data Templates

To graph Queries

  1. Click – Add (top right hand corner)
  2. Set: Data Templates [new] -> “Mysql – Queries”
  3. Tick: Data Source “Use Per-Data Source Value (Ignore this Value)”
  4. Set: Data Input Method -> Mysql – Queries
Tags: cacti, graph, mysql, php, statistics
Last update:
2014-12-16 21:34
Author:
Chris Burton
Revision:
1.0
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.