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.

Screen_Shot_2018-01-18_at_11.46.12.png

Monitored metrics

MetricValues
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.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Monday  —  Friday.

10am  —  6pm UK.

Dedicated Support.