One of the plugins has problems printing undef variable:

Use of uninitialized value in printf at /etc/munin/plugins/mysql_replication line 850.

It seems that the $data->{seconds_behind_master} is not set when the MySQL is not running as SLAVE. Later on the plugin attempts to print this variable. This causes an unnecessary error message to be logged by Munin. I've fixed it with a patch, but I'm not sure if it's the right approach:

diff --git a/mysql_ b/mysql_
index 11edddd..37c8833 100755
--- a/mysql_
+++ b/mysql_
@@ -973,6 +973,11 @@ sub update_master {
 sub update_slave {
     my ($dbh) = @_;

+    # set to empty value if not defined, because we'll need to
+    # print it later
+    $data->{seconds_behind_master} = ''
+        unless defined($data->{seconds_behind_master});
+
     my $sth = $dbh->prepare('SHOW SLAVE STATUS');
     $sth->execute();
     my $row = $sth->fetchrow_hashref();

It just makes sure that the $data->{seconds_behind_master} is set to an empty value before leaving subroutine. Just a small fix for a small annoyance.

Munin (Debian's Perl module to be more specific) complains about unknown 'mysql_innodb_bpool.graph_base' attribute. It seems that using 'graph_args' to set the base of graphs is more to its liking. This patch should fix it:

diff --git a/mysql_ b/mysql_
index 53edc3d..8914e73 100755
--- a/mysql_
+++ b/mysql_
@@ -286,7 +286,7 @@ $graphs{innodb_bpool} = {
         global_attrs => {
             title  => 'InnoDB Buffer Pool',
             vlabel => 'pages',
-            base   => '1024',
+            args   => '--base 1024',
         },
         data_source_attrs => {
             draw => 'LINE2',

Parses SHOW ENGINE INNODB STATUS

In the InnoDB Buffer Pool graph the free pages line is behind the database pages filled graph, which makes it impossible to see all but the last measurement of free pages, if database pages are larger then free pages.

-- 'SHOW STATUS',

-- 'SHOW VARIABLES',

++ 'SHOW /*!41000 GLOBAL */ STATUS',

++ 'SHOW /*!41000 GLOBAL */ VARIABLES',

referencing this - #22

I am trying to get multi-instances working with the mysql munin plugin.

Setup as follows (password removed)

[mysql_4407_*]
env.mysqlconnection DBI:mysql:mysql;host=localhost;port=4407;mysql_socket=/var/lib/mysql-4407/mysql.sock
env.mysqluser root

[mysql_5507_*]
env.mysqlconnection DBI:mysql:mysql;host=localhost;port=5507;mysql_socket=/var/lib/mysql-5507/mysql.sock
env.mysqluser root

[mysql_6607_*]
env.mysqlconnection DBI:mysql:mysql;host=localhost;port=6607;mysql_socket=/var/lib/mysql-6607/mysql.sock
env.mysqluser root

I then added symlinks in the plugins dir.

lrwxrwxrwx. 1 root root 30 May 1 16:58 /etc/munin/plugins/mysql -> /usr/share/munin/plugins/mysql
lrwxrwxrwx. 1 root root 30 May 2 13:03 /etc/munin/plugins/mysql_4407 -> /usr/share/munin/plugins/mysql
lrwxrwxrwx. 1 root root 30 May 2 13:03 /etc/munin/plugins/mysql_5507 -> /usr/share/munin/plugins/mysql
lrwxrwxrwx. 1 root root 30 May 2 13:03 /etc/munin/plugins/mysql_6607 -> /usr/share/munin/plugins/mysql

my munin-server sees all the instances... and creates "space" for them all but after 24 hours no graphs are being rendered. I have all the headers and boxes with question marks.

I can query from telnet from my server;

munin node at replicant1.tor.fmpub.net

fetch mysql_4407
multigraph bin_relay_log-4407
Binlog_cache_disk_use.value 0
Binlog_cache_use.value 0
ma_binlog_size.value 0
relay_log_space.value 305286517
multigraph commands-4407
Com_replace_select.value 0
Com_update_multi.value 0
Com_insert_select.value 114
Com_delete_multi.value 0
Com_load.value 0
Com_delete.value 141103
Com_replace.value 0
Com_update.value 6579

Could you verify that I have things setup properly and perhaps give me some advice on how to fix the graphing?

This depends on which queries needs to be executed to gather the statistics. For some queries USAGE is enough, others requires SUPER.

I have configured munin-mysql version_0.3.1. Ran the 'make install' script and munin-node service got restarted without any issues. However, after checking sometime the mysql2 graph boxes are loading but not data inside the graphs. Using Percona 5.5.13-55.Any help on this will be greatly appreciated.

Can't believe I haven't seen it before, but there's a bcdu=nlog_cache_disk_use where there should be a bcdu=Binlog_cache_disk_use in the order line.

I'm getting this logged in munin-update.log from several servers:

2011/06/24 09:00:09 [WARNING] Service innodb_insert_buf on www.example.com/192.168.0.10:4949 returned no data for label ib_ibuf_merged_rec
2011/06/24 09:00:09 [WARNING] Service innodb_insert_buf on www.example.com/192.168.0.10:4949 returned no data for label ib_ibuf_inserts
2011/06/24 09:00:09 [WARNING] Service innodb_insert_buf on www.example.com/192.168.0.10:4949 returned no data for label ib_ibuf_merges
2011/06/24 09:00:09 [WARNING] Service caches on www.example.com/192.168.0.10:4949 returned no data for label bcu
2011/06/24 09:00:09 [WARNING] Service caches on www.example.com/192.168.0.10:4949 returned no data for label bl_cache_eff
2011/06/24 09:00:09 [WARNING] Service caches on www.example.com/192.168.0.10:4949 returned no data for label qcache_eff
2011/06/24 09:00:09 [WARNING] Service caches on www.example.com/192.168.0.10:4949 returned no data for label krrd
2011/06/24 09:00:09 [WARNING] Service caches on www.example.com/192.168.0.10:4949 returned no data for label key_cache_eff
2011/06/24 09:00:09 [WARNING] Service caches on www.example.com/192.168.0.10:4949 returned no data for label bcdu
2011/06/24 09:00:09 [WARNING] Service caches on www.example.com/192.168.0.10:4949 returned no data for label qh
2011/06/24 09:00:09 [WARNING] Service caches on www.example.com/192.168.0.10:4949 returned no data for label cs
2011/06/24 09:00:09 [WARNING] Service caches on www.example.com/192.168.0.10:4949 returned no data for label krd

I'm also getting these, whose names seem to overlap with the above:

2011/06/24 09:10:24 Missing required attribute 'label' for data source 'Com_select' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'bcu' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'Key_read_requests' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'krrd' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'Binlog_cache_disk_use' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'Binlog_cache_use' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'Key_reads' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'bcdu' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'qh' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'cs' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'krd' in service caches on db2.example.net/192.168.0.11:4949
2011/06/24 09:10:24 Missing required attribute 'label' for data source 'Qcache_hits' in service caches on db2.example.net/192.168.0.11:4949

Though there seem to be quite a few of these, the only graphs that are not working are query cache and cache efficiency.
I'm on Percona Server 5.5.12 - Is this likely to be another thing down to differences in Percona server output?

It is preferable to have as few dependencies as possible. Use a different cache mechanism?

• File
• Low level shared memory

Hi,
There is a broken link in the README file:
"

• Bugs should be reported to the issue tracker on github
  <http://github.com/kjellm/munin-mysql/issues>.
  "

The > should be removed.

It would be nice to graph more than one MySQL instance running on the same host. Right now it seems that the plugin can handle only one 'env.mysqlconnection' so it's not possible to easily specify more databases. I've made a patch that should allow multiple instances:

diff --git a/mysql_ b/mysql_
index 414cba2..f1ffc57 100755
--- a/mysql_
+++ b/mysql_
@@ -141,6 +141,16 @@ my %cache_options = (
     'default_expires_in' => 60,
 );

+my $instance = basename($0);
+if($instance =~ /^mysql_([0-9]+)_/) {
+    # get instance name
+    $instance = "-".substr($instance, $-[1], $+[1] - $-[1]);
+    $cache_options{'namespace'} = "munin_mysql".$instance;
+}
+else {
+    $instance = "";
+}
+
 my $shared_memory_cache = Cache::SharedMemoryCache->new(\%cache_options)
     or die("Couldn't instantiate SharedMemoryCache");

@@ -728,7 +738,10 @@ our $data; # Was 'my'. Changed to 'our' to facilitate testing.


 sub main {
-    my $graph = substr(basename($0), length('mysql_'));
+    my $graph = basename($0);
+    # remove prefix and instance number from command name
+    $graph =~ s/^mysql_([0-9]+_)?//;
+
     my $command = $ARGV[0] || 'show';

     my %command_map = (
@@ -744,7 +757,7 @@ sub main {
     die "Missing dependency Cache::Cache"
         unless $has_cache || $command eq 'autoconf';

-    return $command_map{$command}->($graph);
+    return $command_map{$command}->($graph, $instance);
 }


@@ -806,6 +819,7 @@ sub suggest {

 sub config {
     my $graph_name = shift;
+    my $category_suffix = shift;

     # In MySQL 5.1 (and probably erlier versions as well) status
     # variables are unique when looking at the last 19 characters.
@@ -852,7 +866,7 @@ sub config {
     while (my ($k, $v) = each %conf) {
         print "graph_$k $v\n";
     }
-    print "graph_category mysql2\n";
+    print "graph_category mysql2$category_suffix\n";

     my $i = 0;
     for my $ds (@{$graph->{data_sources}}) {

This should not change plugin's standard behaviour. However if the plugin is symlinked as:

mysq_$instance_$graph

where $instance is a number and $graph is just a standard graph name one can set different environment variables in configuration, e.g.:

[mysql_3306_*]
env.mysqlconnection DBI:mysql:mysql;host=localhost;port=3306
env.mysqluser root

[mysql_3307_*]
env.mysqlconnection DBI:mysql:mysql;host=localhost;port=3307
env.mysqluser root

The plugin will strip the 'mysql_' prefix along with any instance number. It will append the number to category name to distinguish graphs from different instances. Cache namespace also will be unique.

This is just an ugly hack, but maybe someone will find it useful.

I get this error:

# munin-run mysql_select_types
Unknown section: Main thread process no. 19271, id 140295836460800, state: sleeping at /etc/munin/plugins/mysql_select_types line 1091.

And I think this is the relevant section of 'show innodb status'

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 85FC0E68
Read view up limit trx id 85FC0E68
Read view low limit trx id 85FC0E68
Read view individually stored trx ids:
-----------------
Main thread process no. 19271, id 140295836460800, state: flushing log
Number of rows inserted 1361914, updated 5487, deleted 1037040, read 1586995
1.39 inserts/s, 0.65 updates/s, 0.00 deletes/s, 1.06 reads/s
------------

I use mysql 5.5.18 from percona.

A graph displaying the number of joins without indexes would be nice

I'm currently at a customer that have a lot of mysql-databases, and it would be nice to have a graph that showed the uptime for mysql-database.

• http://innotop.sourceforge.net/innotop.html#item_d_3a_innodb_deadlocks
• http://bugs.mysql.com/bug.php?id=19825
• Use some values from SHOW STATUS

Is it possible to configure this plugin to make a single graph or a set of graphs available to munin rather than all of them? The "mysql_" plugin that ships with munin allows you to make symlinks to the plugin source with the name of the graph in the symlink (ie. "mysql_qcache"). Is this functionality not available? Some of the graphs have no value to me and just consume munin processing/graphing time.

The InnoDB engine output parser fails to grab Percona server output due to this section:

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 8AF2A8CF
Read view up limit trx id 8AF2A8CE
Read view low limit trx id 8AF2A8CF
Read view individually stored trx ids:
Read view trx id 8AF2A8CE
-----------------
Main thread process no. 3862, id 1440885056, state: sleeping
Number of rows inserted 676950, updated 975121, deleted 14266, read 1251881
54.32 inserts/s, 159.28 updates/s, 1.33 deletes/s, 160.61 reads/s

The "match_dashes" function finds the end of the "OLDEST VIEW" bit, instead of the start of the next section.

Hi,
I have a customer who is using your graph templates and I noticed that in the graph the 'Uncheckpointed bytes' is often higher than the InnoDB Log size, which to me is wrong.

                                         $_[0]->{ib_log_flush} - $_[0]->{ib_log_checkpoint}

I'm not sure where those two numbers come from, but to me 'Uncheckpointed bytes' should really just be the equivalent of the 'Checkpoint age' in SHOW ENGINE INNODB STATUS, no?

Some graphs have no vlabel defined

Monitoring Amazon RDS instances with Munin and your plugin poses 2 issues:

1. you don't get SUPER or REPLICATION CLIENT privileges in RDS
2. you can't run munin-client on the RDS instance

I've created a patch for your excellent munin plugin that overcomes both of these limitations. It uses the creation of 2 new environment variables:

mysqlhostname - used ONLY for the config "hostname example.com" output, this doesn't obviate the requirement to put a hostname in the dsn string. This allows whatever node is running this client to present separate nodes to the Munin server. There's probably a better way to do this (one that allows a single workstation to monitor multiple RDS instances) but this was the quickest way to achieve a single RDS instance.

mysqlaws - a yes or no value that defaults to no. If set to yes, the update_master and update_slave subroutines are short-circuited.

Here's the diff against the latest version:

https://gist.github.com/825490

The plugin suddenly stopped working:

~ # munin-run mysql_innodb_insert_buf_size
Output from SHOW ENGINE INNDOB STATUS was truncated. This happens if the output of SEIS exceeds 64KB. Several of the InnoDB graphs might be affected by this. at /etc/munin/plugins/mysql_innodb_insert_buf_size line 1578.
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql_innodb_insert_buf_size line 1099.
ib_ibuf_seg_size.value
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql_innodb_insert_buf_size line 1099.
ib_ibuf_size.value
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql_innodb_insert_buf_size line 1099.
ib_ibuf_free_len.value

So as I understand it, the DBI driver has a 64k limit. But what do I do to fix the error? Can I just not have innodb stats anymore?

Hi,

here is a patch against HEAD adding 2 new graphs:

InnoDB Checkpoint Age which draws 2 things:

• total size of InnoDB log files
• amount of uncheckpointed data[1]

InnoDB History List which draws the amount of unpurged transactions[2]

[1] http://dev.mysql.com/doc/refman/5.0/en/innodb-checkpoints.html

[2] http://dev.mysql.com/doc/refman/5.0/en/innodb-multi-versioning.html

Cheers,

$ diff -u mysql_.orig mysql_ 
--- mysql_.orig 2010-05-27 15:46:50.000000000 +0200
+++ mysql_      2010-05-27 15:53:06.000000000 +0200
@@ -351,6 +351,50 @@
     ],
 };
 
+
+#---------------------------------------------------------------------
+
+$graphs{innodb_checkpoint_age} = {
+    config => {
+        global_attrs => {
+            title  => 'InnoDB Checkpoint Age',
+            vlabel => 'Bytes',
+            args   => '--base 1024 -l 0',
+        },
+        data_source_attrs => {
+           draw  => 'AREA',
+            type  => 'GAUGE',
+        },
+    },
+    data_sources => [
+        {name => 'innodb_log_size',  label  => 'InnoDB log size',
+                                     colour => 'cdcfc4',
+                                     info   => 'The size in bytes of InnoDB log space.'},
+        {name => 'ib_log_chkpt_age', label => 'Uncheckpointed bytes',
+                                      colour => 'ffd660',
+                                     info  => 'The age in bytes of InnoDB checkpoint.'},
+    ],
+};
+
+#---------------------------------------------------------------------
+
+$graphs{innodb_history_length} = {
+    config => {
+        global_attrs => {
+            title  => 'InnoDB History List',
+            vlabel => 'Transactions',
+        },
+        data_source_attrs => {
+            draw  => 'LINE1',
+            type  => 'GAUGE',
+        },
+    },
+    data_sources => [
+        {name => 'ib_tnx_hist', label => 'History list length',
+                                info  => 'Number of unpurged transactions in undo space.'},
+    ],
+};
+
 #---------------------------------------------------------------------
 
 $graphs{innodb_insert_buf} = {
@@ -1090,6 +1134,7 @@
 
     my $seconds_in_a_day = 86400;
     $data->{Uptime_days} = floor($data->{Uptime} / $seconds_in_a_day);
+    $data->{innodb_log_size} = $data->{innodb_log_file_size} * $data->{innodb_log_files_in_group};
 }
 
 
@@ -1112,6 +1157,9 @@
     $sth->finish();
 
     parse_innodb_status($status);
+
+    my $ib_log_checkpoint_age = $data->{ib_log_flush} - $data->{ib_log_checkpoint};
+    $data->{ib_log_chkpt_age} = $ib_log_checkpoint_age;
 }
 
 
@@ -1418,6 +1466,10 @@
                 $data->{ib_log_flush} = innodb_bigint($1, $2);
                 return 1;
             };
+            m/\GLast checkpoint at\s+$innodb_bigint_rx\n/gc && do {
+                $data->{ib_log_checkpoint} = innodb_bigint($1, $2);
+                return 1;
+            };
             m/\G(\d+) log i\/o's done.*\n/gc && do {
                 $data->{ib_io_log} = $1;
                 return 1;

it would be nice to have a description of each field name.

This can be done by adding a

.info

directive.

Just filed a bug repot at munin: http://munin-monitoring.org/ticket/1123 but thought I would post here also so others don't get stumped like me.

Using such plugins like: https://github.com/kjellm/munin-mysql/
allows you to monitor multiple mysql instances. Your plugin name looks like: mysql_foo
and the graph it creates looks like bin_relay_log-foo. However when the scale gets attached
to that it will be named bin_relay_log-foo-day.png. Munin builds the rrds just fine but the fastcgi
graph fails to parse the name and fails with an error of "Weird scale foo-day". This patch fixes it.
I believe the same thing should be applied to muning-cgi-graph as well.

# diff -u /var/www/html/munin/cgi/munin-fastcgi-graph.fcgi.orig    /var/www/html/munin/cgi/munin-fastcgi-graph.fcgi
--- /var/www/html/munin/cgi/munin-fastcgi-graph.fcgi.orig   2011-08-23 10:07:11.202004345 -0700
+++ /var/www/html/munin/cgi/munin-fastcgi-graph.fcgi    2011-08-23 10:25:15.118379059 -0700
@@ -72,7 +72,7 @@
     my $path = $ENV{PATH_INFO} || "";
     $path =~ s/^\///;
     ($dom, $host, $serv) = split /\//, $path;
-    ($serv, $scale) = split /-/, $serv, 2;
+    ($serv, $scale) = split /-(?!.*-)/, $serv, 2;
     $scale =~ s/\.png$//;

     if (! &verify_parameters ($dom, $host, $serv, $scale)) {

The clean_fieldname function refers to the munin docs about how variable names must be 19 chars or less, and reduces names to this length. The docs now say that this is no longer the case as of munin 1.2, which is pretty old now. Full-length names are much more readable.

When running munin-run mysql, I see these errors in the output. There are more than these, but they're all the same. I'm running Percona Server 5.5.11.

# munin-run mysql              
multigraph bin_relay_log
Binlog_cache_disk_use.value 0
Binlog_cache_use.value 0
ma_binlog_size.value 907005182
relay_log_space.value 0
multigraph caches
Key_reads.value 4064
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql line 301.
krd.value 
Key_read_requests.value 238120
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql line 301.
krrd.value 
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql line 301.
key_cache_eff.value 
Qcache_hits.value 9423
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql line 301.
qh.value 
Com_select.value 14635
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql line 301.
cs.value 
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql line 301.
qcache_eff.value 
Binlog_cache_disk_use.value 0
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql line 301.
bcdu.value 
Binlog_cache_use.value 0
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql line 301.
bcu.value 
Use of uninitialized value $value in printf at /etc/munin/plugins/mysql line 301.
bl_cache_eff.value 
multigraph commands
Com_replace_select.value 0
Com_update_multi.value 0
Com_insert_select.value 16
Com_delete_multi.value 0
Com_load.value 0
Com_delete.value 27
Com_replace.value 12
Com_update.value 402
Com_insert.value 141
Com_select.value 14635
...

there is no way to add path to socket placed in other location than default...

The Cacti 'Query Cache Memory Usage' graph includes Qcache_total_blocks and cache_free_blocks in addition to query_cache_size Qcache_free_memory. Can't see how these fit in the same graph since they have different scale (blocks vs bytes). Make a separate graph and include Qcache_lowmem_prunes?

It seems that the mysql plugin for "uptime" conflicts with the built-in munin (version 1.4.6) plugin for system uptime. Sure, this is a lame thing about munin, but it could probably be avoided.

I've made a graph module for use with Galera clusters (percona xtradb cluster, etc) which I can contribute to the repository.

It's not quite done yet however; when I install it on a server that isn't a cluster member the graphs are still generated (but empty). Is there a way that I can check if the server has certain config vars before a graph is drawn?

There is a new section called 'BACKGROUND THREAD' as of InnoDB plugin 1.04. Parsing fails if this section is present.

Use of uninitialized value in printf at /etc/munin/plugins/mysql_innodb_insert_buf line 1099.

Happens with mysql_innodb_insert_buf, mysql_innodb_insert_buf_size and mysql_innodb_io_pend using MySQL 5.5.8.

Here are the relevant sections of the SHOW ENGINE INNODB STATUS\G output

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 1 [0, 1, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
5528380 OS file reads, 19874402 OS file writes, 615327 OS fsyncs
19.95 reads/s, 16384 avg bytes/read, 30.74 writes/s, 3.22 fsyncs/s


-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 206, free list len 23078, seg size 23285, 202193 merges
merged operations:
 insert 496342, delete mark 22556, delete 3840
discarded operations:
 insert 1686, delete mark 2, delete 0
Hash table size 57374437, node heap has 54332 buffer(s)
87022.67 hash searches/s, 7098.93 non-hash searches/s

And also take a look at http://www.xaprb.com/blog/2007/03/08/what-to-do-when-mysql-says-skip-innodb-is-defined/

Hello again :)

here is another patch against HEAD adding a graph InnoDB Insert Buffer Size which draws 3 things:

• space allocated for buffer segment
• free space
• unmerged space

All in InnoDB pages. I based on information from [1] and [2].

Cheers,

[1] http://dev.mysql.com/doc/refman/5.0/en/innodb-insert-buffering.html

[2] http://www.mysqlperformanceblog.com/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/

$ diff -u mysql_.orig mysql_
--- mysql_.orig 2010-05-28 14:50:37.000000000 +0200
+++ mysql_      2010-05-28 14:56:17.000000000 +0200
@@ -373,6 +373,33 @@
   
 #---------------------------------------------------------------------
 
+$graphs{innodb_insert_buf_size} = {
+    config => {
+        global_attrs => {
+            title  => 'InnoDB Insert Buffer Size',
+            vlabel => 'Pages',
+            args   => '-l 0',
+        },
+        data_source_attrs => {
+            draw  => 'LINE1',
+            type  => 'GAUGE',
+        },
+    },
+    data_sources => [
+        {name => 'ib_ibuf_seg_size', label  => 'Segment size',
+                                     draw   => 'AREA',
+                                     colour => 'cdcfc4',
+                                     info   => 'Allocated size of insert buffer segment.'},
+        {name => 'ib_ibuf_size',     label  => 'Unmerged pages',
+                                     colour => '0022ff',
+                                     info   => 'Number of pages containing unmerged records.'},
+        {name => 'ib_ibuf_free_len', label  => 'Free pages',
+                                     info   => 'Number of pages which are free.'},
+    ],
+};
+
+#---------------------------------------------------------------------
+
 $graphs{innodb_io} = {
     config => {
         global_attrs => {
@@ -1402,6 +1429,12 @@
                 $data->{ib_ibuf_merges}     = $3;
                 return 1;
             };
+            m/\GIbuf: size (\d+), free list len (\d+), seg size (\d+),\n/gc && do {
+                $data->{ib_ibuf_size}     = $1;
+                $data->{ib_ibuf_free_len} = $2;
+                $data->{ib_ibuf_seg_size} = $3;
+                return 1;
+            };
         }
     );
 }

Looks to be a simple typo in munin-mysql/lib/Munin/MySQL/Graph/QueryCache.pm

Shouldn't it be cdef => 'Qcache_queries_in_cache,1024,/',?

it took me some time until i noticed that i need IPC::ShareLite for the plugin to work, so you should probably add it to the readme...

It would be nice to see a graph showing the Thread Cache Hit Rate

http://www.epigroove.com/posts/63/optimize_mysql_the_thread_cache

I've encountered a strange issue with Munin Perl module on Debian lenny. Graphs that have a '#' characters in their vlabels are causing the munin-html script to exit before doing any work. The error message for every vlabel with '#' inside is:

Malformed configuration line "hostgroup;host:mysql_replication.graph_vlabel". at /usr/share/perl5/Munin.pm line 326.

The munin-html script exits before doing any work. It probably chokes on reading the datafile. After removing every hash sign from vlabels it works like a charm.

There are two additional things we monitor using your plugin

• Key buffer usage
• Engine space usage

Key buffer monitoring draws three things:

• total key_buffer_size (as set in my.cnf)
• bytes currently used by key buffers (together with management memory structures) [1]
• bytes ever allocated to key buffers (this is high watermark indicating how much actual RAM is allocated to key buffers) [1]

Engine space usage shows how much space is used by each engine indexes and data. Currently, only MyISAM and InnoDB engines are included in a graph, but update_size_all method is written to support all engines, so the only thing needed to do when adding new engines is to change graphs{myisam_key_buffer}

[1] http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size

Patch against HEAD below.

diff --git a/mysql_ b/mysql_
index 26b68b1..e06c9fd 100755
--- a/mysql_
+++ b/mysql_
@@ -272,6 +272,32 @@ $graphs{connections} = {
 
 #---------------------------------------------------------------------
 
+$graphs{size_all} = {
+    config => {
+        global_attrs => {
+            title  => 'MySQL engines space usage summary',
+            vlabel => 'Bytes',
+       args   => '--base 1024 -l 0',
+        },
+        data_source_attrs => {
+            draw  => 'LINE2',
+       type  => 'GAUGE',
+        },
+    },
+    data_sources => [
+        {name => 'innodb_index', label => 'InnoDB index',
+                                 draw  => 'AREA'},
+        {name => 'innodb_data',  label => 'InnoDB data',
+                                 draw  => 'STACK'},
+        {name => 'myisam_index', label => 'MyISAM index',
+                                 draw  => 'STACK'},
+        {name => 'myisam_data',  label => 'MyISAM data',
+                                 draw  => 'STACK'},
+    ],
+};
+
+#---------------------------------------------------------------------
+
 $graphs{files_tables} = {
     config => {
         global_attrs => {
@@ -526,7 +552,34 @@ $graphs{myisam_indexes} = {
                                        info  => 'The number of physical writes of a key block to disk.'},
    ],
 };
-  
+
+#---------------------------------------------------------------------
+
+$graphs{myisam_key_buffer} = {
+    config => {
+        global_attrs => {
+            title  => 'MySQL MyISAM Key Buffers Usage',
+       args   => '--base 1024 -l 0',
+            vlabel => 'Bytes',
+            info   => 'MyISAM Key Buffer usage',
+        },
+        data_source_attrs => {
+       type  => 'GAUGE',
+        },
+    },
+    data_sources => [
+        {name => 'key_buffer_size',  label => 'Total',
+                                info  => 'Total memory for MyISAM key buffer',
+                                draw  => 'AREA'},
+        {name => 'key_buffer_used',  label => 'Used',
+                                info  => 'Memory used by MyISAM key buffer (for buffer blocks and administrative structures)',
+                                draw  => 'AREA'},
+        {name => 'key_buffer_alloc', label => 'Ever allocated', 
+                                info  => 'Memory ever allocated for MyISAM key buffer blocks (high watermark)',
+                                draw  => 'LINE2'},
+   ],
+};
+
 #---------------------------------------------------------------------
 
 $graphs{network_traffic} = {
@@ -1057,6 +1110,8 @@ sub update_data {
     update_master($dbh);
     update_slave($dbh);
     update_process_list($dbh);
+    update_size_all($dbh);
+    update_myisam_key_buffer($dbh);
 
     $shared_memory_cache->set('data', $data);
 }
@@ -1115,6 +1170,39 @@ sub update_innodb {
 }
 
 
+sub update_myisam_key_buffer
+{
+    my $key_buffer_used = $data->{key_buffer_size} - $data->{Key_blocks_unused} * $data->{key_cache_block_size};
+    my $key_buffer_alloc = $data->{Key_blocks_used} * $data->{key_cache_block_size};
+    $data->{key_buffer_used}  = $key_buffer_used;
+    $data->{key_buffer_alloc} = $key_buffer_alloc;
+}
+
+
+sub update_size_all
+{
+    my ($dbh) = @_;
+  
+    my $sth = $dbh->prepare('SELECT Engine, Data_length, Index_length FROM information_schema.TABLES WHERE table_schema not in ("mysql", "information_schema") and Engine not in ("BLACKHOLE", "FEDERATED", "EXAMPLE")');
+    $sth->execute();
+
+    my $engine;
+    my $edata;
+    my $eindex;
+
+    while ( my @row = $sth->fetchrow_array( ) )
+    {
+        $engine = lc($row[0]);
+        $eindex = $engine . "_index";
+        $edata  = $engine . "_data";
+        $data->{$edata} += $row[1];
+        $data->{$eindex} += $row[2];
+    }
+
+    $sth->finish();
+}
+
+
 sub update_master {
     my ($dbh) = @_;

I installed "munin-node" on a blank Ubuntu 12.04 machine with MySQL.
The munin-mysql plugin was not enabled by default (which is annoying).

After much searching, I found that the way to find why munin-mysql is not installed is to run:

/usr/share/munin/plugins/mysql_ autoconf

First it said "Missing dependency Cache::Cache". After some searching, I found I needed to do:

apt-get install libcache-cache-perl

(I have added this to the wiki).
The next problem was "Access denied for user 'root'@'localhost' (using password: NO))"
The Ubuntu install puts a plugin config file at "/etc/munin/plugin-conf.d/munin-node", with a "[mysql*]".

This config file has no comments hinting at what config value are available, which is unhelpful.

I found that whatever value I set for "env.mysqluser" or "env.mysqlpassword" in this file were completely ignored by munin-mysql.

In the end, I edited the "/usr/share/munin/plugins/mysql_" script file directly to make my username and password the default.

E.g. Don't suggest InnoDB graphs if InnoDB is not used on the system.

For example for mysql_connections:

Add Warning when using 80% of max_connections and critical for 90%