Skip to main content

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:

MetricDimensionsUnitMySQLMariaDBPercona
mysql.netin, outkilobits/s
mysql.queriesqueries, questions, slow_queriesqueries/s
mysql.queries_typeselect, delete, update, insert, replacequeries/s
mysql.handlerscommit, delete, prepare, read_first, read_key, read_next, read_prev, read_rnd, read_rnd_next, rollback, savepoint, savepointrollback, update, writehandlers/s
mysql.table_open_cache_overflowsopen_cacheoverflows/s
mysql.table_locksimmediate, waitedlocks/s
mysql.join_issuesfull_join, full_range_join, range, range_check, scanjoins/s
mysql.sort_issuesmerge_passes, range, scanissues/s
mysql.tmpdisk_tables, files, tablesevents/s
mysql.connectionsall, abortedconnections/s
mysql.connections_activeactive, limit, max_activeconnections
mysql.threadsconnected, cached, runningthreads
mysql.threads_createdcreatedthreads/s
mysql.thread_cache_missesmissesmisses
mysql.innodb_ioread, writeKiB/s
mysql.innodb_io_opsreads, writes, fsyncsoperations/s
mysql.innodb_io_pending_opsreads, writes, fsyncsoperations
mysql.innodb_logwaits, write_requests, writesoperations/s
mysql.innodb_cur_row_lockcurrent waitsoperations
mysql.innodb_rowsinserted, read, updated, deletedoperations/s
mysql.innodb_buffer_pool_pagesdata, dirty, free, misc, totalpages
mysql.innodb_buffer_pool_pages_flushedflush_pagesrequests/s
mysql.innodb_buffer_pool_bytesdata, dirtyMiB
mysql.innodb_buffer_pool_read_aheadall, evictedpages/s
mysql.innodb_buffer_pool_read_ahead_rndread-aheadoperations/s
mysql.innodb_buffer_pool_opsdisk_reads, wait_freeoperations/s
mysql.innodb_os_logfsyncs, writesoperations
mysql.innodb_os_log_fsync_writesfsyncsoperations/s
mysql.innodb_os_log_iowriteKiB/s
mysql.innodb_deadlocksdeadlocksoperations/s
mysql.filesfilesfiles
mysql.files_ratefilesfiles/s
mysql.connection_errorsaccept, internal, max, peer_addr, select, tcpwraperrors/s
mysql.opened_tablestablestables/s
mysql.open_tablescache, tablestables
mysql.process_list_fetch_query_durationdurationmilliseconds
mysql.process_list_queries_countsystem, userqueries
mysql.process_list_longest_query_durationdurationseconds
mysql.qcache_opshits, lowmem_prunes, inserts, not_cachedqueries/s
mysql.qcachequeriesqueries
mysql.qcache_freememfreeMiB
mysql.qcache_memblocksfree, totalblocks
mysql.galera_writesetsrx, txwritesets/s
mysql.galera_bytesrx, txKiB/s
mysql.galera_queuerx, txwritesets
mysql.galera_conflictsbf_aborts, cert_failstransactions
mysql.galera_flow_controlpausedms
mysql.galera_cluster_statusprimary, non_primary, disconnectedstatus
mysql.galera_cluster_stateundefined, joining, donor, joined, synced, errorstate
mysql.galera_cluster_sizenodesnodes
mysql.galera_cluster_weightweightweight
mysql.galera_connectedconnectedboolean
mysql.galera_readyreadyboolean
mysql.galera_open_transactionsopentransactions
mysql.galera_thread_countthreadsthreads
mysql.key_blocksunused, used, not_flushedblocks
mysql.key_requestsreads, writesrequests/s
mysql.key_disk_opsreads, writesoperations/s
mysql.binlog_cachedisk, alltransactions/s
mysql.binlog_stmt_cachedisk, allstatements/s

Per connection

These metrics refer to the replication connection.

This scope has no labels.

Metrics:

MetricDimensionsUnitMySQLMariaDBPercona
mysql.slave_behindsecondsseconds
mysql.slave_statussql_running, io_runningboolean

Per user

These metrics refer to the MySQL user.

Labels:

LabelDescription
userusername

Metrics:

MetricDimensionsUnitMySQLMariaDBPercona
mysql.userstats_cpuusedpercentage
mysql.userstats_rowsread, sent, updated, inserted, deletedoperations/s
mysql.userstats_commandsselect, update, othercommands/s
mysql.userstats_denied_commandsdeniedcommands/s
mysql.userstats_created_transactionscommit, rollbacktransactions/s
mysql.userstats_binlog_writtenwrittenB/s
mysql.userstats_empty_queriesemptyqueries/s
mysql.userstats_connectionscreatedconnections/s
mysql.userstats_lost_connectionslostconnections/s
mysql.userstats_denied_connectionsdeniedconnections/s

Alerts

The following alerts are available:

Alert nameOn metricDescription
mysql_10s_slow_queries mysql.queriesnumber of slow queries in the last 10 seconds
mysql_10s_table_locks_immediate mysql.table_locksnumber of table immediate locks in the last 10 seconds
mysql_10s_table_locks_waited mysql.table_locksnumber of table waited locks in the last 10 seconds
mysql_10s_waited_locks_ratio mysql.table_locksratio of waited table locks over the last 10 seconds
mysql_connections mysql.connections_activeclient connections utilization
mysql_replication mysql.slave_statusreplication status (0: stopped, 1: working)
mysql_replication_lag mysql.slave_behinddifference 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_sizemaximum galera cluster size in the last 2 minutes starting one minute ago
mysql_galera_cluster_size mysql.galera_cluster_sizecurrent galera cluster size, compared to the maximum size in the last 2 minutes
mysql_galera_cluster_state_warn mysql.galera_cluster_stategalera node state is either Donor/Desynced or Joined
mysql_galera_cluster_state_crit mysql.galera_cluster_stategalera node state is either Undefined or Joining or Error
mysql_galera_cluster_status mysql.galera_cluster_statusgalera 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:

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
NameDescriptionDefaultRequired
update_everyData collection frequency.5no
autodetection_retryRecheck interval in seconds. Zero means no recheck will be scheduled.0no
dsnMySQL server DSN (Data Source Name). See DSN syntax.root@tcp(localhost:3306)/yes
my.cnfSpecifies the my.cnf file to read the connection settings from the [client] section.no
timeoutQuery timeout in seconds.1no

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

Do you have any feedback for this page? If so, you can open a new issue on our netdata/learn repository.