النمط المضاد لقاعدة بيانات مشغولة

يمكن أن يؤدي إلغاء تحميل المعالجة إلى خادم قاعدة بيانات إلى قضاء نسبة كبيرة من الوقت في تشغيل التعليمات البرمجية، بدلاً من الاستجابة لطلبات تخزين البيانات واستردادها.

وصف المشكلة

يمكن للعديد من أنظمة قواعد البيانات تشغيل التعليمات البرمجية. تتضمن الأمثلة الإجراءات المخزنة والمشغلات. غالباً ما يكون إجراء هذه المعالجة بالقرب من البيانات أكثر كفاءة، بدلاً من نقل البيانات إلى تطبيق عميل للمعالجة. ومع ذلك، يمكن أن يضر الاستخدام المفرط لهذه الميزات بالأداء، لعدة أسباب:

• قد يقضي خادم قاعدة البيانات الكثير من الوقت في المعالجة، بدلا من قبول طلبات العميل الجديدة وجلب البيانات.
• عادة ما تكون قاعدة البيانات موردا مشتركا، لذلك يمكن أن تصبح ازدحاما خلال فترات الاستخدام العالي.
• قد تكون تكاليف وقت التشغيل مفرطة إذا تم قياس مخزن البيانات. ينطبق هذا بشكل خاص على خدمات قاعدة البيانات المدارة. على سبيل المثال، رسوم قاعدة بيانات Azure SQL لوحدات معاملات قاعدة البيانات (DTUs).
• قواعد البيانات لديها سعة محدودة لتوسيع نطاقها، وليس من الهزلي توسيع نطاق قاعدة البيانات أفقيا. لذلك، قد يكون من الأفضل نقل المعالجة إلى مورد حساب، مثل جهاز ظاهري أو تطبيق App Service، يمكن توسيع نطاقه بسهولة.

يحدث النمط المضاد هذا عادة بسبب:

• عرض قاعدة البيانات كخدمة بدلاً من مستودع. قد يستخدم التطبيق خادم قاعدة البيانات لتنسيق البيانات (على سبيل المثال، التحويل إلى XML)، أو معالجة بيانات السلسلة، أو إجراء حسابات معقدة.
• يحاول المطورون كتابة الاستعلامات التي يمكن عرض نتائجها مباشرة للمستخدمين. على سبيل المثال، قد يجمع الاستعلام بين الحقول أو تنسيق التواريخ والأوقات والعملة وفقا للترجمة المحلية.
• يحاول المطورون تصحيح النمط المضاد الإحضار الغريب عن طريق دفع الحسابات إلى قاعدة البيانات.
• تستخدم الإجراءات المخزنة لتغليف منطق العمل، ربما لأنها تعتبر أسهل في الصيانة والتحديث.

يسترد المثال التالي أكثر 20 طلباً قيمة لإقليم مبيعات محدد، وينسق النتائج في صورة XML. ويستخدم وظائف Transact-SQL لتحليل البيانات وتحويل النتائج إلى XML. يمكنك العثور على النموذج الكامل هنا.

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

من الواضح أن هذا استعلام معقد. كما سنرى لاحقاً، اتضح استخدام موارد معالجة كبيرة على خادم قاعدة البيانات.

كيفية حل المشكلة

نقل المعالجة من خادم قاعدة البيانات إلى مستويات تطبيق أخرى. من الناحية المثالية، يجب تقييد قاعدة البيانات لتنفيذ عمليات الوصول إلى البيانات، باستخدام القدرات التي تم تحسين قاعدة البيانات لها فقط، مثل التجميع في نظام إدارة قاعدة البيانات الارتباطية (RDBMS).

على سبيل المثال، يمكن استبدال رمز Transact-SQL السابق ببيان يسترد البيانات التي ستتم معالجتها ببساطة.

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]

ثم يستخدم التطبيق واجهات برمجة التطبيقات ‎.NET Framework System.Xml.Linq لتنسيق النتائج كـ 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(" />", "/>");
    }
}

الاعتبارات

• تم تحسين العديد من أنظمة قواعد البيانات بشكل كبير لتنفيذ أنواع معينة من معالجة البيانات، مثل حساب القيم المجمعة عبر مجموعات البيانات الكبيرة. لا تنقل هذه الأنواع من المعالجة خارج قاعدة البيانات.

• لا تقم بنقل المعالجة إذا كان القيام بذلك يؤدي إلى نقل قاعدة البيانات لمزيد من البيانات عبر الشبكة. راجع النمط المضاد لإحضار البيانات الخارجية.

• إذا قمت بنقل المعالجة إلى طبقة تطبيق، فقد يحتاج هذا المستوى إلى التوسع للتعامل مع العمل الإضافي.

كيف تكتشف المشكلة

تتضمن أعراض قاعدة البيانات المزدحمة انخفاضاً غير متناسب في معدل النقل وأوقات الاستجابة في العمليات التي تصل إلى قاعدة البيانات.

يمكنك متابعة الخطوات التالية للمساعدة في تحديد هذه المشكلة:

1. استخدم مراقبة الأداء لتحديد مقدار الوقت الذي يقضيه نظام الإنتاج في أداء نشاط قاعدة البيانات.

2. افحص العمل الذي تقوم به قاعدة البيانات خلال هذه الفترات.

3. إذا كنت تشك في أن عمليات معينة قد تتسبب في الكثير من نشاط قاعدة البيانات، فنفذ اختبار التحميل في بيئة خاضعة للرقابة. يجب أن يقوم كل اختبار بتشغيل مزيج من العمليات المشتبه بها مع تحميل مستخدم متغير. افحص القياس عن بعد من اختبارات التحميل لمراقبة كيفية استخدام قاعدة البيانات.

4. إذا كشف نشاط قاعدة البيانات عن معالجة كبيرة ولكن بقليل من نسبة استخدام الشبكة للبيانات، فراجع التعليمات البرمجية المصدر لتحديد ما إذا كان يمكن إجراء المعالجة بشكل أفضل في مكان آخر.

إذا كان حجم نشاط قاعدة البيانات منخفضاً أو كانت أوقات الاستجابة سريعة نسبياً، فمن غير المحتمل أن تكون قاعدة البيانات المزدحمة مشكلة في الأداء.

مثال التشخيص

تطبق الأقسام التالية هذه الخطوات على نموذج التطبيق الموصوف سابقاً.

مراقبة حجم نشاط قاعدة البيانات

يوضح الرسم البياني التالي نتائج تشغيل اختبار تحميل مقابل نموذج التطبيق، باستخدام تحميل خطوة تصل إلى 50 مستخدما متزامنا. يصل حجم الطلبات بسرعة إلى حد ويبقى عند هذا المستوى، بينما يزيد متوسط وقت الاستجابة باطراد. يتم استخدام مقياس لوغاريتمي لهذين القياسين.

يعرض الرسم البياني الخطي هذا تحميل المستخدم والطلبات في الثانية ومتوسط وقت الاستجابة. يوضح الرسم البياني أن وقت الاستجابة يزيد مع زيادة التحميل.

يوضح الرسم البياني التالي استخدام وحدة المعالجة المركزية ووحدات DTUs كنسبة مئوية من الحصة النسبية للخدمة. توفر وحدات DTUs مقياسا لمقدار معالجة قاعدة البيانات. يوضح الرسم البياني أن استخدام وحدة CPU ووحدة DTU سرعان ما وصلا إلى 100%.

يعرض الرسم البياني الخطي هذا النسبة المئوية لوحدة المعالجة المركزية ونسبة DTU بمرور الوقت. يوضح الرسم البياني أن كلا منهما يصل بسرعة إلى 100%.

فحص العمل الذي تقوم به قاعدة البيانات

قد تكون المهام التي تنفذها قاعدة البيانات هي عمليات الوصول إلى البيانات الأصلية، بدلاً من المعالجة، لذلك من المهم فهم عبارات SQL التي يتم تشغيلها أثناء انشغال قاعدة البيانات. مراقبة النظام لالتقاط نسبة استخدام الشبكة SQL وربط عمليات SQL بطلبات التطبيق.

إذا كانت عمليات قاعدة البيانات عبارة عن عمليات وصول إلى البيانات بحتة، دون الكثير من المعالجة، فقد تكون المشكلة إحضار غريب.

نفذ الحل وتحقق من النتيجة

يعرض الرسم البياني التالي اختبار تحميل باستخدام التعليمات البرمجية المحدثة. معدل النقل أعلى بكثير، أكثر من 400 طلب في الثانية مقابل 12 في السابق. متوسط وقت الاستجابة أيضاً أقل بكثير، فقط فوق 0.1 ثانية مقارنة بأكثر من 4 ثوان.

يعرض الرسم البياني الخطي هذا تحميل المستخدم والطلبات في الثانية ومتوسط وقت الاستجابة. يوضح الرسم البياني أن وقت الاستجابة يظل ثابتا تقريبا طوال اختبار التحميل.

يظهر استخدام وحدة المعالجة المركزية وDTU أن النظام استغرق وقتا أطول للوصول إلى التشبع، على الرغم من زيادة معدل النقل.

يعرض الرسم البياني الخطي هذا النسبة المئوية لوحدة المعالجة المركزية ونسبة DTU بمرور الوقت. يوضح الرسم البياني أن وحدة المعالجة المركزية وDTU يستغرقان وقتاً أطول للوصول إلى 100% من السابق.

الموارد ذات الصلة

• النمط المضاد للإحضار غير المألوف