Antywzorzec zajętej bazy danych
Serwer bazy danych, na który przeniesiono operacje przetwarzania, może przeznaczać znacznie więcej czasu na wykonywanie kodu, a nie na odpowiadanie na żądania zapisu i pobierania danych.
Opis problemu
Wiele systemów baz danych może uruchamiać kod. Przykłady obejmują procedury składowane i wyzwalacze. Wykonywanie operacji przetwarzania blisko danych często jest wydajniejsze niż przesyłanie danych do aplikacji klienckiej. Jednak nadużywanie tych funkcji może obniżyć wydajność. Oto kilka powodów:
- Serwer bazy danych może poświęcać zbyt dużo czasu na przetwarzanie zamiast na obsługę nowych żądań klientów i pobieranie danych.
- Baza danych jest zwykle zasobem współdzielonym, dlatego w okresach wysokiego zapotrzebowania może stać się wąskim gardłem.
- Jeśli korzystanie z magazynu danych odbywa się na podstawie planu taryfowego, koszty pracy mogą nadmiernie wzrosnąć. Jest to szczególnie istotne w przypadku usług zarządzanej bazy danych. Na przykład w usłudze Azure SQL Database opłaty są pobierane za jednostki transakcji bazy danych (DTU, Database Transaction Unit).
- Możliwości skalowania baz danych w górę są ograniczone, a skalowanie ich w poziomie też nie jest proste. W związku z tym lepszym rozwiązaniem może być przeniesienie operacji przetwarzania do zasobu obliczeniowego, na przykład maszyny wirtualnej lub aplikacji usługi App Service, który można łatwo skalować w poziomie.
Oto typowe przyczyny występowania tego antywzorca:
- Baza danych jest traktowana jak usługa, a nie repozytorium. Aplikacje używają serwera baz danych do formatowania danych (na przykład konwertowania ich na format XML), manipulowania ciągami lub przeprowadzania złożonych obliczeń.
- Deweloperzy mają tendencję do pisania zapytań, których wyniki od razu nadają się do przedstawienia użytkownikom. Przykładem może być zapytanie, które łączy pola lub formatuje daty, godziny i waluty w zależności od ustawień regionalnych.
- Deweloperzy usiłują poprawić antywzorzec dotyczący nadmiarowego pobierania, wypychając obliczenia do bazy danych.
- Logika biznesowa jest umieszczana w procedurach składowanych, ponieważ zwykle uważa się, że ich utrzymanie i aktualizowanie jest proste.
W poniższym przykładzie w podanym regionie sprzedaży jest pobieranych 20 zamówień o największej wartości, a wyniki są formatowane do postaci XML. Do analizowania danych i konwertowania wyników na format XML są używane funkcje języka Transact-SQL. Pełny przykład można znaleźć tutaj.
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')
Wyraźnie widać, że stopień złożoności tego zapytania jest znaczny. W dalszej części tego tematu zobaczymy, że korzysta ono z wielu zasobów przetwarzania na serwerze baz danych.
Jak rozwiązać ten problem
Operacje przetwarzania na serwerze baz danych przenieś 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 przedstawiony wcześniej kod języka Transact-SQL można zastąpić instrukcją, która tylko 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 formatuje wyniki w postaci XML za pomocą interfejsów API System.Xml.Linq programu .NET Framework.
// 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
Ten kod jest dość złożony. W nowej aplikacji warto użyć biblioteki serializacji. Jednak zakładamy, że zespół deweloperów pracuje nad refaktoryzacją istniejącej aplikacji, dlatego metoda musi zwracać ten sam format co oryginalny kod.
Kwestie wymagające rozważenia
Wiele systemów baz danych jest wysoce zoptymalizowanych pod kątem niektórych typów operacji przetwarzania danych, takich jak obliczanie wartości zagregowanych w dużych zestawach danych. Tych operacji nie przenoś poza bazę danych.
Jeśli po przeniesieniu przetwarzania znacznie zwiększa się ilość danych przesyłanych przez bazę danych przez sieć, zrezygnuj z tego przeniesienia. Zobacz Extraneous Fetching antipattern (Antywzorzec dotyczący nadmiarowego pobierania).
Po przeniesieniu przetwarzania do warstwy aplikacji może być konieczne jej przeskalowanie w poziomie w celu zapewnienia obsługi dodatkowych operacji.
Jak wykryć problem
Objawy zajętej bazy danych obejmują nieproporcjonalne pogorszenie przepływności i czasów odpowiedzi podczas operacji, które wymagają dostępu do bazy danych.
Możesz wykonać następujące kroki, aby ułatwić zidentyfikowanie tego problemu:
Monitoruj wydajność systemu produkcyjnego, aby ustalić, ile czasu trwają działania na bazie danych.
Przeanalizuj operacje wykonywane wtedy przez bazę danych.
Jeśli podejrzewasz, że jakieś operacje powodują nadmierną aktywność bazy danych, przeprowadź testy obciążeniowe w kontrolowanym środowisku. Powinny one zawierać różne operacje przy zmiennym obciążeniu działaniami użytkowników. Przeanalizuj dane telemetryczne z testów obciążeniowych, aby zobaczyć, jak używana jest baza danych.
Jeśli okaże się, że baza danych wykonuje wiele operacji przetwarzania przy małym ruchu w sieci, przejrzyj kod źródłowy w celu ustalenia, czy warto przenieść te operacje gdzie indziej.
Jeśli aktywność bazy danych jest niewielka lub czasy odpowiedzi są stosunkowo krótkie, najprawdopodobniej nie występują problemy z wydajnością.
Przykładowa diagnostyka
W poniższych sekcjach zastosowano te kroki do opisanej wcześniej przykładowej aplikacji.
Monitorowanie natężenia aktywności bazy danych
Poniższy wykres przedstawia wyniki testu obciążeniowego na przykładowej aplikacji przy użyciu obciążenia krokowego obejmującego maksymalnie 50 równoczesnych użytkowników. Liczba żądań szybko osiąga limit i pozostaje na tym poziomie, a średni czas odpowiedzi stale się wydłuża. Te dwie metryki zostały przedstawione przy użyciu skali logarytmicznej.
Ten wykres liniowy przedstawia obciążenie użytkownikami, liczbę żądań na sekundę oraz średni czas odpowiedzi. Na wykresie widać, że czas odpowiedzi zwiększa się wraz z obciążeniem.
Następny wykres przedstawia wykorzystanie procesora i jednostek DTU jako wartości procentowe limitu przydziału usługi. Wykorzystanie jednostek DTU jest miarą natężenia przetwarzania mającego miejsce w bazie danych. Na wykresie widać, że wykorzystanie zarówno procesora, jak i jednostek DTU szybko osiąga wartość 100%.
Ten wykres liniowy przedstawia procent użycia procesora i procent użycia jednostek DTU względem czasu. Na wykresie widać, że obie te wartości szybko osiągają 100%.
Analiza operacji wykonywanych przez bazę danych
Zadania wykonywane przez bazę danych faktycznie mogą dotyczyć operacji związanych z dostępem do danych, a nie z przetwarzaniem, dlatego ważne jest zrozumienie, jakie instrukcje SQL są uruchamiane, gdy baza danych jest zajęta. Monitorowanie systemu pozwala przechwycić ruch SQL i skorelować operacje SQL z żądaniami aplikacji.
Jeśli operacje bazy danych dotyczą przede wszystkim dostępu do danych, a nie ich przetwarzania, problemem może być nadmiarowe pobieranie.
Implementowanie rozwiązania i weryfikowanie wyniku
Poniższy wykres przedstawia test obciążeniowy przeprowadzony po zaktualizowaniu kodu. Przepływność jest znacznie wyższa i wynosi ponad 400 żądań na sekundę, a nie 12 jak wcześniej. Średni czas odpowiedzi także jest znacznie krótszy — zmienił się z ponad 4 sekund na nieco ponad 0,1 sekundy.
Ten wykres liniowy przedstawia obciążenie użytkownikami, liczbę żądań na sekundę oraz średni czas odpowiedzi. Na wykresie widać, że czas odpowiedzi pozostaje w przybliżeniu stały w trakcie testu obciążeniowego.
Z wykorzystania procesora i jednostek DTU wynika, że nasycenie systemu trwało dłużej mimo większej przepływności.
Ten wykres liniowy przedstawia procent użycia procesora i procent użycia jednostek DTU względem czasu. Na wykresie widać, że użycie procesora i jednostek DTU osiąga 100% w czasie dłuższym niż wcześniej.