Meşgul Veritabanı kötü modeli

  • Makale

İş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.

Sorun açıklaması

Çoğu veritabanı sistemi kod çalıştırabilir. Saklı yordamlar ve tetikleyiciler örnek verilebilir. Ç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. Bununla birlikte, çeşitli nedenlerden dolayı bu özelliklerin aşırı kullanımı performansı olumsuz etkileyebilir:

  • Veritabanı sunucusu yeni istemci isteklerini kabul etmek ve verileri getirmek yerine bunları işlemeye çok fazla zaman harcayabilir.
  • Veritabanı genellikle paylaşılan bir kaynak olduğundan, yüksek kullanım dönemlerinde performans sorunları yaşatabilir.
  • Veri deposu tarifeliyse çalışma zamanı maliyetleri aşırı olabilir. Bu, özellikle de yönetilen veritabanı hizmetleri için geçerlidir. Örneğin, Azure SQL Veritabanı için Veritabanı İşlem Birimleri (DTU) üzerinden ücret uygulanır.
  • Veritabanlarının ölçek artırma kapasitesi sonsuz olmadığı gibi, bir veritabanını yatay olarak genişletmek basit bir iş değildir. 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.

Bu kötü model genellikle aşağıdaki nedenlerden ortaya çıkar:

  • Veritabanı, bir depodan çok bir hizmet gibi görülüyordur. 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.
  • Geliştiriciler sonuçları doğrudan kullanıcılara görüntülenebilen sorgular yazmaya çalışıyordur. Örneğin, bir sorgu, alanları birleştiriyor veya yerel ayara göre tarihleri, saatleri ve para birimlerini biçimlendiriyor olabilir.
  • Geliştiriciler hesaplamaları veritabanına göndererek Fazlalık Getirme kötü modelini düzeltmeye çalışıyordur.
  • İş 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.

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. Transact-SQL işlevleri kullanarak verileri ayrıştırır ve sonuçları XML’ye dönüştürür. Örneğin tamamını burada bulabilirsiniz.

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. Daha sonra, bunun aslında veritabanı sunucusunda önemli miktarda işleme kaynağı kullandığını göreceğiz.

Sorunun çözümü

İşlemeyi veritabanı sunucusundan diğer uygulama katmanlarına kaydırın. İ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.

Örneğin, önceki Transact-SQL kodu basitçe işlenecek verileri getiren bir deyimle değiştirilebilir.

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.

// 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(" />", "/>");
    }
}

Dekont

Bu kod biraz karmaşıktır. Yeni bir uygulama için seri duruma getirme kitaplığı kullanmayı tercih edebilirsiniz. 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.

Dikkat edilmesi gereken noktalar

  • Ç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. Bu tür işlemeleri veritabanı dışına taşımayın.

  • İşleme konumunun değiştirilmesi veritabanının ağ üzerinden çok daha fazla veri aktarmasına neden oluyorsa bunu yapmayın. Bkz. Fazlalık Getirme kötü modeli.

  • İş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.

Sorunu algılama

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.

Bu sorunun belirlenmesine yardımcı olacak aşağıdaki adımları gerçekleştirebilirsiniz:

  1. Performans izlemeyi kullanarak üretim sisteminin veritabanı etkinliği gerçekleştirmeye ne kadar zaman harcadığını belirleyin.

  2. Bu dönemler sırasında veritabanı tarafından gerçekleştirilen işleri inceleyin.

  3. Belirli işlemlerin çok fazla veritabanı etkinliğine yol açabileceğinden şüpheleniyorsanız kontrollü bir ortamda yük testi gerçekleştirin. Her test, çeşitli kullanıcı yükleriyle şüpheli işlemlerin bir karışımını çalıştırmalıdır. Yük testlerinden toplanan telemetriyi inceleyerek veritabanının nasıl kullanıldığını gözlemleyin.

  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.

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.

Örnek tanılama

Aşağıdaki bölümlerde, bu adımlar yukarıda açıklanan örnek uygulamaya uygulanmaktadır.

Veritabanı etkinlik hacmini izleme

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. İstek hacmi hızla bir sınıra ulaşıp bu düzeyde kalırken, ortalama yanıt süresi giderek artar. Bu iki ölçüm için logaritmik bir ölçek kullanılır.

Load-test results for performing processing in the database

Bir sonraki grafikte, hizmet kotasının bir yüzdesi olarak CPU ve DTU kullanımı gösterilmiştir. DTU’lar, veritabanının ne kadar işleme gerçekleştirdiğine ilişkin bir ölçüm sağlar. Grafik, hem CPU hem de DTU kullanımının hızla %100’e ulaştığını gösteriyor.

Azure SQL Database monitor showing the performance of the database while performing processing

Veritabanı tarafından gerçekleştirilen işleri inceleyin

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. Sistemi izleyerek SQL trafiğini yakalayın ve SQL işlemlerini uygulama istekleriyle ilişkilendirin.

Veritabanı işlemleri çok fazla işleme gerektirmeyen saf veri erişimi işlemleriyse sorun Fazlalık Getirme olabilir.

Çözümü uygulama ve sonucu doğrulama

Aşağıdaki grafikte, güncelleştirilmiş kod kullanılarak gerçekleştirilen bir yük testi gösterilmiştir. Daha önce saniyede 12 istek olan aktarım hızı 400’ün üzerine çıkarak önemli ölçüde yükselmiştir. 4 saniyenin üzerindeki ortalama yanıt süresi de çok daha kısalarak 0,1 saniyenin biraz üzerine düşmüştür.

Graph showing load-test results for performing processing in the client application.

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.

Azure SQL Database monitor showing the performance of the database while performing processing in the client application