Bagikan melalui


Antipola Database Sibuk

Memindahkan pemrosesan ke server database dapat menyebabkannya menghabiskan sebagian besar waktu menjalankan kode, daripada menanggapi permintaan untuk menyimpan dan mengambil data.

Deskripsi masalah

Banyak sistem database dapat menjalankan kode. Contohnya termasuk pemicu dan prosedur tersimpan. Sering kali, lebih efisien untuk melakukan pemrosesan ini secara dekat dengan data, daripada mentransmisikan data ke aplikasi klien untuk diproses. Namun, terlalu sering menggunakan fitur-fitur ini dapat menurunkan performa, karena beberapa alasan:

  • Server database mungkin menghabiskan terlalu banyak waktu untuk memproses, daripada menerima permintaan klien baru dan mengambil data.
  • Database biasanya merupakan sumber daya bersama, sehingga dapat terjadi penyempitan selama periode penggunaan tinggi.
  • Biaya runtime mungkin berlebihan jika penyimpanan data diukur. Hal ini terutama berlaku untuk layanan database terkelola. Misalnya, Azure SQL Database membebankan biaya untuk Unit Transaksi Database (DTU).
  • Database memiliki kapasitas terbatas untuk meningkatkan skala, dan menskalakan database secara horizontal bukanlah hal sepele. Oleh karena itu, mungkin lebih baik untuk memindahkan pemrosesan ke sumber daya komputasi, seperti aplikasi VM atau App Service, yang dapat dengan mudah ditingkatkan skalanya.

Anti pola ini biasanya terjadi karena:

  • Database dipandang sebagai layanan daripada repositori. Aplikasi mungkin menggunakan server database untuk memformat data (misalnya, mengonversi ke XML), memanipulasi data untai (karakter), atau melakukan perhitungan yang kompleks.
  • Pengembang mencoba menulis kueri yang hasilnya dapat ditampilkan langsung kepada pengguna. Misalnya, kueri mungkin menggabungkan bidang atau format tanggal, waktu, dan mata uang sesuai dengan lokal.
  • Pengembang mencoba untuk memperbaiki antipola Pengambilan Asing dengan mendorong komputasi ke database.
  • Prosedur tersimpan digunakan untuk merangkum logika bisnis, mungkin karena dianggap lebih mudah untuk dipelihara dan diperbarui.

Contoh berikut mengambil 20 pesanan paling berharga untuk wilayah penjualan tertentu dan memformat hasilnya sebagai XML. Proses ini menggunakan fungsi T-SQL untuk menguraikan data dan mengonversi hasilnya menjadi XML. Anda dapat menemukan sampel lengkap di sini.

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

Jelas, ini adalah kueri yang kompleks. Seperti yang akan kita lihat nanti, hal ini ternyata menggunakan sumber daya pemrosesan yang signifikan pada server database.

Cara memperbaiki masalah ini

Pindahkan pemrosesan dari server database ke tingkatan aplikasi lain. Idealnya, Anda harus membatasi database untuk melakukan operasi akses data, hanya menggunakan kemampuan yang dioptimalkan untuk database, seperti agregasi dalam sistem manajemen database relasional (RDBMS).

Misalnya, kode T-SQL sebelumnya dapat diganti dengan pernyataan yang hanya mengambil data yang akan diproses.

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]

Aplikasi kemudian menggunakan API .NET Framework System.Xml.Linq untuk memformat hasilnya sebagai 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(" />", "/>");
    }
}

Catatan

Kode ini sedikit rumit. Untuk aplikasi baru, Anda mungkin lebih suka menggunakan pustaka serialisasi. Namun, asumsinya adalah bahwa tim pengembangan sedang melakukan refaktor aplikasi yang ada, sehingga metode perlu mengembalikan format yang sama persis dengan kode asli.

Pertimbangan

  • Banyak sistem database sangat dioptimalkan untuk melakukan beberapa jenis pemrosesan data, seperti menghitung nilai agregat untuk himpunan data besar. Jangan memindahkan jenis pemrosesan tersebut keluar dari database.

  • Jangan memindahkan pemrosesan jika hal itu menyebabkan database mentransfer lebih banyak data melalui jaringan. Lihat Antipola Pengambilan Asing.

  • Jika Anda memindahkan pemrosesan ke tingkatan aplikasi, tingkat tersebut mungkin perlu ditingkatkan skalanya untuk menangani pekerjaan tambahan.

Cara mendeteksi masalah

Gejala database sibuk meliputi penurunan throughput dan waktu respons yang tidak proporsional dalam operasi yang mengakses database.

Anda dapat melakukan langkah-langkah berikut untuk membantu mengidentifikasi masalah ini:

  1. Gunakan pemantauan performa untuk mengidentifikasi berapa banyak waktu yang dihabiskan sistem produksi untuk melakukan aktivitas database.

  2. Periksa pekerjaan yang dilakukan oleh database selama periode ini.

  3. Jika Anda menduga bahwa operasi tertentu dapat menyebabkan terlalu banyak aktivitas database, lakukan pengujian muatan di lingkungan yang terkendali. Setiap tes harus menjalankan campuran operasi yang mencurigakan dengan muatan pengguna variabel. Periksa telemetri dari pengujian muatan untuk mengamati bagaimana database digunakan.

  4. Jika aktivitas database mengungkapkan pemrosesan yang signifikan tetapi sedikit lalu lintas data, tinjau kode sumber untuk menentukan apakah pemrosesan dapat dilakukan dengan lebih baik di tempat lain.

Jika volume aktivitas database rendah atau waktu respons relatif cepat, maka database yang sibuk tidak mungkin menyebabkan masalah performa.

Contoh diagnosis

Bagian berikut menerapkan langkah-langkah ini ke aplikasi contoh yang dijelaskan sebelumnya.

Memantau volume aktivitas database

Grafik berikut menunjukkan hasil eksekusi pengujian muatan terhadap aplikasi contoh, menggunakan beban langkah hingga 50 pengguna bersamaan. Volume permintaan dengan cepat mencapai batas dan tetap pada tingkat itu, sementara waktu respons rata-rata terus meningkat. Skala logaritmik digunakan untuk kedua metrik tersebut.

Hasil uji muatan untuk melakukan pemrosesan dalam database

Grafik garis ini menunjukkan muatan pengguna, permintaan per detik, dan waktu respons rata-rata. Grafik menunjukkan bahwa waktu respons meningkat saat muatan meningkat.

Grafik berikutnya menunjukkan pemanfaatan CPU dan DTU sebagai persentase dari kuota layanan. DTU memberikan ukuran berapa banyak pemrosesan yang dilakukan database. Grafik menunjukkan bahwa pemanfaatan CPU dan DTU dengan cepat mencapai 100%.

Azure SQL Database monitor yang menunjukkan kinerja database saat melakukan pemrosesan

Grafik garis ini menunjukkan persentase CPU dan persentase DTU dari waktu ke waktu. Grafik menunjukkan bahwa keduanya dengan cepat mencapai 100%.

Memeriksa pekerjaan yang dilakukan oleh database

Tugas yang dilakukan oleh database kemungkinan adalah operasi akses data asli, daripada pemrosesan, jadi penting untuk memahami pernyataan SQL yang dijalankan saat database sibuk. Pantau sistem untuk menangkap lalu lintas SQL dan menghubungkan operasi SQL dengan permintaan aplikasi.

Jika operasi database murni operasi akses data, tanpa banyak pemrosesan, maka masalahnya mungkin adalah Pengambilan Asing.

Menerapkan solusi dan memverifikasi hasilnya

Grafik berikut menunjukkan uji muatan menggunakan kode yang diperbarui. Throughput secara signifikan lebih tinggi, lebih dari 400 permintaan per detik dibandingkan 12 sebelumnya. Waktu respons rata-rata juga jauh lebih rendah, tepat di atas 0,1 detik dibandingkan dengan lebih dari 4 detik.

Grafik yang menunjukkan hasil uji muatan untuk melakukan pemrosesan dalam aplikasi klien.

Grafik garis ini menunjukkan muatan pengguna, permintaan per detik, dan waktu respons rata-rata. Grafik menunjukkan bahwa waktu respons tetap hampir konstan sepanjang uji muatan.

Pemanfaatan CPU dan DTU menunjukkan bahwa sistem membutuhkan waktu lebih lama untuk mencapai saturasi, meskipun throughput meningkat.

Azure SQL Database monitor yang menunjukkan kinerja database saat melakukan pemrosesan di aplikasi klien

Grafik garis ini menunjukkan persentase CPU dan persentase DTU dari waktu ke waktu. Grafik menunjukkan bahwa CPU dan DTU membutuhkan waktu lebih lama untuk mencapai 100% dari sebelumnya.