Monitoring MySQL

This article will help you get the Mysql plugin for sd-agent configured and returning metrics

Installing the mysql plugin package

Install the mysql plugin on Debian/Ubuntu:

sudo apt-get install sd-agent-mysql

Install the mysql plugin on RHEL/CentOS:

sudo yum install sd-agent-mysql

Read more about agent plugins.

Installing the MySQL / MariaDB plugin package

Install the MySQL / MariaDB plugin on Debian/Ubuntu:

sudo apt-get install sd-agent-mysql

Install the MySQL / MariaDB plugin on RHEL/CentOS:

sudo yum install sd-agent-mysql

Read more about agent plugins.

Configuring the agent to monitor MySQL / MariaDB

The steps for configuring MySQL and MariaDB are exactly the same - MariaDB is a drop-in replacement for MySQL. If you have a master-slave setup, ensure you give the database user the correct permissions (step 2).

1. Connect to the database server and create a monitoring user:

sudo mysql -e "CREATE USER 'serverdensity'@'localhost' IDENTIFIED BY 'supersecretpassword';"

2. Grant the correct permissions to the user:

sudo mysql -e "GRANT PROCESS ON *.* TO 'serverdensity'@'localhost';"
sudo mysql -e "GRANT SELECT ON performance_schema.* TO 'serverdensity'@'localhost';"

3. (Optional) The above user does not require any extra privileges unless you wish to monitor replication, in which case it requires REPLICATION CLIENT privileges:

sudo mysql -e "GRANT REPLICATION CLIENT ON *.* to 'serverdensity'@'localhost';"

4. Configure /etc/sd-agent/conf.d/mysql.yaml

init_config:

instances:
  - server: localhost
    user: serverdensity
    pass: supersecretpassword
    sock: /path/to/socket

• If you want to connect via TCP on a non-standard port, change the server option to 127.0.0.1 and uncomment the port line and amend the port number. Note that you will need to grant access for your user to 127.0.0.1 instead of localhost
• If you do not know your socket location you can find it with the following command mysqladmin variables | grep socket

4. Restart the agent

sudo /etc/init.d/sd-agent restart

or

sudo systemctl restart sd-agent

Replication monitoring

To enable replication monitoring you need to add

options: # Optional
  replication: 1 

To your \{agentdir\}/conf.d/mysql.yaml configuration file. A full example is below:

 instances:
  - server: localhost
    user: my_username
    pass: my_password
    port: 3306             # Optional
    options:              # Optional
       replication: 1

If you have configured replication monitoring, you will see additional metrics:

• Slave_running
• Seconds_behind_master

An important metric is "seconds behind master" because as this shows how far behind the master the slave is. As this increases, the slave is getting increasingly behind.

You should create alerts for this metric so you can be notified when it starts getting too far behind. A good baseline value is 300 or 600 seconds i.e. 5 or 10 minutes. This allows for some variation e.g. networking issues but doesn't let it get too far behind.

Verifying the configuration
Execute info to verify the configuration with the following:

sudo /etc/init.d/sd-agent info 

or

/usr/share/python/sd-agent/agent.py info

If the agent has been configured correctly you'll see an output such as:

mysql
-----
  - instance #0 [OK]
  - Collected * metrics

You can also view the metrics returned with the following command:

sudo -u sd-agent /usr/share/python/sd-agent/agent.py check mysql

Configuring graphs

Click the name of your server from the Devices list in your Server Density account then go to the Metrics tab. Click the + Graph button on the right then choose the mysql metrics to display the graphs. The metrics will also be available to select when building dashboard graphs.

Monitored metrics

Metric	Values
mysql.galera.wsrep_cluster_size	/ Type: int
mysql.innodb.buffer_pool_dirty	/ Type: int
mysql.innodb.buffer_pool_free	byte / Type: int
mysql.innodb.buffer_pool_read_requests	/ Type: int
mysql.innodb.buffer_pool_reads	/ Type: int
mysql.innodb.buffer_pool_total	byte / Type: int
mysql.innodb.buffer_pool_used	byte / Type: int
mysql.innodb.buffer_pool_utilization	/ Type: float
mysql.innodb.current_row_locks The number of current row locks.	lock / None Type: float
mysql.innodb.data_reads	/ Type: int
mysql.innodb.data_writes	/ Type: int
mysql.innodb.mutex_os_waits	/ Type: int
mysql.innodb.mutex_spin_rounds	/ Type: int
mysql.innodb.mutex_spin_waits	/ Type: int
mysql.innodb.os_log_fsyncs	/ Type: int
mysql.innodb.row_lock_current_waits	/ Type: int
mysql.innodb.row_lock_time	millisecond / Type: int
mysql.innodb.row_lock_waits	/ Type: int
mysql.myisam.key_buffer_bytes_unflushed	/ Type: int
mysql.myisam.key_buffer_bytes_used	/ Type: int
mysql.myisam.key_buffer_size	/ Type: int
mysql.myisam.key_read_requests	/ Type: int
mysql.myisam.key_reads	/ Type: int
mysql.myisam.key_write_requests	/ Type: int
mysql.myisam.key_writes	/ Type: int
mysql.net.aborted_clients	/ Type: int
mysql.net.aborted_connects	/ Type: int
mysql.net.connections	/ Type: int
mysql.net.max_connections	/ Type: int
mysql.net.max_connections_available	connection / Type: float
mysql.performance.bytes_received	/ Type: int
mysql.performance.bytes_sent	/ Type: int
mysql.performance.com_delete	/ Type: int
mysql.performance.com_delete_multi	/ Type: int
mysql.performance.com_insert	/ Type: int
mysql.performance.com_insert_select	/ Type: int
mysql.performance.com_load	query / second Type: int
mysql.performance.com_replace	query / second Type: int
mysql.performance.com_replace_select	/ Type: int
mysql.performance.com_select	/ Type: int
mysql.performance.com_update	/ Type: int
mysql.performance.com_update_multi	/ Type: int
mysql.performance.cpu_time	percent / Type: int
mysql.performance.created_tmp_disk_tables	/ Type: int
mysql.performance.created_tmp_files	/ Type: int
mysql.performance.created_tmp_tables	/ Type: int
mysql.performance.kernel_time	percent / Type: int
mysql.performance.key_cache_utilization	/ Type: float
mysql.performance.open_files	/ Type: int
mysql.performance.open_tables	/ Type: int
mysql.performance.qcache.utilization	/ Type: float
mysql.performance.qcache.utilization.instant	/ Type: float
mysql.performance.qcache_hits	/ Type: int
mysql.performance.qcache_inserts	/ Type: int
mysql.performance.qcache_lowmem_prunes	/ Type: int
mysql.performance.qcache_size	/ Type: int
mysql.performance.queries	/ Type: int
mysql.performance.questions	/ Type: int
mysql.performance.slow_queries	/ Type: int
mysql.performance.table_locks_waited	/ Type: int
mysql.performance.table_locks_waited.rate	/ Type: int
mysql.performance.table_open_cache	/ Type: int
mysql.performance.thread_cache_size	/ Type: int
mysql.performance.threads_connected	/ Type: int
mysql.performance.threads_running	/ Type: int
mysql.performance.user_time	percent / Type: int
mysql.replication.seconds_behind_master	second / Type: int
mysql.replication.slave_running	/ Type: int
mysql.replication.slaves_connected Number of slaves connected to a replication master.	None / None Type: float

Metrics Conversion

Please refer to the metrics conversion document to map old metrics names to the new metric names.