mysql

MySQL monitoring with Netdata

MySQL is an open-source relational database management system.

This module monitors one or more MySQL servers, depending on your configuration.

Requirements

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;

User Statistics query is MariaDB specific.

MySQL user 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.

Charts

It produces the following charts:

• Bandwidth in kilobits/s
• Queries in queries/s
• Queries By Type in queries/s
• Handlers in handlers/s
• Table Locks in locks/s
• Table Select Join Issues in joins/s
• Table Sort Issues in joins/s
• Tmp Operations in events/s
• Connections in connections/s
• Active Connections in connections
• Binlog Cache in transactions/s
• Threads in threads
• Threads Creation Rate in threads/s
• Threads Cache Misses in misses
• InnoDB I/O Bandwidth in KiB/s
• InnoDB I/O Operations in operations/s
• InnoDB Pending I/O Operations in operations
• InnoDB Log Operations in operations/s
• InnoDB OS Log Pending Operations in operations
• InnoDB OS Log Operations in operations/s
• InnoDB OS Log Bandwidth in KiB/s
• InnoDB Current Row Locks in operations
• InnoDB Row Operations in operations/s
• InnoDB Buffer Pool Pages in pages
• InnoDB Buffer Pool Flush Pages Requests in requests/s
• InnoDB Buffer Pool Bytes in MiB
• InnoDB Buffer Pool Operations in operations/s
• MyISAM Key Cache Blocks in blocks
• MyISAM Key Cache Requests in requests/s
• MyISAM Key Cache Disk Operations in operations/s
• Open Files in files
• Opened Files Rate in files/s
• Binlog Statement Cache in statements/s
• Connection Errors in errors/s
• Opened Tables in tables/s
• Open Tables in tables
• Process List Fetch Duration in milliseconds
• Process List Queries Count in queries
• Process List Longest Query Duration in seconds

If Query Cache metrics are available (MariaDB and old versions of MySQL):

• QCache Operations in queries/s
• QCache Queries in Cache in queries
• QCache Free Memory in MiB
• QCache Memory Blocks in blocks

If WSRep metrics are available:

• Replicated Writesets in writesets/s
• Replicated Bytes in KiB/s
• Galera Queue in writesets
• Replication Conflicts in transactions
• Flow Control in ms
• Cluster Component Status in status
• Cluster Component State in state
• Number of Nodes in the Cluster in num
• The Total Weight of the Current Members in the Cluster in weight
• Cluster Connection Status in boolean
• Accept Queries Readiness Status in boolean
• Open Transactions in num
• Total Number of WSRep (applier/rollbacker) Threads in num

If Slave Status metrics are available:

• Slave Behind Seconds in seconds
• I/O / SQL Thread Running State in boolean

If User Statistics metrics are available:

• User CPU Time in percentage
• User Rows Operations in operations/s
• User Commands in commands/s
• User Denied Commands in commands/s
• User Transactions in transactions/s
• User Binlog Written in B/s
• User Empty Queries in queries/s
• User Created Connections in connections/s
• User Lost Connections in connections/s
• User Denied Connections in connections/s

Configuration

Edit the go.d/mysql.conf configuration file using edit-config from the Netdata config directory, which is typically at /etc/netdata.

cd /etc/netdata # Replace this path with your Netdata config directory
sudo ./edit-config go.d/mysql.conf

DSN syntax in details.

jobs:
  - name: local
    dsn: '[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]'
    # username:password@protocol(address)/dbname?param=value
    # user:password@/dbname
    # Examples:
    # - name: local
    #   dsn: user:pass@unix(/usr/local/var/mysql/mysql.sock)/
    # - name: remote
    #   dsn: user:pass5@localhost/mydb?charset=utf8

For all available options see module configuration file.

Troubleshooting

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