MySQL Monitoring

Analyze performance of your MySQL database servers and stay on top of issues with our easy-to-use plugin.

MySQL is one of the most popular open-source relational database management systems (RDBMS). Configure this plugin and ensure an efficient database monitoring experience through vital actionable information.

This document details the following:

Performance Metrics

Running Threads

threads_running metric counts the total number of threads that are currently running in the database and not sleeping.

Reads

The metric reads identifies the total number of reads done in a MySQL server. Technically, it is the number of selected queries and number of query cache hits.

Writes

The metric writes identifies the total number of writes done in a MySQL server. It is the sum of inserted queries, replaced queries, updated queries and deleted queries.

Slow Queries

Number of slow queries running on the MySQL server is recorded using the metric slow_queries. Slow queries generate excessive disk reads, memory and CPU usage.

Temporary Tables Created on the Disk

Record the total number of internal on-disk temporary tables created by the server while executing statements with the metric created_tmp_tables_on_disk.

Aborted Clients

Use the metric aborted_clients to get the total number of connections that were aborted because the client died without closing the connection properly. If a client is unable to connect, the server increments the Aborted Connects status variable.

Aborted Connections

Use the metric aborted_connections to get the total number of failed attempts to connect to the MySQL server. If this counter is increasing, your clients are trying and failing to connect to the database.

Connection Usage

connection_usage shows the total connection count with respect to the percentage of maximum connections in the database. This information can be used to tune database connections for better performance.

Maximum Used Connections

Maximum number of connections at a given time, since the start of the server is represented by the metric max_used_connections. If your server reaches the maximum connections limit, it will start to refuse additional connection requests.

Threads Connected

threads_connected metric counts the total number of currently open connections. It can also refer to the number of clients currently connected. If the value is zero or too high, something is wrong.

Temporary Tables Created

Record the total number of internal temporary tables created by the MySQL server with the metric created_tmp_tables. If the value is high, you may want to increase the thread cache size value.

Open Files

Get the count of the total number of files that are open with open_files. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so.

Prerequisites

  • Our Linux/Windows server monitoring agent should be installed in the network or on the specific host where the MySQL instance is running.
  • While adding a plugin, the plugin name and its folder name should be identical.
  • The MySQL plugin will automatically verify, download, and install the 'pymysql' module required for monitoring your MySQL servers. This can be viewed in the mysql.py file. In case the 'pymysql' module is not installed, follow the instructions given below to manually install it.

For Linux:

  • Execute the following command in your server to install pymysql pip install pymysql

Installing pip:

  • Use "pip" to install pymysql module
    Note: pip is a package management system that is used to install and manage software packages written in Python.
  • For CentOS, Fedora, RHEL:
    yum install python-devel
    yum install python-pip (or)
    easy_install pip
  • For Debian, Ubuntu:
    apt-get -y install python-pip

For Windows:

  • Open cmd as administrator.
  • Go to the Python path. cd [python path]
  • Execute the following command: python -m pip install PyMySQL

If pymysql is already installed in your server, follow the steps given in this article to install the MySQL plugin in Windows servers.

Roles and Permissions

SELECT VERSION(), SHOW GLOBAL STATUS, and SHOW VARIABLES are the queries used in the MySQL plugin.

  1. To create a MySQL user:
    CREATE USER username@hostname IDENTIFIED BY 'password';
  2. Select on queries permission is required to execute the queries mentioned above.
    GRANT SELECT ON mysql.* TO username@hostname IDENTIFIED BY password;
For Example, create a user called 'site24x7' with 'site24x7' as password. Give Select permission for the 'site24x7' user and flush the privileges:
CREATE USER site24x7@localhost IDENTIFIED BY 'site24x7';
GRANT SELECT ON mysql.* TO site24x7@localhost IDENTIFIED BY 'site24x7';
FLUSH PRIVILEGES;

Plugin Installation

Linux

  • Download and install the latest version of the Site24x7 Linux agent in the server where you plan to run the plugin. If it is installed successfully, you will see a Linux server monitor in the Site24x7 Control Panel. This confirms that the agent is able to communicate with our data center.
  • Download the mysql.py file from our GitHub repository.
    wget https://raw.githubusercontent.com/site24x7/plugins/master/mysql/mysql.py
  • By default, the plugin will use the below configuration to connect to the MySQL instance. To change the existing configuration, edit the '#Config Section' in the 'mysql.py' file.
    #Config Section:
    MYSQL_HOST = "localhost"
    MYSQL_PORT="3306"
    MYSQL_USERNAME="username"
    MYSQL_PASSWORD="your password"
  • Create a folder with the name 'mysql', under the Site24x7 Linux agent plugin directory '/opt/site24x7/monagent/plugins/' and place the 'mysql.py' under '/opt/site24x7/monagent/plugins/mysql/'

Windows

  • Download and install the latest version of the Site24x7 Windows agent in the network where you plan to run the plugin. If it is installed successfully, you will see a Windows server monitor in the Site24x7 Control Panel. This confirms that the agent is able to communicate with our data center.
  • Download the mysql.py file from our GitHub repository.
  • Follow the steps given in this article to know how to run the python script in Windows server.
  • Create a folder with the name 'mysql', under the Site24x7 Windows agent plugin directory - C:\Program Files (x86)\Site24x7\WinAgent\monitoring\Plugins\ and place the 'mysql.py' file under 'C:\Program Files (x86)\Site24x7\WinAgent\monitoring\Plugins\mysql\'
The agent will automatically execute the plugin within five minutes and send performance data to the Site24x7 data center.
Tip

Manually execute the plugin script using the following command and verify its output:

python mysql.py

View Data in the Site24x7 Web Client

  1. Log in to Site24x7 and go to Server > Plugins > click on the plugin monitor.
  2. You will be able to view the performance charts on the various metrics for your MySQL server.

Plugin Contribution

Feel free to contribute to our existing plugin and come up with suggestions or feedback on our Community.

Other Plugin Integrations

Troubleshooting Tips