Antywzorzec przeciążonej bazy danych

2025-04-17

Odciążanie przetwarzania na serwerze bazy danych może spowodować, że poświęca znaczną część czasu na uruchamianie kodu, zamiast odpowiadać na żądania dotyczące przechowywania i pobierania danych.

Opis problemu

Wiele systemów baz danych może uruchamiać kod. Przykłady obejmują procedury składowane i wyzwalacze. Często wydajniejsze jest wykonywanie tego przetwarzania w pobliżu danych, a nie przesyłanie danych do aplikacji klienckiej na potrzeby przetwarzania. Jednak nadmierne wykorzystanie tych funkcji może zaszkodzić wydajności z kilku powodów:

Serwer bazy danych może poświęcać zbyt dużo czasu na przetwarzanie, a nie akceptowanie nowych żądań klientów i pobieranie danych.
Baza danych jest zwykle zasobem udostępnionym, więc może stać się wąskim gardłem w okresach intensywnego użytkowania.
Koszty środowiska uruchomieniowego mogą być nadmierne, jeśli magazyn danych jest mierzony. Dotyczy to szczególnie zarządzanych usług baz danych. Na przykład opłaty w usłudze Azure SQL Database za jednostki transakcji bazy danych (DTU).
Bazy danych mają ograniczoną pojemność do skalowania w górę i nie jest trywialne skalowanie ich w poziomie. W związku z tym lepszym rozwiązaniem może być przeniesienie przetwarzania do zasobu obliczeniowego, takiego jak maszyna wirtualna lub aplikacja App Service, które można łatwo skalować w poziomie.

Oto typowe przyczyny występowania tego antywzorca:

Baza danych jest postrzegana jako usługa, a nie repozytorium. Aplikacja może używać serwera bazy danych do formatowania danych (na przykład konwersji na xml), manipulowania danymi ciągów lub wykonywania złożonych obliczeń.
Deweloperzy próbują pisać zapytania, których wyniki mogą być wyświetlane bezpośrednio użytkownikom. Na przykład zapytanie może łączyć pola lub formatować daty, godziny i walutę zgodnie z ustawieniami regionalnymi.
Deweloperzy próbują poprawić antywzorzec nadmiarowego pobierania , wypychając obliczenia do bazy danych.
Procedury składowane są używane do hermetyzacji logiki biznesowej, być może dlatego, że są one uważane za łatwiejsze do utrzymania i aktualizacji.

Poniższy przykład pobiera 20 najbardziej wartościowych zamówień dla określonego terytorium sprzedaży i formatuje wyniki jako XML. Używa Transact-SQL funkcji do analizowania danych i konwertowania wyników na xml.

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

Oczywiście jest to złożone zapytanie. Jak zobaczymy później, okazuje się, że na serwerze bazy danych są używane znaczące zasoby obliczeniowe.

Jak rozwiązać problem

Przenoszenie przetwarzania z serwera bazy danych do innych warstw aplikacji. W idealnym przypadku należy ograniczyć bazę danych do wykonywania operacji dostępu do danych przy użyciu tylko możliwości zoptymalizowanych pod kątem bazy danych, takich jak agregacja w systemie zarządzania relacyjnymi bazami danych (RDBMS).

Na przykład poprzedni kod Transact-SQL można zastąpić instrukcją, która po prostu pobiera dane do przetworzenia.

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]

Następnie aplikacja używa interfejsów API programu .NET Framework System.Xml.Linq do formatowania wyników jako 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(" />", "/>");
    }
}

Uwaga / Notatka

Ten kod jest nieco złożony. W przypadku nowej aplikacji możesz użyć biblioteki serializacji. Jednak założeniem jest to, że zespół deweloperów refaktoryzuje istniejącą aplikację, więc metoda musi zwrócić dokładnie taki sam format jak oryginalny kod.

Rozważania

Wiele systemów baz danych jest wysoce zoptymalizowanych pod kątem wykonywania niektórych typów przetwarzania danych, takich jak obliczanie zagregowanych wartości w dużych zestawach danych. Nie przenosij tych typów przetwarzania z bazy danych.
Nie przenoś przetwarzania, jeśli spowoduje to znaczne zwiększenie ilości danych przesyłanych przez bazę danych za pośrednictwem sieci. Zobacz antywzorzec nadmiarowego pobierania.
W przypadku przeniesienia przetwarzania do warstwy aplikacji, konieczne może być skalowanie tej warstwy w poziomie, aby obsłużyć dodatkową pracę.

Jak wykryć problem

Objawy zajętej bazy danych obejmują nieproporcjonalny spadek przepływności i czasów odpowiedzi w operacjach, które uzyskują dostęp do bazy danych.

Aby zidentyfikować ten problem, możesz wykonać następujące kroki:

Użyj monitorowania wydajności, aby określić, ile czasu system produkcyjny spędza na wykonywaniu działań bazy danych.
Zbadaj pracę wykonaną przez bazę danych w tych okresach.
Jeśli podejrzewasz, że określone operacje mogą spowodować zbyt dużą aktywność bazy danych, przeprowadź testowanie obciążenia w kontrolowanym środowisku. Każdy test powinien uruchomić kombinację podejrzanych operacji ze zmiennym obciążeniem użytkownika. Sprawdź dane telemetryczne z testów obciążeniowych, aby zobaczyć, jak jest używana baza danych.
Jeśli działanie bazy danych ujawnia znaczące przetwarzanie, ale mały ruch danych, przejrzyj kod źródłowy, aby ustalić, czy przetwarzanie może być lepiej wykonywane gdzie indziej.

Jeśli ilość aktywności bazy danych jest niska lub czasy odpowiedzi są stosunkowo szybkie, zajęta baza danych prawdopodobnie nie będzie problemem z wydajnością.

Przykładowa diagnostyka

W poniższych sekcjach zastosowano te kroki do opisanej wcześniej przykładowej aplikacji.

Monitorowanie ilości aktywności bazy danych

Na poniższym wykresie przedstawiono wyniki uruchamiania testu obciążeniowego dla przykładowej aplikacji, przy użyciu przyrostowego obciążenia do 50 użytkowników działających jednocześnie. Liczba żądań szybko osiąga limit i pozostaje na tym poziomie, podczas gdy średni czas odpowiedzi stale wzrasta. Skala logarytmiczna jest używana dla tych dwóch metryk.

Wyniki testu obciążeniowego na potrzeby przetwarzania w bazie danych

Następny wykres przedstawia użycie procesora i jednostek DTU jako procent limitu przydziału usługi. DTU mierzą, jak dużo przetwarzania wykonuje baza danych. Wykres pokazuje, że wykorzystanie CPU i DTU szybko osiągnęło 100%.

Monitor usługi Azure SQL Database przedstawiający wydajność bazy danych podczas przetwarzania

Badanie pracy wykonywanej przez bazę danych

Może się okazać, że zadania wykonywane przez bazę danych są prawdziwymi operacjami dostępu do danych, a nie przetwarzaniem, dlatego ważne jest, aby zrozumieć, czy instrukcje SQL są uruchamiane, gdy baza danych jest zajęta. Monitoruj system, aby przechwycić ruch SQL i skorelować operacje SQL z żądaniami aplikacji.

Jeśli operacje bazy danych są operacjami dostępu wyłącznie do danych, bez dużej ilości przetwarzania problem może być nadmiarowe pobieranie.

Implementowanie rozwiązania i weryfikowanie wyniku

Poniższy wykres przedstawia test obciążeniowy przy użyciu zaktualizowanego kodu. Przepływność jest znacznie wyższa, ponad 400 żądań na sekundę w porównaniu z 12 wcześniejszymi. Średni czas odpowiedzi jest również znacznie niższy, nieco powyżej 0,1 sekund w porównaniu do ponad 4 sekund.