Máy chủ MS SQL: SAO LƯU trên steroid

Chờ đợi! Chờ đợi! Đúng, đây không phải là một bài viết khác về các loại bản sao lưu SQL Server. Tôi thậm chí sẽ không nói về sự khác biệt giữa các mô hình khôi phục và cách xử lý nhật ký phát triển quá mức.

Có lẽ (chỉ có lẽ thôi), sau khi đọc bài đăng này, bạn sẽ có thể đảm bảo rằng bản sao lưu bị xóa khỏi bạn bằng các phương tiện tiêu chuẩn sẽ bị xóa vào đêm mai, nhanh hơn 1.5 lần. Và chỉ do bạn sử dụng thêm một chút tham số BACKUP DATABASE.

Nếu nội dung của bài đăng rõ ràng với bạn thì tôi xin lỗi. Tôi đã đọc mọi thứ mà Google tìm thấy về cụm từ “sao lưu máy chủ habr sql” và không một bài viết nào tôi tìm thấy bất kỳ đề cập nào đến thực tế là thời gian sao lưu có thể bị ảnh hưởng bằng cách nào đó bằng cách sử dụng các tham số.

Tôi sẽ ngay lập tức thu hút sự chú ý của bạn đến nhận xét của Alexander Gladchenko (@mssqlhelp):

Không bao giờ thay đổi các thông số BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE trong quá trình sản xuất. Chúng được tạo ra chỉ để viết những bài báo như vậy. Trong thực tế, bạn sẽ nhanh chóng thoát khỏi các vấn đề về trí nhớ.

Tất nhiên, sẽ thật tuyệt nếu trở thành người thông minh nhất và đăng nội dung độc quyền, nhưng thật không may, điều này không xảy ra. Có cả các bài báo/bài viết bằng tiếng Anh và tiếng Nga (tôi luôn bối rối không biết nên gọi chúng là gì cho chính xác) dành cho chủ đề này. Dưới đây là một số trong những cái tôi đã gặp: thời gian, два, ba (trên sql.ru).

Vì vậy, để bắt đầu, tôi sẽ đính kèm cú pháp BACKUP rút gọn một chút từ MSDN (nhân tiện, tôi đã viết ở trên về BACKUP DATABASE, nhưng tất cả điều này áp dụng cho cả sao lưu nhật ký giao dịch và sao lưu vi sai, nhưng có lẽ với tác dụng ít rõ ràng hơn):

BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  <...>
  [ WITH { <...>
           | <general_WITH_options> [ ,...n ] } ]
[;]

<general_WITH_options> [ ,...n ]::=
<...>
--Media Set Options
 <...>
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
<...>

<…> - có nghĩa là có gì đó ở đó nhưng tôi đã xóa nó vì bây giờ nó không liên quan đến chủ đề.

Bạn thường sao lưu bằng cách nào? Họ “dạy” sao lưu trong hàng tỷ bài viết như thế nào? Nói chung, nếu tôi cần tạo bản sao lưu một lần cho một số cơ sở dữ liệu không lớn lắm, tôi sẽ tự động viết một cái gì đó như thế này:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее

Và nói chung, có lẽ 75-90% tất cả các thông số thường được đề cập trong các bài viết về bản sao lưu đều được liệt kê ở đây. À, còn có INIT, SKIP. Bạn đã truy cập MSDN chưa? Bạn có thấy rằng có các tùy chọn cho một màn hình rưỡi không? Tôi cũng thấy...

Có thể bạn đã nhận ra rằng chúng ta sẽ nói thêm về ba tham số còn lại trong khối mã đầu tiên - BLOCKSIZE, BUFFERCOUNT và MAXTRANSFERSIZE. Dưới đây là mô tả của họ từ MSDN:

KÍCH THƯỚC KHỐI = { kích thước khối | @ blocksize_variable } - cho biết kích thước khối vật lý tính bằng byte. Các kích thước được hỗ trợ là 512, 1024, 2048, 4096, 8192, 16, 384 và 32 byte (768 KB). Giá trị mặc định là 65 cho các thiết bị băng và 536 cho các thiết bị khác. Thông thường, tham số này không cần thiết vì câu lệnh BACKUP tự động chọn kích thước khối thích hợp cho thiết bị. Việc đặt kích thước khối sẽ ghi đè rõ ràng việc lựa chọn kích thước khối tự động.

ĐẾM ĐỆM = { số lượng bộ đệm | @ bộ đệm_biến } - Xác định tổng số bộ đệm I/O sẽ được sử dụng cho hoạt động sao lưu. Bạn có thể chỉ định bất kỳ giá trị nguyên dương nào, nhưng số lượng lớn bộ đệm có thể gây ra lỗi hết bộ nhớ do không gian địa chỉ ảo quá mức trong quy trình Sqlservr.exe.

Tổng dung lượng được sử dụng bởi bộ đệm được xác định theo công thức sau: BUFFERCOUNT * MAXTRANSFERSIZE.

KÍCH THƯỚC CHUYỂN TỐI ĐA = { maxtransfersize | @ maxtransfersize_variable } chỉ định kích thước gói dữ liệu lớn nhất, tính bằng byte, để trao đổi giữa SQL Server và bộ phương tiện sao lưu. Hỗ trợ bội số của 65 byte (536 KB) lên tới 64 byte (4 MB).

Tôi thề - tôi đã đọc điều này trước đây, nhưng tôi chưa bao giờ nghĩ rằng chúng có thể ảnh hưởng đến năng suất đến mức nào. Hơn nữa, rõ ràng, tôi cần phải thực hiện một kiểu “ra ngoài” và thừa nhận rằng ngay cả bây giờ tôi vẫn chưa hiểu hết chính xác họ đang làm gì. Có lẽ tôi cần đọc thêm về I/O được đệm và làm việc với ổ cứng. Một ngày nào đó tôi sẽ làm điều này, nhưng hiện tại tôi chỉ có thể viết một tập lệnh để kiểm tra xem các giá trị này ảnh hưởng như thế nào đến tốc độ thực hiện sao lưu.

Tôi đã tạo một cơ sở dữ liệu nhỏ, dung lượng khoảng 10 GB, đặt nó vào ổ SSD và đặt thư mục sao lưu vào ổ cứng HDD.

Mình tạo bảng tạm thời để lưu kết quả (mình không có tạm thời nên có thể tìm hiểu kết quả chi tiết hơn nhưng bạn tự quyết định):

DROP TABLE IF EXISTS ##bt_results; 

CREATE TABLE ##bt_results (
    id              int IDENTITY (1, 1) PRIMARY KEY,
    start_date      datetime NOT NULL,
    finish_date     datetime NOT NULL,
    backup_size     bigint NOT NULL,
    compressed_size bigint,
    block_size      int,
    buffer_count    int,
    transfer_size   int
);

Nguyên tắc của tập lệnh rất đơn giản - các vòng lặp lồng nhau, mỗi vòng lặp thay đổi giá trị của một tham số, chèn các tham số này vào lệnh BACKUP, lưu bản ghi cuối cùng có lịch sử từ msdb.dbo.backupset, xóa tệp sao lưu và lần lặp tiếp theo . Vì dữ liệu thực thi sao lưu được lấy từ bộ sao lưu nên độ chính xác có phần bị mất (không có phân số giây), nhưng chúng tôi sẽ tồn tại được điều này.

Trước tiên, bạn cần kích hoạt xp_cmdshell để xóa các bản sao lưu (sau đó đừng quên tắt nó nếu không cần):

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;  
GO

Vâng, thực sự:

DECLARE @tmplt AS nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:SQLServerbackupbt.bak''
WITH 
    COMPRESSION,
    BLOCKSIZE = {bs},
    BUFFERCOUNT = {bc},
    MAXTRANSFERSIZE = {ts}';

DECLARE @sql AS nvarchar(max);

/* BLOCKSIZE values */
DECLARE @bs     int = 4096, 
        @max_bs int = 65536;

/* BUFFERCOUNT values */
DECLARE @bc     int = 7,
        @min_bc int = 7,
        @max_bc int = 800;

/* MAXTRANSFERSIZE values */
DECLARE @ts     int = 524288,   --512KB, default = 1024KB
        @min_ts int = 524288,
        @max_ts int = 4194304;  --4MB

SELECT TOP 1 
    @bs = COALESCE (block_size, 4096), 
    @bc = COALESCE (buffer_count, 7), 
    @ts = COALESCE (transfer_size, 524288)
FROM ##bt_results
ORDER BY id DESC;

WHILE (@bs <= @max_bs)
BEGIN
    WHILE (@bc <= @max_bc)
    BEGIN       
        WHILE (@ts <= @max_ts)
        BEGIN
            SET @sql = REPLACE (REPLACE (REPLACE(@tmplt, N'{bs}', CAST(@bs AS nvarchar(50))), N'{bc}', CAST (@bc AS nvarchar(50))), N'{ts}', CAST (@ts AS nvarchar(50)));

            EXEC (@sql);

            INSERT INTO ##bt_results (start_date, finish_date, backup_size, compressed_size, block_size, buffer_count, transfer_size)
            SELECT TOP 1 backup_start_date, backup_finish_date, backup_size, compressed_backup_size,  @bs, @bc, @ts 
            FROM msdb.dbo.backupset
            ORDER BY backup_set_id DESC;

            EXEC xp_cmdshell 'del "D:SQLServerbackupbt.bak"', no_output;

            SET @ts += @ts;
        END
        
        SET @bc += @bc;
        SET @ts = @min_ts;

        WAITFOR DELAY '00:00:05';
    END

    SET @bs += @bs;
    SET @bc = @min_bc;
    SET @ts = @min_ts;
END

Nếu bạn đột nhiên cần làm rõ về những gì đang xảy ra ở đây, hãy viết bình luận hoặc PM. Hiện tại, tôi sẽ chỉ cho bạn biết về các tham số mà tôi đưa vào BACKUP DATABASE.

Đối với BLOCKSIZE, chúng tôi có danh sách giá trị “đóng” và tôi không thực hiện sao lưu với BLOCKSIZE < 4KB. MAXTRANSFERSIZE bất kỳ số nào là bội số của 64KB - từ 64KB đến 4MB. Mặc định trên hệ thống của tôi là 1024KB, tôi lấy 512 - 1024 - 2048 - 4096.

Khó khăn hơn với BUFFERCOUNT - nó có thể là bất kỳ số dương nào, nhưng liên kết cho biết nó được tính toán như thế nào trong BACKUP DATABASE và tại sao các giá trị lớn lại nguy hiểm?. Nó cũng cho biết cách lấy thông tin về BUFFERCOUNT bản sao lưu thực sự được tạo bằng - đối với tôi là 7. Không có ích gì khi giảm nó và giới hạn trên đã được phát hiện bằng thực nghiệm - với BUFFERCOUNT = 896 và MAXTRANSFERSIZE = 4194304 bản sao lưu rơi vào một lỗi (về lỗi được viết trong liên kết ở trên):

Msg 3013, Cấp 16, Trạng thái 1, Dòng 7 CƠ SỞ DỮ LIỆU SAO LƯU đang chấm dứt một cách bất thường.

Msg 701, Cấp 17, Trạng thái 123, Dòng 7 Không đủ bộ nhớ hệ thống trong nhóm tài nguyên 'mặc định' để chạy truy vấn này.

Để so sánh, trước tiên tôi sẽ hiển thị kết quả của việc chạy bản sao lưu mà không chỉ định bất kỳ tham số nào:

BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;

Vâng, sao lưu và sao lưu:

Đã xử lý 1070072 trang cho cơ sở dữ liệu 'bt', tệp 'bt' trên tệp 1.

Đã xử lý 2 trang cho cơ sở dữ liệu 'bt', tệp 'bt_log' trên tệp 1.

CƠ SỞ DỮ LIỆU BACKUP đã xử lý thành công 1070074 trang trong 53.171 giây (157.227 MB/giây).

Bản thân tập lệnh, kiểm tra các thông số, hoạt động trong vài giờ, tất cả các phép đo đều được thực hiện bảng tính google. Và đây là tuyển tập các kết quả có ba thời gian thực hiện tốt nhất (Tôi đã cố gắng tạo một biểu đồ đẹp mắt, nhưng trong bài đăng tôi sẽ phải thực hiện bằng bảng và trong phần nhận xét @mixsture đã thêm đồ họa rất tuyệt).

SELECT TOP 7 WITH TIES 
    compressed_size, 
    block_size, 
    buffer_count, 
    transfer_size,
    DATEDIFF(SECOND, start_date, finish_date) AS backup_time_sec
FROM ##bt_results
ORDER BY backup_time_sec ASC;

Chú ý, một lưu ý rất quan trọng từ @mixsture của bình luận:

Chúng tôi có thể tự tin nói rằng mối quan hệ giữa các thông số và tốc độ sao lưu trong phạm vi giá trị này là ngẫu nhiên, không có khuôn mẫu. Nhưng việc loại bỏ các tham số tích hợp rõ ràng đã có tác động tốt đến kết quả

Những thứ kia. Chỉ bằng cách quản lý các tham số BACKUP tiêu chuẩn, thời gian xóa bản sao lưu đã tăng gấp 2 lần: 26 giây, so với 53 giây lúc đầu. Nó không tệ, phải không? Nhưng chúng ta cần xem điều gì xảy ra với quá trình phục hồi. Điều gì sẽ xảy ra nếu bây giờ phải mất thời gian lâu hơn gấp 4 lần để phục hồi?

Trước tiên, hãy đo xem mất bao lâu để khôi phục bản sao lưu với cài đặt mặc định:

RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;

À, chính bạn cũng biết rằng, có cách, thay thế không thay thế, phục hồi không phục hồi. Và tôi làm điều đó như thế này:

Đã xử lý 1070072 trang cho cơ sở dữ liệu 'bt', tệp 'bt' trên tệp 1.

Đã xử lý 2 trang cho cơ sở dữ liệu 'bt', tệp 'bt_log' trên tệp 1.

RESTORE DATABASE đã xử lý thành công 1070074 trang trong 40.752 giây (205.141 MB/giây).

Bây giờ tôi sẽ cố gắng khôi phục các bản sao lưu được thực hiện với BLOCKSIZE, BUFFERCOUNT và MAXTRANSFERSIZE đã thay đổi.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE đã xử lý thành công 1070074 trang trong 32.283 giây (258.958 MB/giây).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE đã xử lý thành công 1070074 trang trong 32.682 giây (255.796 MB/giây).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE đã xử lý thành công 1070074 trang trong 32.091 giây (260.507 MB/giây).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE đã xử lý thành công 1070074 trang trong 32.401 giây (258.015 MB/giây).

Câu lệnh RESTORE DATABASE không thay đổi trong quá trình khôi phục; các tham số này không được chỉ định trong đó; SQL Server tự xác định chúng từ bản sao lưu. Và rõ ràng là ngay cả khi phục hồi vẫn có thể đạt được mức tăng - nhanh hơn gần 20% (Thành thật mà nói, tôi không dành nhiều thời gian cho việc khôi phục, tôi đã chạy qua một số bản sao lưu “nhanh nhất” và đảm bảo rằng không có sự hư hỏng nào).

Để đề phòng, hãy để tôi làm rõ rằng đây không phải là một số thông số tối ưu cho tất cả mọi người. Bạn chỉ có thể có được thông số tối ưu cho mình bằng cách thử nghiệm. Tôi đã nhận được những kết quả này, bạn sẽ nhận được những kết quả khác. Nhưng bạn thấy rằng bạn có thể “điều chỉnh” các bản sao lưu của mình và chúng thực sự có thể hình thành và triển khai nhanh hơn.

Tôi cũng thực sự khuyên bạn nên đọc toàn bộ tài liệu vì có thể có những sắc thái cụ thể đối với hệ thống của bạn.

Kể từ khi tôi bắt đầu viết về các bản sao lưu, tôi muốn viết ngay về một "tối ưu hóa" nữa, phổ biến hơn các tham số "điều chỉnh" (theo như tôi hiểu, nó được sử dụng bởi ít nhất một số tiện ích sao lưu, có lẽ cùng với các tham số). được mô tả trước đó), nhưng nó cũng chưa được mô tả trên Habré.

Nếu chúng ta nhìn vào dòng thứ hai trong tài liệu, ngay bên dưới CƠ SỞ DỮ LIỆU SAO LƯU, chúng ta sẽ thấy:

TO <backup_device> [ ,...n ]

Bạn nghĩ điều gì sẽ xảy ra nếu bạn chỉ định một số backup_devices? Cú pháp cho phép nó. Và một điều rất thú vị sẽ xảy ra - bản sao lưu sẽ đơn giản được "lan rộng" trên một số thiết bị. Những thứ kia. từng “thiết bị” riêng lẻ sẽ vô dụng, mất một cái, mất toàn bộ bản sao lưu. Nhưng việc bôi nhọ như vậy sẽ ảnh hưởng đến tốc độ sao lưu như thế nào?

Hãy thử tạo bản sao lưu trên hai “thiết bị” nằm cạnh nhau trong cùng một thư mục:

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak'   
WITH COMPRESSION;

Hỡi những người cha của thế giới, tại sao điều này lại được thực hiện?

Đã xử lý 1070072 trang cho cơ sở dữ liệu 'bt', tệp 'bt' trên tệp 1.

Đã xử lý 2 trang cho cơ sở dữ liệu 'bt', tệp 'bt'log' trên tệp 1.

CƠ SỞ DỮ LIỆU BACKUP đã xử lý thành công 1070074 trang trong 40.092 giây (208.519 MB/giây).

Quá trình sao lưu có trở nên nhanh hơn 25% một cách bất ngờ không? Điều gì sẽ xảy ra nếu chúng ta thêm một vài thiết bị nữa?

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;

CƠ SỞ DỮ LIỆU BACKUP đã xử lý thành công 1070074 trang trong 34.234 giây (244.200 MB/giây).

Tổng cộng, mức tăng chỉ bằng khoảng 35% thời gian thực hiện sao lưu do bản sao lưu được ghi vào 4 tệp trên một đĩa cùng một lúc. Tôi đã kiểm tra một số lượng lớn hơn - không có mức tăng nào trên máy tính xách tay của tôi, tối ưu - 4 thiết bị. Đối với bạn - Tôi không biết, bạn cần kiểm tra. Nhân tiện, nếu bạn có những thiết bị này - đây thực sự là những đĩa khác nhau, xin chúc mừng, mức tăng sẽ còn đáng kể hơn nữa.

Bây giờ hãy nói về cách khôi phục lại hạnh phúc này. Để thực hiện việc này, bạn sẽ phải thay đổi lệnh khôi phục và liệt kê tất cả các thiết bị:

RESTORE DATABASE [bt]
FROM 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;

RESTORE DATABASE đã xử lý thành công 1070074 trang trong 38.027 giây (219.842 MB/giây).

Nhanh hơn một chút, nhưng ở đâu đó gần, không đáng kể. Nói chung, bản sao lưu được xóa nhanh hơn và được khôi phục theo cách tương tự - thành công? Đối với tôi, đó là một thành công khá lớn. Cái này là rất quan trọng, vì vậy tôi nhắc lại - nếu bạn nếu bạn mất ít nhất một trong các tệp này, bạn sẽ mất toàn bộ bản sao lưu.

Nếu nhìn vào nhật ký thông tin sao lưu được hiển thị bằng Trace Flags 3213 và 3605, bạn sẽ nhận thấy rằng khi sao lưu vào một số thiết bị, ít nhất số lượng BUFFERCOUNT sẽ tăng lên. Có thể, bạn có thể thử chọn các tham số tối ưu hơn cho BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, nhưng tôi đã không thành công ngay lập tức và tôi quá lười để thực hiện lại thử nghiệm đó, nhưng đối với một số lượng tệp khác. Và thật xấu hổ về bánh xe. Nếu muốn tổ chức thử nghiệm như vậy tại nhà thì việc làm lại kịch bản không khó.

Cuối cùng, hãy nói về giá cả. Nếu bản sao lưu bị xóa song song với công việc của người dùng, bạn cần thực hiện một cách tiếp cận kiểm tra rất có trách nhiệm, vì nếu bản sao lưu bị xóa nhanh hơn, các đĩa sẽ bị căng nhiều hơn, tải cho bộ xử lý sẽ tăng lên (bạn vẫn phải nén nó đang diễn ra), và theo đó, khả năng đáp ứng tổng thể của hệ thống sẽ giảm đi.

Đùa thôi, nhưng tôi hiểu rất rõ rằng tôi không tiết lộ bất kỳ điều gì. Những gì được viết ở trên chỉ đơn giản là minh họa cách bạn có thể chọn các tham số tối ưu để thực hiện sao lưu.

Hãy nhớ rằng mọi việc bạn làm đều được thực hiện với sự nguy hiểm và rủi ro của riêng bạn. Kiểm tra bản sao lưu của bạn và đừng quên DBCC CHECKDB.

Nguồn: www.habr.com