OpenTelemetry in Action – Optimizing Database Operations

Software developers know that database performance is a major problem in their software applications. Although many developers prefer to use a database relational for enterprise applications, monitoring and logging solutions only provide limited information to detect problems with database performance. It is difficult to root out bad practices like chatty interactions between application code and the databases.

Developers need to know how their database performs in the context of user transactions. We would like to have a tool that monitors both the application’s performance and the database’s regarding user transactions. OpenTelemetry is a popular tool for application monitor, but it can also serve as an extension for monitoring databases.

This article will discuss common issues with database performance and how OpenTelemetry can help us identify and fix them. We’ll create a simple application using a SQL Server database to get hands-on experience. The application will be connected to an ingestion platform, lightstep. We’ll then use the ingested Telemetry to identify database problems and discuss the best way to fix them.

The Basics of Observability & OpenTelemetry

If you are new to observability and OpenTelemetry, I suggest that you read the previous article in this series. We will be discussing the three types information that are collected by an observable application: logs, metrics and traces. We will also examine the main components of OpenTelemetry’s data model.

This documentation provides more detail on how OpenTelemetry works.

OpenTelemetry is used to monitor databases

OpenTelemetry can be used to monitor databases and instrument applications. We also noted the use of OpenTelemetry for application observability. This monitoring takes place through database clients and not directly on the server.

You may not be able to install monitoring libraries on a server because of access restrictions or the nature your platform. OpenTelemetry instrumentation can be used to monitor the database client-side. Although the instrumentation won’t give you any insight into the internals, it will provide enough information to help you troubleshoot performance issues and improve the user experience.

OpenTelemetry is used to detect database performance issues

Our project setup to detect database performance issues is very similar to the Part One setup, where we used OpenTelemetry for database dependencies.

We’ll again use the .NET SQLClient instrumentation to OpenTelemetry, and Lightstep as telemetry storage/analysis.

To emit observation signals, we’ll instrument our application using the OpenTelemetry SDK. To send Lightstep data, we’ll use the OpenTelemetry Protocol Exporter (OTLP). This will aggregate our traces and provide us with dashboards for analysis.

Demonstration

We’ll demonstrate a simple Employee Management service (EMS) that is ASP.NET core minimal API. These are the endpoints of our API:

1. POST /ems/pay/employeeId: Calculates the pay of an employee based on the hours they logged on various projects. This endpoint will display a friendly interaction with the database.
2. POST /ems/billing/pay-raise: Updates the pay of every employee earning under USD $300 to USD $300. This endpoint will display querying a field that is not indexed in the database.
3. POST /ems/payroll/remove/employeeId: Removes an employee from payroll. This endpoint will display how database locks impact the performance of queries.
4. POST /ems/add-employee/employeeId: Adds an employee to the payroll and timekeeping systems. This simulates the impact of multiple business transactions on system performance, such as multiple database calls.

Although the application is simple, we have kept it short to concentrate on instrumentation and OpenTelemetry. You won’t find coding best practices like exception handling.

Two tables make up the application database: Timekeeping and Payroll. These tables save time and money for employees by reducing their pay rates and working hours.

You can find the complete source code for this EMS application in my GitHub repository.

Read more https://thecloudblog.net/post/opentelemetry-in-action-optimizing-database-operations/