diff options
Diffstat (limited to '')
| -rw-r--r-- | src/go/collectors/go.d.plugin/modules/mysql/integrations/percona_mysql.md | 370 |
1 files changed, 370 insertions, 0 deletions
diff --git a/src/go/collectors/go.d.plugin/modules/mysql/integrations/percona_mysql.md b/src/go/collectors/go.d.plugin/modules/mysql/integrations/percona_mysql.md new file mode 100644 index 000000000..fcd2f1304 --- /dev/null +++ b/src/go/collectors/go.d.plugin/modules/mysql/integrations/percona_mysql.md @@ -0,0 +1,370 @@ +<!--startmeta +custom_edit_url: "https://github.com/netdata/netdata/edit/master/src/go/collectors/go.d.plugin/modules/mysql/integrations/percona_mysql.md" +meta_yaml: "https://github.com/netdata/netdata/edit/master/src/go/collectors/go.d.plugin/modules/mysql/metadata.yaml" +sidebar_label: "Percona MySQL" +learn_status: "Published" +learn_rel_path: "Collecting Metrics/Databases" +most_popular: False +message: "DO NOT EDIT THIS FILE DIRECTLY, IT IS GENERATED BY THE COLLECTOR'S metadata.yaml FILE" +endmeta--> + +# Percona MySQL + + +<img src="https://netdata.cloud/img/percona.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 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 ](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](https://github.com/netdata/netdata/blob/master/docs/netdata-agent/configuration.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><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><summary>Config</summary> + +```yaml +jobs: + - name: local + dsn: netdata@tcp(127.0.0.1:3306)/ + +``` +</details> + +##### Unix socket + +An example configuration. + +<details><summary>Config</summary> + +```yaml +jobs: + - name: local + dsn: netdata@unix(/var/lib/mysql/mysql.sock)/ + +``` +</details> + +##### Connection with password + +An example configuration. + +<details><summary>Config</summary> + +```yaml +jobs: + - name: local + dsn: netconfig:password@tcp(127.0.0.1:3306)/ + +``` +</details> + +##### my.cnf + +An example configuration. + +<details><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><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 + +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 + ``` + + |