Elfoglalt adatbázis-antimintázat

A feldolgozás adatbázis-kiszolgálóra való kiszervezése miatt az idő jelentős hányadát fordíthatja kód futtatására, nem pedig az adatok tárolására és lekérésére irányuló kérésekre való válaszadásra.

A probléma leírása

Sok adatbázisrendszer képes kódot futtatni. Ilyenek például a tárolt eljárások és az eseményindítók. Gyakran hatékonyabb ezt a feldolgozást az adatok közelében elvégezni, ahelyett, hogy az adatokat egy ügyfélalkalmazásnak továbbítanák feldolgozásra. Ezeknek a funkcióknak a túlzott használata azonban több okból is ronthatja a teljesítményt:

• Az adatbázis-kiszolgáló túl sok időt tölthet feldolgozással az új ügyfélkérések elfogadása és az adatok beolvasása helyett.
• Az adatbázisok általában megosztott erőforrások, ezért szűk keresztmetszetté válhatnak a nagy használatú időszakokban.
• A futásidejű költségek túl magasak lehetnek, ha az adattár forgalmi díjas. Ez különösen igaz a felügyelt adatbázis-szolgáltatásokra. Az Azure SQL Database például az adatbázis-tranzakciós egységek (DTU-k) alapján számít fel díjat.
• Az adatbázisok véges kapacitással rendelkeznek a vertikális felskálázáshoz, és nem triviális az adatbázisok horizontális skálázása. Ezért jobb lehet a feldolgozást egy számítási erőforrásba, például egy virtuális gépbe vagy app Service-alkalmazásba áthelyezni, amely könnyen felskálázható.

Ez az antiminta tipikusan azért fordul elő, mert:

• Az adatbázis nem adattárként, hanem szolgáltatásként van megtekintve. Egy alkalmazás az adatbázis-kiszolgálót használhatja adatok formázására (például XML-formátumra való konvertálásra), sztringadatok módosítására vagy összetett számítások elvégzésére.
• A fejlesztők olyan lekérdezéseket próbálnak írni, amelyek eredményei közvetlenül megjeleníthetők a felhasználók számára. Egy lekérdezés például egyesítheti a mezőket, és területi beállítások szerint formázhatja a dátumokat, időpontokat és pénznemeket.
• A fejlesztők az Extraneous Fetching antiminta kijavításán dolgoznak azzal, hogy a számításokat az adatbázisba helyezik át.
• A tárolt eljárások az üzleti logika beágyazására szolgálnak, talán azért, mert könnyebben karbantarthatónak és frissíthetőnek tekinthetők.

Az alábbi példa lekéri egy adott értékesítési terület 20 legértékesebb rendelését, és XML formátumban formázja az eredményeket. Transact-SQL függvényeket használ az adatok elemzéséhez és az eredmények XML-fájllá alakításához.

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

Nyilvánvaló, hogy ez egy összetett lekérdezés. Ahogy később látni fogjuk, kiderül, hogy jelentős feldolgozási erőforrásokat használ az adatbázis-kiszolgálón.

A probléma megoldása

A feldolgozás áthelyezése az adatbázis-kiszolgálóról más alkalmazásszintekre. Ideális esetben korlátoznia kell az adatbázist adatelérési műveletek végrehajtására, csak azokat a képességeket használva, amelyekre az adatbázis optimalizálva van, például egy relációs adatbázis-kezelő rendszerben (RDBMS) való összesítésre.

Az előző Transact-SQL kód például lecserélhető egy olyan utasításra, amely egyszerűen lekéri a feldolgozandó adatokat.

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]

Az alkalmazás ezután a .NET-keretrendszer System.Xml.Linq API-kkal formázza az eredményeket XML-ként.

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

Megjegyzés:

Ez a kód kissé összetett. Új alkalmazás esetén érdemes lehet szerializálási kódtárat használni. A feltételezés azonban az, hogy a fejlesztői csapat egy meglévő alkalmazás refaktorálását végzi, így a metódusnak ugyanazt a formátumot kell visszaadnia, mint az eredeti kód.

Megfontolások

• Számos adatbázisrendszer optimalizálva van bizonyos típusú adatfeldolgozáshoz, például a nagy adathalmazok összesített értékeinek kiszámításához. Ne helyezze át az ilyen típusú feldolgozást az adatbázisból.

• Ne helyezze át a feldolgozást, ha ez azt eredményezi, hogy az adatbázis sokkal több adatot továbbít a hálózaton keresztül. Tekintse meg az Extraneous Fetching antipattern-t.

• Ha a feldolgozást egy alkalmazásszintre helyezi át, előfordulhat, hogy a rétegnek fel kell méreteznie a további munka kezeléséhez.

A probléma észlelése

A forgalmas adatbázisok tünetei közé tartozik az adatbázishoz hozzáférő műveletek átviteli sebességének és válaszidejének aránytalan csökkenése.

A következő lépések végrehajtásával azonosíthatja a problémát:

1. A teljesítményfigyelés használatával megállapíthatja, hogy az éles rendszer mennyi időt tölt az adatbázis-tevékenység végrehajtásával.

2. Vizsgálja meg az adatbázis által ezekben az időszakokban végzett munkát.

3. Ha azt gyanítja, hogy bizonyos műveletek túl sok adatbázis-tevékenységet okozhatnak, végezzen terheléstesztelést egy ellenőrzött környezetben. Minden tesztnek a gyanús műveletek keverékét kell futtatnia egy változó felhasználói terheléssel. Vizsgálja meg a terhelési tesztek telemetriáját az adatbázis használati módjának megfigyeléséhez.

4. Ha az adatbázis-tevékenység jelentős feldolgozást, de kevés adatforgalmat mutat, tekintse át a forráskódot annak megállapításához, hogy a feldolgozás máshol jobban elvégezhető-e.

Ha az adatbázis-tevékenység mennyisége alacsony, vagy a válaszidők viszonylag gyorsak, akkor nem valószínű, hogy egy foglalt adatbázis teljesítményproblémát okoz.

Diagnosztikai példa

Az alábbi szakaszokban ezeket a lépéseket hajtjuk végre a fentebb leírt mintaalkalmazáson.

Az adatbázis-tevékenység mennyiségének figyelése

Az alábbi grafikon a mintaalkalmazásra végzett terheléses teszt eredményeit mutatja be, legfeljebb 50 egyidejű felhasználó lépésenkénti terhelésével. A kérelmek mennyisége gyorsan eléri a korlátot, és ezen a szinten marad, miközben az átlagos válaszidő folyamatosan nő. A két metrikához logaritmikus skálát használunk.

Ez a vonaldiagram a felhasználói terhelést, a másodpercenkénti kérelmeket és az átlagos válaszidőt jeleníti meg. A grafikon azt mutatja, hogy a válaszidő a terhelés növekedésével nő.

A következő grafikon a cpu-kihasználtságot és a DTU-kat jeleníti meg a szolgáltatáskvóta százalékában. A DTU-k mérik az adatbázis feldolgozásának mértékét. A grafikonon látható, hogy a processzor- és DTU-kihasználtság egyaránt gyorsan elérte a 100%.

Ez a vonaldiagram a processzor és a DTU százalékos arányát mutatja az idő függvényében. A grafikonon látható, hogy mindkettő gyorsan eléri a 100%.

Az adatbázis által végzett munka vizsgálata

Előfordulhat, hogy az adatbázis által végrehajtott feladatok valódi adathozzáférési műveletek, nem pedig feldolgozás, ezért fontos tisztában lenni azzal, hogy az adatbázis foglaltsága alatt milyen SQL-utasítások futnak. Monitorozza a rendszert az SQL-forgalom rögzítéséhez és az SQL-műveletek alkalmazáskérelmekhez való korrelálásához.

Ha az adatbázisműveletek pusztán adatelérési műveletek, sok feldolgozás nélkül, akkor a probléma felesleges beolvasással járhat.

A megoldás megvalósítása és az eredmény ellenőrzése

Az alábbi grafikon egy terheléses tesztet mutat be a frissített kód használatával. Az átviteli teljesítmény jelentősen magasabb, másodpercenként több mint 400 kérés a korábbi 12-höz képest. Az átlagos válaszidő is sokkal alacsonyabb, mindössze 0,1 másodperc felett van a több mint 4 másodperchez képest.

Ez a vonaldiagram a felhasználói terhelést, a másodpercenkénti kérelmeket és az átlagos válaszidőt jeleníti meg. A grafikon azt mutatja, hogy a válaszidő nagyjából állandó marad a terhelési teszt során.

A processzor- és DTU-kihasználtság azt mutatja, hogy a rendszer a megnövekedett átviteli sebesség ellenére tovább tartott a telítettség eléréséig.

Ez a vonaldiagram a processzor és a DTU százalékos arányát mutatja az idő függvényében. A grafikonon látható, hogy a processzor és a DTU hosszabb ideig tart, amíg eléri a 100%, mint korábban.

Kapcsolódó erőforrások

• Felesleges adatlekérés antiszabály