MySQL

Overview

This plugin collects runtime metrics from MySQL or MariaDB instances. It gathers information about resource usage and performance characteristics, including information for queries, on your database instance(s).

Note

This plugin is currently only available for x86_64 Linux.

Setup

The mysql plugin is included with the AppOptics host agent by default, please follow the directions below to enable it for a host agent instance.

Prerequisites

This plugin requires the connection credential to the MySQL or MariaDB instance to monitor, please refer to your database manual on setting up the credential.

Configuration

The host agent provides an example configuration file to help you get started quickly. It defines the plugin and task file to be loaded by the agent, but requires you to provide the correct settings for your database. To enable the plugin:

  1. Make a copy of the mysql example configuration file /opt/appoptics/etc/plugins.d/mysql.yaml.example, renaming it to /opt/appoptics/etc/plugins.d/mysql.yaml:
$ sudo cp /opt/appoptics/etc/plugins.d/mysql.yaml.example /opt/appoptics/etc/plugins.d/mysql.yaml
  1. Update the /opt/appoptics/etc/plugins.d/mysql.yaml configuration file with settings specific to your database instance, for example:
collector:
  mysql:
    all:
      mysql_connection_string: "root:admin@tcp(mysql:3306)/"
      mysql_use_innodb: true

load:
  plugin: snap-plugin-collector-aomysql
  task: task-aomysql.yaml

The key mysql_connection_string is a required setting that should set the connection information to the database instance. This plugin is tested to run against both versions of the database software, so enabling the TCP connection on your SQL instance/cluster will be sufficient.

The key mysql_use_innodb is an optional setting that defaults to false. If set to true, the plugin will collect extended metrics from the InnoDB storage engine for your instance/cluster. You can get more information about InnoDB from the MySQL documentation.

  1. Restart the host agent:
$ sudo service appoptics-snapteld restart
  1. Enable the MySQL plugin in the AppOptics UI

On the Integrations Page you will see the MySQL plugin available if the previous steps were successful. If you do not see the plugin, see Troubleshooting.

Select the MySQL plugin to open the configuration menu in the UI, and enable the plugin.

You should soon see the mysql metrics reported to your dashboard.

Metrics and Tags

The tables below outline the default set of metrics collected by the mysql plugin along with the optional metrics available.

Note

All metrics are int64 values collected either as a counter, gauge or derivative value

Default Metrics

Namespace Description InnoDB
mysql.aborted.clients total number of clients aborted/denied N
mysql.aborted.connects total number of connections aborted/denied N
mysql.created.tmp_disk_tables number of temporary disk tables created by the query engine N
mysql.created.tmp_tables number of temporary tables created by the query engine N
mysql.created.tmp_files number of temporary files created by the query engine N
mysql.cache_result.qcache-hits number of query cache hits N
mysql.cache_result.qcache-inserts number of queries added to the query cache N
mysql.cache_result.qcache-not_cached number of noncached queries (not cacheable or not cached due to the query_cache_type setting) N
mysql.cache_result.qcache-prunes number of queries that were deleted from the query cache because of low memory N
mysql.cache_size.qcache number of queries registered in the query cache N
mysql.cache_size.qcache-free_blocks number of free blocks in the query cache N
mysql.cache_size.qcache-total_blocks number of blocks total in the query cache N
mysql.cache_size.qcache-free_memory total amount of free memory in the query cache (bytes) N
mysql.commands.[subnamespace] available namespaces are evaluated in runtime; metrics indicate the number of times each statement has been executed N
mysql.gauge.buffer_pool_pages_data buffer pages containing data (innodb_buffer_pool_pages_data) Y
mysql.gauge.buffer_pool_pages_dirty buffer pages currently dirty (innodb_buffer_pool_pages_dirty) Y
mysql.gauge.buffer_pool_pages_free buffer pages currently free (innodb_buffer_pool_pages_free) Y
mysql.gauge.buffer_pool_pages_misc buffer pages for misc use such as row locks or adaptive hash index (innodb_buffer_pool_pages_misc) Y
mysql.handler.[subnamespace] available namespaces are evaluated in runtime; metrics indicate the number of internal operations N
mysql.innodb_data.fsyncs number of fsync operations Y
mysql.innodb_data.reads total number of data reads Y
mysql.innodb_data.writes total number of data writes Y
mysql.innodb_log.writes number of physical writes to the InnoDB redo log file Y
mysql.innodb_pages.created number of pages created by operations on InnoDB tables Y
mysql.innodb_pages.read number of pages read by operations on InnoDB tables Y
mysql.innodb_pages.written number of pages written by operations on InnoDB tables Y
mysql.innodb_rows.deleted number of rows deleted from InnoDB tables Y
mysql.innodb_rows.inserted number of rows inserted into InnoDB tables Y
mysql.innodb_rows.read number of rows read from InnoDB tables Y
mysql.innodb_rows.updated number of rows updated in InnoDB tables Y
mysql.locks.immediate number of times that a request for a table lock could be granted immediately Y
mysql.locks.lock_deadlocks number of deadlocks Y
mysql.locks.waited number of times request for table lock could not be granted immediately Y
mysql.octets.rx number of bytes received from all clients Y
mysql.octets.tx number of bytes sent to all clients Y
mysql.operations.buffer_pool_read_requests number of logical read requests (innodb_buffer_pool_read_requests) Y
mysql.operations.buffer_pool_reads number of reads directly from disk (innodb_buffer_pool_reads) Y
mysql.operations.buffer_pool_wait_free number of times waited for free buffer (innodb_buffer_pool_wait_free) Y
mysql.operations.innodb_rwlock_s_spin_rounds number of rwlock spin loop rounds due to shared latch request Y
mysql.operations.innodb_rwlock_s_spin_waits number of rwlock spin waits due to shared latch request Y
mysql.operations.innodb_rwlock_x_os_waits number of OS waits due to exclusive latch request Y
mysql.queries.total total number of queries N
mysql.threads.cached number of threads in the thread cache Y
mysql.threads.connected number of currently open connections Y
mysql.threads.running number of threads that are not sleeping Y
mysql.total_threads.created number of threads created to handle connections Y
mysql.select.full_join number of joins that perform table scans because they do not use indexes; if value is not 0 you should carefully check the indexes of your tables N
mysql.select.full_range_join number of joins that used a range search on a reference table N
mysql.select.range number of joins that used ranges on the first table N
mysql.select.range_check number of joins without keys that check for key usage after each row; if value is not 0 you should carefully check the indexes of your tables N
mysql.select.scan number of joins that did a full scan of the first table N
mysql.slow.queries number of queries that have taken more than long_query_time seconds N
mysql.sort.merge_passes number of merge passes that the sort algorithm has had to do N
mysql.sort.range number of sorts that were done using ranges N
mysql.sort.rows number of sorted rows N
mysql.sort.scan number of sorts that were done by scanning the table N
mysql.uptime.total total time (in seconds) the server has been up N
mysql.uptime.since_flush_status total time (in seconds) since the last flush N

Default Metric Tags

All MySQL metrics are tagged with hostname. Instead of using that tag, we recommend using the @host alias.

Optional Metrics

Optinal metrics can be activated by editing the task yaml. For more information please read the Host Agent configuration article.

Namespace Description InnoDB
mysql.bytes.buffer_pool_size number of row locks currently being waited for (innodb_row_lock_current_waits) Y
mysql.bytes.ibuf_size number of row locks currently being waited for (innodb_row_lock_current_waits) Y
mysql.bytes.metadata_mem_pool_size size of memory pool InnoDB uses to store data dictionary and internal data structures Y
mysql.gauge.buffer_pool_bytes_data buffer bytes containing data (innodb_buffer_pool_bytes_data) Y
mysql.gauge.buffer_pool_bytes_dirty buffer bytes currently dirty (innodb_buffer_pool_bytes_dirty) Y
mysql.gauge.buffer_pool_pages_total total buffer pool size in pages (innodb_buffer_pool_pages_total) Y
mysql.gauge.file_num_open_files number of files currently open (innodb_num_open_files) Y
mysql.gauge.innodb_activity_count number of files currently open (innodb_num_open_files) Y
mysql.gauge.innodb_dblwr_page_size InnoDB page size in bytes (innodb_page_size) Y
mysql.gauge.trx_rseg_history_len length of the TRX_RSEG_HISTORY list Y
mysql.bpool_bytes.data total bytes in the InnoDB buffer pool containing data (includes both dirty and clean pages) Y
mysql.bpool_bytes.dirty total number of bytes held in dirty pages in the InnoDB buffer pool Y
mysql.bpool_counters.pages_flushed number of requests to flush pages from the InnoDB buffer pool Y
mysql.bpool_counters.read_ahead number of pages read into InnoDB buffer pool by read-ahead background thread Y
mysql.bpool_counters.read_ahead_evicted number of pages read into the InnoDB buffer pool by the read-ahead background thread subsequently evicted without having been accessed by queries Y
mysql.bpool_counters.read_ahead_rnd number of “random” read-aheads initiated by InnoDB (ccurs when a query scans a large portion of a table in random order) Y
mysql.bpool_counters.read_requests number of logical read requests Y
mysql.bpool_counters.read_requests number of logical read requests Y
mysql.bpool_counters.reads number of logical reads that InnoDB could not satisfy from buffer pool Y
mysql.bpool_counters.write_requests number of writes done to the InnoDB buffer pool Y
mysql.bpool_pages.data number of pages in InnoDB buffer pool containing data (includes both dirty and clean pages) Y
mysql.bpool_pages.dirty total current number of bytes held in dirty pages in InnoDB buffer pool Y
mysql.bpool_pages.free number of free pages in the InnoDB buffer pool Y
mysql.bpool_pages.misc number of pages busy in InnoDB buffer pool; have been allocated for administrative overhead (such as row locks or the adaptive hash index) Y
mysql.bpool_pages.total total size of the InnoDB buffer pool (in pages) Y
mysql.innodb_data.read amount of data read since the server was started Y
mysql.innodb_data.written amount of data written so far (in bytes) Y
mysql.innodb_dblwr.writes number of doublewrite operations that have been performed Y
mysql.innodb_dblwr.written number of pages that have been written to the doublewrite buffer Y
mysql.innodb_log.fsyncs number of fsync writes done to the InnoDB redo log files Y
mysql.innodb_log.waits number of times waited for log buffer to be flushed Y
mysql.innodb_log.write_requests number of write requests for the InnoDB redo log Y
mysql.innodb_log.written number of bytes written to the InnoDB redo log files Y
mysql.innodb_row_lock.time total time spent in acquiring row locks for InnoDB tables (in milliseconds) Y
mysql.innodb_row_lock.waits number of times operations on InnoDB tables had to wait for a row lock Y
mysql.locks.lock_row_lock_current_waits number of row locks currently being waited for (innodb_row_lock_current_waits) Y
mysql.locks.lock_timeouts number of row locks currently being waited for (innodb_row_lock_current_waits) Y
mysql.log_position.master-bin position of the binary log file of the master N
mysql.log_position.slave-exec position in the current master binary log file to which the SQL thread has read and executed; marking start of next transaction or event to be processed N
mysql.log_position.slave-read position in current master binary log file up to which the I.O thread has read N
mysql.log_position.time_offset indication of how “late” the slave is when actively processing updates; shows the difference between the current timestamp on the slave and the original timestamp logged on the master for the event currently being processed on the slave (when no events processed value == 0) N
mysql.operations.adaptive_hash_searches number of successful searches using Adaptive Hash Index N
mysql.operations.buffer_data_reads amount of data read in bytes (innodb_data_reads) Y
mysql.operations.buffer_data_written amount of data written in bytes (innodb_data_written) Y
mysql.operations.buffer_pages_created number of pages created (innodb_pages_created) Y
mysql.operations.buffer_pages_read number of pages read (innodb_pages_read) Y
mysql.operations.buffer_pages_written amount of data written in bytes (innodb_data_written) Y
mysql.operations.buffer_pool_read_ahead number of pages read as read ahead (innodb_buffer_pool_read_ahead) Y
mysql.operations.buffer_pool_read_ahead_evicted read-ahead pages evicted without being accessed (innodb_buffer_pool_read_ahead_evicted) Y
mysql.operations.buffer_pool_write_requests number of write requests (innodb_buffer_pool_write_requests) Y
mysql.operations.dml_deletes number of rows deleted Y
mysql.operations.dml_inserts number of rows inserted Y
mysql.operations.dml_read number of rows read Y
mysql.operations.dml_updates number of rows updated Y
mysql.operations.ibuf_merges_delete number of purge records merged by change buffering Y
mysql.operations.ibuf_merges_delete_mark number of deleted records merged by change buffering Y
mysql.operations.ibuf_merges_discard_delete number of purge merged operations discarded Y
mysql.operations.ibuf_merges_discard_delete_mark number of deleted merged operations discarded Y
mysql.operations.ibuf_merges_discard_insert number of insert merged operations discarded Y
mysql.operations.ibuf_merges_discard_merges number of merged operations discarded Y
mysql.operations.ibuf_merges_insert number of inserted records merged by change buffering Y
mysql.operations.innodb_dblwr_pages_written number of pages that have been written for doublewrite operations innodb_dblwr_pages_written) Y
mysql.operations.innodb_dblwr_writes number of doublewrite operations that have been performed (innodb_dblwr_writes) Y
mysql.operations.innodb_rwlock_s_os_waits number of OS waits due to shared latch request Y
mysql.operations.innodb_rwlock_x_spin_rounds number of rwlock spin loop rounds due to exclusive latch request Y
mysql.operations.innodb_rwlock_x_spin_waits number of rwlock spin waits due to exclusive latch request Y
mysql.operations.log_waits number of log waits due to small log buffer (innodb_log_waits) Y
mysql.operations.log_write_requests number of log write requests (innodb_log_write_requests) Y
mysql.operations.log_writes number of log writes (innodb_log_writes) Y
mysql.operations.os_data_fsyncs number of fsync calls (innodb_data_fsyncs) Y
mysql.operations.os_data_reads number of reads initiated (innodb_data_reads) Y
mysql.operations.os_data_writes number of writes initiated (innodb_data_writes) Y
mysql.operations.os_log_bytes_written bytes of log written (innodb_os_log_written) Y
mysql.operations.os_log_fsyncs number of fsync log writes (innodb_os_log_fsyncs) Y
mysql.operations.os_log_pending_fsyncs number of pending fsync write (innodb_os_log_pending_fsyncs) Y
mysql.operations.os_log_pending_writes number of pending log file writes (innodb_os_log_pending_writes) Y