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
- Put the mysql_stats.php file inside the cacti/scripts/ directory
- 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
- VARIABLE: Any variable the administrator would like to graph: Variable Reference
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