Atom Feed
Comments Atom Feed

Similar Articles

2012-10-16 20:07
iostat on Cacti via SNMP
2012-10-16 19:23
vmstat on Cacti via SNMP
2009-10-31 14:46
SMART stats on Cacti (via SNMP)
2009-10-31 11:03
Linux (Debian, Ubuntu) SNMP basics
2013-03-24 15:44
fail2ban on Cacti via SNMP

Recent Articles

2019-07-28 16:35
git http with Nginx via Flask wsgi application (git4nginx)
2018-05-15 16:48
Raspberry Pi Camera, IR Lights and more
2017-04-23 14:21
Raspberry Pi SD Card Test
2017-04-07 10:54
DNS Firewall (blackhole malicious, like Pi-hole) with bind9
2017-03-28 13:07
Kubernetes to learn Part 4

Glen Pitt-Pladdy :: Blog

MySQL Performance Graphs on Cacti via SNMP

I regularly end up doing work with MySQL and as I regularly point out in other articles, tuning can give massive performance improvements if done right. The first thing is to when tuning is understand what is going on and where the bottlenecks are. Just running on a faster box is all well and good but it may not actually lead to in proportionate performance increases if the bottlenecks lie elsewhere.

These graphs are the product of some tuning work I've been doing over the past few months and are what I use for basic tuning and looking at what impact changes to queries have.

Tuning wisely

There is an awful lot of stuff on the web about measuring miss ratios for things query caches and blindly increasing the size of them if the ratios are not favourable. There are circumstances when this approach is valid, but care is needed when tuning to dig further and understand why miss ratios may be high.

A typical example may be when the bulk of queries are simply not cacheable (eg. the same query doesn't repeat or always will return something different). What happens is that caches fill up with non-cacheable queries leaving less space for queries that are cacheable and at the same time introducing overheads of checking caches which are no use. Possible solutions would be to redesign queries (if possible) to be more cacheable, or just explicitly stop trying to cache a query that can be cached:

SELECT SQL_NO_CACHE * WHERE parameter = 'random stuff' AND anotherparameter = 'more random stuff';

That can give you the performance boosts you may be looking for where just throwing in some more cache memory may have negliagable effect.

This is why hit/miss ratio graphs here have a label saying things like "lower is better... sometimes" as lower may only be better under specific circumstances. They can be very misleading if you don't understand why they are not showing optimum results.

One thing that is often worth checking is memory usage. In many cases caches may have high miss ratios and still have plenty of free space in the cache. This is a sure sign that heaping more cache space on is not going to be of any use at all.

To be useful, these graphs need to be used wisely and with understanding of what is actually happening.

Getting it working

Like with other templates I create, this one relies on SNMP to shift the data. This is a convenient, simple one-stop way of shifting data (with encryption if needed) into Cacti or other monitoring systems and allows one method to be used for all your monitoring irrespective of being local, over the LAN or in a remote locations across the planet. There are already plenty of other templates that run as local scripts or via ssh.

This one is simply an extension script for snmpd that grabs (and caches) the data when called. Assuming you put this extension script in /etc/snmp and have a cache directory for my extensions of /var/local/snmp/cache (as described in previous articles), you can add the following to your /etc/snmp/snmpd.conf file and then restart snmpd:

extend mysqlqcache /etc/snmp/mysql-stats /var/local/snmp/cache/mysql _qcache_miss_ratio _qcache_hit_ratio qcache_hits qcache_inserts qcache_total_blocks qcache_free_blocks _qcache_used_blocks query_cache_size qcache_free_memory _qcache_used_memory qcache_queries_in_cache
extend mysqlbufferpool /etc/snmp/mysql-stats /var/local/snmp/cache/mysql _innodb_buffer_pool_miss_ratio _innodb_buffer_pool_hit_ratio innodb_buffer_pool_reads innodb_buffer_pool_read_requests innodb_buffer_pool_write_requests
extend mysqlbufferpoolstat /etc/snmp/mysql-stats /var/local/snmp/cache/mysql innodb_buffer_pool_pages_total innodb_buffer_pool_pages_data innodb_buffer_pool_pages_free innodb_buffer_pool_pages_dirty innodb_buffer_pool_pages_misc
extend mysqlinnodbrows /etc/snmp/mysql-stats /var/local/snmp/cache/mysql innodb_rows_read innodb_rows_deleted innodb_rows_updated innodb_rows_inserted
extend mysqlkeycache /etc/snmp/mysql-stats /var/local/snmp/cache/mysql _key_miss_ratio _key_hit_ratio key_reads key_read_requests
extend mysqlcomm /etc/snmp/mysql-stats /var/local/snmp/cache/mysql questions com_select com_delete com_insert com_update com_replace com_load com_delete_multi com_insert_select com_update_multi com_replace_select
extend mysqlsort /etc/snmp/mysql-stats /var/local/snmp/cache/mysql sort_rows sort_range sort_merge_passes sort_scan
extend mysqlselects /etc/snmp/mysql-stats /var/local/snmp/cache/mysql select_full_join select_full_range_join select_range select_range_check select_scan

The first argument for mysql-stats is the cache file, and from there it simply picks up and outputs the variables specified on the remainder of the line.

I will add to this as myself (or perhaps others) need more things graphed.

In order to read all these variables from MySQL we need credentials. These are stored in /etc/snmpd/mysql-stats.conf by default and you can modify the script if you need to change that. Typically this would have something along the lines of:

data_source: DBI:mysql::localhost
username: monitor
password: secret

Make sure the permissions are set right on this file - we need the snmpd user to be able to read it by nobody else.

$ ls -l /etc/snmp/mysql-stats.conf
-rw-r----- 1 snmp snmp 83 Dec 27 21:05 /etc/snmp/mysql-stats.conf

Log into MySQL and execute the following (changing "secret" as appropriate) to give the script permission to monitor:

CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'secret';
GRANT PROCESS ON *.* TO 'monitor'@'localhost';

More privileges may be needed in the longer term as more monitoring is added, but that's enough to monitor a basic single node setup and it's always best to give as little privileges as possible.

Download: MySQL monitoring on Cacti is on GitHub

Install the Cacti template cacti_host_template_mysql.xml in the usual way and all going well graphs should start appearing. The usual diagnostics apply if data doesn't appear.

The graphs

MySQL Buffer Pool Miss Ratio

MySQL Buffer Pool Page Status

MySQL Buffer Pool Reads / Read Requests

MySQL Buffer Pool Write Requests

MySQL Commands

MySQL MyISAMM Key Cache Miss Ratio

MySQL MyISAMM Key Cache Reads / Read Requests

MySQL qcache Block Usage

MySQL qcache Hits / Inserts

MySQL qcache Miss Ratio

MySQL qcache Queries in Cache

MySQL qcache Size Usage

MySQL InnoDB Row Operations


MySQL Sorts

MySQL Sorts Rows


Eric O Image  2012-05-30 05:00 :: Eric O

Can this work with a mysql server that is not on localhost?   I tried modifying the line


but have had no luck....assuming everything else is right, is this even possible?   Thank you!

These tutorials are awesome, by the way!

Glen Pitt-Pladdy Image  2012-05-30 06:32 :: Glen Pitt-Pladdy

Glad you find these useful - that's the reason for publishing them!

Technically it should work, but I would suggest making sure you can connect with the "mysql" command line tool first. You will also need to set the user passwords to match the host they are coming from and ensure that MySQL is listening for inbound connections (in many cases it only listens on the local loopback address by default).

As it would be a good thing to be monitoring load, memory, cpu, disk, network etc. as well, it would make sense to be running snmpd on the MySQL box and so there would normally be no need to do anything other than monitoring localhost.

voytek Image  2012-07-20 06:33 :: voytek


many thanks for your treasure trove cacti toolbox!!!

made it easy, really appreciated,

Artem Image  2015-01-09 14:48 :: Artem

dont work!!

1/09/2015 04:40:04 PM - CMDPHP: Poller[0] Host[8] DS[75] WARNING: Result from SNMP not valid. Partial Result: DBI connect(mysql:lo
01/09/2015 04:40:04 PM - CMDPHP: Poller[0] Host[8] DS[74] WARNING: Result from SNMP not valid. Partial Result: U
01/09/2015 04:40:04 PM - CMDPHP: Poller[0] Host[8] DS[73] WARNING: Result from SNMP not valid. Partial Result: Cant call method pre

Glen Pitt-Pladdy Image  2015-01-09 15:37 :: Glen Pitt-Pladdy

Try running the snmpd extension script directly (as same user as snmpd runs as) to see the full error message. I would guess from what I can see that there is a problem with the connection. This could be something like default port/socket configured differently on your system or username/password mismatch between what you have set in the script and the database. Either way the full error message should give you more information on why it is not connecting.

Urs Wirthmueller Image  2020-05-04 14:57 :: Urs Wirthmueller

I got the following error:

Use of uninitialized value $fields[1] in pattern match (m//) at mysql-stats line 148.
FATAL - can't rename "/etc/snmp/mysql_cache.TMP" to "/etc/snmp/mysql_cache": Is a directory

Any help to this?

Thank you

Glen Pitt-Pladdy Image  2020-05-14 15:37 :: Glen Pitt-Pladdy

This seems a little strange since in the current version there is no regex on line 148. Can you tell me which version (git ref) you are using?

Voytek Eymont Image  2020-05-27 09:49 :: Voytek Eymont


where am I going wrong, it seems I'm not reading user/pwd ? or ?

# ./mysql-stats
usage: ./mysql-stats <path to cache file> <variable name> [more variable names.....]

# ./mysql-stats /var/local/snmp/cache/mysql sort_rows sort_range sort_merge_passes sort_scan
DBI connect(':localhost','monitor',...) failed: Access denied for user 'monitor'@'localhost' (using password: NO) at ./mysql-stats line 108.
Can't call method "prepare" on an undefined value at ./mysql-stats line 113.

# ls -al mysql*
-rwxr-xr-x 1 root root 9726 May 27 19:42 mysql-stats
-rw-r--r-- 1 root root  180 May 27 19:28 mysql-stats.conf
# mysql -u monitor -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 169990
Server version: 10.2.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Glen Pitt-Pladdy Image  2020-05-30 16:29 :: Glen Pitt-Pladdy

I suspect the clue is "using password: NO"

What is the commit ref of this version you are running? The current version in GitHub (years old now) has initialising a hash on line 108. That would not create this error. My guess is your version has the connect happening on line 108.

Voytek Eymont Image  2020-06-01 02:09 :: Voytek Eymont


I've d/l few days ago from GitHub, perhaps I picked wrong archive or something?
is this commit ref: '# version 20120426' ?

lines 108/114

my %status;
use DBI;
my $dbh = DBI->connect (
# head -n 114 mysql-stats

Glen Pitt-Pladdy Image  2020-06-06 13:30 :: Glen Pitt-Pladdy

That's weird since the error is complaining about connect failing on line 108, but the connect is from 110.

Experimenting here I find the "using password: NO" appears if there is no matching "password: secret" line in the config. What might be happening here is that this contains characters that are not matched on line 53. The credentials loading is a bit crude and does limit characters that may be used.

I've updated this today so see if that helps. It now picks up anything from the first non-whitespace character to the end of the line.

Voytek Eymont Image  2020-06-18 07:35 :: Voytek Eymont

thanks! I think I'm in:
# ./mysql-stats /var/local/snmp/cache/mysql sort_rows sort_range sort_merge_passes sort_scan

Note: Identity details will be stored in a cookie. Posts may not appear immediately