MySQL monitoring

Analyze performance of your MySQL database servers and stay on top of issues with the Site24x7 MySQL monitoring tool.

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

This document details how to configure the MySQL plugin and the monitoring metrics for providing in-depth visibility into the performance, availability, and usage stats of MySQL servers.

MySQL performance monitoring metrics:

The Various out-of-the-box metrics we support are:

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.

How it works?

  • Log-in to your Site24x7 account. Sign up here if you don't have one.
  • Download and install the latest version of Site24x7 Linux Agent
  • Install the MySQL plugin
  • The agent will execute the MySQL plugin and push the data to the Site24x7 server

Prerequisites:

  • Site24x7 MySQL plugin uses "pymysql" module to get the performance metrics of MySQL server

Note: Site24x7 Linux agent will automatically try to install "pymysql" using the "pip" command. If the installations fails, users can manually install the module following the steps mentioned under the section "How to install pymysql?".

How to install pymysql?

MySQL plugin installation:

  • Create a directory with the name "mysql", under Site24x7 Linux Agent plugin directory - /opt/site24x7/monagent/plugins/mysql
  • Download the file "mysql.py" from our GitHub repository and place it under the "mysql" directory
  • Commands to perform the above step:
  • cd /opt/site24x7/monagent/plugins/
    mkdir mysql
    cd mysql
    wget https://raw.githubusercontent.com/site24x7/plugins/master/mysql/mysql.py
  • Once the plugin file is downloaded, the agent will mark it up in the next data collection and you can view it in Site24x7 client

MySQL plugin configuration:

  • By default, the plugin will monitor MySQL instance that is configured on "localhost"
  • Replace the shebang character "#!" with the appropriate path for Python 3 in your system
  • Eg : #!/usr/local/bin/python3
  • In order to change the monitoring configuration, go to plugins directory and edit the "#Config Section:" in mysql.py file
  • MySQL plugin file location - /opt/site24x7agent/monagent/plugins/mysql/mysql.py

    #Config Section:
    MYSQL_HOST = "localhost"
    MYSQL_PORT="3306"
    MYSQL_USERNAME="root"
    MYSQL_PASSWORD=""

Monitoring additional metrics:

  • To monitor additional metrics, edit the "mysql.py" file and add the new metrics that need monitoring
  • Increment the plugin version value in the file "mysql.py" to view the newly added metrics ( For e.g. change the default plugin version from PLUGIN_VERSION = "1" to "PLUGIN_VERSION = "2")

Related Articles:

  • Server monitoring simplified - Stay on top of server outages and performance issues
  • RabbitMQ plugin - Monitor performance metrics of your RabbitMQ databases
  • MongoDB plugin - Monitor performance metrics of your MongoDB databases
  • Samba plugin - Analyze performance of your Samba server
  • Nagios plugin - Execute thousands of Nagios plugins in Site24x7 without the need of running a Nagios server
  • Out-of-the-box plugins - Monitor your entire app stack with our extensive list of integrations
  • Create custom plugins - Create custom Linux and Windows plugins and monitor custom attributes
Trusted

World's leading companies

Client

Don't have a Site24x7 account? Sign up now!