MySQL icon

MySQL

MySQL

Plugin: go.d.plugin Module: mysql

Overview

This collector monitors the health and performance of MySQL servers and collects general statistics, replication and user metrics.

It connects to the MySQL instance via a TCP or UNIX socket and executes the following commands:

Executed queries:

  • SELECT VERSION();
  • SHOW GLOBAL STATUS;
  • SHOW GLOBAL VARIABLES;
  • SHOW SLAVE STATUS; or SHOW ALL SLAVES STATUS; (MariaDBv10.2+)
  • SHOW USER_STATISTICS; (MariaDBv10.1.1+)
  • SELECT TIME,USER FROM INFORMATION_SCHEMA.PROCESSLIST;

This collector is supported on all platforms.

This collector supports collecting metrics from multiple instances of this integration, including remote instances.

Default Behavior

Auto-Detection

By default, it detects instances running on localhost by trying to connect as root and netdata using known MySQL TCP and UNIX sockets:

  • /var/run/mysqld/mysqld.sock
  • /var/run/mysqld/mysql.sock
  • /var/lib/mysql/mysql.sock
  • /tmp/mysql.sock
  • 127.0.0.1:3306
  • “[::1]:3306”

Limits

The default configuration for this integration does not impose any limits on data collection.

Performance Impact

The default configuration for this integration is not expected to impose a significant performance impact on the system.

Setup

Prerequisites

Create netdata user

A user account should have the following permissions:

To create the netdata user with these permissions, execute the following in the MySQL shell:

CREATE USER 'netdata'@'localhost';
GRANT USAGE, REPLICATION CLIENT, PROCESS ON *.* TO 'netdata'@'localhost';
FLUSH PRIVILEGES;

The netdata user will have the ability to connect to the MySQL server on localhost without a password. It will only be able to gather statistics without being able to alter or affect operations in any way.

Configuration

File

The configuration file name for this integration is go.d/mysql.conf.

You can edit the configuration file using the edit-config script from the Netdata config directory.

cd /etc/netdata 2>/dev/null || cd /opt/netdata/etc/netdata
sudo ./edit-config go.d/mysql.conf

Options

The following options can be defined globally: update_every, autodetection_retry.

Name Description Default Required
update_every Data collection frequency. 5 False
autodetection_retry Recheck interval in seconds. Zero means no recheck will be scheduled. 0 False
dsn MySQL server DSN (Data Source Name). See DSN syntax. root@tcp(localhost:3306)/ True
my.cnf Specifies the my.cnf file to read the connection settings from the [client] section. False
timeout Query timeout in seconds. 1 False

Examples

TCP socket

An example configuration.

jobs:
  - name: local
    dsn: netdata@tcp(127.0.0.1:3306)/
Unix socket

An example configuration.

jobs:
  - name: local
    dsn: netdata@unix(/var/lib/mysql/mysql.sock)/
Connection with password

An example configuration.

jobs:
  - name: local
    dsn: netconfig:password@tcp(127.0.0.1:3306)/
my.cnf

An example configuration.

jobs:
  - name: local
    my.cnf: '/etc/my.cnf'
Multi-instance

Note: When you define multiple jobs, their names must be unique.

Local and remote instances.

jobs:
  - name: local
    dsn: netdata@tcp(127.0.0.1:3306)/

  - name: remote
    dsn: netconfig:password@tcp(203.0.113.0:3306)/

Metrics

Metrics grouped by scope.

The scope defines the instance that the metric belongs to. An instance is uniquely identified by a set of labels.

Per MariaDB instance

These metrics refer to the entire monitored application.

This scope has no labels.

Metrics:

Metric Dimensions Unit MySQL MariaDB Percona
mysql.net in, out kilobits/s • • •
mysql.queries queries, questions, slow_queries queries/s • • •
mysql.queries_type select, delete, update, insert, replace queries/s • • •
mysql.handlers commit, delete, prepare, read_first, read_key, read_next, read_prev, read_rnd, read_rnd_next, rollback, savepoint, savepointrollback, update, write handlers/s • • •
mysql.table_open_cache_overflows open_cache overflows/s • • •
mysql.table_locks immediate, waited locks/s • • •
mysql.join_issues full_join, full_range_join, range, range_check, scan joins/s • • •
mysql.sort_issues merge_passes, range, scan issues/s • • •
mysql.tmp disk_tables, files, tables events/s • • •
mysql.connections all, aborted connections/s • • •
mysql.connections_active active, limit, max_active connections • • •
mysql.threads connected, cached, running threads • • •
mysql.threads_created created threads/s • • •
mysql.thread_cache_misses misses misses • • •
mysql.innodb_io read, write KiB/s • • •
mysql.innodb_io_ops reads, writes, fsyncs operations/s • • •
mysql.innodb_io_pending_ops reads, writes, fsyncs operations • • •
mysql.innodb_log waits, write_requests, writes operations/s • • •
mysql.innodb_cur_row_lock current waits operations • • •
mysql.innodb_rows inserted, read, updated, deleted operations/s • • •
mysql.innodb_buffer_pool_pages data, dirty, free, misc, total pages • • •
mysql.innodb_buffer_pool_pages_flushed flush_pages requests/s • • •
mysql.innodb_buffer_pool_bytes data, dirty MiB • • •
mysql.innodb_buffer_pool_read_ahead all, evicted pages/s • • •
mysql.innodb_buffer_pool_read_ahead_rnd read-ahead operations/s • • •
mysql.innodb_buffer_pool_ops disk_reads, wait_free operations/s • • •
mysql.innodb_os_log fsyncs, writes operations • • •
mysql.innodb_os_log_fsync_writes fsyncs operations/s • • •
mysql.innodb_os_log_io write KiB/s • • •
mysql.innodb_deadlocks deadlocks operations/s • • •
mysql.files files files • • •
mysql.files_rate files files/s • • •
mysql.connection_errors accept, internal, max, peer_addr, select, tcpwrap errors/s • • •
mysql.opened_tables tables tables/s • • •
mysql.open_tables cache, tables tables • • •
mysql.process_list_fetch_query_duration duration milliseconds • • •
mysql.process_list_queries_count system, user queries • • •
mysql.process_list_longest_query_duration duration seconds • • •
mysql.qcache_ops hits, lowmem_prunes, inserts, not_cached queries/s • • •
mysql.qcache queries queries • • •
mysql.qcache_freemem free MiB • • •
mysql.qcache_memblocks free, total blocks • • •
mysql.galera_writesets rx, tx writesets/s • • •
mysql.galera_bytes rx, tx KiB/s • • •
mysql.galera_queue rx, tx writesets • • •
mysql.galera_conflicts bf_aborts, cert_fails transactions • • •
mysql.galera_flow_control paused ms • • •
mysql.galera_cluster_status primary, non_primary, disconnected status • • •
mysql.galera_cluster_state undefined, joining, donor, joined, synced, error state • • •
mysql.galera_cluster_size nodes nodes • • •
mysql.galera_cluster_weight weight weight • • •
mysql.galera_connected connected boolean • • •
mysql.galera_ready ready boolean • • •
mysql.galera_open_transactions open transactions • • •
mysql.galera_thread_count threads threads • • •
mysql.key_blocks unused, used, not_flushed blocks • • •
mysql.key_requests reads, writes requests/s • • •
mysql.key_disk_ops reads, writes operations/s • • •
mysql.binlog_cache disk, all transactions/s • • •
mysql.binlog_stmt_cache disk, all statements/s • • •

Per connection

These metrics refer to the replication connection.

This scope has no labels.

Metrics:

Metric Dimensions Unit MySQL MariaDB Percona
mysql.slave_behind seconds seconds • • •
mysql.slave_status sql_running, io_running boolean • • •

Per user

These metrics refer to the MySQL user.

Labels:

Label Description
user username

Metrics:

Metric Dimensions Unit MySQL MariaDB Percona
mysql.userstats_cpu used percentage • •
mysql.userstats_rows read, sent, updated, inserted, deleted operations/s • •
mysql.userstats_commands select, update, other commands/s • •
mysql.userstats_denied_commands denied commands/s • •
mysql.userstats_created_transactions commit, rollback transactions/s • •
mysql.userstats_binlog_written written B/s • •
mysql.userstats_empty_queries empty queries/s • •
mysql.userstats_connections created connections/s • •
mysql.userstats_lost_connections lost connections/s • •
mysql.userstats_denied_connections denied connections/s • •

Alerts

The following alerts are available:

Alert name On metric Description
mysql_10s_slow_queries mysql.queries number of slow queries in the last 10 seconds
mysql_10s_table_locks_immediate mysql.table_locks number of table immediate locks in the last 10 seconds
mysql_10s_table_locks_waited mysql.table_locks number of table waited locks in the last 10 seconds
mysql_10s_waited_locks_ratio mysql.table_locks ratio of waited table locks over the last 10 seconds
mysql_connections mysql.connections_active client connections utilization
mysql_replication mysql.slave_status replication status (0: stopped, 1: working)
mysql_replication_lag mysql.slave_behind difference between the timestamp of the latest transaction processed by the SQL thread and the timestamp of the same transaction when it was processed on the master
mysql_galera_cluster_size_max_2m mysql.galera_cluster_size maximum galera cluster size in the last 2 minutes starting one minute ago
mysql_galera_cluster_size mysql.galera_cluster_size current galera cluster size, compared to the maximum size in the last 2 minutes
mysql_galera_cluster_state_warn mysql.galera_cluster_state galera node state is either Donor/Desynced or Joined
mysql_galera_cluster_state_crit mysql.galera_cluster_state galera node state is either Undefined or Joining or Error
mysql_galera_cluster_status mysql.galera_cluster_status galera node is part of a nonoperational component. This occurs in cases of multiple membership changes that result in a loss of Quorum or in cases of split-brain situations.

Troubleshooting

Debug Mode

To troubleshoot issues with the mysql collector, run the go.d.plugin with the debug option enabled. The output should give you clues as to why the collector isn’t working.

  • Navigate to the plugins.d directory, usually at /usr/libexec/netdata/plugins.d/. If that’s not the case on your system, open netdata.conf and look for the plugins setting under [directories].

    cd /usr/libexec/netdata/plugins.d/

  • Switch to the netdata user.

    sudo -u netdata -s

  • Run the go.d.plugin to debug the collector:

    ./go.d.plugin -d -m mysql

Get Netdata

Sign up for free

Want to see a demonstration of Netdata for multiple use cases?

Go to Live Demo