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+) or SHOW REPLICA STATUS; (MySQL 8.0.22+)
• 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.

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.

Setup

Prerequisites

Create netdata user

A user account should have the following permissions:

• USAGE
• REPLICATION CLIENT
• PROCESS

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.

Config options

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

Examples

TCP socket

An example configuration.

Config

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

Unix socket

An example configuration.

Config

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

Connection with password

An example configuration.

Config

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

my.cnf

An example configuration.

Config

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.

Config

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

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

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