mysql-monitoring

Monitor MySQL Performance Using Telegraf

Table of Contents

Introduction

Monitoring the performance of your MySQL database will help identify performance bottlenecks, inefficient queries, and resource-intensive processes. By tracking metrics like query execution times, server load, and resource usage, administrators can optimize configurations and fine-tune the database for better efficiency and speed. Additionally, monitoring any running process allows for the early detection of potential problems such as server overload, disk space shortages, or network issues. It helps in spotting anomalies, system errors, or slowdowns, enabling prompt investigation and resolution before they escalate into critical issues that could affect application performance or cause downtime.

In this article, we'll detail how to use the Telegraf agent to collect MySQL performance data that you can forward to a datasource of your choice.

        

Getting Started with the Telegraf Agent

Telegraf is a plugin-driven server agent built on InfluxDB, and is used for collecting and sending metrics/events from databases, systems, processes, devices, and applications. Telegraf is written in Go and compiles into a single binary with no external dependencies, and requires a very minimal memory footprint. It is compatible with many operating systems, and has many useful output plugins and input plugins for collecting and forwarding a wide variety of system performance metrics. 

Install Telegraf (linux/redhat)

Download Telegraf and unzip it (see the telegraf docs for up-to-date versions and installation commands for many operating systems). Packages and files are generally installed at /etc/telegraf/
Ubuntu/Debian
wget https://dl.influxdata.com/telegraf/releases/telegraf_1.21.2-1_amd64.deb
sudo dpkg -i telegraf_1.21.2-1_amd64.deb

RedHat/CentOS

wget https://dl.influxdata.com/telegraf/releases/telegraf-1.21.4-1.x86_64.rpm
sudo yum localinstall telegraf-1.21.4-1.x86_64.rpm

Configure an Output

You can configure telegraf to output to a variety of sources, like Kafka, Graphite, InfluxDB, Prometheus, SQL, NoSQL, and more.

In this example we will configure telegraf with a Graphite output. If you're not currently hosting your own datasource, you can start a 14 day free trial with Hosted Graphite by MetricFire in order to follow along with these next steps.

A Hosted Graphite account will provide the datasource, offers an alerting feature, and includes Hosted Grafana as a visualization tool.

To configure the Graphite output, you need to locate the downloaded telegraf configuration file at /etc/telegraf/telegraf.conf and open it in your preferred text editor. Then you will need to make the following changes to the file:

Locate and comment out the line:

# [[outputs.influxdb]]

Then, uncomment the line:

[[outputs.graphite]]

Next, uncomment and edit the servers line to:

servers = ["carbon.hostedgraphite.com:2003"]

Finally, uncomment and edit the prefix line to:

prefix = "<YOUR_API_KEY>.telegraf"
If you don't already have a Hosted Graphite account, sign up for a free trial here to obtain a Hosted Graphite API key.
Otherwise, you can configure a different telegraf output to forward metrics to another datasource.

Configure the Input Plugin for MySQL Monitoring

Telegraf has many input plugins that can collect a wide range of network performance statistics. In this example, we will show you how to configure the mysql plugin in 3 easy steps.

1. You will need to open your telegraf.conf file in a vim shell or text editor, locate and uncomment the [[inputs.mysql]] line.

Then uncomment the 'servers' line and specify your server and db credentials:

NOTE: If no server is specified, localhost will be used. However, it's best practice to configure db credentials for authenticating the connection to the agent. This is an example of how to configure the 'servers' line to a local running instance of MySQL with tcp as the default protocol and 3306 as the default port:


## server format = [username[:password]@][protocol[(address)]]/[?tls=[true|false|skip-verify|custom]]
servers = ["telegraftest:password123@tcp(127.0.0.1:3306)/?tls=false"]

2. Now you can create a test user in your MySQL database and grant privileges to allow a connection with the telegraf agent. 


CREATE USER 'telegraftest'@'localhost' IDENTIFIED BY 'telegraftest';
GRANT ALL PRIVILEGES ON * . * TO 'telegraftest'@'localhost';
FLUSH PRIVILEGES;

3. Run telegraf to see if there are any configuration errors in the output:

telegraf --config telegraf.conf

If no errors can be seen in the output of the above command, you can restart the telegraf service and it will run in the background on your machine and telegraf will forward roughly 450 mysql performance metrics.

NOTE: if you need to filter out certain patterns to reduce the number of metrics being forwarded, you can do so with the 'fielddrop' option in telegraf by adding a similar line to your config: fielddrop = ["innodb_*"]

The metrics will include statistics into query execution times/throughput/cache utilization, active/aborted connections, InnoDB storage/buffer pool usage/transactions/locks, replication lag/delay, db commands, and many more!

See the official GitHub repository for more configuration options and full list of metrics returned by the mysql plugin.

Use Hosted Graphite by MetricFire to Create Custom Dashboards and Alerts

MetricFire provides a monitoring platform that enables you to gather, visualize, analyze, and alert on metrics from sources such as servers, databases, networks, devices, and applications. By utilizing MetricFire, you can effortlessly identify problems and optimize resources from within your infrastructure. Hosted Graphite by MetricFire takes away the burden of self-hosting your own monitoring solution, allowing you more time and freedom to work on your most important tasks.

Once you have signed up for a Hosted Graphite account and used the above steps to configure your server with the Telegraf Agent, metrics will be forwarded, timestamped, ingested, and aggregated into the Hosted Graphite backend.

They will be sent and stored in the Graphite format of: metric.name.path <numeric-value> <unix-timestamp>, which provides a tree like data structure and makes them easy to query.

You can locate these metrics in your Hosted Graphite account, and use them to build custom Alerts and Grafana dashboards.

Create Dashboards in Hosted Graphite's Hosted Grafana

In the Hosted Graphite UI, navigate to Dashboards => Primary Dashboards, and select the + button to create a new panel:

Monitor MySQL Performance Using Telegraf - 1

Then you can use the query UI to select a graphite metric path (the default datasource will be the hosted graphite backend if you are accessing Grafana through your Hosted Graphite account):

Monitor MySQL Performance Using Telegraf - 2

Graphite datasources also supports wildcard (*) searching to grab all metrics that match a specified path.

Now you can apply Graphite functions to these metrics, like exclude() to remove a pattern from a wildcard query:

Monitor MySQL Performance Using Telegraf - 3

Grafana has many additional options like configuring dashboard variables and annotations. You can also use different visualizations, modify the display settings, define the unit of measurement, and much more.

See the Hosted Graphite dashboard docs for more details.

Creating Graphite Alerts

In the Hosted Graphite UI, navigate to Alerts => Graphite Alerts to create a new alert. Name the alert, add one of your graphite metrics to the alerting metric field, and add a description of what this alert is:

Monitor MySQL Performance Using Telegraf - 4

Then select the Criteria tab which will set the threshold, and select a notification channel. The default notification channel will be the email address you used to signup for the Hosted Graphite account, but you can easily configure channels for Slack, PagerDuty, Microsoft Teams, custom webhooks, and more. See the Hosted Graphite docs for more details on notification channels:

Monitor MySQL Performance Using Telegraf - 5

Conclusion

Monitoring database performance is crucial for any business as it directly impacts application performance, user satisfaction, operational continuity, cost efficiency, scalability, security, compliance, and the ability to derive meaningful insights from data - all of which are fundamental to a successful and competitive business operation.

MySQL performance monitoring provides valuable data, and MetricFire's dashboards and alerts complement this data by providing real-time visualization, proactive identification of issues, historical trend analysis, and facilitating informed decision-making, all of which are essential for maintaining a robust and efficient network infrastructure.

Sign up for the free trial, and experiment with monitoring your MySQL database performance today. You can also book a demo and talk to the MetricFire team directly about your monitoring needs.

You might also like other posts...
metricfire Jan 24, 2024 · 6 min read

Monitor Heroku Add-Ons Using Hosted Graphite

Monitoring your Heroku stack helps you understand the performance of your application and infrastructure.... Continue Reading

metricfire Jan 23, 2024 · 8 min read

Monitor Redis Using Telegraf and MetricFire

Monitoring Redis instances is essential for maintaining performance, reliability, and security. It allows you... Continue Reading

metricfire Jan 19, 2024 · 8 min read

Monitor Your Mailchimp Campaigns Using Telegraf

Monitoring your email campaigns helps you track key performance indicators (KPIs) such as open... Continue Reading

header image

We strive for
99.999% uptime

Because our system is your system.

14-day trial 14-day trial
No Credit Card Required No Credit Card Required