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 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 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.
use_ssl	Use SSL when connecting to the MySQL server. Default: False
ca_file	Optional: Location of the CA file to use for SSL connection. Default: None
key_file	Optional: Location of the key file used by the SSL connection for client verification. Default: None
cert_file	Optional: Location of the cert file used by the SSL connection for client verification. Default: None

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