Setting up MySQL Monitoring With New Relic Infrastructure Pro

Posted in: Cloud, DevOps, MySQL, Technical Track

If you have a New Relic Infrastructure Pro license, and unmonitored MySQL servers, there’s now an easy solution at your fingertips. With the New Relic MySQL integration you can monitor and graph almost any detailed metric you could possibly want. New Relic recently unified its analytics tools with New Relic One, a dashboard that provides quick access to all the New Relic tools. With an Infrastructure Pro subscription, you get access to:

  • New Relic Infrastructure: Flexible, dynamic monitoring of your entire infrastructure, from services running in the cloud or on dedicated hosts, to containers running in orchestrated environments.
  • New Relic Alerts: A flexible, centralized notification system that unlocks the operational potential of New Relic. Alerts is a single tool to manage alert policies and alert conditions for all of your New Relic data.
  • New Relic Logs: A fast, scalable log management platform that allows you to connect your log data with the rest of your telemetry data.
  • New Relic Insights: Allows you to query and chart your New Relic data. Insights is a software analytics resource for gathering and visualizing data about your software, and understanding what that data says about your business.
New Relic infrastructure agent install

New Relic provides a nice wizard page that generates the commands needed to install the infrastructure agent and configure it for your account. For our target OS (CentOS 7) the wizard generated the following commands for installation:

# Create a configuration file and add your license key 
echo "license_key: xxxxxxxxxxxxxxxxxxxxxx" | sudo tee -a /etc/newrelic-infra.yml && 
# Create the agent’s yum repository 
sudo curl -o /etc/yum.repos.d/newrelic-infra.repo https://download.newrelic.com/infrastructure_agent/linux/yum/el/7/x86_64/newrelic-infra.repo && 
# Update your yum cache 
sudo yum -q makecache -y --disablerepo='*' --enablerepo='newrelic-infra' && 
# Run the installation script 
sudo yum install newrelic-infra -y

Once you run the above commands, the infrastructure agent will begin sending OS metric data to New Relic (Note: We are making the assumption that you’ve already set up any firewalls needed for the New Relic infrastructure agent). If you want a custom name for your host (Default is hostname) you can add  “display_name:  Name” to the /etc/newrelic-infra.yml file. You can add tags as custom_attributes and can use them to describe the host’s function. All of the configuration options can be found here and below is a sample /etc/newrelic-infra.yml file using the display_name and custom_attributes options:

license_key: 1db211983ab4871600faef016f5235066f208fd5 
display_name: mysql-qa-usw-a-1
verbose: 0
custom_attributes:
   env: QA
   app: MySQL
   team: Pythian

This takes care of getting the host level metrics into New Relic, but we really want to collect MySQL metrics so we can be alerted for specific database problems. To collect MySQL metrics, we need to install the MySQL integration and have an Infrastructure Pro account. The Link to the MySQL integration provides these simple instructions for installation:

sudo yum -q makecache -y --disablerepo='*' --enablerepo='newrelic-infra'
sudo yum install nri-mysql

The above commands will install the MySQL integration on my CentOS 7 hosts but we will still need to set up a MySQL user and customize the MySQL integrations configuration file before we start collecting MySQL data. Using the MySQL command line, create a user with replication privileges :

mysql> CREATE USER 'newrelic'@'localhost' IDENTIFIED BY 'YOUR_SELECTED_PASSWORD';

mysql> GRANT REPLICATION CLIENT ON *.* TO 'newrelic'@'localhost' WITH MAX_USER_CONNECTIONS 5

Change the directory to the integration’s folder and copy the sample configuration file:

# Change the directory to the integration's folder.
cd /etc/newrelic-infra/integrations.d
# Copy the sample configuration file:
sudo cp mysql-config.yml.sample mysql-config.yml
# Edit the file mysql-config.yml as shown below substituting the hostname, user, password:
vi /etc/newrelic-infra/integrations.d/mysql-config.yml
integration_name: com.newrelic.mysql
instances:
  - name: mysql-server
    command: status
    arguments:
    	hostname: localhost
    	port: 3306
    	username: newrelic
    	password: xxxxxxxxxxx
    	extended_metrics: 1
    	extended_innodb_metrics : 1
    	# New users should leave this property as `true`, to identify the
    	# monitored entities as `remote`. Setting this property to `false` (the
    	# default value) is deprecated and will be removed soon, disallowing
    	# entities that are identified as `local`.
    	# Please check the documentation to get more information about local
    	# versus remote entities:
    	# https://github.com/newrelic/infra-integrations-sdk/blob/master/docs/entity-definition.md
    	remote_monitoring: true
    labels:
    	env: production
    	role: Master

Note, in the above configuration file we have enabled both extended_metrics and extended_innodb_metrics for MySQL. The complete list of the MySQL metrics is quite extensive, and custom graphs and alerts can be created for any of them. The next step is to restart the infrastructure agent:

systemctl restart newrelic-infra.service

Now that we have New Relic Infrastructure collecting data about our MySQL server(s) we can start creating custom graphs along with alert policies and notification actions.

By signing in at https://login.newrelic.com/login we can see the MySQL hosts we added to New Relic Infrastructure.

MySQL hosts added to New Relic Infrastructure.

Now we’re ready to create alerts on any conditions / data New Relic collects. With the MySQL integration we have a large choice of metrics to choose from:

Metrics available from MySQL integration when creating alerts on any conditions / data New Relic collects.

For our setup we are going to create a simple alert that will notify us whenever replication is stopped. In order to set up alerting, you need to create a new notification channel. To set up a notification channel, click on the alerts menu item. Next, pick the “Notifications channels” tab on the alerts page.

Using the alert notifications tab to create an alert to warning of stopped replication.

New Relic Alerts provides a nice selection of notification channels / integrations for alerting including Slack, PagerDuty, OpsGenie, XMatters and, of course, email.  Once you have a notification channel set up, you can create a channel policy for receiving notifications:

Creating a channel policy for alerting covering Slack, PagerDuty, OpsGenie, XMatters and email.

Once you have a notification channel and policy defined you can start creating alert conditions:

Creating alert conditions within the notification channel.

Below you can see I picked the “Integrations” alert type to get the list of MySQL metrics. I then chose the “MySQLSample” and narrowed the scope to MySQL servers with the “cluster – node type” of slave. This will enable this alert on all New Relic hosts set up with the MySQL integration that are slave members using replication.

Enabling an alert on all New Relic hosts set up with the MySQL integration that are slave members using replication.

We define the measurement threshold for the metric. In this case it’s always “1” when replication is running and “0” when it’s stopped. We also choose the alert policy / notification channel, then click “create.”

Defining the measurement threshold for the metric and choosing the alert policy / notification channel.

And we have created our first alert.

First alert is created.

It’s important to make sure you’re targeting the proper servers for each alert. In our example above, for the “narrow down entities” option, we indirectly targeted only MySQL DB Servers by choosing a metric that only MySQL instances will send to our New Relic account. That’s great for this metric, but as the DBA (database administrator) we don’t want to get paged for anything other than the database hosts and database server issues.

One way we can narrow down entities is via tags or other attributes. To ensure you are only monitoring the hosts you are targeting it’s a best practice to use tags generously in your infrastructure deployment code and leverage them in “narrow down entities” for precise monitor targeting. For monitoring the “MySQL Server Down” condition, we’ll use the general host level metrics and leverage the process information to alert only when we see a count of zero /usr/sbin/mysqld processes. With a few more clicks I have now added MySQL database server alerts for excessive CPU conditions, max_connections or MySQL Server failures:

Adding MySQL database server alerts for excessive CPU conditions, Max Connections or MySQL Server failures.

Another way to explore these metrics is to use the Data Explorer which is part of “New Relic Insights” and can be found as a tab in New Relic Infrastructure. Data Explorer lets you list / graph individual metrics and build alerts if desired. Below you can see I was examining the metric “Avg Cluster.Seconds Behind Master”:

Data Explorer allows listing / graphing of individual metrics and building of alerts if desired.

With Insights and its tools you can explore your data and create custom dashboards:

Exploring your data and creating custom dashboards in New Relic Insights.

Hopefully this post has provided you with a helpful overview of the type of MySQL alerting and monitoring you can set up using New Relic Infrastructure.

email

Authors

Interested in working with Kevin? Schedule a tech call.

About the Author

Database Consultant
IT Professional with 30 Years experience overall and 20 Years specializing in tuning and optimizing OLTP databases and providing database administration support.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *