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
Thank you!