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

Setup

The mysql plugin is included with the SolarWinds Snap Agent by default, please follow the directions below to enable it for an 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 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/SolarWinds/Snap/etc/plugins.d/mysql.yaml.example, renaming it to /opt/SolarWinds/Snap/etc/plugins.d/mysql.yaml:

   sudo cp -p /opt/SolarWinds/Snap/etc/plugins.d/mysql.yaml.example /opt/SolarWinds/Snap/etc/plugins.d/mysql.yaml

2. Update the /opt/SolarWinds/Snap/etc/plugins.d/mysql.yaml configuration file with settings specific to your database instance, for example:

   collector:
     mysql:
       all:
         mysql_connection_string: "user:passwd@tcp(localhost:3306)/"
         # multiple connection strings
         # mysql_connection_string: |
         #   user:passwd@tcp(localhost:3306)/
         #   user:passwd@tcp(localhost:3307)/
         mysql_use_innodb: false
         
   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. It accepts single line or multiline (embedded yaml) connection strings. The credentials should be entered in the following format:

   [username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

   Using the root user for monitoring can cause compatibility issues. We recommend creating a new user with full read rights for use with this plugin.

   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. If set to false, InnoDB metrics will not be collected, and corresponding metrics needs to be commented out in task file.

3. Restart the agent:

   sudo service swisnapd restart

4. 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 Linux.

   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. You enable or disable individual metrics from the task-aomysql.yaml file:

/opt/SolarWinds/Snap/etc/tasks.d/task-aomysql.yaml

To enable all available metrics comment out existing metrics and add the following line to the metrics list in the file above:

/mysql/*: {}.

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, address, database and dbversion. Instead of using that tag, we recommend using the @host alias. The address tag is the address of database from connection string. database is a name of database from connection string. dbversion is version of database.

Optional Metrics

Optional metrics can be activated by editing the task yaml. For more information please read the SolarWinds Snap Agent Task File 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

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.