Graph MySQL Stats

 

Background

This Cacti script/template set was originally written by Otto Berger. I took over as caretaker 3/2011, made a few patches and released it here. There are newer templates that add a lot of sugar, but I always loved this one because of it’s simplicity. Graph MySQL Stats comes with eight different graphs, which are described below. This set of graphs is designed to give a competent systems or database administrator a practical handle on what is going on inside of MySQL.

 

Download

Cacti Wiki

Cacti Forums

Versioned Code

 

Installation

Manual

In case of errors or to prevent them, you have to edit the “data input methods” manually through the web interface. For each MySQL-
input method you have to change the username/password. This is necessary when adding machines manually in the web interface.

The following files are required for installation

  1. Put the mysql_stats.php file inside the cacti/scripts/ directory
  1. Import the .xml-Files using the cacti webinterface

 

Scripted/Automated

To avoid errors and to specify custom username/password/ports, the best way to add graphs is with the cacti command line interface. The following is given as an example. With a few simple loops, installation can be completely automated. For more examples, look at the test rig code in the crunchtools repository: here This is what I use to do test installs.

Get the graph id values

php $cli/add_graphs.php --list-graph-templates | grep MySQL

Get the host id value

php $cli/add_graphs.php --list-hosts | grep HOSTNAME

Add the graphs

php $cli/add_graphs.php \
            --graph-template-id=$1 \
            --host-id=$2 \
            --graph-type=cg \
            --input-fields="username=$mysql_mystats_username password=$mysql_mystats_password"

 

Architecture

There are eight main graphs supported. Field from each graph are described below. Care has been taken to describe each graph for novice administrators of MySQL. A description of each variable can be found here: Variable Reference

 

MySQL – Command Statistics

These are the basic mysql queries being performed.

Test Command

php mysql_stats.php command localhost dbuser dbpass

 

Field Descriptions

  • Select: Incremented each time a select statement is executed which is not in the query cache
  • Change_DB: Incremented each time a use command is executed
  • Delete: Incremented each time a delete statement is executed
  • Insert: Incremented each time an insert statement is executed
  • Update: Incremented each time an update statement is executed

 

MySQL – Connections

Counter for number of connection attempts to the database

Test Command

php mysql_stats.php status localhost adbuser dbpass Connections

 

Field Descriptions

  • Connections: Incremented each time connection is attempted, failed or successful

 

MySQL – Handler Statistics

Internal statistics on how MySQL is selecting, updating, inserting, and modifying rows, tables, and indexes

Test Command

php mysql_stats.php handler localhost adbuser dbpass

 

Field Descriptions

  • Read Rnd Next: Incremented when a request to read the next row in the table is received. Often incremented because of a full table scan. This can indicate that you need to index one of your heavily used tables
  • Read First: Incremented when the first entry in an index has been read. This can indicate a large number of full scans of an indexed table, such as SELECT column FROM table.
  • Read Key: Incremented when a request to read a row based on a key has been received. A high value for this variable can indicate properly indexed tables.
  • Read Next: The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan.
  • Read Prev: The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY … DESC.
  • Read Rnd: The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.
  • Delete: The number of times that rows have been deleted from tables. Same as Command Statistic: delete field
  • Update: The number of requests to update a row in a table. Same as Command Statistic: update field
  • Write: The number of requests to insert a row in a table. Not the same as the Command Statistic: insert field. It is normal for this variable to constantly increment.

 

MySQL – QueryCache Statistics

Bytes used for the query cache

Test Command

php mysql_stats.php cache localhost dbuser dbpass

 

Field Descriptions

  • Used: Uses query_cache_size which is the number of bytes of memory allocated for caching query results. The default value is 0, which disables the query cache. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple. The query cache needs a minimum size of about 40KB to allocate its structures. If you set the value of query_cache_size too small, a warning will occur.
  • Available: Uses Qcache_free_memory which is the number of bytes of free memory for the query cache.

 

MySQL – QueryCache Hits

Detailed breakdown of events in the MySQL cache

Test Command

php mysql_stats.php qcache localhost dbuser dbpass

 

Field Descriptions

  • Cache hits: Number of query cache hits
  • Cache inserts: Number of queries added to the query cache
  • Low Memory Prunes: Number of queries that are removed from the query cache because of low memory
  • Queries Not Cached: Reports the number of queries which are not cached or not cacheable because. This can be controlled by both the client and the server
  • Queries in Cache: Number of queries saved in the cache. This is the number of data structures in the cache, not the amount of RAM used.

 

MySQL – Questions

The number of statements executed by the server.

Test Command

php mysql_stats.php status localhost dbuser dbpass Questions

 

Field Descriptions

  • Questions: The number of statements executed by the server. Only statements executed from clients, not stored programs.

 

MySQL – Single Statistics

This graph does not display any particular variable and is free to be used for any singe variable. This can be done by modifying the Internal Data Source Name within the Data Template

Test Command

php mysql_stats.php status localhost dbuser dbpass VARIABLE

 

Field Descriptions

 

MySQL – Thread Statistics

Test Command

php mysql_stats.php thread localhost dbuser dbpass

 

Field Descriptions

  • Connected: Number of open connections to the server
  • Running: Number of connections which are not sleeping, eg: running a query
  • Cached: Number of threads in the thread cache

 

MySQL – Traffic

Test Command

php mysql_stats.php traffic localhost dbuser dbpass

 

Field Descriptions

  • In: : Uses Bytes_received which is the number of bytes received from all clients
  • Out: : Uses Bytes_sent which is the number of bytes sent to all clients