Technical “How-To’s”

How to Monitor MySQL with OpenTelemetry

Daniel Kuiper
Daniel Kuiper
Share:

MySQL is the trusted open-source database management system for many desktop, mobile, web, and cloud applications. Monitoring the performance of MySQL is critical but as the applications expand over multi-cloud, cloud-native, and hybrid cloud, monitoring also grows in complexity. Continuous monitoring and scaling help applications take advantage of MySQL’s capabilities such as reliability, security, flexibility, availability, and performance scalability. Our primary focus for monitoring a MySQL database is to:

  • Identify areas of improvement in the user experience.
  • Find possible security vulnerabilities
  • Create an effective resource management plan
  • Perform impact analysis easily for database-related changes
  • Optimize database performance

Below are steps to get up and running quickly with the contrib distribution of the OpenTelemetry collector. We'll be collecting and shipping MySQL metrics to a popular backend, Google Cloud.

What signals matter?

The metrics categories that matter the most to maintain the health and efficiency of the MySQL database are:

  • Connections and connection-related errors
  • Queries related to throughput
  • Threads related to running, creation, and connection
  • Commands executed
  • InnoDB related actions
  • Query Cache
  • Tables statuses

All of the above categories can be gathered with the MySQL receiver – so let’s get started.

Installing the collector

MacOS/Linux:

  • Follow the steps for Mac or Linux according to your OS and architecture. When running commands, replace 'otelcol' with 'otelcol-contrib' as the otelcol version does not include the MySQL receiver or Google Cloud exporter.

Windows:

  • Download the most recent 'otelcol-contrib' tarball for windows from the releases linked in the getting started docs.
  • Extract the tarball after you download, I used 7-Zip.

Configuring the MySQL receiver

  1. Add MySQL receiver to your collector configuration.
  2. Set MYSQL_USERNAME and MYSQL_PASSWORD environment variables.

Modify config.yaml to add this receiver configuration: (see steps below)

yaml
1receivers:
2  mysql:
3    endpoint: localhost:3306
4    username: $MYSQL_USERNAME
5    password: $MYSQL_PASSWORD
6    collection_interval: 60s

You may have to change the default endpoint to match your environment.

Linux:

  • Overwrite the default configuration at /etc/otelcol-contrib/config.yaml to add the receiver.
  • Edit the service to add environment variables:
text
1systemctl edit --full otelcol-contrib.service
text
1[Service]
2Environment=MYSQL_USERNAME=(your mysql username)
3Environment=MYSQL_PASSWORD=(your mysql password)

Mac:

  • Create the config.yaml file manually.
  • Add environment variables in your .bash-profile or include them in the command you run the collector with.

Windows:

  • Create the config.yaml file manually.
  • Use the setx command to set environment variables:
text
1setx MYSQL_USERNAME "(value)" /m
2setx MYSQL_PASSWORD "(value)" /m

Configuring the Google Cloud exporter

To send to google cloud, you'll need a service account in your project with the following roles:

  • Logs Writer
  • Monitoring Admin

Create a new json key for your service account and copy it into a file for the collector to access.

Set the full path to your json key file in the GOOGLE_APPLICATION_CREDENTIALS environment variable using the methods shown above.

There are also other means of authenticating available depending on your setup.

Using the collector

Add the exporter configuration and then include the receiver and exporter in your service pipeline so it looks like the following:

yaml
1receivers:
2  mysql:
3    endpoint: localhost:3306
4    username: $MYSQL_USERNAME
5    password: $MYSQL_PASSWORD
6    collection_interval: 60s
7
8exporters:
9  googlecloud:
10
11service:
12  pipelines:
13    metrics:
14      receivers: [mysql]
15      exporters: [googlecloud]

Note that you might configure your receivers, exporters, and any processors differently depending on your environment and monitoring needs.

You can always configure more exporters if you'd like to send telemetry to multiple destinations.

Linux

  • Run the collector by restarting the service:
sh
1systemctl restart otelcol-contrib
  • You can check the health of the service with:
sh
1systemctl status otelcol-contrib
  • You can check the collector log output with:
sh
1journalctl -u otelcol-contrib

Mac

  • You can include environment variables here if you didn't set them previously:
sh
1./otelcol-contrib.exe --config ./config.yaml

or

sh
1MYSQL_USERNAME=(username) MYSQL_PASSWORD=(password) GOOGLE_APPLICATION_CREDENTIALS=(path) ./otelcol-contrib.exe --config ./config.yaml

Windows

sh
1.\otelcol-contrib.exe --config .\config.yaml

Adding a processor

  • You can differentiate multiple MySQL hosts by including the hostname gathered by the Resource Detection Processor:
text
1receivers:
2  mysql:
3    endpoint: localhost:3306
4    username: $MYSQL_USERNAME
5    password: $MYSQL_PASSWORD
6    collection_interval: 60s
7
8processors:
9  resourcedetection:
10    detectors: ["system"]
11    system:
12      hostname_sources: ["os"]
13
14exporters:
15  googlecloud:
16
17service:
18  pipelines:
19    metrics:
20      receivers: [mysql]
21      processors: [resourcedetection]
22      exporters: [googlecloud]

Viewing the metrics collected

If you followed the steps detailed above, the following MySQL metrics will be available in your google cloud metrics explorer. Some may not be collected if the corresponding functionality is inactive in your MySQL instance.

Check out the MySQL receiver readme and documentation for more configuration and metric options.

MetricDescription
mysql.buffer_pool.data_pagesThe number of data pages in the InnoDB buffer pool.
mysql.buffer_pool.limitThe configured size of the InnoDB buffer pool.
mysql.buffer_pool.operationsThe number of operations on the InnoDB buffer pool.
mysql.buffer_pool.page_flushesThe number of requests to flush pages from the InnoDB buffer pool.
mysql.buffer_pool.pagesThe number of pages in the InnoDB buffer pool.
mysql.buffer_pool.usageThe number of bytes in the InnoDB buffer pool.
mysql.double_writesThe number of writes to the InnoDB doublewrite buffer.
mysql.handlersThe number of requests to various MySQL handlers.
mysql.index.io.wait.countThe total count of I/O wait events for an index.
mysql.index.io.wait.timeThe total time of I/O wait events for an index.
mysql.locksThe number of MySQL locks.
mysql.log_operationsThe number of InnoDB log operations.
mysql.mysqlx_connectionsThe number of mysqlx connections.
mysql.opened_resourcesThe number of opened resources.
mysql.operationsThe number of InnoDB operations.
mysql.page_operationsThe number of InnoDB page operations.
mysql.prepared_statementsThe number of times each type of prepared statement command has been issued.
mysql.row_locksThe number of InnoDB row locks.
mysql.row_operationsThe number of InnoDB row operations.
mysql.sortsThe number of MySQL sorts.
mysql.table.io.wait.countThe total count of I/O wait events for a table.
mysql.table.io.wait.timeThe total time of I/O wait events for a table.
mysql.threadsThe state of MySQL threads.
mysql.tmp_resourcesThe number of created temporary resources.
mysql.uptimeThe number of seconds that the server has been up.

Follow this space to keep up with all our future posts and simplified configurations for various sources. For questions, requests, and suggestions, reach out to our support team at support@observIQ.com.

Daniel Kuiper
Daniel Kuiper
Share:

Related posts

All posts

Get our latest content
in your inbox every week

By subscribing to our Newsletter, you agreed to our Privacy Notice

Community Engagement

Join the Community

Become a part of our thriving community, where you can connect with like-minded individuals, collaborate on projects, and grow together.

Ready to Get Started

Deploy in under 20 minutes with our one line installation script and start configuring your pipelines.

Try it now