Monitor MySQL Performance and Availability

Are you using MySQL database in your application? You can use IDERA Uptime Cloud Monitor(UCM) to monitor your MySQL database performance. As an illustration, you can configure and receive alerts for the following scenarios:

  • Performance: alert if the MySQL performance is poor.
  • If the slow_queries value is significantly above its normal range, that would imply that the MySQL server is under heavy load.
  • Bytes_received and bytes_written values track the read-write performance of the MySQL database.

 

Prerequisites

Uptime Cloud Monitor from IDERA offers a SAAS based out-of-the-box monitoring solution for MySQL that is easy to set up. If you are not familiar with the concept of custom metrics as used in Uptime Cloud Monitor, please go through Custom Metrics information once before continuing with this document.  

 

  1. Uptime Cloud Monitor account.

If you don’t already have an account, you can sign up for a 14 day FREE TRIAL of Uptime Cloud Monitor here (no Credit Card required).

  1. MySQL up and running in your environment (this can be on your own server or using Amazon Web Services’ RDS.)
  2. Root access to server to install the MySQL agent.

 

 

Monitor MySQL using out-of-the-box custom metrics installer from Uptime Cloud Monitor

 

There are three steps that you need to do to start monitoring a MySQL server:

  1. MySQL Agent Installation
  2. Verify that your MySQL Dashboard is up and running
  3. Configure alerts to be notified about MySQL performance issues

 

1.MySQL Agent Installation

 

 

image1.png

Figure 1: In your Uptime Cloud Monitor Account, navigate to Databases>Getting Started

 

Login to your Uptime Cloud Monitor account and navigate to Custom tab -> Getting Started. Click on MySQL icon.  image3.png.

image2.png

Figure 2: The MySQL agent installation script

 

Click on image5.png icon to copy the installer script to your clipboard. Paste the command into the terminal window on the server where you want to install the agent.  Script can be run on the server running MySQL, or it can be a different server, but this server will need to be continually running in order to monitor MySQL.  (If you’re using AWS RDS and you don’t have direct server access to the RDS you’ll need to use a different EC2 instance.  Make sure your security groups and any other networking configuration are setup to allow this server to access RDS.)

 

As soon as the script is run on a terminal with root access, it starts downloading the required MySQL monitoring agent on your server. You will need to configure the following items in the agent installation process. Item specified in square brackets ( [ ] ) are the default.

 

 


image4.png

Figure 3: Executing the MySQL agent installation script

 

1. Press y to monitor MySQL.

image7.png

Figure 4: Executing the MySQL agent installation script with user as copperegg, Group as copperegg and monitoring frequency as 15 seconds

 

2. Monitoring frequency: 15, [60], 300, 900, 3600 seconds

3. User and Group for running service as default values: [copperegg][copperegg]

image6.png

Figure 5: Executing the MySQL agent installation script with group name as “MySQL”, group label as “MySQL Metrics” and Dashboard  as “MySQL”

 

4. Group label, group name and dashboard name for custom metrics [MySQL][MySQL Metrics][MySQL]

 

 

image9.png

Figure 6: Executing the MySQL agent installation script with unique id, server hostname , username, password and database. Only one MySQL server is added for monitoring.

 

5. Unique name for this node (good to differentiate if you are monitoring different MySQL databases). [<user>-MySQL]

6. Server details : hostname username,password and database for service. [localhost][user][password][database_name]

7. After configuring one database, you’ll get an option to add more MySQL databases.

 

 

image8.png

 Figure 7: Executing the MySQL agent installation script with an upstart init file and the default value for log file name. The installation completes successfully!

 

8. Configure log file name and upstart job name (defaults present) [/usr/local/copperegg/log/copperegg-metrics.log][/etc/init/copperegg-metrics.conf]

 

2.Verify that your MySQL Dashboard is up and running

 

Navigate to Dashboard and you will see a new MySQL Dashboard has been added automatically by the installer.

 

image12.png

Figure 8: A new Dashboard for MySQL has been added

 

As the installed script executes, you will notice data samples appearing on the widgets for this Dashboard.

 

image10.png

Figure 9: Widget inside MySQL Dashboard displaying Threads connected.

image11.png

Figure 10: Widget inside MySQL Dashboard displaying count of queries.

 

image13.png

Figure 11: Widget inside MySQL Dashboard displaying count of slow queries.

 

Monitored metrics

Uptime Cloud Monitor currently monitors 10 metrics which cover most of the important metrics for MySQL from a performance and stability perspective. You can see these 10 metrics by navigating to the Custom Tab > Custom Objects and selecting Details on the Custom Metrics Group Label [MySQL Metrics] that was given as installation input. For some of these metrics, alerts have been automatically created (see section 3 below). To create new alerts see section: Configuring your own Alerts for MySQL Database.

 

The metrics are:

 

  • threads_connected : Threads connected (alerts automatically created)
  • created_tmp_disk_tables : Created tmp disk tables
  • qcache_hits : Qcache hits
  • queries : Queries
  • slow_queries : Slow queries (alerts automatically created)
  • bytes_received : Bytes received 
  • bytes_sent : Bytes sent 
  • com_insert : Com insert 
  • com_select : Com select 
  • com_update : Com update 

 

3.Configure Alerts to be notified about MySQL performance issues

Default Pre-Configured Alerts for the MySQL Database

As soon as your MySQL Database Monitoring Dashboard comes up, you will notice that some alert configurations have been automatically created by our Installation Script (under Alerts > Configure Alerts). These pre-configured alerts for MySQL Database include:

 

Sr. No.

Metric Name

Definition

Alert Definitions

Duration

1

Threads connected

The number of currently open connections

Alert will be triggered if open connections are more than 800.

3 Min

2

Slow Queries

The rate of slow queries

 Alert will trigger for all slow queries.

3 Min

 

If the alerting condition is met for these pre-configured alerts, some alert notifications may also be triggered.

 

Configuring your own Alerts for the MySQL Database

image14.png

Figure 12: A new alert being added that will be triggered when MySQL slow queries metric exceeds a threshold of <entered value> within a time period of 1 min

 

In addition to the default pre-configured alerts that are created by the Installer, alerts can also be configured against any of these 10 monitored metrics for MySQL.

 

You can configure new alerts that will be triggered when performance issues with MySQL arise. You can also configure the notification mechanisms for a triggered alert.

  1. Go to Alerts Tab > Configure alerts and click on “New Alerts” button
  2. Provide values for these fields in the New Alert page:
  • Description: A description of the alert that will be easily recognized by you and your team if the alert is triggered
  • Alert me when: Select the metric of interest and the condition upon which the alert is triggered. In the Alert me when dropdown, you can prefill “Custom: <your MySQL Metric Group>” to get only the list of monitored metrics for your specific MySQL database. From this list, you can then select the specific metric for which you want to configure an alert. As an illustration, in Figure 12, the chosen metric of interest in ‘Alert me when’ is ‘Custom: MySQL Metrics: Slow Queries’  and the condition for triggering the alert is if the average value is more than <entered value from dropdown>.
  • For at least: The duration for which the alert condition must be valid for the alert to be triggered
  • Matching tags: By default, (match everything) is chosen. If needed, you can attach tags to a particular metric object and select those tags here, which would cause only alerts on that metric object to be triggered.
  • Excluding tags: By default, (exclude nothing) is chosen. If needed, you can attach tags to a particular metric object and select those tags here, which would cause alerts on that metric object to be skipped.
  • Annotate: When enabled, an annotation is automatically created when the alert is triggered. Annotations will be visible in the custom metrics dashboard where the data stream is displayed.
  • Automatic Clear: When enabled, the alert issue is automatically cleared if the triggering condition is no longer true
  • Notify on clear: When enabled, notifications are also sent when the alert issue is cleared. Please do note that notifications are always sent when the alert issue is triggered.
  • Send Notifications To: Here you can configure the notification mechanisms by which the alert is communicated to you and others in your team.

 

Uptime Cloud Monitor supports notifying different sets of users with differing notification mechanisms for each alert type.

Notification mechanisms include:

  • Email
  • SMS
  • PagerDuty
  • Twitter
  • HipChat
  • Campfire
  • Slack
  • OpsGenie

Webhooks are also exposed for clients to configure custom notification mechanisms.

More about setting up website probe alerts can be found here.

 

Uptime Cloud Monitor Free Trial! Sign up today!

Sign up for a 14 day FREE TRIAL of Uptime Cloud Monitor (no Credit Card required).

You can find more information about Uptime Cloud Monitor pricing options here.

We charge only $0.08 per month per metric!

Sign up today!

Powered by Zendesk