MySQL Monitor

This agent monitor plugin records performance and usage data from a MySQL server.

NOTE: the MySQL monitor requires Python 2.6 or higher. (This 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 Dashboards menu and select MySQL. (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 ServerHost 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 Refine search by dropdown to narrow your search to specific servers and monitors.

The View Logs 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".