Logging is here!

MySQL

Agent Check Agent Check

Supported OS: Linux Mac OS Windows

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.

Setup

Installation

The MySQL check is included in the Datadog Agent package, so you don’t need to install anything else on your MySQL servers.

Configuration

Edit conf.d/mysql.d/conf.yaml in the root of your Agent’s configuration directory in order to connect the Agent to your MySQL server. You will begin collecting your MySQL metrics and logs right away. See the sample configuration file for all available configuration options.

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. For more information, see the MySQL documentation.

Verify that the user was created successfully using the following command - 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 in order 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)

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)

Metric Collection

  • Add this configuration block to your mysql.d/conf.yaml in order to start gathering 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: 1
          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.

In order to gather 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. In order 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, so you need to enable it in datadog.yaml:

    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.

Learn more about log collection in the log documentation

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) 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.
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 in order to enable the respective metrics:

extra_status_metrics adds the following metrics:

Metric name Metric type
mysql.binlog.cache_disk_use GAUGE
mysql.binlog.cache_use GAUGE
mysql.performance.handler_commit RATE
mysql.performance.handler_delete RATE
mysql.performance.handler_prepare RATE
mysql.performance.handler_read_first RATE
mysql.performance.handler_read_key RATE
mysql.performance.handler_read_next RATE
mysql.performance.handler_read_prev RATE
mysql.performance.handler_read_rnd RATE
mysql.performance.handler_read_rnd_next RATE
mysql.performance.handler_rollback RATE
mysql.performance.handler_update RATE
mysql.performance.handler_write RATE
mysql.performance.opened_tables RATE
mysql.performance.qcache_total_blocks GAUGE
mysql.performance.qcache_free_blocks GAUGE
mysql.performance.qcache_free_memory GAUGE
mysql.performance.qcache_not_cached RATE
mysql.performance.qcache_queries_in_cache GAUGE
mysql.performance.select_full_join RATE
mysql.performance.select_full_range_join RATE
mysql.performance.select_range RATE
mysql.performance.select_range_check RATE
mysql.performance.select_scan RATE
mysql.performance.sort_merge_passes RATE
mysql.performance.sort_range RATE
mysql.performance.sort_rows RATE
mysql.performance.sort_scan RATE
mysql.performance.table_locks_immediate GAUGE
mysql.performance.table_locks_immediate.rate RATE
mysql.performance.threads_cached GAUGE
mysql.performance.threads_created MONOTONIC

extra_innodb_metrics adds the following metrics:

Metric name Metric type
mysql.innodb.active_transactions GAUGE
mysql.innodb.buffer_pool_data GAUGE
mysql.innodb.buffer_pool_pages_data GAUGE
mysql.innodb.buffer_pool_pages_dirty GAUGE
mysql.innodb.buffer_pool_pages_flushed RATE
mysql.innodb.buffer_pool_pages_free GAUGE
mysql.innodb.buffer_pool_pages_total GAUGE
mysql.innodb.buffer_pool_read_ahead RATE
mysql.innodb.buffer_pool_read_ahead_evicted RATE
mysql.innodb.buffer_pool_read_ahead_rnd GAUGE
mysql.innodb.buffer_pool_wait_free MONOTONIC
mysql.innodb.buffer_pool_write_requests RATE
mysql.innodb.checkpoint_age GAUGE
mysql.innodb.current_transactions GAUGE
mysql.innodb.data_fsyncs RATE
mysql.innodb.data_pending_fsyncs GAUGE
mysql.innodb.data_pending_reads GAUGE
mysql.innodb.data_pending_writes GAUGE
mysql.innodb.data_read RATE
mysql.innodb.data_written RATE
mysql.innodb.dblwr_pages_written RATE
mysql.innodb.dblwr_writes RATE
mysql.innodb.hash_index_cells_total GAUGE
mysql.innodb.hash_index_cells_used GAUGE
mysql.innodb.history_list_length GAUGE
mysql.innodb.ibuf_free_list GAUGE
mysql.innodb.ibuf_merged RATE
mysql.innodb.ibuf_merged_delete_marks RATE
mysql.innodb.ibuf_merged_deletes RATE
mysql.innodb.ibuf_merged_inserts RATE
mysql.innodb.ibuf_merges RATE
mysql.innodb.ibuf_segment_size GAUGE
mysql.innodb.ibuf_size GAUGE
mysql.innodb.lock_structs RATE
mysql.innodb.locked_tables GAUGE
mysql.innodb.locked_transactions GAUGE
mysql.innodb.log_waits RATE
mysql.innodb.log_write_requests RATE
mysql.innodb.log_writes RATE
mysql.innodb.lsn_current RATE
mysql.innodb.lsn_flushed RATE
mysql.innodb.lsn_last_checkpoint RATE
mysql.innodb.mem_adaptive_hash GAUGE
mysql.innodb.mem_additional_pool GAUGE
mysql.innodb.mem_dictionary GAUGE
mysql.innodb.mem_file_system GAUGE
mysql.innodb.mem_lock_system GAUGE
mysql.innodb.mem_page_hash GAUGE
mysql.innodb.mem_recovery_system GAUGE
mysql.innodb.mem_thread_hash GAUGE
mysql.innodb.mem_total GAUGE
mysql.innodb.os_file_fsyncs RATE
mysql.innodb.os_file_reads RATE
mysql.innodb.os_file_writes RATE
mysql.innodb.os_log_pending_fsyncs GAUGE
mysql.innodb.os_log_pending_writes GAUGE
mysql.innodb.os_log_written RATE
mysql.innodb.pages_created RATE
mysql.innodb.pages_read RATE
mysql.innodb.pages_written RATE
mysql.innodb.pending_aio_log_ios GAUGE
mysql.innodb.pending_aio_sync_ios GAUGE
mysql.innodb.pending_buffer_pool_flushes GAUGE
mysql.innodb.pending_checkpoint_writes GAUGE
mysql.innodb.pending_ibuf_aio_reads GAUGE
mysql.innodb.pending_log_flushes GAUGE
mysql.innodb.pending_log_writes GAUGE
mysql.innodb.pending_normal_aio_reads GAUGE
mysql.innodb.pending_normal_aio_writes GAUGE
mysql.innodb.queries_inside GAUGE
mysql.innodb.queries_queued GAUGE
mysql.innodb.read_views GAUGE
mysql.innodb.rows_deleted RATE
mysql.innodb.rows_inserted RATE
mysql.innodb.rows_read RATE
mysql.innodb.rows_updated RATE
mysql.innodb.s_lock_os_waits RATE
mysql.innodb.s_lock_spin_rounds RATE
mysql.innodb.s_lock_spin_waits RATE
mysql.innodb.semaphore_wait_time GAUGE
mysql.innodb.semaphore_waits GAUGE
mysql.innodb.tables_in_use GAUGE
mysql.innodb.x_lock_os_waits RATE
mysql.innodb.x_lock_spin_rounds RATE
mysql.innodb.x_lock_spin_waits RATE

extra_performance_metrics adds the following metrics:

Metric name Metric type
mysql.performance.query_run_time.avg GAUGE
mysql.performance.digest_95th_percentile.avg_us GAUGE

schema_size_metrics adds the following metric:

Metric name Metric type
mysql.info.schema.size GAUGE

Events

The MySQL check does not include any events.

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

Further Reading

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


Mistake in the docs? Feel free to contribute!