MySQL Monitor
This agent monitor plugin records performance and usage data from a MySQL server.
NOTE: The MySQL monitor requires Python 2.7 or higher as of agent release 2.0.52. Earlier versions of the agent required Python 2.6. (The Python version requirement applies to the server on which the Scalyr Agent is running, which needn't necessarily be the same machine where the MySQL server is running.) If you need to monitor MySQL from a machine running an older version of Python, let us know.
An agent monitor plugin is a component of the Scalyr Agent. To use a plugin, simply add it to the monitors section of the Scalyr Agent configuration file (/etc/scalyr/agent.json). For more information, see Agent Plugins.
Sample Configuration
To configure the MySQL monitor plugin, you will need the following information:
- A MySQL username with administrative privileges. The user needs to be able to query the information_schema table, as well as assorted global status information.
- The password for that user.
Here is a sample configuration fragment:
monitors: [
{
module: "scalyr_agent.builtin_monitors.mysql_monitor",
database_socket: "default",
database_username: "USERNAME",
database_password: "PASSWORD"
}
]
This configuration assumes that MySQL is running on the same server as the Scalyr Agent, and is using the default MySQL socket. If not, you will need to specify the server's socket file, or hostname (or IP address) and port number; see Configuration Reference.
Viewing Data
After adding this plugin to the agent configuration file, wait one minute for data to begin recording. Then click the
menu and select . (The dashboard may not be listed until the agent begins sending MySQL data.) You will see an overview of MySQL performance statistics across all servers where you are running the MySQL plugin. Use the dropdown to show data for a specific server.The dashboard shows only some of the data collected by the MySQL monitor plugin. To explore the full range of data collected, go to the Search page and search for $monitor = 'mysql_monitor'. This will show all data collected by this plugin, across all servers. You can use the dropdown to narrow your search to specific servers and monitors.
The Search overview page describes the tools you can use to view and analyze log data. Query Language lists the operators you can use to select specific metrics and values. You can also use this data in Dashboards and Alerts.
Configuration Reference
Option | Usage |
---|---|
module | Always scalyr_agent.builtin_monitors.mysql_monitor |
id | Optional. Included in each log message generated by this monitor, as a field named instance. Allows you to distinguish between values recorded by different monitors. This is especially useful if you are running multiple MySQL instances on a single server; you can monitor each instance with a separate mysql_monitor record in the Scalyr Agent configuration. |
database_username | Username which the agent uses to connect to MySQL to retrieve monitoring data. |
database_password | Password for connecting to MySQL. |
database_socket | Location of the socket file for connecting to MySQL, e.g. /var/run/mysqld_instance2/mysqld.sock. If MySQL is running on the same server as the Scalyr Agent, you can usually set this to "default". |
database_hostport | Hostname (or IP address) and port number of the MySQL server, e.g. dbserver:3306, or simply 3306 when connecting to the local machine. You should specify one of database_socket or database_hostport, but not both. |
Log Reference
Each event recorded by this plugin will have the following fields:
Field | Meaning |
---|---|
monitor | Always mysql_monitor |
instance | The id value from the monitor configuration. |
metric | The name of a metric being measured, e.g. "mysql.vars" |
value | The metric value |
Data Categories
This plugin records an extensive array of values, in several categories. The exact list of metrics will vary, depending on which version of MySQL you are using and how you have configured MySQL.
mysql.global
These values are the output of the "SHOW GLOBAL STATUS" query. These are discussed in the MySQL documentation chapter "Server Status Variables". To reduce the number of metrics recorded, not all command counts are reported.
Metric name | Description |
---|---|
mysql.global.aborted_clients | The number of connections aborted because the client died or didn't close the connection properly. The value is relative to the uptime of the server. |
mysql.global.aborted_connects | The number of failed connection attempts. The value is relative to the uptime of the server. |
mysql.global.bytes_received | How much data has been sent to the database from all clients. The value is relative to the uptime of the server. |
mysql.global.bytes_sent | How much data has been sent from the database to all clients. The value is relative to the uptime of the server. |
mysql.global.com_insert | The number of insert commands run against the server |
mysql.global.com_delete | The number of delete commands run against the server |
mysql.global.com_replace | The number of replace commands run against the server |
mysql.global.com_select | The number of select commands run against the server |
mysql.global.connections | Total number of connection attempts (successful and failed). The value is relative to the uptime of the server. |
mysql.global.key_blocks_unused | The total number of keyblocks unused at the time of the monitor check. A high number indicates that the key cache might be large. |
mysql.global.key_blocks_used | Maximum number of key blocks used at any one point. Indicates a high water mark of the number used. The value is relative to the uptime of the server. |
mysql.global.max_used_connections | High water mark for the total number of connections used at any one time since the server was started. |
mysql.global.slow_queries | The total number of queries over the uptime of the server that exceeded the "long_query_time" configuration. |
mysql.innodb
If MySQL is configured to use the InnoDB storage engine, information about InnoDB usage will be emitted. These are discussed in the MySQL documentation chapter "SHOW ENGINE INNODB STATUS and the InnoDB Monitors".
Metric name | Description |
---|---|
mysql.innodb.oswait_array.reservation_count | A measure of how actively innodb uses it's internal sync array. Specifically, how frequently slots are allocated. |
mysql.innodb.oswait_array.signal_count | As above, part of the measure of activity of the internal sync array, in this case how frequently threads are signaled using the sync array. |
mysql.innodb.locks.spin_waits | The number of times since server start that a thread tried to a mutex that wasn't available. |
mysql.innodb.locks.rounds | The number of times since server start that a thread looped through the spin-wait cycle. |
mysql.innodb.locks.os_waits | The number of times since server start that a thread gave up spin-waiting and went to sleep. |
mysql.innodb.history_list_length | The number of unpurged transactions in the internal undo buffer.It typically increases while transactions with updates are run and will decrease once the internal purge runs. |
mysql.innodb.innodb.ibuf.size | The size of the insert buffer. |
mysql.innodb.innodb.ibuf.free_list_len | The size free list for the insert buffer. |
mysql.innodb.innodb.ibuf.seg_size | The segment size of the insert buffer. |
mysql.innodb.innodb.ibuf.inserts | The total number of inserts since server start into the insert buffer. |
mysql.innodb.innodb.ibuf.merged_recs | The total number of records merged in the insert buffer since server start. |
mysql.innodb.innodb.ibuf.merges | The total number of merges for the insert buffer since server start. |
mysql.innodb.queries_queued | The number of queries waiting to be processed. The value is based on the time the monitor sample is run. |
mysql.innodb.opened_read_views | The number of views into the db, this is "started transactions" which have no current statement actively operating. |
mysql.process
The result of "SHOW PROCESSLIST". These show the types of commands being run and the number of threads performing each command.
Metric name | Description |
---|---|
mysql.process.query | The number of threads performing a query. |
mysql.process.sleep | The number of threads sleeping. |
... | ... |
mysql.process.xxx | The number of threads in state xxx |
mysql.vars
These values reflect the current configuration of the MySQL server. They are discussed MySQL documentation chapter titled "Using System Variables". Currently, the monitor only records two specific variables.
Metric name | Description |
---|---|
mysql.max_connections | The maximum number of allowed open connections to server. |
mysql.open_files_limit | The maximum number of allowed open files. |
mysql.slave
If your MySQL instance is configured as a slave, the values from "SHOW SLAVE STATUS" are listed in this category. See the MySQL documentation chapter "Checking Replication Status".