Azure反模式——繁忙的数据库

azure反模式-繁忙的数据库


将工作量卸载到数据库服务器可能会导致数据库花费大量的时间在代码的运行上面，而不是响应数据的读写请求上。


问题描述
许多数据库系统可以运行代码。例如存储过程和触发器。通常更有效的做法是在靠近数据的位置来进行处理，而不是将数据传输到客户端应用程序处理。 


但是，过多使用这些功能可能会损害性能，原因有多种：
数据库服务器可能花费过多的时间处理业务逻辑，而不是接受新数据请求并提取。
数据库通常是共享的资源，因此，在使用高峰期可能会成为瓶颈。
如果数据存储是按使用收费，运行时成本可能会非常高昂。对于托管数据库服务尤其如此。例如，AzureSQL数据库按照数据库事务单位(DTU)来计费。
数据库的纵向扩展容量有限，而横向扩展数据库的过程又不是一个简单的流程。因此，最好将工作量转移到可轻松横向扩展的计算资源，例如VM或服务应用。


出现该反模式的原因通常是：
将服务器视为服务而不是存储。应用程序可能使用了数据库服务器来完成数据格式处理（例如，转换为XML）、处理字符串数据或执行复杂计算。
开发人员尝试编写可直接向用户显示其结果的查询。例如，某查询可能合并了字段，或根据区域设置使用日期、时间和货币的格式。
开发人员尝试通过将计算推送到数据库来解决过量提取反模式(https://docs.microsoft.com/en-us/azure/architecture/antipatterns/extraneous-fetching/index)产生的问题。
使用存储过程来封装业务逻辑，原因也许是开发人员认为存储过程更容易维护和更新。


以下示例检索指定销售区域的20份最有价值的订单，并将结果转化为XML格式。它使用Transact-SQL函数来分析数据，并将结果转换为XML。可在此处找到完整示例（https://github.com/mspnp/performance-optimization/tree/master/BusyDatabase）。

SELECT TOP 20
  soh.[SalesOrderNumber]  AS '@OrderNumber',
  soh.[Status]            AS '@Status',
  soh.[ShipDate]          AS '@ShipDate',
  YEAR(soh.[OrderDate])   AS '@OrderDateYear',
  MONTH(soh.[OrderDate])  AS '@OrderDateMonth',
  soh.[DueDate]           AS '@DueDate',
  FORMAT(ROUND(soh.[SubTotal],2),'C')
                          AS '@SubTotal',
  FORMAT(ROUND(soh.[TaxAmt],2),'C')
                          AS '@TaxAmt',
  FORMAT(ROUND(soh.[TotalDue],2),'C')
                          AS '@TotalDue',
  CASE WHEN soh.[TotalDue] > 5000 THEN 'Y' ELSE 'N' END
                          AS '@ReviewRequired',
  (
  SELECT
    c.[AccountNumber]     AS '@AccountNumber',
    UPPER(LTRIM(RTRIM(REPLACE(
    CONCAT( p.[Title], ' ', p.[FirstName], ' ', p.[MiddleName], ' ', p.[LastName], ' ', p.[Suffix]),
    '  ', ' '))))         AS '@FullName'
  FROM [Sales].[Customer] c
    INNER JOIN [Person].[Person] p
  ON c.[PersonID] = p.[BusinessEntityID]
  WHERE c.[CustomerID] = soh.[CustomerID]
  FOR XML PATH ('Customer'), TYPE
  ),


  (
  SELECT
    sod.[OrderQty]      AS '@Quantity',
    FORMAT(sod.[UnitPrice],'C')
                        AS '@UnitPrice',
    FORMAT(ROUND(sod.[LineTotal],2),'C')
                        AS '@LineTotal',
    sod.[ProductID]     AS '@ProductId',
    CASE WHEN (sod.[ProductID] >= 710) AND (sod.[ProductID] <= 720) AND (sod.[OrderQty] >= 5) THEN 'Y' ELSE 'N' END
                        AS '@InventoryCheckRequired'


  FROM [Sales].[SalesOrderDetail] sod
  WHERE sod.[SalesOrderID] = soh.[SalesOrderID]
  ORDER BY sod.[SalesOrderDetailID]
  FOR XML PATH ('LineItem'), TYPE, ROOT('OrderLineItems')
  )


FROM [Sales].[SalesOrderHeader] soh
WHERE soh.[TerritoryId] = @TerritoryId
ORDER BY soh.[TotalDue] DESC
FOR XML PATH ('Order'), ROOT('Orders')

SELECT TOP 20
  soh.[SalesOrderNumber]  AS '@OrderNumber',
  soh.[Status]            AS '@Status',
  soh.[ShipDate]          AS '@ShipDate',
  YEAR(soh.[OrderDate])   AS '@OrderDateYear',
  MONTH(soh.[OrderDate])  AS '@OrderDateMonth',
  soh.[DueDate]           AS '@DueDate',
  FORMAT(ROUND(soh.[SubTotal],2),'C')
                          AS '@SubTotal',
  FORMAT(ROUND(soh.[TaxAmt],2),'C')
                          AS '@TaxAmt',
  FORMAT(ROUND(soh.[TotalDue],2),'C')
                          AS '@TotalDue',
  CASE WHEN soh.[TotalDue] > 5000 THEN 'Y' ELSE 'N' END
                          AS '@ReviewRequired',
  (
  SELECT
    c.[AccountNumber]     AS '@AccountNumber',
    UPPER(LTRIM(RTRIM(REPLACE(
    CONCAT( p.[Title], ' ', p.[FirstName], ' ', p.[MiddleName], ' ', p.[LastName], ' ', p.[Suffix]),
    '  ', ' '))))         AS '@FullName'
  FROM [Sales].[Customer] c
    INNER JOIN [Person].[Person] p
  ON c.[PersonID] = p.[BusinessEntityID]
  WHERE c.[CustomerID] = soh.[CustomerID]
  FOR XML PATH ('Customer'), TYPE
  ),


  (
  SELECT
    sod.[OrderQty]      AS '@Quantity',
    FORMAT(sod.[UnitPrice],'C')
                        AS '@UnitPrice',
    FORMAT(ROUND(sod.[LineTotal],2),'C')
                        AS '@LineTotal',
    sod.[ProductID]     AS '@ProductId',
    CASE WHEN (sod.[ProductID] >= 710) AND (sod.[ProductID] <= 720) AND (sod.[OrderQty] >= 5) THEN 'Y' ELSE 'N' END
                        AS '@InventoryCheckRequired'


  FROM [Sales].[SalesOrderDetail] sod
  WHERE sod.[SalesOrderID] = soh.[SalesOrderID]
  ORDER BY sod.[SalesOrderDetailID]
  FOR XML PATH ('LineItem'), TYPE, ROOT('OrderLineItems')
  )


FROM [Sales].[SalesOrderHeader] soh
WHERE soh.[TerritoryId] = @TerritoryId
ORDER BY soh.[TotalDue] DESC
FOR XML PATH ('Order'), ROOT('Orders')




显然，这个查询非常复杂。 稍后看到它使用了大量的数据库服务器资源。


如何解决这个问题
将处理工作从数据库服务器转移到应用层。理想情况下，应该将数据库限制为只执行数据访问，并只使用针对数据库所优化的函数，例如RDBMS中的聚合函数。


例如，可将前面的Transact-SQL代码替换为一个只检索待处理数据的语句。

SELECT
soh.[SalesOrderNumber]  AS [OrderNumber],
soh.[Status]            AS [Status],
soh.[OrderDate]         AS [OrderDate],
soh.[DueDate]           AS [DueDate],
soh.[ShipDate]          AS [ShipDate],
soh.[SubTotal]          AS [SubTotal],
soh.[TaxAmt]            AS [TaxAmt],
soh.[TotalDue]          AS [TotalDue],
c.[AccountNumber]       AS [AccountNumber],
p.[Title]               AS [CustomerTitle],
p.[FirstName]           AS [CustomerFirstName],
p.[MiddleName]          AS [CustomerMiddleName],
p.[LastName]            AS [CustomerLastName],
p.[Suffix]              AS [CustomerSuffix],
sod.[OrderQty]          AS [Quantity],
sod.[UnitPrice]         AS [UnitPrice],
sod.[LineTotal]         AS [LineTotal],
sod.[ProductID]         AS [ProductId]
FROM [Sales].[SalesOrderHeader] soh
INNER JOIN [Sales].[Customer] c ON soh.[CustomerID] = c.[CustomerID]
INNER JOIN [Person].[Person] p ON c.[PersonID] = p.[BusinessEntityID]
INNER JOIN [Sales].[SalesOrderDetail] sod ON soh.[SalesOrderID] = sod.[SalesOrderID]
WHERE soh.[TerritoryId] = @TerritoryId
AND soh.[SalesOrderId] IN (
    SELECT TOP 20 SalesOrderId
    FROM [Sales].[SalesOrderHeader] soh
    WHERE soh.[TerritoryId] = @TerritoryId
    ORDER BY soh.[TotalDue] DESC)
ORDER BY soh.[TotalDue] DESC, sod.[SalesOrderDetailID]

SELECT
soh.[SalesOrderNumber]  AS [OrderNumber],
soh.[Status]            AS [Status],
soh.[OrderDate]         AS [OrderDate],
soh.[DueDate]           AS [DueDate],
soh.[ShipDate]          AS [ShipDate],
soh.[SubTotal]          AS [SubTotal],
soh.[TaxAmt]            AS [TaxAmt],
soh.[TotalDue]          AS [TotalDue],
c.[AccountNumber]       AS [AccountNumber],
p.[Title]               AS [CustomerTitle],
p.[FirstName]           AS [CustomerFirstName],
p.[MiddleName]          AS [CustomerMiddleName],
p.[LastName]            AS [CustomerLastName],
p.[Suffix]              AS [CustomerSuffix],
sod.[OrderQty]          AS [Quantity],
sod.[UnitPrice]         AS [UnitPrice],
sod.[LineTotal]         AS [LineTotal],
sod.[ProductID]         AS [ProductId]
FROM [Sales].[SalesOrderHeader] soh
INNER JOIN [Sales].[Customer] c ON soh.[CustomerID] = c.[CustomerID]
INNER JOIN [Person].[Person] p ON c.[PersonID] = p.[BusinessEntityID]
INNER JOIN [Sales].[SalesOrderDetail] sod ON soh.[SalesOrderID] = sod.[SalesOrderID]
WHERE soh.[TerritoryId] = @TerritoryId
AND soh.[SalesOrderId] IN (
    SELECT TOP 20 SalesOrderId
    FROM [Sales].[SalesOrderHeader] soh
    WHERE soh.[TerritoryId] = @TerritoryId
    ORDER BY soh.[TotalDue] DESC)
ORDER BY soh.[TotalDue] DESC, sod.[SalesOrderDetailID]




然后，应用程序使用.NET Framework中System.Xml.Linq将结果转化为XML。

// Create a new SqlCommand to run the Transact-SQL query
using (var command = new SqlCommand(...))
{
    command.Parameters.AddWithValue("@TerritoryId", id);


    // Run the query and create the initial XML document
    using (var reader = await command.ExecuteReaderAsync())
    {
        var lastOrderNumber = string.Empty;
        var doc = new XDocument();
        var orders = new XElement("Orders");
        doc.Add(orders);


        XElement lineItems = null;
        // Fetch each row in turn, format the results as XML, and add them to the XML document
        while (await reader.ReadAsync())
        {
            var orderNumber = reader["OrderNumber"].ToString();
            if (orderNumber != lastOrderNumber)
            {
                lastOrderNumber = orderNumber;


                var order = new XElement("Order");
                orders.Add(order);
                var customer = new XElement("Customer");
                lineItems = new XElement("OrderLineItems");
                order.Add(customer, lineItems);


                var orderDate = (DateTime)reader["OrderDate"];
                var totalDue = (Decimal)reader["TotalDue"];
                var reviewRequired = totalDue > 5000 ? 'Y' : 'N';


                order.Add(
                    new XAttribute("OrderNumber", orderNumber),
                    new XAttribute("Status", reader["Status"]),
                    new XAttribute("ShipDate", reader["ShipDate"]),
                    ... // More attributes, not shown.


                    var fullName = string.Join(" ",
                        reader["CustomerTitle"],
                        reader["CustomerFirstName"],
                        reader["CustomerMiddleName"],
                        reader["CustomerLastName"],
                        reader["CustomerSuffix"]
                    )
                   .Replace("  ", " ") //remove double spaces
                   .Trim()
                   .ToUpper();


               customer.Add(
                    new XAttribute("AccountNumber", reader["AccountNumber"]),
                    new XAttribute("FullName", fullName));
            }


            var productId = (int)reader["ProductID"];
            var quantity = (short)reader["Quantity"];
            var inventoryCheckRequired = (productId >= 710 && productId <= 720 && quantity >= 5) ? 'Y' : 'N';


            lineItems.Add(
                new XElement("LineItem",
                    new XAttribute("Quantity", quantity),
                    new XAttribute("UnitPrice", ((Decimal)reader["UnitPrice"]).ToString("C")),
                    new XAttribute("LineTotal", RoundAndFormat(reader["LineTotal"])),
                    new XAttribute("ProductId", productId),
                    new XAttribute("InventoryCheckRequired", inventoryCheckRequired)
                ));
        }
        // Match the exact formatting of the XML returned from SQL
        var xml = doc
            .ToString(SaveOptions.DisableFormatting)
            .Replace(" />", "/>");
    }
}

备注
上例代码有点复杂。对于新应用程序，有时最好使用序列化库。但是，在这里的假设是开发团队正在重构现有的应用程序，因此，该方法需要返回与原始代码完全相同的格式。


注意事项
许多数据库系统对特定类型的数据操作进行了优化，例如，对大型数据集计算聚合值。如果将处理逻辑放应用去做会导致数据库网络传输过多数据，那么就不必这样做。 请参阅超量提取反模式。（https://docs.microsoft.com/en-us/azure/architecture/antipatterns/extraneous-fetching/index）
如果将处理工作转移到了应用程序层，该层可能需要横向扩展以处理额外的工作。

如何检测问题
繁忙数据库的症状包括在访问数据库的操作中，吞吐量和响应时间不成比例的下降。


可执行以下步骤来帮助检测问题：
使用性能监视来统计生产系统花费了多少时间执行数据库活动。
检查在这些时段内数据库执行的工作。
如果怀疑特定操作导致数据库活动过多，在受控环境中执行负载测试。每项测试应该在施加可变用户负载的情况下，混合运行可疑的操作。检查负载测试返回的数据，以确认数据库的使用方式。
如果数据库活动表现为工作量很大，但数据流量很小，检查代码，调试是否在其他位置执行处理可获得更好的效果。
如果数据库活动量较低或响应时间相对较快，则性能问题不太可能是数据库繁忙造成的。


诊断过程示例
以下诊断步骤是基于前面所述的示例应用程序完成的。


监视数据库活动量
下图显示了50个并发用户对示例应用程序运行的负载测试的结果。请求数量很快达到限制并保持水平，同时，平均响应时间稳步增加。请注意，这两个指标使用了对数刻度。



下图以服务配额百分比的形式显示了CPU利用率和DTU。DTU可以度量数据库所执行的工作量。该图显示了CPU和DTU利用率都很快达到100%。



检查数据库执行的工作
有可能数据库执行的任务是真正的数据访问操作而不是业务处理，因此，必须了解数据库繁忙时运行的SQL语句。监视系统以捕获SQL流量并将SQL操作与应用程序请求相关联。如果数据库操作是单纯的数据访问而没有执行大量的数据处理，则问题可能在于超量提取。


实施解决方案并验证结果
下图显示了使用更新的代码执行负载测试。吞吐量明显提高，每秒超过了400个请求，而上次测试中为12个。平均响应时间也大大降低，略大于0.1秒，而前次测试中为4秒。



CPU和DTU利用率显示，尽管吞吐量已提高，但系统花费了较长的时间才达到饱和。



相关模式阅读
超量提取反模式（https://docs.microsoft.com/en-us/azure/architecture/antipatterns/extraneous-fetching/index）