Introduction

In today's increasingly complex software landscape, understanding and debugging the inner workings of distributed applications are more challenging than ever before. As applications become more distributed, spanning multiple services and environments, traditional monitoring approaches often fall short of providing comprehensive insights into system behavior and performance.

This is where observability comes into play. Observability is not just about monitoring; it's about gaining deep insights into the inner workings of your applications and infrastructure, allowing you to understand, troubleshoot, and optimize performance with precision. At the forefront of the observability movement is the OpenTelemetry project.

In this blog post, we'll explore how to instrument MySQL Connector/J, the official JDBC driver for MySQL, and unlock a new level of observability for your database applications. We'll walk through the steps to set up OpenTelemetry in a Java database application, enable tracing in MySQL Enterprise Server, and demonstrate how to leverage OpenTelemetry to gain comprehensive insights into your database application stack.

Why Adopt OpenTelmetry in MySQL Connector/J?

OpenTelemetry is a set of APIs, libraries, agents, and instrumentation used to provide observability for applications and their interactions with one another. It enables developers to instrument their code so that they can export observability data: traces, metrics, and logs, enabling increased granularity of profiling, debugging, and testing. Consult the official documentation for details about the OpenTelemetry project.

The OpenTelemetry project provides automatic instrumentation for JDBC libraries. However, when it comes to distributed tracing, the automatic instrumentation is not able to propagate the context to the database layer, causing the trace chain to be broken. Also, the automatic instrumentation applies only to the visible layer of the JDBC instrumentation, keeping out any internal operations that are worth tracing as well.

MySQL Enterprise Server 8.1.0 introduced the capability of collecting observability data for server operations in the OpenTelemetry format. This feature is supported by the component_telemetry component.

MySQL Connector/J 8.4.0 introduces the client-side counterpart feature, with the capability of propagating the context to the MySQL Server it connects to and allowing a more complete observability for an application stack.

Instrumenting your Database Application Stack

Observing database telemetry data provides valuable insights into the health, performance, and reliability of the application's data layer. By monitoring database queries, transactions, and interactions, you can proactively identify performance bottlenecks, optimize database queries, or troubleshoot issues to ensure optimal application performance and user experience.

This short demo shows you how to enable OpenTelemetry in a simple Java database application using MySQL Connector/J and a MySQL Enterprise Server. You should start by setting up an Observability backend.—Jaeger running in a Docker container is used in this demo.

In the end, you shall be able to observe the following tracing information in Jaeger interface, at the URL http://localhost:16686.

This image shows a trace originated in the client application when calling a method named listFiveFilms. This trace includes a Create connection operation and an Execute statement operation, each comprising one or more sub-operations that are propagated all the way down to the database process.

This demonstration assumes that client application, MySQL Server, and the Observability backend are all running on the same machine.

Enable OpenTelemetry in the MySQL Enterprise Server

Although not strictly necessary, we want to see traces containing information from the full stack of our application. As such, the OpenTelemetry server component must be installed in the MySQL Server if it doesn't have it yet. Simply run the following SQL command inside the mysql client:

mysql> install component 'file://component_telemetry';

This component provides multiple OpenTelemetry configuration options you can use to customize the telemetry exporter, metrics, and what data to expose. Once the component is loaded, MySQL Server immediately starts generating telemetry data. This can be later turned off using the server variable telemetry.trace_enabled.

Get the Required Libraries

An application using MySQL Connector/J that wishes to enable OpenTelemetry tracing requires the following essential 3rd-party libraries:

• The mysql-connector-j library.
• The opentelemetry-api library.
• The opentelemetry-context library.

Then an OpenTelemetry implementation of your choice. Typically:

• The opentelemetry-sdk library, if you choose to do manual instrumentation;
• Or the opentelemetry-javaagent, if you prefer to use automatic instrumentation.

For the sake of simplicity, we will use the java agent in this demonstration. We will also use the opentelemetry-instrumentation-annotations library to avoid writing any OpenTelemetry code in our class.

Demo Code

This simple demo contains a class OTelDemo, which creates a connection to the 'Sakila' database and executes an SQL SELECT statement that returns five rows from the table film. The sole purpose of the demo is to generate the sequence of traces shown in the screenshot above, not to produce anything practically useful.

Here are the contents of the file src/demo/OTelDemo.java:

package demo;

import java.sql.*;
import io.opentelemetry.instrumentation.annotations.WithSpan;

public class OTelDemo {
    public static void main(String[] args) throws Exception {
        listFiveFilms();
    }

    @WithSpan
    private static void listFiveFilms() throws Exception {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://johndoe:s3cr3t@localhost:3306/sakila")) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM film LIMIT 5");
            while (rs.next()) {
                System.out.println(rs.getString(2));
            }
        }
    }
}

The top level trace that can be observed in the image is created within the application code via the annotation @WithSpan. This trace becomes the current context (parent) for any subsequent spans created inside the JDBC driver. Subsequently, via context propagation with the executed commands, a trace created by the driver becomes the parent of the spans created within the MySQL Server.

The code can be compiled by issuing the command:

$ javac -classpath "lib/*" -d bin src/demo/OTelDemo.java

And then executed with:

$ java -javaagent:agent/opentelemetry-javaagent.jar \
  -Dotel.traces.exporter=jaeger \
  -Dotel.metrics.exporter=none \
  -Dotel.service.name=OTelDemo \
  -Dotel.instrumentation.common.default-enabled=false \
  -Dotel.instrumentation.opentelemetry-api.enabled=true \
  -Dotel.instrumentation.opentelemetry-instrumentation-annotations.enabled=true \
  -classpath "bin:lib/*" \
  demo.OTelDemo
[otel.javaagent 2024-04-12 16:10:32:140 +0100] [main] INFO io.opentelemetry.javaagent.tooling.VersionLogger - opentelemetry-javaagent - version: 1.32.0
ACADEMY DINOSAUR
ACE GOLDFINGER
ADAPTATION HOLES
AFFAIR PREJUDICE
AFRICAN EGG

You can now open the Jaeger backend in your web browser and search for the traces of the OTelDemo service.

Final Considerations

Controlling observability data production

MySQL Connector/J 8.4.0 supports a new connection property named openTelemetry which allows controlling how observability data production is handled on a per connection basis. This option accepts the values:

• REQUIRED: An OpenTelemetry library must be available at runtime, or connections to the MySQL Server will fail. Note that the opentelemetry-api library alone does not produce any output traces.
• PREFERRED: Enables generating OpenTelemetry instrumentation, provided that an OpenTelemetry library is available at runtime; a warning is issued if a library is not available.
• DISABLED: Turns off generating OpenTelemetry instrumentation by MySQL Connector/J. However, this does not prevent external means of instrumentation, such as the automatic instrumentation provided by the OpenTelemetry Java agent.

Not setting a value for the openTelemetry property is equivalent to setting it as PREFERRED, except that no warning is issued when no OpenTelemetry library is available at runtime.

Notice that MySQL Connector/J does not provide any means for configuring its own OpenTelemetry exporters—it relies entirely on the calling application for the exporter configuration.

Not all spans can be linked to their corresponding trace

Distributed tracing in MySQL is limited to statement executions.

This limitation comes from the fact that Context propagation is implemented through query attributes and is only supported for query executions. While running, the server generates spans for other operations as well. Those spans can be seen in the Observability backend too, but they are unlinked from any of the traces started by the client application or library.

Similarly, spans created by MySQL Connector/J that do not produce server commands or those producing commands lacking support for query attributes are depicted as terminal nodes in the trace graphs. This is exemplified by operations such as the PING command. Nonetheless, the server still generates a span for the corresponding operation, just that it appears unlinked from the originating trace.

Conclusion

Enabling OpenTelemetry observability in MySQL Connector/J is now possible in a couple of ways.

While the OpenTelemetry project equips developers with the tools to automatically generate traces for any JDBC library, this approach has its own constraints. Alternatively, MySQL Connector/J introduces native support for producing observability data within its driver code, offering a more comprehensive and tightly integrated solution with the MySQL Enterprise Server. However, it's important to note that each approach has its own limitations. Ultimately, you have the freedom to explore these options and choose the one that best suits your needs. Should you have any questions or concerns, we're here to assist you. Your feedback is invaluable to us.

You can find the MySQL Connector/J development team in #connectors channel in MySQL Community Slack (Sign-up required if you do not have an Oracle account), the MySQL Connector/J, JDBC and Java forum, or you can file a bug in MySQL Bugs Database if you find something is not working properly.