Antipattern zaneprázdněné databáze
Snižování zátěže zpracování na databázový server může způsobit, že tento server stráví podstatnou část času spouštěním kódu místo toho, aby reagoval na žádosti o uložení a načtení dat.
Popis problému
Řada databázových systémů dokáže spouštět kód. Příkladem můžou být uložené procedury a triggery. Často je místo odesílání dat ke zpracování do klientské aplikace efektivnější provádět toto zpracování blízko datům. Nadměrné používání těchto funkcí však může snížit výkon, a to z několika důvodů:
- Databázový server může strávit příliš mnoho času zpracováním místo toho, aby přijímal nové požadavky klientů a načítal data.
- Databáze je obvykle sdíleným prostředkem, takže se v období vysokého využití může stát kritickým bodem.
- Náklady na modul runtime můžou být nadměrné v případě, že se měří úložiště dat. To platí hlavně pro spravované databázové služby. Například Azure SQL Database se účtuje na základě jednotek databázové transakce (DTU).
- Databáze mají omezené vertikální navýšení kapacity a horizontální škálování databáze není jednoduché. Proto může být lepší přesunout zpracování do výpočetního prostředku, například virtuálního počítače nebo aplikace App Service, který umožňuje snadné škálování na více instancí.
Možné důvody vzniku tohoto antipatternu:
- Na databázi se pohlíží jako na službu, a ne jako na úložiště. Aplikace může databázový server využívat k formátování dat (například k převodu do XML), manipulaci s řetězcovými daty nebo provádění složitých výpočtů.
- Vývojáři se pokoušejí psát dotazy, jejichž výsledky je možné přímo zobrazit uživatelům. Dotaz může například kombinovat pole nebo formátovat data, časy a měnu v závislosti na národním prostředí.
- Vývojáři se pokoušejí opravit antipattern nadbytečného načítání tím, že do databáze odesílají výpočty.
- Uložené procedury slouží k zapouzdření obchodní logiky, možná proto, že se považují za jednodušší na správu a aktualizace.
Následující příklad načte 20 nejhodnotnějších objednávek pro zadanou prodejní oblast a výsledky zformátuje do XML. K parsování dat a převodu výsledků do XML využívá funkce jazyka Transact-SQL. Kompletní ukázku najdete tady.
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')
Očividně se jedná o složitý dotaz. Jak si ukážeme později, tento dotaz na databázovém serveru využívá významné množství prostředků pro zpracování.
Jak problém vyřešit
Přesuňte zpracování z databázového serveru do jiných aplikačních vrstev. V ideálním případě byste měli databázi omezit na provádění operací přístupu k datům pomocí pouze funkcí, pro které je databáze optimalizovaná, například agregace v systému pro správu relačních databází (RDBMS).
Například předchozí kód jazyka Transact-SQL je možné nahradit příkazem, který jednoduše načte data ke zpracování.
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]
Aplikace pak pomocí rozhraní API System.Xml.Linq pro .NET Framework zformátuje výsledky do 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(" />", "/>");
}
}
Poznámka:
Tento kód je poněkud složitý. V nové aplikaci pravděpodobně budete chtít použít knihovnu serializace. Tady však předpokládáme, že vývojový tým refaktoruje existující aplikaci, takže metoda musí vracet naprosto stejný formát jako původní kód.
Důležité informace
Řada databázových systémů je vysoce optimalizovaných pro provádění určitých typů zpracování dat, jako je výpočet agregovaných hodnot pro velké datové sady. Tyto typy zpracování nepřesouvejte mimo databázi.
Nepřemisťujte zpracování, pokud by to způsobilo, že databáze bude přes síť přenášet mnohem více dat. Viz téma Antipattern nadbytečného načítání .
Pokud přesunete zpracování do aplikační vrstvy, možná bude potřeba škálovat tuto vrstvu na více instancí tak, aby zvládla dodatečnou práci.
Jak zjistit problém
Mezi příznaky zaneprázdněné databáze patří nepřiměřené snížení propustnosti a zpomalení doby odezvy v operacích, které přistupují k databázi.
Následující postup vám pomůže tento problém identifikovat:
Pomocí monitorování výkonu identifikujte, kolik času produkční systém stráví prováděním databázové aktivity.
Zkontrolujte práci, kterou databáze během těchto období provedla.
Pokud máte podezření, že konkrétní operace můžou způsobovat příliš velkou databázovou aktivitu, proveďte zátěžové testování v řízeném prostředí. Každý test by měl spustit kombinaci podezřelých operací s proměnlivým uživatelským zatížením. Zkontrolujte telemetrická data ze zátěžových testů a prozkoumejte využití databáze.
Pokud databázová aktivita odhalí výrazný objem zpracování, ale malý přenos dat, zkontrolujte zdrojový kód, abyste určili, jestli není možné provádět zpracování lépe na jiném místě.
Pokud je objem databázové aktivity nízký, nebo jsou doby odezvy poměrně rychlé, pak problém s výkonem pravděpodobně nezpůsobuje zaneprázdněná databáze.
Ukázková diagnostika
V následujících částech se tento postup použije u ukázkové aplikace popsané výše.
Monitorování objemu databázové aktivity
Následující graf ukazuje výsledky spuštění zátěžového testu na ukázkové aplikaci s použitím krokového zatížení až 50 souběžných uživatelů. Objem požadavků rychle dosáhne limitu a na této úrovni zůstane, zatímco se plynule zvyšuje průměrná doba odezvy. Pro tyto dvě metriky se používá logaritmické měřítko.
Tento spojnicový graf zobrazuje uživatelské zatížení, požadavky za sekundu a průměrnou dobu odezvy. Graf ukazuje, že s nárůstem zatížení se prodlužuje doba odezvy.
Další graf ukazuje využití procesoru a jednotek DTU jako procentní podíl kvóty služby. Jednotky DTU poskytují měřítko objemu zpracování, které databáze provádí. Graf ukazuje, že využití procesoru i jednotek DTU rychle dosáhlo 100 %.
Tento spojnicový graf znázorňuje procento využití procesoru a procento DTU v čase. Graf ukazuje, že obě hodnoty rychle dosahují 100 %.
Kontrola práce, kterou databáze provedla
Úlohy prováděné databází můžou být skutečnými operacemi přístupu k datům, a ne zpracování, proto je důležité porozumět příkazům jazyka SQL, které se spouští při zaneprázdnění databáze. Monitorujte systém, abyste zachytili provoz SQL, a proveďte korelaci operací SQL s požadavky aplikace.
Pokud jsou databázové operace čistě operacemi přístupu k datům, které nevyžadují velké zpracování, pak problémem může být nadbytečné načítání.
Implementace řešení a ověření výsledku
Následující graf ukazuje zátěžový test s použitím aktualizovaného kódu. Propustnost je výrazně vyšší a dosahuje až 400 požadavků za sekundu oproti dřívějším 12. Průměrná doba odezvy je také mnohem kratší – o něco více než 0,1 sekundy v porovnání s více než 4 sekundami.
Tento spojnicový graf zobrazuje uživatelské zatížení, požadavky za sekundu a průměrnou dobu odezvy. Graf ukazuje, že doba odezvy zůstává v průběhu zátěžového testu přibližně konstantní.
Využití procesoru a jednotek DTU ukazuje, že systému i přes zvýšenou propustnost trvalo déle, než dosáhl sytosti.
Tento spojnicový graf znázorňuje procento využití procesoru a procento DTU v čase. Graf ukazuje, že dosažení 100 % u procesoru i DTU trvá déle než minule.