行动中的OpenTelemetry：优化数据库操作

了解如何使用OpenTelemetry来发现数据库性能问题，并在不显著改变你的应用程序的情况下对其进行补救。

许多软件开发人员可以证明，他们的应用程序中一些最重要的问题来自数据库性能。尽管许多开发者喜欢在企业应用中使用关系型数据库，但典型的日志和监控解决方案在检测数据库性能问题方面提供的信号有限。根除常见的不良做法，如应用程序代码和数据库之间的闲聊式互动，并不是一件简单的事情。

作为开发者，我们需要了解我们的数据库是如何从用户事务的背景下执行的。理想情况下，我们会有一个通用的工具，可以监控应用程序和数据库关于用户事务的性能。OpenTelemetry已经成为一个流行的应用程序监控工具，但它也可以扩展到监控数据库。

在这篇文章中，我们将看看一些常见的数据库性能问题，看看我们如何使用OpenTelemetry来识别并轻松解决这些问题。为了获得实际的学习经验，我们将建立一个使用SQL Server数据库的简单应用。我们将用标准的OpenTelemetry库来检测这个应用程序，并把这个应用程序连接到一个摄取平台Lightstep。最后，我们将使用摄取的遥测数据来显示我们的数据库问题，讨论解决这些问题的步骤。

可观察性和OpenTelemetry的基础知识

如果你不熟悉可观察性或OpenTelemetry，我建议你先熟悉一下这个 "OpenTelemetry在行动 "系列中的前一篇文章。在那篇文章中，我们介绍了在可观察的应用程序中收集的三种类型的信息（日志、度量和跟踪）。我们还看了OpenTelemetry数据模型的关键组件。

关于OpenTelemetry的工作原理，你可以参考该文档的更深入介绍。

使用OpenTelemetry来监控数据库

我们注意到了OpenTelemetry是如何用于仪表应用程序的可观察性的，以及如何为我们在监控数据库方面进行扩展。这种监控是通过数据库客户端而不是直接在数据库服务器上进行的。

由于访问限制或你的平台的性质，你可能被限制在数据库服务器上安装监控库。相反，你可以使用OpenTelemetry工具来从客户端监控数据库。尽管该工具不会让你了解数据库的内部情况，但它将为你提供足够的信息来解决性能问题，以改善应用程序的用户体验。

使用OpenTelemetry来检测数据库性能问题

我们检测数据库性能问题的项目设置与第一部分中的设置非常相似，在第一部分中我们使用OpenTelemetry来识别数据库的依赖关系。

同样，我们将使用.NET SQLClient 仪表进行 OpenTelemetry，并使用Lightstep进行遥测存储和分析。

我们将用OpenTelemetry SDK对我们的应用程序进行检测，以发射可观察性信号。我们将使用OpenTelemetry协议（OTLP）导出器将数据发送到Lightstep，聚合我们的轨迹并为我们提供仪表板来分析洞察力。

演示

对于我们的演示，我们将创建一个简单的员工管理服务（EMS），作为ASP.NET Core最小的API模型。我们的API有以下几个端点：

POST /ems/pay/{employeeId}:根据员工在各种项目上记录的时间来计算他们的工资。这个端点将表现出与数据库的聊天式互动。
POST /ems/billing/pay-raise:将每个收入低于300美元的员工的工资更新为300美元。这个端点将表现为查询数据库中的一个非索引字段。
POST /ems/payroll/remove/{employeeId}:从工资单中删除一个雇员。这个端点将展示数据库锁如何影响查询的性能。
POST /ems/add-employee/{employeeId}:将一个雇员添加到工资和计时系统中。这模拟了一个跨越多个服务的业务交易的性能--因此，多个数据库调用--如何影响系统性能。

这个应用很简单，但我们保持了简洁，以关注仪器和OpenTelemetry的使用。由于这个原因，你不会看到编码的最佳实践，如异常处理。

应用程序数据库由两个表组成。薪资表和计时表，它们保存了雇员的工资率和项目的工作时间。

这个EMS应用程序的完整源代码可以在GitHub仓库中找到。

启用数据库

我们首先在docker中启动了一个SQL服务器实例：

壳

docker run \
-e "ACCEPT_EULA=Y" \
-e "SA_PASSWORD=Str0ngPa$$w0rd" \
-p 1433:1433 \
--name monolith-db \
--hostname sql1 \
-d mcr.microsoft.com/mssql/server:2019-latest

然后，我们创建EMS数据库和应用程序使用的表，以及存储一些种子数据：

SQL

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'EMSDb')
BEGIN
    CREATE DATABASE EMSDb
END
GO

USE EMSDb

IF OBJECT_ID('[dbo].[Timekeeping]', 'U') IS NULL
BEGIN
    CREATE TABLE [Timekeeping] (
        [EmployeeId]      INT  NOT NULL,
        [ProjectId]       INT  NOT NULL,
        [WeekClosingDate] DATETIME NOT NULL,
        [HoursWorked]     INT  NOT NULL,
        CONSTRAINT [PK_Timekeeping] PRIMARY KEY CLUSTERED ([EmployeeId] ASC, [ProjectId] ASC,  [WeekClosingDate] ASC)
    )
END
GO

IF OBJECT_ID('[dbo].[Payroll]', 'U') IS NULL
BEGIN
    CREATE TABLE [Payroll] (
        [EmployeeId]   INT   NOT NULL,
        [PayRateInUSD] MONEY DEFAULT 0 NOT NULL,
        CONSTRAINT [PK_Payroll] PRIMARY KEY CLUSTERED ([EmployeeId] ASC)
    )
END
GO

TRUNCATE TABLE Payroll
TRUNCATE TABLE Timekeeping


INSERT INTO Payroll Values(1, 100)
INSERT INTO Payroll Values(2, 200)
INSERT INTO Payroll Values(3, 300)

INSERT INTO Timekeeping Values(1, 1111, GETDATE(), 10)
INSERT INTO Timekeeping Values(1, 2222, GETDATE(), 15)
INSERT INTO Timekeeping Values(2, 1111, GETDATE(), 15)
INSERT INTO Timekeeping Values(3, 2222, GETDATE(), 20)
GO

实现API端点

然后，我们编写API端点的代码。我们用以下内容替换程序类中的模板代码。

using System.Data.SqlClient;
using System.Diagnostics;
using Dapper;
using OpenTelemetry.Exporter;
using OpenTelemetry.Resources;
using OpenTelemetry.Trace;

var builder = WebApplication.CreateBuilder(args);

var lsToken = builder.Configuration.GetValue<string>("LsToken");

builder.Services.AddScoped(_ =>
    new SqlConnection(
      builder.Configuration.GetConnectionString("EmployeeDbConnectionString")
    )
);
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

app.UseSwagger();
app.UseSwaggerUI();

app.MapGet("/ems/pay/{empId}", async (int empId, SqlConnection db) =>
    {
        // op 1
        var payroll =
            await db.QuerySingleOrDefaultAsync<Payroll>("SELECT EmployeeId,PayRateInUSD FROM Payroll WHERE EmployeeId=@EmpId",
                new { EmpId = empId });

        // op 2
        var projects = await db.QueryAsync<Timekeeping>("SELECT EmployeeId,ProjectId,WeekClosingDate,HoursWorked FROM Timekeeping WHERE EmployeeId=@EmpId",
            new { EmpId = empId });

        var moneyEarned = projects.Sum(p => p.HoursWorked) * payroll.PayRateInUSD;
        return Results.Ok(moneyEarned);
    })
    .WithName("GetPayment")
    .Produces(StatusCodes.Status200OK);

app.MapPost("/ems/billing/pay-raise/", async (SqlConnection db) =>
    {
        var recordsAffected = await db.ExecuteAsync("UPDATE Payroll SET PayRateInUSD = 300 WHERE PayRateInUSD < 300");
        return Results.Ok(recordsAffected);
    })
    .WithName("Pay-Raise")
    .Produces(StatusCodes.Status200OK);

app.MapPost("/ems/payroll/remove/{empId}", async (int empId, SqlConnection db) =>
    {
        Payroll payrollRecord = new();
        async Task DeleteRecord()
        {
            db.Open();
            await using var tr = await db.BeginTransactionAsync();
            await db.ExecuteAsync("DELETE FROM Payroll WHERE EmployeeId=@EmpId", new { EmpId = empId }, tr);
            Thread.Sleep(5000);
            await tr.CommitAsync();
        }

        async Task GetRecord()
        {
            await using var db1 =
                new SqlConnection(builder.Configuration.GetConnectionString("EmployeeDbConnectionString"));
            Thread.Sleep(100);
            db1.Open();
            payrollRecord =
                await db1.QuerySingleOrDefaultAsync<Payroll>(
                    "SELECT EmployeeId,PayRateInUSD FROM Payroll WHERE EmployeeId=@EmpId", new { EmpId = empId });
            await db1.CloseAsync();
        }

        await Task.WhenAll(DeleteRecord(), GetRecord());

        return Results.Ok(payrollRecord);
    })
    .WithName("RemoveEmployeeFromPayroll")
    .Produces(StatusCodes.Status200OK);

app.MapPost("/ems/add-employee/{empId}", async (int empId, SqlConnection db) =>
    {
        //op 1
        await db.ExecuteAsync("INSERT INTO Payroll Values(@EmployeeId, @PayRateInUSD)",
            new Payroll { EmployeeId = empId, PayRateInUSD = 100 });

        // Simulate service call
        // Mock network call delay
        Thread.Sleep(1000);

        //op 2
        await db.ExecuteAsync(
            "INSERT INTO Timekeeping Values(@EmployeeId, @ProjectId, @WeekClosingDate, @HoursWorked)",
            new Timekeeping
            { EmployeeId = empId, HoursWorked = 0, ProjectId = 1, WeekClosingDate = DateTime.Today });

        return Results.Ok();    })
    .WithName("AddEmployee")
    .Produces(StatusCodes.Status201Created);

app.Run();


public class Timekeeping
{
    public int EmployeeId { get; set; }
    public int ProjectId { get; set; }
    public DateTime WeekClosingDate { get; set; }
    public int HoursWorked { get; set; }
}

public class Payroll
{
    public int EmployeeId { get; set; }
    public decimal PayRateInUSD { get; set; }
}

现在我们的应用代码已经到位，让我们把检测我们所造成的性能问题的过程自动化。

添加仪表

我们用OpenTelemetry SDK和.NET的SqlClient工具库来检测应用程序。首先，我们在API的项目文件中添加以下NuGet包参考。

XML

<PackageReference Include="OpenTelemetry" Version="1.2.0-rc2" />
<PackageReference Include="OpenTelemetry.Exporter.OpenTelemetryProtocol" Version="1.2.0-rc2" />
<PackageReference Include="OpenTelemetry.Extensions.Hosting" Version="1.0.0-rc9" />
<PackageReference Include="OpenTelemetry.Instrumentation.AspNetCore" Version="1.0.0-rc9" />
<PackageReference Include="OpenTelemetry.Instrumentation.Http" Version="1.0.0-rc9" />
<PackageReference Include="OpenTelemetry.Instrumentation.SqlClient" Version="1.0.0-rc9" />

SDK为我们提供了几个扩展方法，我们可以使用这些方法来快速将OpenTelemetry插入到请求处理管道中。

接下来，我们在我们的应用程序中检测OpenTelemetry。这也将使SqlClient ，以发出冗长的遥测信息。这些遥测数据将是揭示数据库性能问题的关键。

// Configure tracing
builder.Services.AddOpenTelemetryTracing(builder => builder
    // Customize the traces gathered by the HTTP request handler
    .AddAspNetCoreInstrumentation(options =>
    {
        // Only capture the spans generated from the ems/* endpoints
        options.Filter = context => context.Request.Path.Value?.Contains("ems") ?? false;
        options.RecordException = true;
        // Add metadata for the request such as the HTTP method and response length
        options.Enrich = (activity, eventName, rawObject) =>
        {
            switch (eventName)
            {
                case "OnStartActivity":
                {
                    if (rawObject is not HttpRequest httpRequest)
                    {
                        return;
                    }

                    activity.SetTag("requestProtocol", httpRequest.Protocol);
                    activity.SetTag("requestMethod", httpRequest.Method);
                    break;
                }
                case "OnStopActivity":
                {
                    if (rawObject is HttpResponse httpResponse)
                    {
                        activity.SetTag("responseLength", httpResponse.ContentLength);
                    }

                    break;
                }
            }
        };
    })
    // Customize the telemetry generated by the SqlClient
    .AddSqlClientInstrumentation(options =>
    {
        options.EnableConnectionLevelAttributes = true;
        options.SetDbStatementForStoredProcedure = true;
        options.SetDbStatementForText = true;
        options.RecordException = true;
        options.Enrich = (activity, x, y) => activity.SetTag("db.type", "sql");
    })
    .AddSource("my-corp.ems.ems-api")
    // Create resources (key-value pairs) that describe your service such as service name and version
    .SetResourceBuilder(ResourceBuilder.CreateDefault().AddService("ems-api")
        .AddAttributes(new[] { new KeyValuePair<string, object>("service.version", "1.0.0.0") }))
    // Ensures that all activities are recorded and sent to exporter
    .SetSampler(new AlwaysOnSampler())
    // Exports spans to Lightstep
    .AddOtlpExporter(otlpOptions =>
    {
        otlpOptions.Endpoint = new Uri("https://ingest.lightstep.com:443/traces/otlp/v0.9");
        otlpOptions.Headers = $"lightstep-access-token={lsToken}";
        otlpOptions.Protocol = OtlpExportProtocol.HttpProtobuf;
    }));

尽管在目前的状态下，仪器化对我们来说已经足够了，让我们通过添加相关的跟踪来进一步丰富数据。

首先，我们定义一个追踪器，我们的应用跨度将从这里开始：

var activitySource = new ActivitySource("my-corp.ems.ems-api");

接下来，我们创建一个跨度并添加相关的细节--属性和事件：

app.MapGet("/ems/pay/{empId}", async (int empId, SqlConnection db) =>
    {
        using var activity = activitySource.StartActivity("Chatty db operation", ActivityKind.Server);
        activity?.SetTag(nameof(Timekeeping.EmployeeId), empId);

        // op 1
        var payroll =
            await db.QuerySingleOrDefaultAsync<Payroll>("SELECT EmployeeId,PayRateInUSD FROM Payroll WHERE EmployeeId=@EmpId",
                new { EmpId = empId });

        // op 2
        var projects = await db.QueryAsync<Timekeeping>("SELECT EmployeeId,ProjectId,WeekClosingDate,HoursWorked FROM Timekeeping WHERE EmployeeId=@EmpId",
            new { EmpId = empId });

        var moneyEarned = projects.Sum(p => p.HoursWorked) * payroll.PayRateInUSD;
        return Results.Ok(moneyEarned);
    })
    .WithName("GetPayment")
    .Produces(StatusCodes.Status200OK);

我们按照同样的程序对其余的端点进行检测。

向Lightstep发送仪表数据

为了将我们的应用程序连接到Lightstep进行数据摄取，我们需要一个API密钥。首先，我们在Lightstep创建一个账户。然后，从项目设置页面，我们复制Token，这就是我们的API密钥。

我们把该令牌粘贴到我们的appsettings 文件中。

JSON

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "EmployeeDbConnectionString": "Server=localhost;Database=EMSDb;User Id=sa;Password=Str0ngPa$$w0rd;"
  },
  "LsToken": "<Lightstep token>"
}

寻找常见的数据库问题

我们的应用程序现在已经准备好了。让我们逐一回顾一下我们常见的数据库问题。

与数据库的聊天/顺序互动

让我们把/ems/pay/{empId} 端点重新作为重点。通过检查上面的代码，你会发现这个端点对数据库进行了两次调用，一个接着一个。

非最佳的、喋喋不休的数据库调用拖慢了用户的交易。诚然，你会遇到这样的情况：你需要读取一条记录，根据记录的状态做出决定，然后更新记录。在这种情况下，有多个数据库调用是不可避免的。然而，对于获取记录，你几乎总是可以使用一个单一的查询。我们启动应用程序(dotnet run)，向/ems/pay/{empId} 端点发送一些请求。接下来，我们导航到Lightstep可观察性门户，点击操作标签，查看Lightstep从应用中收到的所有跨度。

我们点击/ems/pay/{employeeId} 操作，查看其端到端的跟踪。通过查看跨度，我们可以确定任何请求的操作顺序，包括数据库交互。点击跨度带来了它的事件和属性，让我们更深入地了解该操作。

在跟踪中可见的最后两个跨度是EMSDb ，由我们的工具化SQL客户端生成。我们点击这些跨度来查看其属性和事件。从这些细节中，我们可以获得一些重要的见解：

数据库的名称
在数据库操作中使用的SQL语句
SQL语句的类型（文本或存储过程）。
提出请求的服务的主机名
数据库操作的持续时间

端点的聊天行为在追踪中非常明显。一个致命的问题是，在一个读操作中出现了两个数据库操作，而在这两个操作之间却没有记录任何业务逻辑的自定义跟踪。为了解决这个问题，我们需要结合查询，使它们产生一个单一的结果集。

未经优化的查询

查询性能是一个需要监控的重要指标。如果查询请求大量的数据（例如，通过使用SELECT * 方法或在非索引字段上过滤数据），那么用户事务的性能将受到影响。让我们重新审视一下/emp/billing/pay-raise 端点：

app.MapPost("/ems/billing/pay-raise/", async (SqlConnection db) =>
    {
        using var activity = activitySource.StartActivity("Non optimized query", ActivityKind.Server);
        var recordsAffected = await db.ExecuteAsync("UPDATE Payroll SET PayRateInUSD = 300 WHERE PayRateInUSD < 300");
        return Results.Ok(recordsAffected);
    })
    .WithName("Pay-Raise")
    .Produces(StatusCodes.Status200OK);

该查询在PayRateInUSD 字段上过滤记录，我们还没有对其进行索引。虽然这个问题在有少量记录的数据库中不会很明显，但在大型数据库中这样的查询会花费很多时间。如果查询持续时间超过了可接受的限度，你可以进一步检查操作，对用于过滤的任何字段进行索引。这将加快你的数据库性能。

数据库锁

数据库锁是要找到的最复杂的问题之一，因为它们只影响等待释放锁的操作。同时，获取锁的操作不受影响。然而，OpenTelemetry使得检测数据库锁变得很容易，因为我们可以在同一个跟踪中查看罪魁祸首的操作和等待锁的操作。让我们来讨论一下我们的违规端点背后的代码，/ems/payroll/remove/{empId} 。

app.MapPost("/ems/payroll/remove/{empId}", async (int empId, SqlConnection db) =>
    {
        using var activity = activitySource.StartActivity("Db lock", ActivityKind.Server);
        activity?.SetTag(nameof(Timekeeping.EmployeeId), empId);

        Payroll payrollRecord = new();
        async Task DeleteRecord()
        {
            db.Open();
            await using var tr = await db.BeginTransactionAsync();
            await db.ExecuteAsync("DELETE FROM Payroll WHERE EmployeeId=@EmpId", new { EmpId = empId }, tr);
            Thread.Sleep(5000);
            await tr.CommitAsync();
        }

        async Task GetRecord()
        {
            await using var db1 =
                new SqlConnection(builder.Configuration.GetConnectionString("EmployeeDbConnectionString"));
            Thread.Sleep(100);
            db1.Open();
            payrollRecord =
                await db1.QuerySingleOrDefaultAsync<Payroll>(
                    "SELECT EmployeeId,PayRateInUSD FROM Payroll WHERE EmployeeId=@EmpId", new { EmpId = empId });
            await db1.CloseAsync();
        }

        await Task.WhenAll(DeleteRecord(), GetRecord());

        return Results.Ok(payrollRecord);
    })
    .WithName("RemoveEmployeeFromPayroll")
    .Produces(StatusCodes.Status200OK);

DELETE 操作开始了一个事务，但在一段时间内没有提交。这个操作锁定了记录，一旦事务提交就会被删除。在给DELETE 事务一个小的开始后，我们执行一个SELECT 操作来读取相同的记录。除非DELETE 操作释放锁，否则SELECT 操作不能继续。从这个操作的OpenTelemetry跟踪中可以清楚地看到这一点。

如果你要单独调查这些操作，你可能会认为SELECT 操作是性能问题的来源。然而，数据库操作的聚合跨度指出了DELETE 和SELECT 操作之间的顺序依赖，这将促使你考虑它们之间的关系。

解决这个问题的方法是立即提交一个事务，而不用等待任何冗长的操作完成。

跨越多个服务的业务交易

如果你的用户的交易跨越了多个服务，那么你应该测量应用程序和网络的不同部分的响应时间。/ems/add-employee/{empId} 端点模拟了一个跨越两个服务的业务交易，如下所示。

app.MapPost("/ems/add-employee/{empId}", async (int empId, SqlConnection db) =>
    {
        using var activity =
            activitySource.StartActivity("Multiple ops in a business transaction", ActivityKind.Server);
        activity?.SetTag(nameof(Timekeeping.EmployeeId), empId);

        //op 1
        await db.ExecuteAsync("INSERT INTO Payroll Values(@EmployeeId, @PayRateInUSD)",
            new Payroll { EmployeeId = empId, PayRateInUSD = 100 });

        // Simulate service call by creating another span
        using var innerActivity = activitySource.StartActivity("Second operation of business transaction", ActivityKind.Server);
        {
            // Mock network call delay
            Thread.Sleep(1000);

            //op 2
            await db.ExecuteAsync(
                "INSERT INTO Timekeeping Values(@EmployeeId, @ProjectId, @WeekClosingDate, @HoursWorked)",
                new Timekeeping
                    { EmployeeId = empId, HoursWorked = 0, ProjectId = 1, WeekClosingDate = DateTime.Today });
        }

        return Results.Ok();
    })
    .WithName("AddEmployee")
    .Produces(StatusCodes.Status201Created);

这类问题的解决方案在复杂性上可能有所不同。对于简单的解决方法，你可以通过把服务放在物理上接近彼此的地方（例如，在同一个数据中心）来优化网络性能。一个更复杂的方法是改造应用程序，使其最终一致，这样你就可以异步地完成一个事务。

总结

这篇文章讨论了如何使用OpenTelemetry来轻松检测应用数据库的性能问题。应用数据库的性能应该与用户事务一起被监控。这有助于我们避免进行不必要的、低价值的数据库优化。

在不以任何方式改变数据库的情况下，我们对一个简单的应用程序进行了检测，以发现几种类型的数据库性能问题。有了足够的背景，我们就可以快速地修复潜在的问题，或者我们可以自信地制定一个计划来提高数据库的性能。

使用OpenTelemetry发现数据库性能问题及修补的实例