HomeMySQL Monitoring
MySQL Monitoring

MySQL is a free, open-source relational database management system (RDBMS for short). It is capable to provide high-performance, high-availability, Robust transaction,… etc.

MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. MariaDB Server is an enterprise-grade database, extensible and secure at every layer.

Nodelizer helps you to monitor following metrics about MySQL / MariaDB installed on your server.

Metrics collected by MySQL / MariaDB Integration
Metric
– Description
Uptime
– The amount of time the server has been up.
Aborted Clients
– The number of connections that were aborted because the client died without closing the connection properly.
Aborted Connects
– The number of failed attempts to connect to the MySQL server.
Bytes Received
– The rate of number of bytes received from all clients.
Bytes Sent
– The rate of number of bytes sent to all clients.
Alter Table Command
– The rate of ALTER TABLE statement execution.
Begin Command
– The rate of BEGIN statement execution.
Commit Command
– The rate of COMMIT statement execution.
Create DB Command
– The rate of CREATE DATABASE statement execution.
Create Table Command
– The rate of CREATE TABLE statement execution.
Create View Command
– The rate of CREATE VIEW statement execution.
Delete Command
– The rate of DELETE statement execution.
Drop DB Command
– The rate of DROP DATABASE statement execution.
Drop Table Command
– The rate of DROP TABLE statement execution.
Drop View Command
– The rate of DROP VIEW statement execution.
Execute Sql Command
– The rate of EXECUTE statement execution (older statement counter variable).
Flush Command
– The rate of FLUSH statement execution.
Grant Command
– The rate of GRANT statement execution.
Insert Command
– The rate of INSERT statement execution.
Insert Select Command
– The rate of INSERT … SELECT statement execution.
Load Command
– The rate of LOAD statement execution.
Revoke Command
– The rate of REVOKE statement execution.
Rollback Command
– The rate of ROLLBACK statement execution.
Select Command
– The rate of SELECT statement execution.
Update Command
– The rate of UPDATE statement execution.
Connections
– The rate of number of connection attempts (successful or not) to the MySQL server.
Connection Errors Accept
– The number of errors that occurred during calls to accept() on the listening port.
Connection Errors Max Connections
– The number of connections refused because the server max_connections limit was reached.
Created Tmp Tables
– The number of internal temporary tables created by the server while executing statements.
Created Tmp Disk Tables
– The number of internal on-disk temporary tables created by the server while executing statements.
Created Tmp Files
– The number of temporary files created.
Innodb Buffer Pool Bytes Data
– The total number of bytes in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.
Innodb Buffer Pool Pages Data
– The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.
Innodb Buffer Pool Pages Dirty
– The current number of dirty pages in the InnoDB buffer pool.
Innodb Buffer Pool Pages Flushed
– The rate of number of requests to flush pages from the InnoDB buffer pool.
Innodb Buffer Pool Pages Free
– The number of free pages in the InnoDB buffer pool.
Innodb Buffer Pool Pages Total
– The total size of the InnoDB buffer pool, in pages.
Innodb Buffer Pool Read Ahead
– The rate of number of pages read into the InnoDB buffer pool by the read-ahead background thread.
Innodb Buffer Pool Read Ahead Evicted
– The rate of number of pages read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries.
Innodb Buffer Pool Read Ahead Random
– The number of “random” read-aheads initiated by InnoDB. This happens when a query scans a large portion of a table but in random order.
Innodb Buffer Pool Read Requests
– The rate of number of logical read requests.
Innodb Buffer Pool Reads
– The rate of number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.
Innodb Buffer Pool Wait Free
– When InnoDB needs to read or create a page and no clean pages are available, InnoDB flushes some dirty pages first and waits for that operation to finish. This counter counts instances of these waits.
Innodb Buffer Pool Write Requests
– The rate of number of writes done to the InnoDB buffer pool.
Innodb Data Read
– The rate of amount of data read.
Innodb Data Written
– The rate of amount of data written.
Innodb Data Reads
– The rate of number of data reads (OS file reads).
Innodb Data Writes
– The rate of number of data writes.
Key Read Requests
– The rate of number of requests to read a key block from the MyISAM key cache.
Key-block Reads
– The rate of number of physical reads of a key block from disk into the MyISAM key cache.
Key Write Requests
– The rate of number of requests to write a key block to the MyISAM key cache.
Key-block Writes
– The rate of number of physical writes of a key block from the MyISAM key cache to disk.
Max Execution Time Exceeded
– The number of SELECT statements for which the execution timeout was exceeded.
Max Used Connections
– The maximum number of connections that have been in use simultaneously since the server started.
Open Files
– The number of regular files that are opened by the server.
Open Tables
– The number of tables that are open.
Opened Tables
– The rate of number of tables that have been opened.
Qcache Free Memory
– The amount of free memory for the query cache.
Qcache Hits
– The rate of number of query cache hits.
Qcache Inserts
– The rate of number of queries added to the query cache.
Qcache LowMem Prunes
– The rate of number of queries that were deleted from the query cache because of low memory.
Qcache Not Cached
– The rate of number of noncached queries (not cacheable, or not cached due to the query_cache_type setting).
Qcache Queries In Cache
– The rate of number of queries registered in the query cache.
Qcache Total Blocks
– The total number of blocks in the query cache.
Queries
– The rate of number of statements executed by the server including those executed within stored programs.
Questions
– The rate of number of statements executed by the server including only those sent to the server by clients.
Slow Queries
– The rate of number of queries that have taken more than long_query_time seconds.
Select Full Join
– The rate of number of joins that perform table scans because they do not use indexes.
Select Full Range Join
– The rate of number of joins that used a range search on a reference table.
Table Locks Immediate
– The number of times that a request for a table lock could be granted immediately.
Table Locks Waited
– The number of times that a request for a table lock could not be granted immediately and a wait was needed.
Threads Connected
– The number of currently open connections.
Threads Created
– The number of threads created to handle connections.
Threads Running
– The number of threads that are not sleeping.
Seconds Behind Master
– The time in seconds that the Slave’s SQL thread lags while processing Master’s binary log.
Configuring
  1. Installing module: rptmonitor uses PyMySQL module to interact with MySQL/MariaDB. So this module should be installed for monitoring MySQL/MariaDB.

    For PyMySQL Installation :

    • installing with pip: pip install PyMySQL & pip3 install PyMySQL (python3)
    • Alternatively (e.g. if pip is not available), a tarball can be downloaded from GitHub and installed with Setuptools:

      curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz

      # (X.X is the desired PyMySQL version (e.g. 0.5 or 0.6) or look for latest).

      cd PyMySQL*

      python setup.py install

      # The folder PyMySQL* can be safely removed now.

    • ‘pip’ is preferred installer program.

      Note : Installing pip on CentOS / RHEL / Ubuntu / Debian

  2. Edit the agent’s config.cfg file.

    • Fill in values for variables – mysql_server, mysql_usrnm, mysql_pswd. Uncomment the lines containing these variables (remove # symbol).
    • You can also customize port &/or socket if not running the default, through mysql_socket, mysql_port config variables.
    • To enable replication checks, remove # symbol before mysql_replication config variable.

      • You’ll see Seconds Behind Master – metric in MySQL/MariaDB Stats if replication is enabled.
      • Imp Note : The user to connect to the database does not require any special privileges unless you wish to monitor replication (which will require SUPER or REPLICATION CLIENT privileges). It is recommended you create a specific user with no privileges (except for connect). See the documentation for user management instructions for MySql and MariaDB.
  3. Restart rptmonitor :

    • For deb/rpm package supported Linux distros :

      sudo /etc/init.d/rptmonitor restart
    • For openSUSE(11.4+) with systemd:

      sudo systemctl restart rptmonitor.service
    • For other Linux distro, FreeBSD 8+, Mac OS X+ :

      sudo python /usr/local/bin/rptmonitor/monitor.py restart
MySQL Alerts

Alert for Replication :

  • Seconds Behind Master –

    • metric shows the time in seconds that the Slave’s SQL thread lags while processing Master’s binary log.
    • A continuous increase in this value is not a very good sign as it means that the slave is not able to catch up with its master.
    • (You’ll see this metric in MySQL Stats if replication is enabled in config.)
  • If MySQL replication fails completely then the agent will return -1 for Seconds Behind Master – metric. So an alert for this metric can notify you if there’s a failure.

Other Recommended Metric Alerts :

  • Threads Connected – Number of clients currently connected. If none or too high, something is wrong.
  • Aborted Connects – Number of failed connection attempts. If this counter is increasing, clients are probably trying and failing to connect to the database.
  • Slow Queries – Number of queries that took more than long_query_time seconds to execute. Slow queries produce more memory usage, CPU usage, disk reads. Check slow_query_log for them.
  • Max Used Connections – maximum no. of connections MySQL has had open at the same time since the server was last restarted. Helps you decide the max no. of connections your server should support. Helps in traffic analysis.
  • Questions – Count of executed statements sent by client. Alert on sudden changes in query volume – drastic drops in throughput, can indicate a serious problem.