Datadog-MySQL Integration

Overview

The Datadog Agent can collect many metrics from MySQL databases, including:

  • Query throughput
  • Query performance (average query run time, slow queries, etc)
  • Connections (currently open connections, aborted connections, errors, etc)
  • InnoDB (buffer pool metrics, etc)

And many more. You can also invent your own metrics using custom SQL queries.

Setup

Installation

The MySQL check is included in the Datadog Agent package, so simply install the Agent on your MySQL servers. If you need the newest version of the check, install the dd-check-mysql package.

Configuration

Prepare MySQL

On each MySQL server, create a database user for the Datadog Agent:

mysql> CREATE USER 'datadog'@'localhost' IDENTIFIED BY '<UNIQUEPASSWORD>';
Query OK, 0 rows affected (0.00 sec)

Please note that @'localhost' is only for local connections, use the hostname/IP of your agent for remote connections, learn more here

Verify that the user was created successfully using the following command, replacing <UNIQUEPASSWORD> with the password above:

mysql -u datadog --password=<UNIQUEPASSWORD> -e "show status" | \
grep Uptime && echo -e "\033[0;32mMySQL user - OK\033[0m" || \
echo -e "\033[0;31mCannot connect to MySQL\033[0m"
mysql -u datadog --password=<UNIQUEPASSWORD> -e "show slave status" && \
echo -e "\033[0;32mMySQL grant - OK\033[0m" || \
echo -e "\033[0;31mMissing REPLICATION CLIENT grant\033[0m"

The Agent needs a few privileges to collect metrics. Grant its user ONLY the following privileges:

mysql> GRANT REPLICATION CLIENT ON *.* TO 'datadog'@'localhost' WITH MAX_USER_CONNECTIONS 5;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT PROCESS ON *.* TO 'datadog'@'localhost';
Query OK, 0 rows affected (0.00 sec)

If the MySQL server has the performance_schema database enabled and you want to collect metrics from it, the Agent’s user needs one more GRANT. Check that performance_schema exists and run the GRANT if so:

mysql> show databases like 'performance_schema';
+-------------------------------+
| Database (performance_schema) |
+-------------------------------+
| performance_schema            |
+-------------------------------+
1 row in set (0.00 sec)

mysql> GRANT SELECT ON performance_schema.* TO 'datadog'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Connect the Agent

Create a basic mysql.yaml in the Agent’s conf.d directory to connect it to the MySQL server. See the sample mysql.yaml for all available configuration options:

init_config:

instances:
  - server: localhost
    user: datadog
    pass: <YOUR_CHOSEN_PASSWORD> # from the CREATE USER step earlier
    port: <YOUR_MYSQL_PORT> # e.g. 3306
    options:
        replication: 0
        galera_cluster: 1
        extra_status_metrics: true
        extra_innodb_metrics: true
        extra_performance_metrics: true
        schema_size_metrics: false
        disable_innodb_metrics: false

If you found above that MySQL doesn’t have performance_schema enabled, do not set extra_performance_metrics to true.

See our sample mysql.yaml for all available configuration options, including those for custom metrics.

Restart the Agent to start sending MySQL metrics to Datadog.

Validation

Run the Agent’s info subcommand and look for mysql under the Checks section:

  Checks
  ======

    [...]

    mysql
    -----
      - instance #0 [OK]
      - Collected 168 metrics, 0 events & 1 service check

    [...]

If the status is not OK, see the Troubleshooting section.

Compatibility

The MySQL integration is supported on versions x.x+

Data Collected

Metrics

mysql.galera.wsrep_cluster_size
(gauge)
The current number of nodes in the Galera cluster.
shown as node
mysql.innodb.buffer_pool_free
(gauge)
The number of free pages in the InnoDB Buffer Pool.
shown as page
mysql.innodb.buffer_pool_total
(gauge)
The total number of pages in the InnoDB Buffer Pool.
shown as page
mysql.innodb.buffer_pool_used
(gauge)
The number of used pages in the InnoDB Buffer Pool.
shown as page
mysql.innodb.buffer_pool_utilization
(gauge)
The utilization of the InnoDB Buffer Pool.
shown as fraction
mysql.innodb.current_row_locks
(gauge)
The number of current row locks.
shown as lock
mysql.innodb.data_reads
(gauge)
The rate of data reads.
shown as read
mysql.innodb.data_writes
(gauge)
The rate of data writes.
shown as write
mysql.innodb.mutex_os_waits
(gauge)
The rate of mutex OS waits.
shown as event
mysql.innodb.mutex_spin_rounds
(gauge)
The rate of mutex spin rounds.
shown as event
mysql.innodb.mutex_spin_waits
(gauge)
The rate of mutex spin waits.
shown as event
mysql.innodb.os_log_fsyncs
(gauge)
The rate of fsync writes to the log file.
shown as write
mysql.innodb.row_lock_time
(gauge)
Fraction of time spent (ms/s) acquring row locks.
shown as fraction
mysql.innodb.row_lock_waits
(gauge)
The number of times per second a row lock had to be waited for.
shown as event
mysql.net.connections
(gauge)
The rate of connections to the server.
shown as connection
mysql.net.max_connections
(gauge)
The maximum number of connections that have been in use simultaneously since the server started.
shown as connection
mysql.performance.com_delete
(gauge)
The rate of delete statements.
shown as query
mysql.performance.com_delete_multi
(gauge)
The rate of delete-multi statements.
shown as query
mysql.performance.com_insert
(gauge)
The rate of insert statements.
shown as query
mysql.performance.com_insert_select
(gauge)
The rate of insert-select statements.
shown as query
mysql.performance.com_replace_select
(gauge)
The rate of replace-select statements.
shown as query
mysql.performance.com_select
(gauge)
The rate of select statements.
shown as query
mysql.performance.com_update
(gauge)
The rate of update statements.
shown as query
mysql.performance.com_update_multi
(gauge)
The rate of update-multi.
shown as query
mysql.performance.created_tmp_disk_tables
(gauge)
The rate of internal on-disk temporary tables created by second by the server while executing statements.
shown as table
mysql.performance.created_tmp_files
(gauge)
The rate of temporary files created by second.
shown as file
mysql.performance.created_tmp_tables
(gauge)
The rate of internal temporary tables created by second by the server while executing statements.
shown as table
mysql.performance.kernel_time
(gauge)
Percentage of CPU time spent in kernel space by MySQL.
shown as percent
mysql.performance.key_cache_utilization
(gauge)
The key cache utilization ratio.
shown as fraction
mysql.performance.open_files
(gauge)
The number of open files.
shown as file
mysql.performance.open_tables
(gauge)
The number of of tables that are open.
shown as table
mysql.performance.qcache_hits
(gauge)
The rate of query cache hits.
shown as hit
mysql.performance.questions
(gauge)
The rate of statements executed by the server.
shown as query
mysql.performance.slow_queries
(gauge)
The rate of slow queries.
shown as query
mysql.performance.table_locks_waited
(gauge)
The total number of times that a request for a table lock could not be granted immediately and a wait was needed.
shown as
mysql.performance.threads_connected
(gauge)
The number of currently open connections.
shown as connection
mysql.performance.threads_running
(gauge)
The number of threads that are not sleeping.
shown as thread
mysql.performance.user_time
(gauge)
Percentage of CPU time spent in user space by MySQL.
shown as percent
mysql.replication.seconds_behind_master
(gauge)
The lag in seconds between the master and the slave.
shown as second
mysql.replication.slave_running
(gauge)
A boolean showing if this server is a replication slave that is connected to a replication master.
shown as
mysql.replication.slaves_connected
(gauge)
Number of slaves connected to a replication master.
shown as
mysql.performance.queries
(gauge)
The rate of queries.
shown as query

The check does not collect all metrics by default. Set the following boolean configuration options to true to enable its metrics:

extra_status_metrics adds the following metrics:

Metric nameMetric type
mysql.binlog.cache_disk_useGAUGE
mysql.binlog.cache_useGAUGE
mysql.performance.handler_commitRATE
mysql.performance.handler_deleteRATE
mysql.performance.handler_prepareRATE
mysql.performance.handler_read_firstRATE
mysql.performance.handler_read_keyRATE
mysql.performance.handler_read_nextRATE
mysql.performance.handler_read_prevRATE
mysql.performance.handler_read_rndRATE
mysql.performance.handler_read_rnd_nextRATE
mysql.performance.handler_rollbackRATE
mysql.performance.handler_updateRATE
mysql.performance.handler_writeRATE
mysql.performance.opened_tablesRATE
mysql.performance.qcache_total_blocksGAUGE
mysql.performance.qcache_free_blocksGAUGE
mysql.performance.qcache_free_memoryGAUGE
mysql.performance.qcache_not_cachedRATE
mysql.performance.qcache_queries_in_cacheGAUGE
mysql.performance.select_full_joinRATE
mysql.performance.select_full_range_joinRATE
mysql.performance.select_rangeRATE
mysql.performance.select_range_checkRATE
mysql.performance.select_scanRATE
mysql.performance.sort_merge_passesRATE
mysql.performance.sort_rangeRATE
mysql.performance.sort_rowsRATE
mysql.performance.sort_scanRATE
mysql.performance.table_locks_immediateGAUGE
mysql.performance.table_locks_immediate.rateRATE
mysql.performance.threads_cachedGAUGE
mysql.performance.threads_createdMONOTONIC

extra_innodb_metrics adds the following metrics:

Metric nameMetric type
mysql.innodb.active_transactionsGAUGE
mysql.innodb.buffer_pool_dataGAUGE
mysql.innodb.buffer_pool_pages_dataGAUGE
mysql.innodb.buffer_pool_pages_dirtyGAUGE
mysql.innodb.buffer_pool_pages_flushedRATE
mysql.innodb.buffer_pool_pages_freeGAUGE
mysql.innodb.buffer_pool_pages_totalGAUGE
mysql.innodb.buffer_pool_read_aheadRATE
mysql.innodb.buffer_pool_read_ahead_evictedRATE
mysql.innodb.buffer_pool_read_ahead_rndGAUGE
mysql.innodb.buffer_pool_wait_freeMONOTONIC
mysql.innodb.buffer_pool_write_requestsRATE
mysql.innodb.checkpoint_ageGAUGE
mysql.innodb.current_transactionsGAUGE
mysql.innodb.data_fsyncsRATE
mysql.innodb.data_pending_fsyncsGAUGE
mysql.innodb.data_pending_readsGAUGE
mysql.innodb.data_pending_writesGAUGE
mysql.innodb.data_readRATE
mysql.innodb.data_writtenRATE
mysql.innodb.dblwr_pages_writtenRATE
mysql.innodb.dblwr_writesRATE
mysql.innodb.hash_index_cells_totalGAUGE
mysql.innodb.hash_index_cells_usedGAUGE
mysql.innodb.history_list_lengthGAUGE
mysql.innodb.ibuf_free_listGAUGE
mysql.innodb.ibuf_mergedRATE
mysql.innodb.ibuf_merged_delete_marksRATE
mysql.innodb.ibuf_merged_deletesRATE
mysql.innodb.ibuf_merged_insertsRATE
mysql.innodb.ibuf_mergesRATE
mysql.innodb.ibuf_segment_sizeGAUGE
mysql.innodb.ibuf_sizeGAUGE
mysql.innodb.lock_structsRATE
mysql.innodb.locked_tablesGAUGE
mysql.innodb.locked_transactionsGAUGE
mysql.innodb.log_waitsRATE
mysql.innodb.log_write_requestsRATE
mysql.innodb.log_writesRATE
mysql.innodb.lsn_currentRATE
mysql.innodb.lsn_flushedRATE
mysql.innodb.lsn_last_checkpointRATE
mysql.innodb.mem_adaptive_hashGAUGE
mysql.innodb.mem_additional_poolGAUGE
mysql.innodb.mem_dictionaryGAUGE
mysql.innodb.mem_file_systemGAUGE
mysql.innodb.mem_lock_systemGAUGE
mysql.innodb.mem_page_hashGAUGE
mysql.innodb.mem_recovery_systemGAUGE
mysql.innodb.mem_thread_hashGAUGE
mysql.innodb.mem_totalGAUGE
mysql.innodb.os_file_fsyncsRATE
mysql.innodb.os_file_readsRATE
mysql.innodb.os_file_writesRATE
mysql.innodb.os_log_pending_fsyncsGAUGE
mysql.innodb.os_log_pending_writesGAUGE
mysql.innodb.os_log_writtenRATE
mysql.innodb.pages_createdRATE
mysql.innodb.pages_readRATE
mysql.innodb.pages_writtenRATE
mysql.innodb.pending_aio_log_iosGAUGE
mysql.innodb.pending_aio_sync_iosGAUGE
mysql.innodb.pending_buffer_pool_flushesGAUGE
mysql.innodb.pending_checkpoint_writesGAUGE
mysql.innodb.pending_ibuf_aio_readsGAUGE
mysql.innodb.pending_log_flushesGAUGE
mysql.innodb.pending_log_writesGAUGE
mysql.innodb.pending_normal_aio_readsGAUGE
mysql.innodb.pending_normal_aio_writesGAUGE
mysql.innodb.queries_insideGAUGE
mysql.innodb.queries_queuedGAUGE
mysql.innodb.read_viewsGAUGE
mysql.innodb.rows_deletedRATE
mysql.innodb.rows_insertedRATE
mysql.innodb.rows_readRATE
mysql.innodb.rows_updatedRATE
mysql.innodb.s_lock_os_waitsRATE
mysql.innodb.s_lock_spin_roundsRATE
mysql.innodb.s_lock_spin_waitsRATE
mysql.innodb.semaphore_wait_timeGAUGE
mysql.innodb.semaphore_waitsGAUGE
mysql.innodb.tables_in_useGAUGE
mysql.innodb.x_lock_os_waitsRATE
mysql.innodb.x_lock_spin_roundsRATE
mysql.innodb.x_lock_spin_waitsRATE

extra_performance_metrics adds the following metrics:

Metric nameMetric type
mysql.performance.query_run_time.avgGAUGE
mysql.performance.digest_95th_percentile.avg_usGAUGE

schema_size_metrics adds the following metric:

Metric nameMetric type
mysql.info.schema.sizeGAUGE

Events

The MySQL check does not include any event at this time.

Service Checks

mysql.replication.slave_running:

Returns CRITICAL for a slave that’s not running, otherwise OK.

mysql.can_connect:

Returns CRITICAL if the Agent cannot connect to MySQL to collect metrics, otherwise OK.

Troubleshooting

You may observe one of these common problems in the output of the Datadog Agent’s info subcommand.

Agent cannot authenticate

    mysql
    -----
      - instance #0 [ERROR]: '(1045, u"Access denied for user \'datadog\'@\'localhost\' (using password: YES)")'
      - Collected 0 metrics, 0 events & 1 service check

Either the 'datadog'@'localhost' user doesn’t exist or the Agent is not configured with correct credentials. Review the Configuration section to add a user, and review the Agent’s mysql.yaml.

Database user lacks privileges

    mysql
    -----
      - instance #0 [WARNING]
          Warning: Privilege error or engine unavailable accessing the INNODB status                          tables (must grant PROCESS): (1227, u'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation')
      - Collected 21 metrics, 0 events & 1 service check

The Agent can authenticate, but it lacks privileges for one or more metrics it wants to collect. In this case, it lacks the PROCESS privilege:

mysql> select user,host,process_priv from mysql.user where user='datadog';
+---------+-----------+--------------+
| user    | host      | process_priv |
+---------+-----------+--------------+
| datadog | localhost | N            |
+---------+-----------+--------------+
1 row in set (0.00 sec)

Review the Configuration section and grant the datadog user all necessary privileges. Do NOT grant all privileges on all databases to this user.

Further Reading

Read our series of blog posts about monitoring MySQL with Datadog.