src/go/plugin/go.d/modules/mysql/integrations/mariadb.md
<!--startmeta
custom_edit_url: "https://github.com/netdata/netdata/edit/master/src/go/plugin/go.d/modules/mysql/integrations/mariadb.md"
meta_yaml: "https://github.com/netdata/netdata/edit/master/src/go/plugin/go.d/modules/mysql/metadata.yaml"
sidebar_label: "MariaDB"
learn_status: "Published"
learn_rel_path: "Collecting Metrics/Databases"
most_popular: True
message: "DO NOT EDIT THIS FILE DIRECTLY, IT IS GENERATED BY THE COLLECTOR'S metadata.yaml FILE"
endmeta-->
# MariaDB
<img src="https://netdata.cloud/img/mariadb.svg" width="150"/>
Plugin: go.d.plugin
Module: mysql
<img src="https://img.shields.io/badge/maintained%20by-Netdata-%2300ab44" />
## 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 sockets:
- 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 ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | mysql.queries | number of slow queries in the last 10 seconds |
| [ mysql_10s_table_locks_immediate ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | mysql.table_locks | number of table immediate locks in the last 10 seconds |
| [ mysql_10s_table_locks_waited ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | mysql.table_locks | number of table waited locks in the last 10 seconds |
| [ mysql_10s_waited_locks_ratio ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | mysql.table_locks | ratio of waited table locks over the last 10 seconds |
| [ mysql_connections ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | mysql.connections_active | client connections utilization |
| [ mysql_replication ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | mysql.slave_status | replication status (0: stopped, 1: working) |
| [ mysql_replication_lag ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | 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 ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | mysql.galera_cluster_size | maximum galera cluster size in the last 2 minutes starting one minute ago |
| [ mysql_galera_cluster_size ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | mysql.galera_cluster_size | current galera cluster size, compared to the maximum size in the last 2 minutes |
| [ mysql_galera_cluster_state_warn ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | mysql.galera_cluster_state | galera node state is either Donor/Desynced or Joined |
| [ mysql_galera_cluster_state_crit ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | mysql.galera_cluster_state | galera node state is either Undefined or Joining or Error |
| [ mysql_galera_cluster_status ](https://github.com/netdata/netdata/blob/master/src/health/health.d/mysql.conf) | 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](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html):
- [`USAGE`](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_usage)
- [`REPLICATION CLIENT`](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-client)
- [`PROCESS`](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_process)
To create the `netdata` user with these permissions, execute the following in the MySQL shell:
```mysql
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](/docs/netdata-agent/configuration/README.md#the-netdata-config-directory).
```bash
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.
<details open><summary>Config options</summary>
| 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](https://github.com/go-sql-driver/mysql#dsn-data-source-name). | 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 |
</details>
#### Examples
##### TCP socket
An example configuration.
<details open><summary>Config</summary>
```yaml
jobs:
- name: local
dsn: netdata@tcp(127.0.0.1:3306)/
```
</details>
##### Unix socket
An example configuration.
<details open><summary>Config</summary>
```yaml
jobs:
- name: local
dsn: netdata@unix(/var/lib/mysql/mysql.sock)/
```
</details>
##### Connection with password
An example configuration.
<details open><summary>Config</summary>
```yaml
jobs:
- name: local
dsn: netconfig:password@tcp(127.0.0.1:3306)/
```
</details>
##### my.cnf
An example configuration.
<details open><summary>Config</summary>
```yaml
jobs:
- name: local
my.cnf: '/etc/my.cnf'
```
</details>
##### Multi-instance
> **Note**: When you define multiple jobs, their names must be unique.
Local and remote instances.
<details open><summary>Config</summary>
```yaml
jobs:
- name: local
dsn: netdata@tcp(127.0.0.1:3306)/
- name: remote
dsn: netconfig:password@tcp(203.0.113.0:3306)/
```
</details>
## Troubleshooting
### Debug Mode
**Important**: Debug mode is not supported for data collection jobs created via the UI using the Dyncfg feature.
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]`.
```bash
cd /usr/libexec/netdata/plugins.d/
```
- Switch to the `netdata` user.
```bash
sudo -u netdata -s
```
- Run the `go.d.plugin` to debug the collector:
```bash
./go.d.plugin -d -m mysql
```
### Getting Logs
If you're encountering problems with the `mysql` collector, follow these steps to retrieve logs and identify potential issues:
- **Run the command** specific to your system (systemd, non-systemd, or Docker container).
- **Examine the output** for any warnings or error messages that might indicate issues. These messages should provide clues about the root cause of the problem.
#### System with systemd
Use the following command to view logs generated since the last Netdata service restart:
```bash
journalctl _SYSTEMD_INVOCATION_ID="$(systemctl show --value --property=InvocationID netdata)" --namespace=netdata --grep mysql
```
#### System without systemd
Locate the collector log file, typically at `/var/log/netdata/collector.log`, and use `grep` to filter for collector's name:
```bash
grep mysql /var/log/netdata/collector.log
```
**Note**: This method shows logs from all restarts. Focus on the **latest entries** for troubleshooting current issues.
#### Docker Container
If your Netdata runs in a Docker container named "netdata" (replace if different), use this command:
```bash
docker logs netdata 2>&1 | grep mysql
```