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:
-
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
-
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&...¶mN=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 tofalse
. If set totrue
, 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 tofalse
, InnoDB metrics will not be collected, and corresponding metrics needs to be commented out in task file. -
Restart the agent:
sudo service swisnapd restart
-
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/*: {}.
To be able to collect slave/master status, the configred user needs to have SUPER
or REPLICATION CLIENT
privilege. See mysql documentation for reference.
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 |
As of MySQL 5.7.20 query cache is deprecated and is removed in MySQL 8.0 and corresponding mysql.cache_result.qcache* metrics will not be gathered.
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 |
Navigation Notice: When the APM Integrated Experience is enabled, SolarWinds Template Product shares a common navigation and enhanced feature set with other integrated experience products. How you navigate SolarWinds Template Product and access its features may vary from these instructions.
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.