Meşgul Veritabanı kötü modeliBusy Database antipattern

İşleme yükünün bir veritabanı sunucusuna boşaltılması, sunucunun verileri depolama ve alma isteklerine yanıt vermektense zamanının önemli bir bölümünü kod çalıştırmaya harcamasına yol açabilir.Offloading processing to a database server can cause it to spend a significant proportion of time running code, rather than responding to requests to store and retrieve data.

Sorun açıklamasıProblem description

Çoğu veritabanı sistemi kod çalıştırabilir.Many database systems can run code. Saklı yordamlar ve tetikleyiciler örnek verilebilir.Examples include stored procedures and triggers. Çoğunlukla, verilerin işlenmek üzere bir istemci uygulamasına aktarılmasındansa bu işlemlerin verilere yakın bir biçimde gerçekleştirilmesi daha verimlidir.Often, it's more efficient to perform this processing close to the data, rather than transmitting the data to a client application for processing. Bununla birlikte, çeşitli nedenlerden dolayı bu özelliklerin aşırı kullanımı performansı olumsuz etkileyebilir:However, overusing these features can hurt performance, for several reasons:

  • Veritabanı sunucusu yeni istemci isteklerini kabul etmek ve verileri getirmek yerine bunları işlemeye çok fazla zaman harcayabilir.The database server may spend too much time processing, rather than accepting new client requests and fetching data.
  • Veritabanı genellikle paylaşılan bir kaynak olduğundan, yüksek kullanım dönemlerinde performans sorunları yaşatabilir.A database is usually a shared resource, so it can become a bottleneck during periods of high use.
  • Veri deposu tarifeliyse çalışma zamanı maliyetleri aşırı olabilir.Runtime costs may be excessive if the data store is metered. Bu, özellikle de yönetilen veritabanı hizmetleri için geçerlidir.That's particularly true of managed database services. Örneğin, Azure SQL Veritabanı için Veritabanı İşlem Birimleri (DTU) üzerinden ücret uygulanır.For example, Azure SQL Database charges for Database Transaction Units (DTUs).
  • Veritabanlarının ölçek artırma kapasitesi sonsuz olmadığı gibi, bir veritabanını yatay olarak genişletmek basit bir iş değildir.Databases have finite capacity to scale up, and it's not trivial to scale a database horizontally. Bu nedenle, işlemenin ölçeği kolayca genişletilebilen VM veya App Service uygulaması gibi bir işlem kaynağına taşınması daha iyi olabilir.Therefore, it may be better to move processing into a compute resource, such as a VM or App Service app, that can easily scale out.

Bu kötü model genellikle aşağıdaki nedenlerden dolayı ortaya çıkar:This antipattern typically occurs because:

  • Veritabanı, bir depodan çok bir hizmet gibi görülüyordur.The database is viewed as a service rather than a repository. Bir uygulama, veritabanı sunucusunu verileri biçimlendirmek (örneğin, XML’ye dönüştürmek), dize verilerini işlemek veya karmaşık hesaplamalar gerçekleştirmek için kullanıyor olabilir.An application might use the database server to format data (for example, converting to XML), manipulate string data, or perform complex calculations.
  • Geliştiriciler sonuçları doğrudan kullanıcılara görüntülenebilen sorgular yazmaya çalışıyordur.Developers try to write queries whose results can be displayed directly to users. Örneğin, bir sorgu, alanları birleştiriyor veya yerel ayara göre tarih, saat ve para birimini biçimlendiriyor olabilir.For example a query might combine fields, or format dates, times, and currency according to locale.
  • Geliştiriciler hesaplamaları veritabanına göndererek Fazlalık Getirme kötü modelini düzeltmeye çalışıyordur.Developers are trying to correct the Extraneous Fetching antipattern by pushing computations to the database.
  • İş mantığının kapsüllenmesi için, belki de bakımı ve güncelleştirilmesi daha kolay görüldüğünden saklı yordamlar kullanılıyordur.Stored procedures are used to encapsulate business logic, perhaps because they are considered easier to maintain and update.

Aşağıdaki örnek, belirli bir satış bölgesi için en değeri 20 siparişi döndürür ve sonuçları XML olarak biçimlendirir.The following example retrieves the 20 most valuable orders for a specified sales territory and formats the results as XML. Transact-SQL işlevleri kullanarak verileri ayrıştırır ve sonuçları XML’ye dönüştürür.It uses Transact-SQL functions to parse the data and convert the results to XML. Örneğin tamamını burada bulabilirsiniz.You can find the complete sample here.

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

Açıkça görülebileceği gibi bu karmaşık bir sorgudur.Clearly, this is complex query. Daha sonra, bunun aslında veritabanı sunucusunda önemli miktarda işleme kaynağı kullandığını göreceğiz.As we'll see later, it turns out to use significant processing resources on the database server.

Sorunun çözümüHow to fix the problem

İşlemeyi veritabanı sunucusundan diğer uygulama katmanlarına kaydırın.Move processing from the database server into other application tiers. İdeal olarak veritabanını, bir RDBMS’de toplama gibi yalnızca veritabanının yapmak için iyileştirildiği özellikleri kullanarak veri erişimi işlemleri gerçekleştirmeyle kısıtlamalısınız.Ideally, you should limit the database to performing data access operations, using only the capabilities that the database is optimized for, such as aggregation in an RDBMS.

Örneğin, önceki Transact-SQL kodu basitçe işlenecek verileri getiren bir deyimle değiştirilebilir.For example, the previous Transact-SQL code can be replaced with a statement that simply retrieves the data to be processed.

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]

Uygulama daha sonra .NET Framework System.Xml.Linq API’lerini kullanarak sonuçları XML olarak biçimlendirir.The application then uses the .NET Framework System.Xml.Linq APIs to format the results as 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(" />", "/>");
    }
}

Not

Bu kod biraz karmaşıktır.This code is somewhat complex. Yeni bir uygulama için seri duruma getirme kitaplığı kullanmayı tercih edebilirsiniz.For a new application, you might prefer to use a serialization library. Bununla birlikte, buradaki varsayım geliştirme ekibinin mevcut bir uygulamayı yeniden düzenlediği olduğundan, metodun özgün kodla tam olarak aynı biçimi döndürmesi gerekir.However, the assumption here is that the development team is refactoring an existing application, so the method needs to return the exact same format as the original code.

Dikkat edilmesi gerekenlerConsiderations

  • Çoğu veritabanı, büyük veri kümelerinde toplu değerleri hesaplama gibi belirli veri işleme türlerini gerçekleştirmek için yüksek oranda iyileştirilmiştir.Many database systems are highly optimized to perform certain types of data processing, such as calculating aggregate values over large datasets. Bu tür işlemeleri veritabanı dışına taşımayın.Don't move those types of processing out of the database.

  • İşleme konumunun değiştirilmesi veritabanının ağ üzerinden çok daha fazla veri aktarmasına neden oluyorsa bunu yapmayın.Do not relocate processing if doing so causes the database to transfer far more data over the network. Bkz. Fazlalık Getirme kötü modeli.See the Extraneous Fetching antipattern.

  • İşlemeyi bir uygulama katmanına kaydırırsanız bu katmanın ek yükü kaldırabilmesi için ölçeğinin genişletilmesi gerekebilir.If you move processing to an application tier, that tier may need to scale out to handle the additional work.

Sorunu algılamaHow to detect the problem

Bir veritabanının meşgul olduğunu gösteren belirtiler arasında, veritabanına erişen işlemlerde aktarım hızının ve yanıt sürelerinin orantısız olarak kötüleşmesi yer alır.Symptoms of a busy database include a disproportionate decline in throughput and response times in operations that access the database.

Bu sorunun belirlenmesine yardımcı olacak aşağıdaki adımları gerçekleştirebilirsiniz:You can perform the following steps to help identify this problem:

  1. Performans izlemeyi kullanarak üretim sisteminin veritabanı etkinliği gerçekleştirmeye ne kadar zaman harcadığını belirleyin.Use performance monitoring to identify how much time the production system spends performing database activity.

  2. Bu dönemler sırasında veritabanı tarafından gerçekleştirilen işleri inceleyin.Examine the work performed by the database during these periods.

  3. Belirli işlemlerin çok fazla veritabanı etkinliğine yol açabileceğinden şüpheleniyorsanız kontrollü bir ortamda yük testi gerçekleştirin.If you suspect that particular operations might cause too much database activity, perform load testing in a controlled environment. Her test, çeşitli kullanıcı yükleriyle şüpheli işlemlerin bir karışımını çalıştırmalıdır.Each test should run a mixture of the suspect operations with a variable user load. Yük testlerinden toplanan telemetriyi inceleyerek veritabanının nasıl kullanıldığını gözlemleyin.Examine the telemetry from the load tests to observe how the database is used.

  4. Veritabanı etkinliğinde önemli oranda işleme olmasına rağmen veri trafiğinin düşük olduğu görülürse, kaynak kodu gözden geçirerek işlemenin başka bir yerde daha iyi gerçekleştirilip gerçekleştirilemeyeceğini belirleyin.If the database activity reveals significant processing but little data traffic, review the source code to determine whether the processing can better be performed elsewhere.

Veritabanı etkinlik hacmi düşükse veya yanıt süreleri görece hızlıysa, muhtemelen performans sorununun nedeni veritabanının meşgul olması değildir.If the volume of database activity is low or response times are relatively fast, then a busy database is unlikely to be a performance problem.

Örnek tanılamaExample diagnosis

Aşağıdaki bölümlerde, bu adımlar daha önce açıklanan örnek uygulamaya uygulanmıştır.The following sections apply these steps to the sample application described earlier.

Veritabanı etkinlik hacmini izlemeMonitor the volume of database activity

Aşağıdaki grafikte, 50’ye kadar eş zamanlı kullanıcının aşamalı artan yükü kullanılarak örnek bir uygulamada yük testi çalıştırmanın sonuçları gösterilmiştir.The following graph shows the results of running a load test against the sample application, using a step load of up to 50 concurrent users. İstek hacmi hızla bir sınıra ulaşıp bu düzeyde kalırken, ortalama yanıt süresi giderek artar.The volume of requests quickly reaches a limit and stays at that level, while the average response time steadily increases. Bu iki ölçüm için logaritmik bir ölçek kullanıldığına dikkat edin.Note that a logarithmic scale is used for those two metrics.

Veritabanında işleme gerçekleştirmek için yük testi sonuçları

Bir sonraki grafikte, hizmet kotasının bir yüzdesi olarak CPU ve DTU kullanımı gösterilmiştir.The next graph shows CPU utilization and DTUs as a percentage of service quota. DTU’lar veritabanının ne kadar işleme gerçekleştirdiğine ilişkin bir ölçüm sağlar.DTUs provides a measure of how much processing the database performs. Grafik, hem CPU hem de DTU kullanımının hızla %100’e ulaştığını gösteriyor.The graph shows that CPU and DTU utilization both quickly reached 100%.

İşlem gerçekleştirirken veritabanının performansını gösteren Azure SQL Veritabanı izleme

Veritabanı tarafından gerçekleştirilen işleri inceleyinExamine the work performed by the database

Veritabanı tarafından gerçekleştirilen görevler işleme değil de gerçekten veri erişimi işlemleri olabileceğinden, veritabanı meşgul olduğu sırada çalıştırılan SQL deyimlerini anlamak önemlidir.It could be that the tasks performed by the database are genuine data access operations, rather than processing, so it is important to understand the SQL statements being run while the database is busy. Sistemi izleyerek SQL trafiğini yakalayın ve SQL işlemlerini uygulama istekleriyle ilişkilendirin.Monitor the system to capture the SQL traffic and correlate the SQL operations with application requests.

Veritabanı işlemleri çok fazla işleme gerektirmeyen saf veri erişimi işlemleriyse sorun Fazlalık Getirme olabilir.If the database operations are purely data access operations, without a lot of processing, then the problem might be Extraneous Fetching.

Çözümü uygulama ve sonucu doğrulamaImplement the solution and verify the result

Aşağıdaki grafikte, güncelleştirilmiş kod kullanılarak gerçekleştirilen bir yük testi gösterilmiştir.The following graph shows a load test using the updated code. Daha önce saniyede 12 istek olan aktarım hızı 400’ün üzerine çıkarak önemli ölçüde yükselmiştir.Throughput is significantly higher, over 400 requests per second versus 12 earlier. 4 saniyenin üzerindeki ortalama yanıt süresi de çok daha kısalarak 0,1 saniyenin biraz üzerine düşmüştür.The average response time is also much lower, just above 0.1 seconds compared to over 4 seconds.

İşlemenin veritabanında gerçekleştirilmesi için yük testi sonuçları

CPU ve DTU kullanımı, aktarım hızı artmasına rağmen sistemin doygunluğa ulaşmasının daha uzun sürdüğünü göstermektedir.CPU and DTU utilization shows that the system took longer to reach saturation, despite the increased throughput.

İşleme istemci uygulamasında gerçekleştirilirken veritabanının performansını gösteren Azure SQL Veritabanı izleyicisi