Antimönstret Upptagen databas
Avlastning av bearbetning till en databasserver kan göra så att den tillbringar en betydande del av tiden med att köra kod, istället för att svara på begäranden för att lagra och hämta data.
Problembeskrivning
Många databaser kan köra kod. Bland exemplen finns lagrade procedurer och utlösare. Ofta är det effektivare att utföra den här bearbetningen nära data, istället för att sända data till ett klientprogram för bearbetning. Men överanvändning av de här funktionen kan försämra prestandan, av flera skäl:
- Databasen kan lägga för mycket tid på bearbetning, istället för att acceptera nya klientbegäranden och hämta data.
- En databas är normalt en delad resurs, så den kan bli en flaskhals i perioder med hög användning.
- Körningskostnaderna kan bli orimliga om datalagringen har rörliga kostnader. Det gäller särskilt för hanterade databastjänster. Till exempel debiteras Azure SQL Database för databastransaktionsenheter (DTU:er).
- Databaser har begränsad kapacitet att skala upp och det är inte helt enkelt att skala en databas vågrätt. Därför kan det vara bättre att flytta bearbetningen till en beräkningsresurs, till exempel en virtuell dator eller en App Service-app, som kan skala ut enkelt.
Det här antimönstret inträffar normalt eftersom:
- Databasen visas som en tjänst istället för en lagringsplats. Ett program kan använda databasservern för att formatera data (t.ex. konvertera till XML), ändra strängdata eller utföra komplexa beräkningar.
- Utvecklare försöker skriva frågor vars resultat kan visas direkt för användare. Till exempel kan en fråga kombinera fält eller formatera datum, tider och valuta enligt nationella inställningar.
- Utvecklare försöker korrigera antimönstret Överflödig hämtning genom att överföra beräkningar till databasen.
- Lagrade procedurer används för att kapsla in affärslogik, kanske eftersom de anses vara enklare att underhålla och uppdatera.
I följande exempel hämtas de 20 mest värdefulla beställningarna för ett angivet försäljningsområde och resultatet formateras som XML. Transact-SQL-funktioner används för att parsa data och konvertera resultatet till XML. Du hittar hela exemplet här.
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')
Det här är helt klart en komplex fråga. Det visar sig senare att den använder betydande bearbetningsresurser på databasservern.
Åtgärda problemet
Flytta bearbetning från databasservern till andra programnivåer. Helst bör du begränsa databasen till att utföra dataåtkomståtgärder, med endast de funktioner som databasen är optimerad för, till exempel aggregering i ett relationsdatabashanteringssystem (RDBMS).
Till exempel kan den tidigare Transact-SQL-koden ersättas med en instruktion som helt enkelt hämtar data som ska bearbetas.
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]
Programmet använder sedan .NET Framework System.Xml.Linq-API:erna för att formatera resultatet som 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(" />", "/>");
}
}
Kommentar
Den här koden är något komplex. För ett nytt program kanske du föredrar att använda ett serialiseringsbibliotek. Men här antas det att utvecklingsteamet omstrukturerar ett befintligt program, så att metoden behöver returnera exakt samma format som ursprungskoden.
Att tänka på
Många databassystem är optimerade för att utföra vissa typer av databearbetning, till exempel beräkna aggregerade värden över stora datauppsättningar. Flytta inte ut de typerna av bearbetning ur databasen.
Flytta inte bearbetningen om det orsakar att databasen överför mycket mer data över nätverket. Se antimönstret Överflödig hämtning.
Om du flyttar bearbetning till en programnivå kan den nivån behöva skala ut för att hantera extraarbetet.
Identifiera problemet
Symtomen för en upptagen databas är en oproportionerlig nedgång i dataflöde och svarstider i åtgärder som använder databasen.
Du kan göra följande för att identifiera problemet:
Använd prestandaövervakning till att identifiera hur mycket tid som produktionssystemet lägger på att utföra databasaktivitet.
Undersök arbetet som utförs av databasen under de här perioderna.
Om du misstänker att vissa åtgärder kan orsaka för mycket databasaktivitet utför du belastningstestning i en kontrollerad miljö. Varje test bör köra en blandning av de misstänkta åtgärderna med en variabel användarbelastning. Undersök telemetrin från belastningstesterna för att observera hur databasen används.
Om databasaktiviteten visar på betydande bearbetning med lite datatrafik granskar du källkoden för att fastställa om bearbetningen kan utföras bättre någon annanstans.
Om mängden databasaktivitet är låg eller svarstiderna är relativt snabba är det inte troligt med prestandaproblem på grund av en upptagen databas.
Exempeldiagnos
I följande avsnitt används stegen på exempelprogrammet som beskrivs ovan.
Övervaka mängden databasaktivitet
I följande diagram visas resultatet av att köra ett belastningstest mot exempelprogrammet, med en belastning på upp till 50 samtidiga användare. Mängden begäranden når snabbt en gräns och stannar på den nivån, medan den genomsnittliga svarstiden stadigt ökar. En logaritmisk skala används för de två måtten.
Det här linjediagrammet visar användarbelastning, begäranden per sekund och genomsnittlig svarstid. Diagrammet visar att svarstiden ökar när belastningen ökar.
Nästa diagram visar processoranvändning och DTU:er som procent av tjänstkvoten. DTU:er ger ett mått på hur mycket bearbetning databasen utför. Diagrammet visar att processor- och DTU-användning båda snabbt nådde 100 %.
Det här linjediagrammet visar CPU-procent och DTU-procent över tid. Diagrammet visar att båda snabbt når 100 %.
Granska arbetet som utförs av databasen
Det kan vara att uppgifterna som utförs av databasen är genuina åtgärder för dataåtkomst, istället för bearbetning, så det är viktigt att förstå SQL-instruktionerna som körs när databasen är upptagen. Övervaka systemet för att registrera SQL-trafiken och korrelera SQL-åtgärderna med programbegäranden.
Om databasåtgärderna är rena åtgärder för dataåtkomst, utan mycket bearbetning, kan problemet vara överflödig hämtning.
Implementera lösningen och verifiera resultatet
I följande diagram visas ett belastningstest med den uppdaterade koden. Dataflödet är betydligt högre, över 400 begäranden per sekund mot 12 tidigare. Den genomsnittliga svarstiden är också mycket lägre, precis över 0,1 sekunder jämfört med över 4 sekunder.
Det här linjediagrammet visar användarbelastning, begäranden per sekund och genomsnittlig svarstid. Diagrammet visar att svarstiden förblir ungefär konstant under belastningstestet.
Processor- och DTU-användningen visar att det tog längre tid för systemet att nå mättnad, trots det ökade dataflödet.
Det här linjediagrammet visar CPU-procent och DTU-procent över tid. Diagrammet visar att det tar längre tid för CPU och DTU att nå 100 % än tidigare.