MySQL monitoring integration

Access to this feature depends on your subscription level. Requires Infrastructure Pro.

New Relic Infrastructure's on-host integrations include a MySQL integration for sending data from your MySQL database to New Relic products. This document explains how to install and activate the MySQL integration, and describes the data that can be captured.

Compatibility and requirements

To use the MySQL integration, ensure your system meets these requirements:

Install and activate

On-host integrations do not automatically update. For best results, you should occasionally update the integration and update the Infrastructure agent.

To install the MySQL integration:

  1. Follow the instructions for installing an integration, using the file name nri-mysql.

  2. From the command line, create a user with replication privileges:

    sudo mysql -e "CREATE USER 'newrelic'@'localhost' IDENTIFIED BY 'YOUR_SELECTED_PASSWORD';"
    sudo mysql -e "GRANT REPLICATION CLIENT ON *.* TO 'newrelic'@'localhost' WITH MAX_USER_CONNECTIONS 5;"
    
  3. Change the directory to the integration's folder.

    cd /etc/newrelic-infra/integrations.d
    
  4. Create a copy of the sample configuration file by running:

    sudo cp mysql-config.yml.sample mysql-config.yml
  5. Edit the configuration file mysql-config.yml as explained in the next section.
  6. Restart the Infrastructure agent.

Configuration

Only one MySQL server instance can be monitored per host by this integration.

Use the MySQL integration's configuration file (mysql-config.yml) to put required login credentials and configure how data is collected. The mysql-config.yml file provides a single command, status, that captures the metrics and all the configuration options. It accepts these arguments:

  • hostname: The MySQL hostname.
  • port: The port where the MySQL server is listening.
  • username: The user connected to the MySQL server. If you used the CREATE USER command in the activation instructions, this should be set to newrelic.
  • password: The password for the user specified above.
  • extended_metrics: Captures an extended set of metrics. Disabled by default. Set to 1 to enable. This also enables the capture of slave metrics.
  • extended_innodb_metrics: Captures additional innodb metrics. Disabled by default. Set to 1 to enable.
  • extended_myisam_metrics: Captures additional MyISAM metrics. Disabled by default. Set to 1 to enable.
  • Optional: labels field. For example, the env label controls the environment inventory attribute. The default value is production.
  • Optional: metrics field. Set to 1 to disable the collection of inventory.

Find and use data

To find your integration data in Infrastructure, go to infrastructure.newrelic.com > Integrations > On-host integrations and select one of the MySQL integration links.

In New Relic Insights, MySQL data is attached to the MysqlSample event type.

For more on how to find and use your data, see Understand and use integration data.

Metric data

The MySQL integration collects the following metrics:

These metrics are captured by default:

Name Description
cluster.slaveRunning Boolean. 1 if this server is a replication slave that is connected to a replication master, and both the I/O and SQL threads are running; otherwise, it is 0. For metrics reported if enabled, see replication slave metrics.
db.handlerRollbackPerSecond Rate of requests for a storage engine to perform a rollback operation, per second.
db.innodb.bufferPoolPagesData Number of pages in the InnoDB buffer pool containing data.
db.innodb.bufferPoolPagesFree Number of free pages in the InnoDB buffer pool.
db.innodb.bufferPoolPagesTotal Total number of pages of the InnoDB buffer pool.
db.innodb.dataReadBytesPerSecond Rate at which data is read from InnoDB tables in bytes per second.
db.innodb.dataWrittenBytesPerSecond Rate at which data is written to InnoDB tables in bytes per second.
db.innodb.logWaitsPerSecond Number of times that the log buffer was too small and a wait was required for it to be flushed before continuing, in waits per second.
db.innodb.rowLockCurrentWaits Number of row locks currently being waited for by operations on InnoDB tables.
db.innodb.rowLockTimeAvg Average time to acquire a row lock for InnoDB tables, in milliseconds.
db.innodb.rowLockWaitsPerSecond Number of times operations on InnoDB tables had to wait for a row lock per second.
db.openedTablesPerSecond Number of files that have been opened with my_open() (a mysys library function) per second. Parts of the server that open files without using this function do not increment the count.
db.openFiles Number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes.
db.openTables Number of tables that are open.
db.qCacheFreeMemoryBytes Amount of free memory in bytes for the query cache.
db.qCacheHitRatio Percentage of queries that are retrieved from the cache.
db.qCacheNotCachedPerSecond Number of noncached queries (not cacheable, or not cached due to the query_cache_type setting) per second.
db.qCacheUtilization Percentage of query cache memory that is being used.
db.tablesLocksWaitedPerSecond Number of times per second that a request for a table lock could not be granted immediately and a wait was needed.
net.abortedClientsPerSecond Number of connections per second that were aborted because the client died without closing the connection properly.
net.abortedConnectsPerSecond Number of failed attempts to connect to the MySQL server, per second.
net.bytesReceivedPerSecond Byte throughput received from all clients, per second.
net.bytesSentPerSecond Byte throughput sent to all clients, per second.
net.connectionErrorsMaxConnectionsPerSecond Rate per second at which connections were refused because the server max_connections limit was reached.
net.connectionsPerSecond Number of connection attempts per second.
net.maxUsedConnections Maximum number of connections that have been in use simultaneously since the server started.
net.threadsConnected Number of currently open connections.
net.threadsRunning Number of threads that are not sleeping.
query.comDeletePerSecond Number of DELETE statements executed per second.
query.comDeleteMultiPerSecond Number of DELETE statements that use the multiple-table syntax executed per second.
query.comInsertPerSecond Number of INSERT statements executed per second.
query.comInsertSelectPerSecond Number of INSERT SELECT statements executed per second.
query.comReplaceSelectPerSecond Number of REPLACE SELECT statements executed per second.
query.comSelectPerSecond Number of SELECT statements executed per second.
query.comUpdateMultiPerSecond Number of UPDATE statements that use the multiple-table syntax executed per second.
query.comUpdatePerSecond Number of UPDATE statements executed per second.
query.preparedStmtCountPerSecond Current number of prepared statements per second. (The maximum number of statements is given by the max_prepared_stmt_count system variable.)
query.queriesPerSecond Total number of statements executed by the server per second, including statements executed within stored programs.
query.questionsPerSecond Number of statements executed by the server per second, limited to only those sent by clients.
query.slowQueriesPerSecond Number of queries per second that have taken more than long_query_time seconds. This counter increments regardless of whether the slow query log is enabled.

Additional metrics captured when extended_metrics is enabled (set to 1 in the configuration file):

Name Description
db.createdTmpDiskTablesPerSecond Number of internal on-disk temporary tables created per second by the server while executing statements.
db.createdTmpFilesPerSecond Number of temporary files created per second by mysqld.
db.createdTmpTablesPerSecond Number of internal temporary tables created per second by the server while executing statements.
db.handlerDeletePerSecond Number of times per second that rows have been deleted from tables.
db.handlerReadFirstPerSecond Number of times per second the first entry in an index was read.
db.handlerReadKeyPerSecond Number of requests per second to read a row based on a key.
db.handlerReadRndNextPerSecond Number of requests per second to read the next row in the data file.
db.handlerReadRndPerSecond Number of requests per second to read a row based on a fixed position.
db.handlerUpdatePerSecond Number of requests per second to update a row in a table.
db.handlerWritePerSecond Number of requests per second to insert a row in a table.
db.maxExecutionTimeExceededPerSecond Number of SELECT statements per second for which the execution timeout was exceeded.
db.qCacheFreeBlocks Number of free memory blocks in the query cache.
db.qCacheHitsPerSecond Number of query cache hits per second.
db.qCacheInserts Number of queries added to the query cache.
db.qCacheLowmemPrunesPerSecond Number of queries per second that were deleted from the query cache because of low memory.
db.qCacheQueriesInCachePerSecond Number of queries per second registered in the query cache.
db.qCacheTotalBlocks Total number of blocks in the query cache.
db.selectFullJoinPerSecond Number of joins that perform table scans because they do not use indexes, per second.
db.selectFullJoinRangePerSecond Number of joins per second that used a range search on a reference table.
db.selectRangeCheckPerSecond Number of joins per second without keys that check for key usage after each row.
db.selectRangePerSecond Number of joins per second that used ranges on the first table.
db.sortMergePassesPerSecond Number of merge passes that the sort algorithm has had to do, per second.
db.sortRangePerSecond Number of sorts per second that were done using ranges.
db.sortRowsPerSecond Number of sorted rows per second.
db.sortScanPerSecond Number of sorts that were done by scanning the table, per second.
db.tableOpenCacheHitsPerSecond Number of hits per second for open tables cache lookups.
db.tableOpenCacheMissesPerSecond Number of misses per second for open tables cache lookups.
db.tableOpenCacheOverflowsPerSecond Number of overflows per second for the open tables cache.
db.threadCacheMissRate Percent of threads that need to be created to handle new connections because there are not enough threads available in the cache.
db.threadsCached Number of threads in the thread cache.
db.threadsCreatedPerSecond Number of threads per second created to handle connections.

Additional metrics captured when extended_innodb_metrics is enabled (set to 1 in the configuration file):

Name Description
db.innodb.bufferPoolPagesDirty Current number of dirty pages in the InnoDB buffer pool.
db.innodb.bufferPoolPagesFlushedPerSecond Number of requests per second to flush pages from the InnoDB buffer pool.
db.innodb.bufferPoolReadAheadEvictedPerSecond Number of pages per second read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries.
db.innodb.bufferPoolReadAheadPerSecond Number of pages per second read into the InnoDB buffer pool by the read-ahead background thread.
db.innodb.bufferPoolReadAheadRndPerSecond Number of “random” read-aheads per second initiated by InnoDB. This happens when a query scans a large portion of a table but in random order.
db.innodb.bufferPoolReadRequestsPerSecond Number of logical read requests per second.
db.innodb.bufferPoolReadsPerSecond Number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk, per second.
db.innodb.bufferPoolWaitFreePerSecond Number of times per second a read or write to InnoDB had to wait because there were not clean pages available in the buffer pool.
db.innodb.bufferPoolWriteRequestsPerSecond Number of writes per second done to the InnoDB buffer pool.
db.innodb.dataFsyncsPerSecond Number of fsync() operations per second.
db.innodb.dataPendingFsyncs Current number of pending fsync() operations.
db.innodb.dataPendingReads Current number of pending reads.
db.innodb.dataPendingWrites Current number of pending writes.
db.innodb.dataReadsPerSecond Number of data reads (OS file reads) per second.
db.innodb.dataWritesPerSecond Number of data writes per second.
db.innodb.logWriteRequestsPerSecond Number of write requests for the InnoDB redo log per second.
db.innodb.logWritesPerSecond Number of physical writes per second to the InnoDB redo log file.
db.innodb.numOpenFiles Number of files InnoDB currently holds open.
db.innodb.osLogFsyncsPerSecond Number of fsync() writes per second done to the InnoDB redo log files.
db.innodb.osLogPendingFsyncs Number of pending fsync() operations for the InnoDB redo log files.
db.innodb.osLogPendingWrites Number of pending writes per second to the InnoDB redo log files.
db.innodb.osLogWrittenBytesPerSecond
rate
Number of bytes written per second to the InnoDB redo log files.
db.innodb.pagesCreatedPerSecond The number of pages created per second by operations on InnoDB tables.
db.innodb.pagesReadPerSecond Number of pages read per second from the InnoDB buffer pool by operations on InnoDB tables.
db.innodb.pagesWrittenPerSecond Number of pages written per second by operations on InnoDB tables.
db.innodb.rowsDeletedPerSecond Number of rows deleted per second from InnoDB tables.
db.innodb.rowsInsertedPerSecond Number of rows per second inserted into InnoDB tables.
db.innodb.rowsReadPerSecond Number of rows per second read from InnoDB tables.
db.innodb.rowsUpdatedPerSecond Number of rows per second updated in InnoDB tables.

Additional metrics captured when extended_myisam_metrics is enabled in the configuration file:

Name Description
db.myisam.keyBlocksNotFlushed Number of key blocks in the MyISAM key cache that have changed but have not yet been flushed to disk.
db.myisam.keyCacheUtilization Percentage of the key cache that is being used.
db.myisam.keyReadRequestsPerSecond Number of requests to read a key block from the MyISAM key cache, per second.
db.myisam.keyReadsPerSecond Number of physical reads of a key block from disk into the MyISAM key cache, per second.
db.myisam.keyWriteRequestsPerSecond Number of requests per second to write a key block to the MyISAM key cache.
db.myisam.keyWritesPerSecond Number of physical writes of a key block from the MyISAM key cache to disk, per second.

Additional metrics captured when the extended metrics flag is enabled in the configuration file and the cluster.slaveRunning metric is set to 1:

Name Description
db.relayLogSpace Total combined number of bytes for all existing relay log files.
cluster.lastIOErrno Error number of the most recent error that caused the I/O thread to stop.
cluster.lastIOError Error message of the most recent error that caused the I/O thread to stop.
cluster.lastSQLErrno Error number of the most recent error that caused the SQL thread to stop.
cluster.lastSQLError Error message of the most recent error that caused the SQL thread to stop.
cluster.slaveIORunning Boolean: 0 or 1. Status of whether the I/O thread is started and has connected successfully to the master.
cluster.slaveSQLRunning Boolean: 0 or 1. Status of whether the SQL thread is started.
cluster.secondsBehindMaster Difference in seconds between the slave’s clock time and the timestamp of the query when it was recorded in the master’s binary log. When the slave is not correctly connected to the master, this metric won’t be reported.
cluster.masterLogFile Name of the master binary log file from which the I/O thread is currently reading.
cluster.readMasterLogPos Position in the current master binary log file up to which the I/O thread has read.
cluster.relayMasterLogFile Name of the master binary log file containing the most recent event executed by the SQL thread.
cluster.execMasterLogPos Position in the current master binary log file to which the SQL thread has read and executed, marking the start of the next transaction or event to be processed.

Inventory

The MySQL integration captures the configuration parameters of the MySQL node returned by SHOW GLOBAL VARIABLES. The data is available on the Infrastructure Inventory page, under the config/mysql source.

System metadata

The MySQL integration collects the following metadata attributes about your MySQL system:

Name Description
software.edition

software.edition takes the value of the MySQL version_comment variable.

software.version The MySQL server version.
cluster.nodeType Either master or slave, depending on the role of the MySQL node being monitored.

For more help

Recommendations for learning more: