Antimuster „Ausgelastete Datenbank“

Das Auslagern der Verarbeitung auf einen Datenbankserver kann dazu führen, dass er einen erheblichen Teil der Zeit mit dem Ausführen von Code verbringt, anstatt auf Anforderungen zum Speichern und Abrufen von Daten zu reagieren.

Problembeschreibung

Viele Datenbanksysteme können Code ausführen. Beispiele sind gespeicherte Prozeduren und Trigger. Häufig ist es effizienter, diese Verarbeitung in der Nähe der Daten durchzuführen, anstatt die Daten zur Verarbeitung an eine Clientanwendung zu übertragen. Die Überlastung dieser Features kann jedoch die Leistung beeinträchtigen, aus mehreren Gründen:

• Der Datenbankserver verbringt möglicherweise zu viel Zeit mit der Verarbeitung, anstatt neue Clientanforderungen zu akzeptieren und Daten abzurufen.
• Eine Datenbank ist in der Regel eine freigegebene Ressource, sodass sie zu einem Engpass in Zeiträumen mit hoher Verwendung werden kann.
• Laufzeitkosten können übermäßig hoch sein, wenn der Datenspeicher getaktet wird. Das gilt besonders für verwaltete Datenbankdienste. Azure SQL-Datenbank berechnet Gebühren für Datenbanktransaktionseinheiten (DTUs).
• Datenbanken verfügen über eine begrenzte Kapazität zum Skalieren, und es ist nicht trivial, eine Datenbank horizontal zu skalieren. Daher ist es möglicherweise besser, die Verarbeitung in eine Computeressource zu verschieben, z. B. eine VM oder App Service-App, die problemlos skaliert werden kann.

Dieses Antimuster tritt üblicherweise aus folgenden Gründen auf:

• Die Datenbank wird nicht als Repository, sondern als Dienst betrachtet. Eine Anwendung kann den Datenbankserver verwenden, um Daten zu formatieren (z. B. in XML zu konvertieren), Zeichenfolgendaten zu bearbeiten oder komplexe Berechnungen durchzuführen.
• Entwickler versuchen, Abfragen zu schreiben, deren Ergebnisse direkt für Benutzer angezeigt werden können. Beispielsweise kann eine Abfrage Felder kombinieren oder Datumsangaben, Uhrzeiten und Währungen entsprechend dem Gebietsschema formatieren.
• Entwickler versuchen, das Zusätzliches Abrufen-Antimuster zu korrigieren, indem sie Berechnungen an die Datenbank übertragen.
• Gespeicherte Prozeduren werden verwendet, um Geschäftslogik zu kapseln, vielleicht weil sie als einfacher zu verwalten und zu aktualisieren gilt.

Im folgenden Beispiel werden die 20 wertvollsten Bestellungen für ein bestimmtes Vertriebsgebiet abgerufen und die Ergebnisse als XML formatiert. Es verwendet Transact-SQL Funktionen, um die Daten zu analysieren und die Ergebnisse in XML zu konvertieren.

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')

Dies ist eindeutig eine komplexe Abfrage. Wie wir später sehen, stellt sich heraus, dass erhebliche Verarbeitungsressourcen auf dem Datenbankserver verwendet werden.

So lösen Sie das Problem

Verschieben sie die Verarbeitung vom Datenbankserver in andere Anwendungsebenen. Im Idealfall sollten Sie die Datenbank auf die Durchführung von Datenzugriffsvorgängen beschränken, wobei nur die Funktionen verwendet werden, für die die Datenbank optimiert ist, z. B. Aggregation in einem relationalen Datenbankverwaltungssystem (RDBMS).

Beispielsweise kann der vorherige Transact-SQL Code durch eine Anweisung ersetzt werden, die einfach die zu verarbeitenden Daten abruft.

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]

Die Anwendung verwendet dann die .NET Framework-APIs System.Xml.Linq , um die Ergebnisse als XML zu formatieren.

// 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(" />", "/>");
    }
}

Überlegungen

• Viele Datenbanksysteme sind hoch optimiert, um bestimmte Arten von Datenverarbeitung durchzuführen, z. B. das Berechnen von Aggregatwerten über große Datasets. Verschieben Sie diese Verarbeitungstypen nicht aus der Datenbank.

• Verschieben Sie die Verarbeitung nicht, wenn dies bewirkt, dass die Datenbank wesentlich mehr Daten über das Netzwerk überträgt. Weitere Informationen finden Sie unter Antimuster „Extraneous Fetching“ (Irrelevante Abrufe).

• Wenn Sie die Verarbeitung auf eine Anwendungsebene verschieben, muss diese Ebene möglicherweise skaliert werden, um die zusätzliche Arbeit zu verarbeiten.

So erkennen Sie das Problem

Symptome einer ausgelasteten Datenbank umfassen einen unverhältnismäßigen Rückgang des Durchsatzes und Reaktionszeiten in Vorgängen, die auf die Datenbank zugreifen.

Sie können die folgenden Schritte ausführen, um dieses Problem zu identifizieren:

1. Verwenden Sie die Leistungsüberwachung, um zu ermitteln, wie viel Zeit das Produktionssystem für die Durchführung von Datenbankaktivitäten aufwendet.

2. Überprüfen Sie die arbeit, die die Datenbank während dieser Zeiträume ausgeführt hat.

3. Wenn Sie vermuten, dass bestimmte Vorgänge zu viel Datenbankaktivität verursachen können, führen Sie Auslastungstests in einer kontrollierten Umgebung durch. Jeder Test sollte eine Mischung der verdächtigen Vorgänge mit einer variablen Benutzerlast ausführen. Überprüfen Sie die Telemetrie aus den Ladetests, um zu beobachten, wie die Datenbank verwendet wird.

4. Wenn die Datenbankaktivität erhebliche Verarbeitung, aber wenig Datenverkehr zeigt, überprüfen Sie den Quellcode, um festzustellen, ob die Verarbeitung an anderer Stelle besser ausgeführt werden kann.

Wenn das Volumen der Datenbankaktivität gering ist oder die Antwortzeiten relativ schnell sind, ist es unwahrscheinlich, dass eine ausgelastete Datenbank ein Leistungsproblem darstellt.

Beispieldiagnose

In den folgenden Abschnitten werden diese Schritte auf die zuvor beschriebene Beispielanwendung angewendet.

Überwachen des Umfangs der Datenbankaktivität

Das folgende Diagramm zeigt die Ergebnisse der Ausführung eines Auslastungstests für die Beispielanwendung mit einer Schrittlast von bis zu 50 gleichzeitigen Benutzern. Die Anzahl der Anforderungen erreicht schnell einen Grenzwert und bleibt auf dieser Ebene, während die durchschnittliche Reaktionszeit stetig zunimmt. Eine logarithmische Skalierung wird für diese beiden Metriken verwendet.

In diesem Liniendiagramm werden benutzerlasten, Anforderungen pro Sekunde und durchschnittliche Antwortzeit angezeigt. Das Diagramm zeigt, dass sich die Reaktionszeit erhöht, wenn die Last erhöht wird.

Das nächste Diagramm zeigt die CPU-Auslastung und DTUs als Prozentsatz des Dienstkontingents. DTUs stellen ein Maß dafür bereit, wie viel Verarbeitung der Datenbank ausgeführt wird. Das Diagramm zeigt, dass die CPU- und DTU-Auslastung beide schnell 100%erreicht haben.

In diesem Liniendiagramm wird der CPU-Prozentsatz und der DTU-Prozentsatz im Laufe der Zeit angezeigt. Das Diagramm zeigt, dass beide Werte schnell 100 Prozent erreichen.

Überprüfen der von der Datenbank ausgeführten Arbeit

Es könnte sein, dass die von der Datenbank ausgeführten Aufgaben echte Datenzugriffsvorgänge sind, anstatt zu verarbeiten, daher ist es wichtig, die SQL-Anweisungen zu verstehen, die ausgeführt werden, während die Datenbank ausgelastet ist. Überwachen Sie das System, um den SQL-Datenverkehr zu erfassen und die SQL-Vorgänge mit Anwendungsanforderungen zu korrelieren.

Wenn es sich bei den Datenbankvorgängen um rein Datenzugriffsvorgänge handelt, ohne dass viele Verarbeitungen erforderlich sind, kann das Problem möglicherweise eine überflüssige Abrufung sein.

Implementieren der Lösung und Überprüfen des Ergebnisses

Das folgende Diagramm zeigt einen Auslastungstest mit dem aktualisierten Code. Der Durchsatz ist deutlich höher, über 400 Anforderungen pro Sekunde im Vergleich zu 12 früheren Versionen. Die durchschnittliche Reaktionszeit ist ebenfalls viel niedriger, knapp über 0,1 Sekunden im Vergleich zu mehr als 4 Sekunden.

In diesem Liniendiagramm werden benutzerlasten, Anforderungen pro Sekunde und durchschnittliche Antwortzeit angezeigt. Das Diagramm zeigt, dass die Reaktionszeit während des Gesamten Auslastungstests ungefähr konstant bleibt.

Die CPU- und DTU-Auslastung zeigt, dass das System trotz des erhöhten Durchsatzes länger dauerte, um die Sättigung zu erreichen.

In diesem Liniendiagramm wird der CPU-Prozentsatz und der DTU-Prozentsatz im Laufe der Zeit angezeigt. Das Diagramm zeigt, dass CPU und DTU länger brauchen, um 100% zu erreichen als zuvor.

Verwandte Ressourcen

• Antimuster „Extraneous Fetching“ (Irrelevante Abrufe)