Menu
Index

Contact
LinkedIn
GitHub
Atom Feed
Comments Atom Feed



Tweet

Similar Articles

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

Recent Articles

23/04/2017 14:21
Raspberry Pi SD Card Test
07/04/2017 10:54
DNS Firewall (blackhole malicious, like Pi-hole) with bind9
28/03/2017 13:07
Kubernetes to learn Part 4
23/03/2017 16:09
Kubernetes to learn Part 3
21/03/2017 15:18
Kubernetes to learn Part 2

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 SELECT

MySQL Sorts

MySQL Sorts Rows

Comments:

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

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

data_source: DBI:mysql::fqdn.com  

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

These tutorials are awesome, by the way!
Eric

Glen Pitt-Pladdy Image  30/05/2012 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  20/07/2012 06:33 :: voytek

Glen,

many thanks for your treasure trove cacti toolbox!!!

made it easy, really appreciated,

Artem Image  09/01/2015 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  09/01/2015 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.




Are you human? (reduces spam)
Note: Identity details will be stored in a cookie. Posts may not appear immediately