Monitoring MySQL

Metrics

The following metrics are submitted to Nixstats.

max_used_connections
open_files
open_tables
qcache_free_blocks
qcache_free_memory
qcache_total_blocks
slave_open_temp_tables
threads_cached
threads_connected
threads_running
uptime
aborted_clients
aborted_connects
binlog_cache_disk_use
binlog_cache_use
bytes_received
bytes_sent
com_delete
com_delete_multi
com_insert
com_insert_select
com_load
com_replace
com_replace_select
com_select
com_update
com_update_multi
connections
created_tmp_disk_tables
created_tmp_files
created_tmp_tables
key_reads
key_read_requests
key_writes
key_write_requests
max_used_connections
open_files
open_tables
opened_tables
qcache_free_blocks
qcache_free_memory
qcache_hits
qcache_inserts
qcache_lowmem_prunes
qcache_not_cached
qcache_queries_in_cache
qcache_total_blocks
questions
select_full_join
select_full_range_join
select_range
select_range_check
select_scan
slave_open_temp_tables
slave_retried_transactions
slow_launch_threads
slow_queries
sort_range
sort_rows
sort_scan
table_locks_immediate
table_locks_waited
threads_cached
threads_connected
threads_created
threads_running

Dependencies

MySQL-python

Run apt-get install python-mysqldb on debian based distro's or yum install MySQL-python to install the required plugin. Alternatively you can run pip install MySQL-python .

If you're running python3 you have to run apt-get install python3-mysqldb or pip3 install mysqlclient (mysql-devel is required on CentOS yum install mysql-devel)

Create a mysql user

Create a mysql user that has access to the performance_schema database:

sudo mysql -e "CREATE USER 'nixstats'@'localhost' IDENTIFIED BY 'safeandsecurepassword';"
sudo mysql -e "GRANT PROCESS ON *.* TO 'nixstats'@'localhost';"
sudo mysql -e "GRANT SELECT ON performance_schema.* TO 'nixstats'@'localhost';"

In case you want to enable monitoring of a replica you will have to run the following query as well:

sudo mysql -e "grant replication client on *.* to 'nixstats'@'localhost';"

Configure the plugin

To enable the plugin edit the nixstats configuration file. This file is usually located at /etc/nixstats.ini.

Add the following settings to the end of the file.

[mysql]
enabled=yes
username=nixstats
password=safeandsecurepassword
host=127.0.0.1
database=performance_schema
port=3306
;socket=null

Test to see if the plugin is working by running nixstatsagent --test mysql. It should show the following metrics:

mysql:
{
    "aborted_clients": 0,
    "aborted_connects": 0,
    "binlog_cache_disk_use": 0,
    "binlog_cache_use": 0,
    "bytes_received": 0,
    "bytes_sent": 0,
    "com_delete": 0,
    "com_delete_multi": 0,
    "com_insert": 0,
    "com_insert_select": 0,
    "com_load": 0,
    "com_replace": 0,
    "com_replace_select": 0,
    "com_select": 0,
    "com_update": 0,
    "com_update_multi": 0,
    "connections": 0,
    "created_tmp_disk_tables": 0,
    "created_tmp_files": 0,
    "created_tmp_tables": 0,
    "key_read_requests": 0,
    "key_reads": 0,
    "key_write_requests": 0,
    "key_writes": 0,
    "max_used_connections": 2.0,
    "open_files": 6.0,
    "open_tables": 71.0,
    "opened_tables": 0,
    "qcache_free_blocks": 1.0,
    "qcache_free_memory": 67091584.0,
    "qcache_hits": 0,
    "qcache_inserts": 0,
    "qcache_lowmem_prunes": 0,
    "qcache_not_cached": 0,
    "qcache_queries_in_cache": 0,
    "qcache_total_blocks": 1.0,
    "questions": 0,
    "select_full_join": 0,
    "select_full_range_join": 0,
    "select_range": 0,
    "select_range_check": 0,
    "select_scan": 0,
    "slave_open_temp_tables": 0.0,
    "slow_launch_threads": 0,
    "slow_queries": 0,
    "sort_range": 0,
    "sort_rows": 0,
    "sort_scan": 0,
    "table_locks_immediate": 0,
    "table_locks_waited": 0,
    "threads_cached": 1.0,
    "threads_connected": 1.0,
    "threads_created": 0,
    "threads_running": 1.0,
    "uptime": 59949.0
}

Now you can restart the agent service nixstatsagent restart to start monitoring MySQL.

Updated on: 18/03/2020