SQL Server — Ghi nhật ký truy cập phải chờ log từng dòng: delayed durability

Đọc nhanh

Vấn đề: Worker ghi nhật ký truy cập của BanHang commit từng dòng, mỗi COMMIT chờ log record xuống đĩa; 45% thời gian của worker là chờ WRITELOG.
Cách giải: Cho phép delayed durability ở database, và chỉ lệnh ghi nhật ký commit WITH (DELAYED_DURABILITY = ON); đơn hàng và thanh toán giữ commit bền.
Chứng minh: Một luồng ghi 20.000 dòng: từ 1.229 lên 3.826 dòng/s, từ 20.000 xuống 356 lần ghi file log; khi giết máy chủ, mất trung vị 53 dòng đã báo thành công, commit bền mất 0.
Trong .NET: Gửi BEGIN TRAN; INSERT ...; COMMIT TRAN WITH (DELAYED_DURABILITY = ON); trong một SqlCommand, vì SqlTransaction.CommitAsync không có tùy chọn này.

1. Vấn đề: mỗi dòng nhật ký là một lần chờ đĩa log

API của BanHang ghi một dòng vào dbo.NhatKyTruyCap cho mỗi request, khoảng 300 request mỗi giây lúc cao điểm 10:00–11:30. Middleware đẩy lượt truy cập vào hàng đợi trong bộ nhớ, và một worker lấy ra ghi từng dòng, mỗi dòng một giao dịch. Nhật ký chỉ dùng để xem lưu lượng và lỗi theo đường dẫn; mất vài dòng cuối khi máy chủ sập là chấp nhận được.

Cái giá của việc ghi từng dòng nằm ở COMMIT. Mỗi lần commit, SQL Server chờ log record của giao dịch xuống file log trên ổ L:, cùng ổ với commit của đơn hàng. Dựng lại worker trên LocalDB và cho nó ghi 20.000 dòng:

Đo	Một luồng, commit bền từng dòng
Thời gian ghi 20.000 dòng	16.274 ms, tức 1.229 dòng/s
Tổng thời gian chờ `WRITELOG`	7.295 ms, 45% thời gian chạy
Số lần ghi file log	20.000, mỗi dòng một lần

Ở 300 dòng mỗi giây, đó là 300 lần ghi file log mỗi giây chỉ để lưu những dòng mà nghiệp vụ chấp nhận mất. Worker cũng mất gần một nửa thời gian đứng chờ đĩa.

2. Mục đích: worker không chờ đĩa, rủi ro được đếm bằng số

Worker một luồng không còn chờ WRITELOG: tổng thời gian chờ gần 0.
Số lần ghi file log cho 20.000 dòng giảm ít nhất 10 lần.
Thông lượng của worker một luồng tăng ít nhất gấp đôi.
Rủi ro được đo: số dòng đã báo thành công mà mất khi tiến trình SQL Server bị giết, so với commit bền.
Ngoài phạm vi: nhật ký đăng nhập, kiểm toán và mọi dữ liệu tiền. Những dòng đó giữ commit bền.

3. Cơ sở lý thuyết: commit bền chờ log, delayed durability thì không

Commit bền là một lần chờ đĩa

Giao dịch ghi log record vào bộ đệm log trong bộ nhớ. Khi COMMIT, SQL Server ghi bản ghi commit rồi chờ phần bộ đệm chứa nó xuống file log, sau đó mới báo thành công. Thời gian chờ đó hiện thành wait type WRITELOG của phiên.

Một worker ghi tuần tự thì không ai chia sẻ lần ghi đĩa với nó: 20.000 commit là 20.000 lần ghi file log, đúng như số đo ở mục 1. Trên máy đo, mỗi lần chờ trung bình 0,36 ms, tức 7.295 ms chia cho 20.000 lần. Con số này nhỏ vì file log nằm trên SSD NVMe của máy đo; ổ log chậm hơn thì mỗi commit chờ lâu hơn đúng phần chênh đó.

Khi nhiều phiên commit cùng lúc, một lần ghi mang được nhiều bản ghi commit. Cùng 20.000 dòng chia cho 8 luồng chỉ cần 8.184 lần ghi file log (trung vị 5 lần đo), nên ghi song song đã tự gom một phần.

Delayed durability: trả về trước khi ghi

Với delayed durability, COMMIT trả về ngay khi bản ghi commit vào bộ đệm log. Bộ đệm được ghi xuống đĩa sau, theo các mốc tài liệu nêu:

khi một giao dịch bền trong cùng database commit;
khi gọi sys.sp_flush_log;
khi SQL Server tự ghi theo lượng log sinh ra và theo thời gian, nhưng không có cam kết cứng nào cho trường hợp này.

sequenceDiagram
  participant W as Worker nhật ký
  participant S as SQL Server
  participant L as File log trên L:
  W->>S: INSERT, COMMIT (bền)
  S->>L: ghi bộ đệm log
  L-->>S: đã ghi xong
  S-->>W: thành công
  W->>S: INSERT, COMMIT WITH (DELAYED_DURABILITY = ON)
  S-->>W: thành công ngay
  S->>L: ghi sau, gộp nhiều commit một lần

Commit đã báo thành công nhưng chưa xuống đĩa sẽ mất nếu máy chủ sập. Tài liệu coi tắt máy có kế hoạch cũng như sập: phải tính là có thể mất. Dữ liệu không hỏng; recovery vẫn đưa database về trạng thái nhất quán, chỉ thiếu các giao dịch cuối.

Ai được phép trả về sớm

Database đặt DELAYED_DURABILITY ở một trong ba mức. DISABLED, mặc định, buộc mọi commit bền. ALLOWED để từng giao dịch tự chọn bằng COMMIT ... WITH (DELAYED_DURABILITY = ON). FORCED biến mọi commit trong database thành commit trả về sớm.

Một số đường luôn bền hoặc không nhận phần chưa bền:

Trường hợp	Hành vi
Giao dịch liên database hoặc qua DTC	Luôn bền, bất kể thiết lập
Log backup, log shipping	Chỉ chứa giao dịch đã xuống đĩa
Availability Group	Không bảo đảm gì cho giao dịch trả về sớm, kể cả với replica đồng bộ
CDC, transactional replication	Không hỗ trợ delayed durability

4. Cách giải quyết: commit trả về sớm cho đúng lệnh ghi nhật ký

Cách	Ưu	Nhược	Khi nào dùng
Giữ commit bền từng dòng	Không mất dòng nào đã báo thành công	Mỗi dòng một lần ghi đĩa log	Đơn hàng, tiền, kiểm toán
`COMMIT WITH (DELAYED_DURABILITY = ON)` cho lệnh ghi nhật ký	Đổi một dòng T-SQL; giao dịch khác không đổi	Mất các dòng cuối khi máy chủ sập hoặc tắt	Dữ liệu mất vài dòng được
`FORCED` trên một database riêng cho nhật ký	Không đổi code ghi	Thêm database; mọi giao dịch trong đó đều có thể mất	Không sửa được lệnh ghi
Gom lô 100 dòng một giao dịch	Nhanh nhất; vẫn bền khi commit	Phải đổi worker thành vòng gom; mất phần đang gom khi ứng dụng sập	Lưu lượng vượt sức một worker
Ổ log nhanh hơn	Không đổi code	Tốn tiền; vẫn một lần chờ mỗi commit	`WRITELOG` cao với mọi loại giao dịch

BanHang chọn cách thứ hai cho worker nhật ký. Nó đáp ứng cả ba tiêu chí về tốc độ ở mục 2, và 3.826 dòng/s của một luồng còn dư nhiều so với 300 request mỗi giây. Gom lô nhanh hơn nữa, 22.344 dòng/s, nhưng phải viết lại worker; đó là bước kế tiếp khi lưu lượng vượt vài nghìn dòng mỗi giây.

Các bước:

Đo WRITELOG và thời gian ghi của file log trên database thật, để biết có gì để bỏ.
ALTER DATABASE BanHang SET DELAYED_DURABILITY = ALLOWED; Mức ALLOWED không đổi giao dịch nào chưa tự chọn.
Đổi lệnh ghi nhật ký thành một lô T-SQL có COMMIT TRAN WITH (DELAYED_DURABILITY = ON).
Trước khi tắt SQL Server có kế hoạch, chạy EXEC sys.sp_flush_log; để ghi nốt phần đang chờ.

5. Cách cài đặt: một lệnh ALTER DATABASE và một lô T-SQL

Delayed durability chỉ bỏ được phần chờ WRITELOG. Trên database thật, hai câu dưới cho biết phần đó lớn cỡ nào: thời gian chờ ghi trung bình của file log, và tổng thời gian chờ WRITELOG của cả instance từ lúc khởi động.

SELECT DB_NAME(database_id) AS db, num_of_writes,
       io_stall_write_ms * 1.0 / NULLIF(num_of_writes, 0) AS ms_moi_lan_ghi
FROM sys.dm_io_virtual_file_stats(DB_ID(N'BanHang'), 2);

SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = N'WRITELOG';

Nếu WRITELOG không nằm gần đầu danh sách chờ của luồng ghi nhật ký, đổi kiểu commit không làm nó nhanh hơn đáng kể.

Sau khi đo, bật mức ALLOWED và tạo bảng nhật ký, cột xếp từ cố định đến thay đổi độ dài. Bảng này mới, không đổi bảng nào đã có của BanHang.

ALTER DATABASE BanHang SET DELAYED_DURABILITY = ALLOWED;

CREATE TABLE dbo.NhatKyTruyCap (
    NhatKyId bigint IDENTITY NOT NULL CONSTRAINT PK_NhatKyTruyCap PRIMARY KEY CLUSTERED,
    ThoiDiem datetime2(3) NOT NULL,
    ThoiGianMs int NOT NULL,
    MaTrangThai smallint NOT NULL,
    DuongDan varchar(200) NOT NULL
);

SqlTransaction.CommitAsync không có tham số nào cho delayed durability, nên giao dịch được mở và commit ngay trong lô T-SQL. Đó là hai hằng số của bản đầy đủ: Ben là lệnh cũ, Tre là lệnh mới.

const string Ben = "INSERT dbo.NhatKyTruyCap (ThoiDiem, ThoiGianMs, MaTrangThai, DuongDan) VALUES (SYSUTCDATETIME(), @ms, 200, @duong);";
const string Tre = "BEGIN TRAN; " + Ben + " COMMIT TRAN WITH (DELAYED_DURABILITY = ON);";

var cmd = new SqlCommand(sql, cn);   // sql là Ben hoặc Tre
cmd.Parameters.Add("@ms", SqlDbType.Int).Value = i % 500;
cmd.Parameters.Add("@duong", SqlDbType.VarChar, 200).Value = "/api/don-hang/" + i;
await cmd.ExecuteNonQueryAsync();

Bản đầy đủ chạy trên LocalDB SQL Server 2019 (15.0.4382), .NET 10.0.401, Microsoft.Data.SqlClient 7.1.1. Nó dùng một instance LocalDB riêng, KumeoC, vì phần 2 giết tiến trình SQL Server; giết instance MSSQLLocalDB dùng chung sẽ cắt mọi ứng dụng khác trên máy.

Phần 1 ghi 20.000 dòng theo ba cách, với 1 rồi 8 luồng, mỗi cấu hình 5 vòng. Nó đọc số lần ghi file log từ sys.dm_io_virtual_file_stats và thời gian chờ WRITELOG của từng phiên từ sys.dm_exec_session_wait_stats. Phần 2 cho 8 luồng ghi liên tục, sau 3 giây gọi sqllocaldb stop KumeoC -k để giết tiến trình, khởi động lại, rồi so số dòng còn trong bảng với số lệnh máy chủ đã báo thành công.

NhatKyTruyCap.cs: đo ba cách ghi và đếm dòng mất khi giết máy chủ (dotnet run NhatKyTruyCap.cs)C# · 167 dòng

#:package Microsoft.Data.SqlClient@7.1.1
// Ghi nhật ký truy cập vào SQL Server: commit bền từng dòng, delayed durability từng dòng, gom lô 100 dòng.
// Phần 1 đo thông lượng, số lần ghi file log, thời gian chờ WRITELOG. Phần 2 giết tiến trình SQL Server giữa lúc ghi
// rồi đếm dòng đã báo thành công mà không còn sau khi khởi động lại.
// Chuẩn bị: sqllocaldb create KumeoC 15.0   (instance LocalDB riêng, vì phần 2 tắt cả instance)
// Chạy:     dotnet run NhatKyTruyCap.cs
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.Text;
using Microsoft.Data.SqlClient;

CultureInfo.DefaultThreadCurrentCulture = CultureInfo.CurrentCulture = new CultureInfo("vi-VN");
const string Cs = @"Server=(localdb)\KumeoC;Database=Kumeo_C;Integrated Security=true;TrustServerCertificate=true;Connect Timeout=10";

// Ba cách ghi. Hai cách đầu một dòng một giao dịch; cách thứ ba 100 dòng một câu INSERT, một giao dịch.
const string Ben = "INSERT dbo.NhatKyTruyCap (ThoiDiem, ThoiGianMs, MaTrangThai, DuongDan) VALUES (SYSUTCDATETIME(), @ms, 200, @duong);";
const string Tre = "BEGIN TRAN; " + Ben + " COMMIT TRAN WITH (DELAYED_DURABILITY = ON);";
var cachGhi = new (string ten, string? sql, int lo)[] { ("commit bền", Ben, 1), ("delayed durability", Tre, 1), ("gom lô 100 dòng", null, 100) };

await ChuanBi();
Console.WriteLine("Phần 1: 20.000 dòng mỗi lượt");
foreach (var luong in new[] { 1, 8 })
    for (var vong = 1; vong <= 5; vong++)
        foreach (var (ten, sql, lo) in cachGhi)
            await DoThongLuong($"{ten,-18} {luong} luồng, vòng {vong}", luong, 20_000, sql, lo);

Console.WriteLine("\nPhần 2: 8 luồng ghi liên tục, giết tiến trình SQL Server sau 3 giây");
for (var vong = 1; vong <= 5; vong++)
    foreach (var (ten, sql, lo) in cachGhi.Take(2))
        await DemDongMat($"{ten,-18} vòng {vong}", sql!);

static async Task DoThongLuong(string ten, int soLuong, int soDong, string? sql, int lo)
{
    var (ghiLog0, _) = await FileLog();
    var tre = new List<double>();
    double writelogMs = 0;
    var sw = Stopwatch.StartNew();
    await Task.WhenAll(Enumerable.Range(0, soLuong).Select(async t =>
    {
        var cua = new List<double>();
        await using var cn = new SqlConnection(Cs);
        await cn.OpenAsync();
        for (var i = 0; i < soDong / soLuong / lo; i++)
        {
            await using var cmd = TaoLenh(cn, sql, lo, i);
            var bd = Stopwatch.GetTimestamp();
            await cmd.ExecuteNonQueryAsync();
            cua.Add(Stopwatch.GetElapsedTime(bd).TotalMilliseconds);
        }
        await using var w = new SqlCommand("SELECT ISNULL(SUM(wait_time_ms), 0) FROM sys.dm_exec_session_wait_stats WHERE session_id = @@SPID AND wait_type = 'WRITELOG';", cn);
        var ms = Convert.ToDouble(await w.ExecuteScalarAsync());
        lock (tre) { tre.AddRange(cua); writelogMs += ms; }
    }));
    sw.Stop();
    var (ghiLog1, _) = await FileLog();
    tre.Sort();
    Console.WriteLine($"{ten}: {soDong / sw.Elapsed.TotalSeconds,6:N0} dòng/s, mỗi lệnh p50 {tre[tre.Count / 2],5:N2} ms, "
        + $"ghi file log {ghiLog1 - ghiLog0,6:N0} lần, tổng chờ WRITELOG {writelogMs,6:N0} ms trong {sw.Elapsed.TotalMilliseconds:N0} ms");
}

static SqlCommand TaoLenh(SqlConnection cn, string? sql, int lo, int i)
{
    if (lo == 1)
    {
        var cmd = new SqlCommand(sql, cn);
        cmd.Parameters.Add("@ms", SqlDbType.Int).Value = i % 500;
        cmd.Parameters.Add("@duong", SqlDbType.VarChar, 200).Value = "/api/don-hang/" + i;
        return cmd;
    }
    var sb = new StringBuilder("INSERT dbo.NhatKyTruyCap (ThoiDiem, ThoiGianMs, MaTrangThai, DuongDan) VALUES ");
    var lenh = new SqlCommand { Connection = cn };
    for (var j = 0; j < lo; j++)
    {
        sb.Append(j == 0 ? "" : ",").Append($"(SYSUTCDATETIME(), @ms{j}, 200, @d{j})");
        lenh.Parameters.Add($"@ms{j}", SqlDbType.Int).Value = j % 500;
        lenh.Parameters.Add($"@d{j}", SqlDbType.VarChar, 200).Value = "/api/don-hang/" + (i * lo + j);
    }
    lenh.CommandText = sb.ToString();
    return lenh;
}

static async Task<(long soLanGhi, long byteGhi)> FileLog()
{
    await using var cn = new SqlConnection(Cs);
    await cn.OpenAsync();
    await using var cmd = new SqlCommand("SELECT num_of_writes, num_of_bytes_written FROM sys.dm_io_virtual_file_stats(DB_ID(), 2);", cn);
    await using var r = await cmd.ExecuteReaderAsync();
    await r.ReadAsync();
    return (r.GetInt64(0), r.GetInt64(1));
}

static async Task ChuanBi()
{
    var b = new SqlConnectionStringBuilder(Cs) { InitialCatalog = "master" };
    for (var k = 0; ; k++)
    {
        try
        {
            await using var m = new SqlConnection(b.ConnectionString);
            await m.OpenAsync();
            await new SqlCommand("IF DB_ID(N'Kumeo_C') IS NULL CREATE DATABASE Kumeo_C COLLATE Vietnamese_100_CI_AS;", m).ExecuteNonQueryAsync();
            break;
        }
        catch (SqlException) when (k < 30) { await Task.Delay(1000); }
    }
    await using var cn = new SqlConnection(Cs);
    await cn.OpenAsync();
    await new SqlCommand("""
        ALTER DATABASE Kumeo_C SET DELAYED_DURABILITY = ALLOWED;
        DROP TABLE IF EXISTS dbo.NhatKyTruyCap;
        CREATE TABLE dbo.NhatKyTruyCap (
            NhatKyId bigint IDENTITY NOT NULL CONSTRAINT PK_NhatKyTruyCap PRIMARY KEY CLUSTERED,
            ThoiDiem datetime2(3) NOT NULL,
            ThoiGianMs int NOT NULL,
            MaTrangThai smallint NOT NULL,
            DuongDan varchar(200) NOT NULL
        );
        """, cn).ExecuteNonQueryAsync();
}

// 8 luồng ghi liên tục; sau 3 giây giết tiến trình của instance KumeoC, khởi động lại,
// so số dòng còn trong bảng với số lệnh máy chủ đã báo thành công.
static async Task DemDongMat(string ten, string sql)
{
    await ChuanBi();
    SqlConnection.ClearAllPools();
    long daBao = 0;
    var luong = Enumerable.Range(0, 8).Select(t => Task.Run(async () =>
    {
        try
        {
            await using var cn = new SqlConnection(Cs);
            await cn.OpenAsync();
            for (var i = 0; ; i++)
            {
                await using var cmd = TaoLenh(cn, sql, 1, i);
                await cmd.ExecuteNonQueryAsync();
                Interlocked.Increment(ref daBao); // máy chủ đã báo commit thành công
            }
        }
        catch (SqlException) { }
    })).ToArray();
    await Task.Delay(3000);
    await ChayLenh("sqllocaldb", "stop KumeoC -k");
    await Task.WhenAll(luong);
    SqlConnection.ClearAllPools();
    await ChayLenh("sqllocaldb", "start KumeoC");
    long con = -1;
    for (var k = 0; k < 60 && con < 0; k++)
    {
        try
        {
            await using var cn = new SqlConnection(Cs);
            await cn.OpenAsync();
            con = Convert.ToInt64(await new SqlCommand("SELECT COUNT_BIG(*) FROM dbo.NhatKyTruyCap;", cn).ExecuteScalarAsync());
        }
        catch (SqlException) { await Task.Delay(1000); }
    }
    Console.WriteLine($"{ten}: máy chủ đã báo thành công {daBao,6:N0} dòng, còn sau khi khởi động lại {con,6:N0}, mất {daBao - con,5:N0}");
}

static async Task ChayLenh(string file, string args)
{
    using var p = Process.Start(new ProcessStartInfo(file, args) { RedirectStandardOutput = true, UseShellExecute = false })!;
    await p.WaitForExitAsync();
}

Output của NhatKyTruyCap.cs43 dòng

Phần 1: 20.000 dòng mỗi lượt
commit bền         1 luồng, vòng 1:    720 dòng/s, mỗi lệnh p50  0,61 ms, ghi file log 20.000 lần, tổng chờ WRITELOG 11.784 ms trong 27.791 ms
delayed durability 1 luồng, vòng 1:  4.064 dòng/s, mỗi lệnh p50  0,17 ms, ghi file log    346 lần, tổng chờ WRITELOG      0 ms trong 4.922 ms
gom lô 100 dòng    1 luồng, vòng 1: 37.002 dòng/s, mỗi lệnh p50  2,46 ms, ghi file log    200 lần, tổng chờ WRITELOG     48 ms trong 541 ms
commit bền         1 luồng, vòng 2:  1.230 dòng/s, mỗi lệnh p50  0,45 ms, ghi file log 20.000 lần, tổng chờ WRITELOG  6.829 ms trong 16.258 ms
delayed durability 1 luồng, vòng 2:  1.597 dòng/s, mỗi lệnh p50  0,26 ms, ghi file log    773 lần, tổng chờ WRITELOG      0 ms trong 12.522 ms
gom lô 100 dòng    1 luồng, vòng 2: 16.043 dòng/s, mỗi lệnh p50  4,51 ms, ghi file log    200 lần, tổng chờ WRITELOG    290 ms trong 1.247 ms
commit bền         1 luồng, vòng 3:  1.482 dòng/s, mỗi lệnh p50  0,36 ms, ghi file log 20.004 lần, tổng chờ WRITELOG  6.908 ms trong 13.492 ms
delayed durability 1 luồng, vòng 3:  3.829 dòng/s, mỗi lệnh p50  0,20 ms, ghi file log    356 lần, tổng chờ WRITELOG      0 ms trong 5.224 ms
gom lô 100 dòng    1 luồng, vòng 3: 24.576 dòng/s, mỗi lệnh p50  3,83 ms, ghi file log    200 lần, tổng chờ WRITELOG     55 ms trong 814 ms
commit bền         1 luồng, vòng 4:  1.229 dòng/s, mỗi lệnh p50  0,39 ms, ghi file log 20.004 lần, tổng chờ WRITELOG  7.295 ms trong 16.274 ms
delayed durability 1 luồng, vòng 4:  3.359 dòng/s, mỗi lệnh p50  0,21 ms, ghi file log    396 lần, tổng chờ WRITELOG      0 ms trong 5.954 ms
gom lô 100 dòng    1 luồng, vòng 4: 22.344 dòng/s, mỗi lệnh p50  4,21 ms, ghi file log    200 lần, tổng chờ WRITELOG     58 ms trong 895 ms
commit bền         1 luồng, vòng 5:  1.196 dòng/s, mỗi lệnh p50  0,42 ms, ghi file log 20.000 lần, tổng chờ WRITELOG  7.753 ms trong 16.722 ms
delayed durability 1 luồng, vòng 5:  3.826 dòng/s, mỗi lệnh p50  0,21 ms, ghi file log    346 lần, tổng chờ WRITELOG      0 ms trong 5.228 ms
gom lô 100 dòng    1 luồng, vòng 5:  3.901 dòng/s, mỗi lệnh p50 10,83 ms, ghi file log    200 lần, tổng chờ WRITELOG  1.635 ms trong 5.127 ms
commit bền         8 luồng, vòng 1:  5.140 dòng/s, mỗi lệnh p50  0,92 ms, ghi file log 13.788 lần, tổng chờ WRITELOG 10.655 ms trong 3.891 ms
delayed durability 8 luồng, vòng 1:  7.721 dòng/s, mỗi lệnh p50  0,70 ms, ghi file log    356 lần, tổng chờ WRITELOG      0 ms trong 2.590 ms
gom lô 100 dòng    8 luồng, vòng 1:  7.570 dòng/s, mỗi lệnh p50 57,53 ms, ghi file log    207 lần, tổng chờ WRITELOG    175 ms trong 2.642 ms
commit bền         8 luồng, vòng 2:  1.945 dòng/s, mỗi lệnh p50  1,20 ms, ghi file log  8.806 lần, tổng chờ WRITELOG 34.192 ms trong 10.285 ms
delayed durability 8 luồng, vòng 2:  7.309 dòng/s, mỗi lệnh p50  0,75 ms, ghi file log    273 lần, tổng chờ WRITELOG      0 ms trong 2.736 ms
gom lô 100 dòng    8 luồng, vòng 2: 34.460 dòng/s, mỗi lệnh p50 18,17 ms, ghi file log    129 lần, tổng chờ WRITELOG    391 ms trong 580 ms
commit bền         8 luồng, vòng 3:  5.155 dòng/s, mỗi lệnh p50  0,97 ms, ghi file log  8.184 lần, tổng chờ WRITELOG 12.524 ms trong 3.879 ms
delayed durability 8 luồng, vòng 3:  3.611 dòng/s, mỗi lệnh p50  0,82 ms, ghi file log    280 lần, tổng chờ WRITELOG      0 ms trong 5.538 ms
gom lô 100 dòng    8 luồng, vòng 3: 28.013 dòng/s, mỗi lệnh p50 21,92 ms, ghi file log    127 lần, tổng chờ WRITELOG    487 ms trong 714 ms
commit bền         8 luồng, vòng 4:  5.334 dòng/s, mỗi lệnh p50  1,02 ms, ghi file log  7.329 lần, tổng chờ WRITELOG 11.963 ms trong 3.750 ms
delayed durability 8 luồng, vòng 4:  3.958 dòng/s, mỗi lệnh p50  1,06 ms, ghi file log    400 lần, tổng chờ WRITELOG      0 ms trong 5.054 ms
gom lô 100 dòng    8 luồng, vòng 4: 19.372 dòng/s, mỗi lệnh p50 32,90 ms, ghi file log    161 lần, tổng chờ WRITELOG    292 ms trong 1.032 ms
commit bền         8 luồng, vòng 5:  5.158 dòng/s, mỗi lệnh p50  1,01 ms, ghi file log  7.700 lần, tổng chờ WRITELOG 11.608 ms trong 3.878 ms
delayed durability 8 luồng, vòng 5:  3.471 dòng/s, mỗi lệnh p50  0,84 ms, ghi file log    338 lần, tổng chờ WRITELOG      0 ms trong 5.761 ms
gom lô 100 dòng    8 luồng, vòng 5:  9.437 dòng/s, mỗi lệnh p50 58,28 ms, ghi file log    148 lần, tổng chờ WRITELOG  1.129 ms trong 2.119 ms

Phần 2: 8 luồng ghi liên tục, giết tiến trình SQL Server sau 3 giây
commit bền         vòng 1: máy chủ đã báo thành công  2.422 dòng, còn sau khi khởi động lại  2.425, mất    -3
delayed durability vòng 1: máy chủ đã báo thành công 22.007 dòng, còn sau khi khởi động lại 21.906, mất   101
commit bền         vòng 2: máy chủ đã báo thành công 13.740 dòng, còn sau khi khởi động lại 13.747, mất    -7
delayed durability vòng 2: máy chủ đã báo thành công 29.717 dòng, còn sau khi khởi động lại 29.664, mất    53
commit bền         vòng 3: máy chủ đã báo thành công  2.596 dòng, còn sau khi khởi động lại  2.596, mất     0
delayed durability vòng 3: máy chủ đã báo thành công 18.186 dòng, còn sau khi khởi động lại 18.128, mất    58
commit bền         vòng 4: máy chủ đã báo thành công 14.297 dòng, còn sau khi khởi động lại 14.297, mất     0
delayed durability vòng 4: máy chủ đã báo thành công 20.421 dòng, còn sau khi khởi động lại 20.406, mất    15
commit bền         vòng 5: máy chủ đã báo thành công 18.884 dòng, còn sau khi khởi động lại 18.886, mất    -2
delayed durability vòng 5: máy chủ đã báo thành công  3.686 dòng, còn sau khi khởi động lại  3.676, mất    10

6. Chứng minh: nhanh gấp 3,1 lần, mất trung vị 53 dòng mỗi lần sập

Môi trường: LocalDB SQL Server 2019 Express 15.0.4382 trên Windows 11, file log trên SSD NVMe, máy dùng chung với tiến trình khác. Bảng ghi trung vị 5 vòng.

Một luồng, 20.000 dòng

Tiêu chí	Commit bền	Delayed durability	Gom lô 100 dòng	Đạt
Tổng chờ `WRITELOG`	7.295 ms trong 16.274 ms	0 ms	58 ms	Có
Số lần ghi file log	20.000	356	200	Có, giảm 56 lần
Thông lượng	1.229 dòng/s	3.826 dòng/s	22.344 dòng/s	Có, gấp 3,1 lần
Mỗi lệnh, p50	0,42 ms	0,21 ms	4,21 ms cho 100 dòng

Worker một luồng ghi 20.000 dòng: delayed durability gấp 3,1 lần commit bền, gom lô 100 dòng gấp 18 lần

Commit bền từng dòng1.229 dòng/sDelayed durability3.826 dòng/sGom lô 100 dòng, bền22.344 dòng/s

LocalDB SQL Server 2019 Express, instance riêng KumeoC, trung vị 5 vòng.

Bảng số liệu

	Giá trị
Commit bền từng dòng	1.229 dòng/s
Delayed durability	3.826 dòng/s
Gom lô 100 dòng, bền	22.344 dòng/s

Tám luồng: không kết luận được

Với 8 luồng, commit bền đạt 5.155 dòng/s và delayed durability 3.958 dòng/s (trung vị). Số lần ghi file log vẫn giảm, từ 8.184 xuống 338, nhưng thông lượng dao động mạnh giữa các vòng: delayed durability chạy từ 3.471 đến 7.721 dòng/s. Commit song song đã tự chia sẻ lần ghi đĩa, nên trên máy này lợi ích về tốc độ không còn đo ra được. Bài chỉ khẳng định kết quả cho worker một luồng.

Rủi ro khi máy chủ bị giết

Vòng	Delayed durability: mất	Commit bền: đã lưu mà báo lỗi
1	101	3
2	53	7
3	58	0
4	15	0
5	10	2

Giết tiến trình SQL Server giữa lúc ghi: delayed durability mất 10 đến 101 dòng đã báo thành công, commit bền mất 0

Vòng 1101 dòngVòng 253 dòngVòng 358 dòngVòng 415 dòngVòng 510 dòng

Delayed durability, 8 luồng ghi liên tục, sqllocaldb stop KumeoC -k sau 3 giây. Commit bền mất 0 dòng ở cả 5 vòng.

Bảng số liệu

	Giá trị
Vòng 1	101 dòng
Vòng 2	53 dòng
Vòng 3	58 dòng
Vòng 4	15 dòng
Vòng 5	10 dòng

Commit bền không mất dòng nào đã báo thành công. Nó còn cho thấy chiều ngược lại: có vòng bảng có nhiều hơn số lệnh được báo thành công, vì commit đã xuống đĩa nhưng câu trả lời không kịp về trước khi tiến trình chết. Với nhật ký thì không sao; với lệnh trừ tiền thì đó là lý do cần khóa chống trùng.

Cột "mất" đếm dòng máy chủ đã báo thành công mà không còn sau khi khởi động lại. Phép thử giết tiến trình chỉ đại diện cho sập máy. Mất điện cả máy, hay ổ log chậm hơn SSD của máy đo, cho con số khác; bài chưa đo hai trường hợp đó.

7. Kết luận

Delayed durability bỏ lần chờ đĩa của từng commit, và cái giá là các giao dịch cuối khi máy chủ sập. Dùng nó cho đúng lệnh ghi dữ liệu mất được, bật bằng ALLOWED, không bằng FORCED trên database chứa đơn hàng.

Trong dự án .NET của bạn:

Đo trước: nếu WRITELOG không chiếm phần lớn thời gian của luồng ghi, delayed durability không giúp được gì.
Viết lệnh ghi nhật ký thành một lô BEGIN TRAN; INSERT ...; COMMIT TRAN WITH (DELAYED_DURABILITY = ON); trong SqlCommand; không dùng BeginTransactionAsync cho lệnh này.
Giữ commit bền cho mọi giao dịch có tiền, đơn hàng, đăng nhập, và mọi giao dịch đi qua DTC.
Khi một worker không theo kịp, chuyển sang gom lô bằng một câu INSERT nhiều dòng hoặc SqlBulkCopy.

Những chỗ hay hiểu sai

"Delayed durability có thể làm hỏng database." Recovery vẫn nhất quán; chỉ mất các giao dịch cuối chưa xuống đĩa.
"Tắt máy có kế hoạch thì không mất gì." Tài liệu yêu cầu tính như sập; chạy sys.sp_flush_log trước khi tắt.
"Có Availability Group đồng bộ thì commit trả về sớm vẫn an toàn." Không có bảo đảm nào cho giao dịch đó trên replica.

Đọc tiếp

Bài trước: Máy chủ chính chết giữa giờ cao điểm.
Bài sau: Chọn kiểu cột: mỗi byte nhân lên 10 triệu dòng.
Log record, VLF và đường ghi của một commit: Write-ahead log và recovery model.
Bản tương ứng bên PostgreSQL, synchronous_commit = off: WAL và checkpoint.

Nguồn

Control transaction durability: khi nào bộ đệm log được ghi, mức DISABLED, ALLOWED, FORCED, quan hệ với AG, log backup, CDC, DTC, và các tình huống mất dữ liệu.
sys.sp_flush_log
Editions and supported features of SQL Server 2019: delayed durability có ở mọi edition.
sys.dm_io_virtual_file_stats và sys.dm_exec_session_wait_stats

Ghi nhật ký truy cập phải chờ log từng dòng: delayed durability

1. Vấn đề: mỗi dòng nhật ký là một lần chờ đĩa log

2. Mục đích: worker không chờ đĩa, rủi ro được đếm bằng số

3. Cơ sở lý thuyết: commit bền chờ log, delayed durability thì không

Commit bền là một lần chờ đĩa

Delayed durability: trả về trước khi ghi

Ai được phép trả về sớm

4. Cách giải quyết: commit trả về sớm cho đúng lệnh ghi nhật ký

5. Cách cài đặt: một lệnh ALTER DATABASE và một lô T-SQL

6. Chứng minh: nhanh gấp 3,1 lần, mất trung vị 53 dòng mỗi lần sập

Một luồng, 20.000 dòng

Tám luồng: không kết luận được

Rủi ro khi máy chủ bị giết

7. Kết luận

Những chỗ hay hiểu sai

Đọc tiếp

Nguồn

Đọc tiếp

Chọn kiểu cột: mỗi byte nhân lên 10 triệu dòng

Dọn cả năm đơn cũ mà không khóa bảng: SWITCH partition

File backup bị mang ra ngoài: mã hóa file trên đĩa bằng TDE

Blocking: tìm phiên đầu chuỗi

1. Vấn đề: mỗi dòng nhật ký là một lần chờ đĩa log #

2. Mục đích: worker không chờ đĩa, rủi ro được đếm bằng số #

3. Cơ sở lý thuyết: commit bền chờ log, delayed durability thì không #

Commit bền là một lần chờ đĩa #

Delayed durability: trả về trước khi ghi #

Ai được phép trả về sớm #

4. Cách giải quyết: commit trả về sớm cho đúng lệnh ghi nhật ký #

5. Cách cài đặt: một lệnh ALTER DATABASE và một lô T-SQL #

6. Chứng minh: nhanh gấp 3,1 lần, mất trung vị 53 dòng mỗi lần sập #

Một luồng, 20.000 dòng #

Tám luồng: không kết luận được #

Rủi ro khi máy chủ bị giết #

7. Kết luận #

Những chỗ hay hiểu sai #

Đọc tiếp #

Nguồn #

Đọc tiếp

Chọn kiểu cột: mỗi byte nhân lên 10 triệu dòng

Dọn cả năm đơn cũ mà không khóa bảng: SWITCH partition

File backup bị mang ra ngoài: mã hóa file trên đĩa bằng TDE

Blocking: tìm phiên đầu chuỗi

1. Vấn đề: mỗi dòng nhật ký là một lần chờ đĩa log

2. Mục đích: worker không chờ đĩa, rủi ro được đếm bằng số

3. Cơ sở lý thuyết: commit bền chờ log, delayed durability thì không

Commit bền là một lần chờ đĩa

Delayed durability: trả về trước khi ghi

Ai được phép trả về sớm

4. Cách giải quyết: commit trả về sớm cho đúng lệnh ghi nhật ký

5. Cách cài đặt: một lệnh ALTER DATABASE và một lô T-SQL

6. Chứng minh: nhanh gấp 3,1 lần, mất trung vị 53 dòng mỗi lần sập

Một luồng, 20.000 dòng

Tám luồng: không kết luận được

Rủi ro khi máy chủ bị giết

7. Kết luận

Những chỗ hay hiểu sai

Đọc tiếp

Nguồn