Network Performance Monitoring is now generally available! Network Monitoring is now available!

MySQL

Agent Check Agent Check

Supported OS: Linux Mac OS Windows

MySQL Dashboard

Overview

The Datadog Agent can collect many metrics from MySQL databases, including (but not limited to):

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

You can also create your own metrics using custom SQL queries.

Note: This integration is also compatible with MariaDB, as it serves as a “drop-in replacement” for MySQL.

Setup

Installation

The MySQL check is included in the Datadog Agent package. No additional installation is needed on your MySQL server.

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)

For mySQL 8.0+ create the datadog user with the native password hashing method:

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

Note: @'localhost' is only for local connections - use the hostname/IP of your Agent for remote connections. For more information, see the MySQL documentation.

Verify the user was created successfully using the following commands - replace <UNIQUEPASSWORD> with the password you created 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 the user the following limited privileges ONLY:

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)

For mySQL 8.0+ set max_user_connections with:

mysql> ALTER USER 'datadog'@'localhost' WITH MAX_USER_CONNECTIONS 5;
Query OK, 0 rows affected (0.00 sec)

If enabled, metrics can be collected from the performance_schema database by granting an additional privilege:

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)

Configuration

Follow the instructions below to configure this check for an Agent running on a host. For containerized environments, see the Containerized section.

Host

Edit the mysql.d/conf.yaml file, in the conf.d/ folder at the root of your Agent’s configuration directory to start collecting your MySQL metrics and logs. See the sample mysql.d/conf.yaml for all available configuration options.

Metric collection
  • Add this configuration block to your mysql.d/conf.yaml to collect your MySQL metrics:
  init_config:

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

Note: Wrap your password in single quotes in case a special character is present.

To collect extra_performance_metrics, your MySQL server must have performance_schema enabled - otherwise set extra_performance_metrics to false. For more information on performance_schema, see the MySQL documentation.

Note that the datadog user should be set up in the MySQL integration configuration as host: 127.0.0.1 instead of localhost. Alternatively, you may also use sock.

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.

Log collection

Available for Agent >6.0

  1. By default MySQL logs everything in /var/log/syslog which requires root access to read. To make the logs more accessible, follow these steps:

    • Edit /etc/mysql/conf.d/mysqld_safe_syslog.cnf and remove or comment the lines.
    • Edit /etc/mysql/my.cnf and add following lines to enable general, error, and slow query logs:
        [mysqld_safe]
        log_error=/var/log/mysql/mysql_error.log
        [mysqld]
        general_log = on
        general_log_file = /var/log/mysql/mysql.log
        log_error=/var/log/mysql/mysql_error.log
        slow_query_log = on
        slow_query_log_file = /var/log/mysql/mysql-slow.log
        long_query_time = 2
    
    • Save the file and restart MySQL using following commands: service mysql restart
    • Make sure the Agent has read access on the /var/log/mysql directory and all of the files within. Double-check your logrotate configuration to make sure those files are taken into account and that the permissions are correctly set there as well.
    • In /etc/logrotate.d/mysql-server there should be something similar to:
        /var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
                daily
                rotate 7
                missingok
                create 644 mysql adm
                Compress
        }
    
  2. Collecting logs is disabled by default in the Datadog Agent, enable it in your datadog.yaml file:

      logs_enabled: true
  3. Add this configuration block to your mysql.d/conf.yaml file to start collecting your MySQL logs:

      logs:
          - type: file
            path: /var/log/mysql/mysql_error.log
            source: mysql
            sourcecategory: database
            service: myapplication
    
          - type: file
            path: /var/log/mysql/mysql-slow.log
            source: mysql
            sourcecategory: database
            service: myapplication
    
          - type: file
            path: /var/log/mysql/mysql.log
            source: mysql
            sourcecategory: database
            service: myapplication
            # For multiline logs, if they start by the date with the format yyyy-mm-dd uncomment the following processing rule
            # log_processing_rules:
            #   - type: multi_line
            #     name: new_log_start_with_date
            #     pattern: \d{4}\-(0?[1-9]|1[012])\-(0?[1-9]|[12][0-9]|3[01])

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

  4. Restart the Agent.

Containerized

For containerized environments, see the Autodiscovery Integration Templates for guidance on applying the parameters below.

Metric collection
ParameterValue
<INTEGRATION_NAME>mysql
<INIT_CONFIG>blank or {}
<INSTANCE_CONFIG>{"server": "%%host%%", "user": "datadog","pass": "<UNIQUEPASSWORD>"}

See the Autodiscovery template variables documentation to learn how to pass <UNIQUEPASSWORD> as an Environment variable instead of a label.

Log collection

Available for Agent v6.5+

Collecting logs is disabled by default in the Datadog Agent. To enable it, see Docker log collection.

ParameterValue
<LOG_CONFIG>{"source": "mysql", "service": "mysql"}

Validation

Run the Agent’s status subcommand and look for mysql under the Checks section.

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) acquiring 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.net.max_connections_available
(gauge)
The maximum permitted number of simultaneous client connections.
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.
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.
mysql.replication.slaves_connected
(gauge)
Number of slaves connected to a replication master.
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 the respective 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 events.

Service Checks

mysql.replication.slave_running:
Returns CRITICAL if the Agent is unable to connect to the monitored MySQL instance, otherwise returns OK. See this for more details.

mysql.can_connect:
Returns CRITICAL if the Agent cannot connect to MySQL to collect metrics, otherwise returns OK.

Troubleshooting

Further Reading

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


Mistake in the docs? Feel free to contribute!