Anti-padrão de Base de Dados Ocupada
O descarregamento de processamento para um servidor de base de dados pode fazer com que passe bastante tempo a executar o código, em vez de responder a pedidos para armazenar e obter dados.
Descrição do problema
Muitos sistemas de base de dados podem executar código. Os exemplos incluem acionadores e procedimentos armazenados. Muitas vezes, é mais eficiente realizar este processamento próximo aos dados, em vez de os transmitir os dados para uma aplicação de cliente para processamento. No entanto, utilizar estas funcionalidades em demasia pode prejudicar o desempenho, por vários motivos:
- O servidor de base de dados pode passar demasiado tempo a processar, em vez de aceitar novos pedidos de cliente e obter dados.
- Uma base de dados é normalmente um recurso partilhado, pelo que esta pode tornar-se um estrangulamento durante períodos de utilização elevada.
- Os custos de runtime podem ser excessivos se o arquivo de dados for limitado. É particularmente verdadeiro nos serviços de base de dados geridos. Por exemplo, as cobranças da Base de Dados SQL do Azure relativamente às Unidades de Transação de Base de Dados (DTUs).
- As bases de dados têm capacidade finita para aumentar verticalmente e não é trivial para aumentar horizontalmente uma base de dados. Por conseguinte, poderá ser melhor mover o processamento para um recurso de computação, como uma aplicação de VM ou do Serviço de Aplicações, que pode facilmente aumentar horizontalmente.
Este anti-padrão ocorre normalmente porque:
- A base de dados é apresentada como um serviço, em vez de um repositório. Uma aplicação poderá utilizar o servidor de base de dados para formatar os dados (por exemplo, converter em XML), manipular dados de cadeia ou realizar cálculos complexos.
- Os programadores tentam escrever consultas cujos resultados podem ser apresentados diretamente aos utilizadores. Por exemplo, uma consulta pode combinar campos ou formatar datas, horas e a moeda, de acordo com a região.
- Os programadores estão a tentar corrigir o anti-padrão Obtenção Externa ao enviar cálculos para a base de dados.
- Os procedimentos armazenados servem para encapsular a lógica de negócio, talvez porque sejam considerados mais fáceis de manter e atualizar.
O exemplo seguinte obtém as 20 ordens mais valiosas para um território de venda especificado e formata os resultados como XML. Utiliza as funções do Transact-SQL para analisar os dados e converter os resultados em XML. Pode encontrar o exemplo completo aqui.
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')
Claramente, esta consulta é complexa. Como iremos ver mais tarde, acaba por utilizar recursos de processamento significativos no servidor da base de dados.
Como resolver o problema
Mova o processamento do servidor de base de dados para outros escalões de aplicação. Idealmente, deve limitar a base de dados para realizar operações de acesso de dados, ao utilizar apenas as capacidades que a base de dados está otimizada, como a agregação num RDBMS.
Por exemplo, o código anterior do Transact-SQL pode ser substituído por uma instrução que obtém apenas os dados a serem processados.
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]
A aplicação utiliza então APIs System.Xml.Linq do .NET Framework para formatar os resultados como 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(" />", "/>");
}
}
Nota
Este código é um pouco complexo. Para uma nova aplicação, poderá preferir utilizar uma biblioteca de serialização. No entanto, o pressuposto é de que a equipa de desenvolvimento está a refatorizar uma aplicação existente, pelo que o método tem de devolver o mesmo formato exato do código original.
Considerações
Muitos sistemas de base de dados são altamente otimizados para realizar determinados tipos de processamento de dados, como calcular valores agregados ao longo de grandes conjuntos de dados. Não mova esses tipos de processamento para fora da base de dados.
Não altere a localização do processamento, uma vez que se o fizer a base de dados irá transferir muitos mais dados através da rede. Veja o Anti-padrão de Obtenção Externa.
Se mover o processamento para uma camada de aplicação, essa camada poderá ter de aumentar horizontalmente para lidar com o trabalho adicional.
Como detetar o problema
Os sintomas de uma base de dados ocupada incluem um declínio desproporcional nos tempos de resposta e no débito em operações que acedem à base de dados.
Pode realizar os passos seguintes para ajudar a identificar este problema:
Utilize a monitorização de desempenho para identificar quanto tempo o sistema de produção passa a realizar a atividade de base de dados.
Examine o trabalho realizado pela base de dados durante estes períodos.
Se suspeitar que operações específicas possam causar demasiada atividade da base de dados, execute um teste de carga num ambiente controlado. Cada teste deve executar uma combinação das operações suspeitas com uma carga de variável de utilizador. Examine a telemetria dos testes de carga para observar a forma como a base de dados é utilizada.
Se a atividade da base de dados revelar processamento significativo, mas pouco tráfego de dados, reveja o código de origem para determinar se o processamento pode ser melhor realizado noutro local.
Se o volume de atividades de base de dados for baixo ou os tempos de resposta forem relativamente rápidos, então é pouco provável que uma base de dados ocupada tenha um problema de desempenho.
Diagnóstico de exemplo
As secções seguintes aplicam estes passos para o exemplo de aplicação descrito anteriormente.
Monitorizar o volume de atividades de base de dados
O gráfico seguinte mostra os resultados da execução de um teste de carga de um exemplo de aplicação, através de uma carga de passos de até 50 utilizadores em simultâneo. O volume de pedidos atinge rapidamente um limite e permanece nesse nível, enquanto o tempo de resposta médio aumenta constantemente. Uma escala logarítmica é utilizada para essas duas métricas.
Este grafo de linha mostra a carga do utilizador, os pedidos por segundo e o tempo médio de resposta. O grafo mostra que o tempo de resposta aumenta com o aumento da carga.
O gráfico seguinte mostra a utilização da CPU e de DTUs como uma percentagem da quota de serviço. As DTUs indicam uma medida da quantidade processamento realizada pela base de dados. O gráfico mostra que tanto a utilização da CPU, como da DTU, atingiram rapidamente 100%.
Este grafo de linha mostra a percentagem de CPU e a percentagem de DTU ao longo do tempo. O grafo mostra que ambos atingem rapidamente os 100%.
Examinar o trabalho realizado pela base de dados
É possível que as tarefas executadas pela base de dados sejam operações de acesso de dados genuínas, em vez de processamento, pelo que é importante compreender as instruções SQL que estão a ser executadas enquanto a base de dados está ocupada. Monitorize o sistema para capturar o tráfego SQL e correlacione as operações SQL com pedidos de aplicações.
Se as operações da base de dados são puramente operações de acesso a dados, sem uma grande quantidade de processamento, então, o problema poderá ser Obtenção Externa.
Implementar a solução e verificar o resultado
O gráfico seguinte mostra um teste de carga com o código atualizado. O débito é significativamente superior, mais de 400 pedidos por segundo em relação aos 12 anteriores. O tempo médio de resposta também é muito inferior, imediatamente acima de 0,1 segundos, em comparação com mais de 4 segundos.
Este grafo de linha mostra a carga do utilizador, os pedidos por segundo e o tempo médio de resposta. O grafo mostra que o tempo de resposta permanece sensivelmente constante ao longo do teste de carga.
A utilização da CPU e da DTU mostra que o sistema demorou mais tempo a alcançar a saturação, apesar do débito aumentado.
Este grafo de linha mostra a percentagem de CPU e a percentagem de DTU ao longo do tempo. O grafo mostra que a CPU e a DTU demoram mais a atingir os 100% do que antes.
Recursos relacionados
Comentários
Brevemente: Ao longo de 2024, vamos descontinuar progressivamente o GitHub Issues como mecanismo de feedback para conteúdos e substituí-lo por um novo sistema de feedback. Para obter mais informações, veja: https://aka.ms/ContentUserFeedback.
Submeter e ver comentários