这是用户在 2024-11-17 17:57 为 https://observiq.com/blog/how-to-monitor-mysql-with-opentelemetry 保存的双语快照页面,由 沉浸式翻译 提供双语支持。了解如何保存?
Complimentary Gartner® Report! 'A CTO's Guide to Open-Source Software: Answering the Top 10 FAQs.'Read more
Technical “How-To’s”

How to Monitor MySQL Using OpenTelemetry

Daniel Kuiper
Daniel Kuiper
Share:

MySQL is a widely used open-source database management system that is the backbone for many desktop, mobile, web, and cloud applications. It’s best known for being the ‘M’ in the still-prevalent LAMP stack (Linux, Apache, MySQL, PHP) and is often used as a supporting database for various web applications such as E-commerce, CMS, CRM, and forums.

As we move forward into 2024, it’s important to reassess your monitoring strategy. Your strategy should be designed to adapt to the changing demands of distributed hybrid and multi-cloud architectures. It should be flexible and comprehensive enough to monitor the rest of your stack.

The Benefits of Monitoring MySQL

There are several key benefits to be aware of as you weigh how much effort you want to dedicate to implementing a solution. Here are some of the key benefits:

  • Provides the context to optimize database performance by monitoring resource contention and query performance
  • Exposes user trends to pinpoint friction points in your attached application
  • Surfaces critical vulnerabilities by capturing anomalous usage patterns
  • Enables rapid root-cause analysis and resolution through the collection and layering of metrics of log data
  • Provides an audit trail for database changes and user activity, provides the meat impact analysis

Related Content: How to Monitor SQL Server with OpenTelemetry

What Signals Matter?

It’s crucial to capture the 4 golden signals to ensure uptime for your database and attached application. In the MySQL context, this means capturing a representative set of the following signals:

  • Query Performance
    • Query response time/latency
    • Slow/Long running queries
    • Query Errors
  • Throughput:
    • Queries per second
    • Transactions per second
  • Resource Utilization:
    • CPU Usage
    • Memory Usage
    • Disk I/O
    • Network I/O
  • Connections:
    • Max Connections
    • Active Connections/Threads
    • Running Connections/Threads
    • Failed Connections
  • Replication:
    • Replication latency
    • Replication errors
  • Database Growth
    • Database growth rate
    • InnoDB Row Operations
  • Storage Engine (for InnoDB):
    • Buffer Pool Usage
    • InnoDB Row Operations
  • Database Errors
    • Configuration and operational errors

The Case for Monitoring MySQL with OpenTelemetry

Though there are many solutions teams can implement, such as the built-in MySQL Enterprise Monitor for example, OpenTelemetry provides the means to collect signals in parity with existing solutions but pulls ahead as the best long-term solution for those mulling over a fresh monitoring and observability strategy for MySQL and the rest of their stack.

The project's primary goals of data ownership and vendor-neutrality alone make it worth considering, but the maturity and comprehensive toolset of offers make it a clear leader. Any effort spent in OTel now will yield dividends later in the form of cost and time savings and flexibility as monitoring needs of your organization grow and change.

Core Components of the OpenTelemetry Collector

As a quick primer, the OpenTelemetry Collector has a few primary components that facilitate data collection, processing, and transmission of the above signals. Here’s a quick breakdown:

  • OpenTelemetry Collector: a lightweight data collector that can be deployed as an on-host agent (this is how we’ll be using it to monitor MySQL) or as a gateway for other collectors, shipping data to one or many configured destinations. The collector has a few primary components:
  • Receivers: collect telemetry from a specific application or system (like MySQL) or another OpenTelemetry collector via OTLP.
  • Processors: transform the data by providing the levers to enrich, filter, mask (PII), and other data refinement techniques. Advanced users can utilize OTTL to do really interesting transformations.
  • Exporters: transmit telemetry to another destination: another OpenTelemetry collector, to file, to an observability/SIEM backend

Each component can be logically connected as a pipeline, the collector’s configuration file.

  • mysqlreceiver collects MySQL database/instance metrics
  • hostmetricsreceiver collects operating system and specific process metrics
  • filelogreceiver captures logs from the specified file path(s). These events can be processed and turned into structured log data like JSON.

Related Content: OpenTelemetry in Production: A Primer

Implementation: Monitoring MySQL with OpenTelemetry

Prerequisites

Before starting, there’s a few things you’ll need:

  • MySQL instance (5.7+) running on a Linux or Windows VM with admin privileges.
    • To collect MySQL logs (optionally), you must enable the 3 log types (error, general query, slow query) in your configuration file. Here are the steps to do so:
  • Have a backend ready to go as a destination for your MySQL monitoring data. For this example, I’m using Google Cloud Operations. If you choose Google Cloud Operations, you’ll need you'll need:
    • Service account (and corresponding JSON key) in your project with the following roles:
      • Logs Writer
      • Monitoring Admin
    • Set the full path to your JSON key file in the GOOGLE_APPLICATION_CREDENTIALS environment variable using the methods mentioned above on your MySQL host
    • Other means of authenticating are available depending on your setup.

Installing the OpenTelemetry Collector

For this example, we’ll use the contrib distribution of the OpenTelemetry collector. Generally, we recommend ‘contrib’ as it provides all the necessary components to kick the tires on the bleeding-edge components the project offers. We'll be installing the OTel Collector on the same host as our MySQL instance.

Linux:

  • Follow the steps for Linux here. When running commands, replace 'otelcol' with 'otelcol-contrib' as the otelcol version does not include the MySQL receiver or Google Cloud exporter. Here’s an example for Debian:
bash
1sudo apt-get update
2sudo apt-get -y install wget systemctl
3wget https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.98.0/otelcol-contrib_0.98.0_linux_amd64.deb
4sudo dpkg -i otelcol-contrib_0.98.0_linux_amd64.deb

Windows:

  • Download the most recent 'otelcol-contrib' tarball for Windows from the releases linked in the getting started docs.
  • Extract the tarball to a directory where you'd like the executable to run. I’d recommend downloading 7-zip, or you can use the Windows tar PowerShell command:
text
1tar -xvzf .\otelcol-contrib_0.99.0_windows_amd64.tar.gz
  • Create an empty file in the collector's root directory called config.yaml. This will be used as the collector's configuration file. (This is a required step for Windows, but not Linux deployments)

Configuring the OpenTelemetry Collector

Next, you can open the config.yaml for your collector and begin adding and configuring the abovementioned components. Note, for Linux, edit and overwrite the configuration file that was automatically created:

bash
1vim ./etc/otelcol-contrib/config.yaml

MySQL Receiver

  1. Add the mysqlreceiver to the receivers section.
  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.

Host Metrics Receiver

  1. Add the hostmetrics receiver to your collector configuration.
  2. Call out the specific metrics the host receiver should gather or use the defaults provided in the example.
yaml
1receivers:
2  hostmetrics:
3    scrapers:
4      filesystem:
5        metrics:
6          system.filesystem.utilization:
7              enabled: true
8      memory:
9        metrics:
10          system.memory.utilization:
11            enabled: true
12      network:
13        metrics:
14          system.network.conntrack.count:
15            enabled: true
16          system.network.conntrack.max:
17            enabled: true
18      paging:
19        metrics:
20          system.paging.utilization:
21            enabled: true

File Log Receiver

  1. Add filelog receiver to your collector configuration.
  2. Configure the receiver to point at the log files you enabled on your MySQL instance.
yaml
1receivers:
2  filelog:
3    include:
4        - /var/log/mysql/mysql_error.log
5        - /var/log/mysql/mysql.log
6        - /var/log/mysql/mysql-slow.log

Google Cloud Exporter

  1. Add the Google Cloud exporter to your collector configuration.
yaml
1exporters:
2    googlecloud:

Assemble the Telemetry Pipeline

Now, create a pipeline in the service configuation, referencing the components we called out above. Make sure to call out the appropriate receiver in the 'metrics' or 'logs' pipeline, where applicable.

yaml
1service:
2  pipelines:
3    metrics:
4      receivers: [mysql,hostmetrics]
5      exporters: [googlecloud]
6    logs:
7      receivers: [filelog]
8      exporters: [googlecloud]

Set Environment Variables and Modify Service File

Lastly, set the credentials as an environment variable, matching the variable specified in the configuration file.

Linux:
  • 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)
Windows:
  • Use the setx command to set environment variables:
text
1setx MYSQL_USERNAME "(value)" /m
2setx MYSQL_PASSWORD "(value)" /m

Running the OpenTelemetry Collector

After adding each receiver/exporter and constructing your pipeline, in your config you should look like this:

yaml
1receivers:
2  mysql:
3    endpoint: localhost:3306
4    username: $MYSQL_USERNAME
5    password: $MYSQL_PASSWORD
6    collection_interval: 60s
7
8  hostmetrics:
9    scrapers:
10      filesystem:
11        metrics:
12          system.filesystem.utilization:
13              enabled: true
14      memory:
15        metrics:
16          system.memory.utilization:
17            enabled: true
18      network:
19        metrics:
20          system.network.conntrack.count:
21            enabled: true
22          system.network.conntrack.max:
23            enabled: true
24      paging:
25        metrics:
26          system.paging.utilization:
27            enabled: true
28    
29  filelog:
30    include:
31      - /var/log/mysql/mysql_error.log
32      - /var/log/mysql/mysql.log
33      - /var/log/mysql/mysql-slow.log
34
35exporters:
36  googlecloud:
37
38service:
39  pipelines:
40    metrics:
41      receivers: [mysql,hostmetrics]
42      exporters: [googlecloud]
43    logs:
44      receivers: [filelog]
45      exporters: [googlecloud]

Just so you know, 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

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
8  hostmetrics:
9    scrapers:
10      filesystem:
11        metrics:
12          system.filesystem.utilization:
13              enabled: true
14      memory:
15        metrics:
16          system.memory.utilization:
17            enabled: true
18      network:
19        metrics:
20          system.network.conntrack.count:
21            enabled: true
22          system.network.conntrack.max:
23            enabled: true
24      paging:
25        metrics:
26          system.paging.utilization:
27            enabled: true
28    
29  filelog:
30    include:
31      - /var/log/mysql/mysql_error.log
32      - /var/log/mysql/mysql.log
33      - /var/log/mysql/mysql-slow.log
34      
35processors:
36  resourcedetection:
37    detectors: ["system"]
38    system:
39      hostname_sources: ["os"]
40
41exporters:
42  googlecloud:
43
44service:
45  pipelines:
46    metrics:
47      receivers: [mysql,hostmetrics]
48      processors: [resourcedetection]
49      exporters: [googlecloud]
50    logs:
51      receivers: [filelog]
52      processors: [resourcedetection]
53      exporters: [googlecloud]

Viewing MySQL OpenTelemetry Data

If you follow the detailed steps above, the following MySQL metrics (and logs) will be available in your Google Cloud Operations Metrics and Logs Explorer. Some may only be collected if your MySQL instance's corresponding functionality is active.

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 our future posts and simplified configurations for various sources. For questions, requests, and suggestions, contact our support team at info@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