MS SQL Server: BACKUP на стероїдах

Зачекайте! Зачекайте! Правда, це не ще одна стаття про типи бекапів SQL Server. Я навіть не розповідатиму про відмінності моделей відновлення і як боротися з «логом», що розрісся.

Можливо (тільки можливо), після прочитання цієї посади, ви зможете зробити так, щоб бекап, який знімається у вас стандартними засобами, завтра вночі знявся, ну в 1.5 рази швидше. І лише за рахунок того, що ви використовуєте трохи більше параметрів BACKUP DATABASE.

Якщо для вас вміст посту був очевидним, вибачте. Я прочитав усе, до чого дістався гугл за фразою "habr sql server backup", і в жодній статті не знайшов згадки про те, що на час бекапу можна якимось чином вплинути за допомогою параметрів.

Відразу зверну вашу увагу на коментар Олександра Гладченка (@mssqlhelp):

Ніколи не змінюйте параметри BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE на продажі. Вони створені лише для написання подібних статей. На практиці пограбуєте проблеми з пам'яттю по поній.

Було б, звичайно, круто, виявитися найрозумнішим і викласти ексклюзивний контент, але це, на жаль, не так. Є і англомовні, і російськомовні статті/пости (завжди плутаюсь як їх правильно називати), присвячені цій темі. Ось частина з тих, що попалися мені: раз, два, три (на sql.ru).

Отже, для початку докладу дещо урізаний синтаксис BACKUP з MSDN (До речі, там вище я писав про BACKUP DATABASE, але все це застосовно і до бекапу журналу транзакцій, і до диференціального бекапу, але, можливо, з менш явним ефектом):

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 }
<...>

<…> — означає, що там щось було, але я це прибрав, бо зараз це не стосується теми.

Як ви зазвичай знімаєте бекап? Як «вчать» знімати бекап у мільярдах статей? Взагалі, якщо потрібно буде разово зняти бекап якоїсь не дуже великої бази, я автоматично напишу щось на кшталт такого:

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

І, загалом, тут перераховано, напевно 75-90% всіх параметрів, які зазвичай згадуються у статтях про бекапи. Ну, там INIT, SKIP ще. А ви ходили до MSDN? Ви бачили, що там опцій на півтора екрани? Я ось теж бачив.

Напевно, ви вже зрозуміли, що далі йтиметься про три параметри, які залишилися в першому блоці коду — BLOCKSIZE, BUFFERCOUNT та MAXTRANSFERSIZE. Ось їх описи з MSDN:

БЛОЧНИЙ РОЗМІР = { розмір блоку | @ blocksize_variable } - Вказує розмір фізичного блоку в байтах. Підтримуються розміри 512, 1024, 2048, 4096, 8192, 16, 384 і 32 байт (768 КБ). Значення за замовчуванням дорівнює 65 для стрічкових пристроїв і 536 для інших пристроїв. Зазвичай у цьому параметрі немає потреби, оскільки інструкція BACKUP автоматично вибирає розмір блоку, що відповідає пристрою. Явне встановлення розміру блоку перевизначає автоматичний вибір розміру блоку.

BUFFERCOUNT = { buffercount | @ buffercount_variable } — визначає загальну кількість буферів вводу-виводу, які будуть використовуватись для операції резервного копіювання. Можна вказати будь-яке ціле позитивне значення, проте велика кількість буферів може викликати помилку нестачі пам'яті через надмірний віртуальний адресний простір у процесі Sqlservr.exe.

Загальний обсяг простору, що використовується буферами, визначається за такою формулою: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } вказує найбільший обсяг пакету даних у байтах для обміну даними між SQL Server та носієм резервного набору. Підтримуються значення, кратні 65 байтам (536 КБ), аж до 64 байт (4 МБ).

Клянуся — я читав це раніше, але мені й на думку не спадало, який вплив на продуктивність вони можуть чинити. Понад те, мабуть, потрібно зробити своєрідний «камінг-аут» і визнати, що навіть зараз я не до кінця розумію, що саме вони роблять. Напевно, треба більше почитати про буферизоване введення-виведення і роботу з жорстким диском. Коли я це зроблю, а зараз можу просто написати скрипт, який перевірить як ці значення впливають на швидкість, з якої знімається бекап.

Зробив невелику базу розміром близько 10 ГБ, поклав її на SSD, а каталог для бекапів поклав на HDD.

Створюю тимчасову таблицю для зберігання результатів (у мене вона не тимчасова, щоб можна було поколупати результати докладніше, але ви вже самі вирішуйте):

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

Принцип роботи скрипта простий — вкладені цикли, кожен із яких змінює значення одного параметра, підсовую до команди BACKUP ці параметри, зберігаю останній запис із історією з msdb.dbo.backupset, видаляю файл бэкапа і така ітерація. Оскільки дані про виконання бекапу беруться з backupset, точність дещо втрачається (там немає секунд), але це ми переживемо.

Спочатку потрібно дозволити використання xp_cmdshell, щоб видаляти бекапи (потім не забудьте вимкнути, якщо він вам не потрібен):

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

Ну і, власне:

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

Якщо раптом вам потрібні пояснення щодо того, що тут відбувається — пишіть у коментарі чи личку. Поки що розповім тільки про параметри, які підсовую в BACKUP DATABASE.

Для BLOCKSIZE у нас «закритий» список значень, причому у мене не виконувався бекап з BLOCKSIZE <4КБ. MAXTRANSFERSIZE будь-яке число кратне 64КБ - від 64КБ до 4МБ. За замовчуванням на моїй системі 1024КБ, я взяв 512 - 1024 - 2048 - 4096.

Складніше було з BUFFERCOUNT — він може бути будь-яким позитивним числом, а за посиланням написано як він розраховується в BACKUP DATABASE і чим небезпечні великі значення. Там же написано як отримати інформацію про те, з яким BUFFERCOUNT реально знімається бекап - у мене це 7. Зменшувати його сенсу не було, а верхня межа була виявлена ​​досвідченим шляхом - при BUFFERCOUNT = 896 і MAXTRANSFERSIZE = 4194304 бекап впав з помилкою ( написано за посиланням вище):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE is terminating abnormally.

Msg 701, Level 17, State 123, Line 7 Там є insufficient system memory in resource pool 'default' to run this query.

Для порівняння спочатку покажу результати виконання бекапу без вказівки параметрів взагалі:

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

Ну, бекап і бекап:

Processed 1070072 pages for database 'bt', file 'bt' on file 1.

Processed 2 pages for database 'bt', file 'bt_log' on file 1.

BACKUP DATABASE успішно procesed 1070074 pages in 53.171 seconds (157.227 MB/sec).

Сам скрипт, що тестує параметри, відпрацював за пару годин, всі виміри в гугл таблиці. А ось вибірка результатів, що мають три найкращі часи виконання (я намагався зробити гарний графік, але, в пості, доведеться обійтися табличкою, а в коментарях @mixsture додав дуже круті графіки).

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;

Увага, відразу дуже важливе зауваження від @mixsture з коментаря:

впевнено можна сказати, що зв'язок між параметрами та швидкістю бекапа в цих межах значень випадковий, ніякої закономірності немає. Але відхід від вбудованих параметрів, очевидно, добре вплинув на результат

Тобто. тільки за рахунок управління стандартними параметрами BACKUP було отримано виграш у часі зняття бекапу в 2 рази: 26 секунд проти 53 на початку. А непогано, так? Але треба подивитися, що там із відновленням. А раптом тепер відновлюватиметься вчетверо довше?

Для початку виміряємо, скільки відбувається відновлення бекапу з налаштуваннями за замовчуванням:

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

Ну це ви і самі знаєте, шляхи там, replace-не replace, recovery-не recovery. І в мене виконується так:

Processed 1070072 pages for database 'bt', file 'bt' on file 1.

Processed 2 pages for database 'bt', file 'bt_log' on file 1.

RESTORE DATABASE успішно procesed 1070074 pages in 40.752 seconds (205.141 MB/sec).

А тепер спробую відновити бекапи, зняті зі зміненими BLOCKSIZE, BUFFERCOUNT та MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE успішно procesed 1070074 pages in 32.283 seconds (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE успішно procesed 1070074 pages in 32.682 seconds (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE успішно procesed 1070074 pages in 32.091 seconds (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE успішно procesed 1070074 pages in 32.401 seconds (258.015 MB/sec).

Інструкція RESTORE DATABASE при відновленні не змінюється, в ній не вказуються ці параметри, SQL Server сам визначає їх по бекапу. І видно, що навіть за відновлення може бути виграш — практично на 20% швидше (Чесно кажучи, не багато часу приділив відновленню, протикав кілька найшвидших бекапів і переконався, що погіршення немає).

Про всяк випадок уточню - тут описані не якісь оптимальні для всіх параметри. Оптимальні параметри для себе ви можете отримати лише тестування. Я одержав такі результати, ви отримаєте інші. Але ви бачите, що свої бекапи можна «потюнити» і вони реально можуть формуватися та розгортатися швидше.

Ще наполегливо рекомендую, прочитати документацію цілком, тому що саме для вашої системи можуть бути нюанси.

Раз вже почав писати про бекапи, хочу відразу написати ще про одну «оптимізацію», яка зустрічається частіше, ніж «тюнінг» параметрів (її ж, наскільки я розумію, використовує як мінімум частину утиліт для резервного копіювання, можливо, разом з параметрами, описаними раніше), але на Хабре вона теж поки що не була описана.

Якщо подивитися на другий рядок у документації, одразу під BACKUP DATABASE, там ми бачимо:

TO <backup_device> [ ,...n ]

Як ви вважаєте, що буде, якщо вказати кілька backup_device'ів? Адже синтаксис дозволяє. А буде дуже цікава річ — бекап просто «розмажеться» кількома девайсами. Тобто. кожен «девайс» окремо буде марний, втратили один, втратили весь бекап. Але як таке розмазування позначиться на швидкості резервного копіювання?

Спробуємо зробити бекап на два «девайси», які лежать поряд в одній папці:

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

Батюшки-світла, та що ж це робиться?

Processed 1070072 pages for database 'bt', file 'bt' on file 1.

Processed 2 pages for database 'bt', file 'btlog' on file 1.

BACKUP DATABASE успішно procesed 1070074 pages in 40.092 seconds (208.519 MB/sec).

Бекап зробився на 25% швидше просто на рівному місці? А якщо ще кілька пристроїв додати?

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

BACKUP DATABASE успішно procesed 1070074 pages in 34.234 seconds (244.200 MB/sec).

Разом, виграш близько 35% часу зняття бекапу лише за рахунок того, що бекап пишеться відразу в 4 файли на одному диску. Перевіряв більшу кількість – на моєму ноуті виграш відсутній, оптимально – 4 пристрої. Для вас не знаю, потрібно перевіряти. Ну і, до речі, якщо у вас ці пристрої – це реально різні диски, вітаю, виграш має бути ще значнішим.

Тепер поговоримо, як це щастя відновити. Для цього доведеться міняти команду відновлення та перераховувати всі девайси:

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 успішно procesed 1070074 pages in 38.027 seconds (219.842 MB/sec).

Трохи швидше, але десь поряд, не суттєво. Загалом, бекап знімається швидше, а відновлюється так само успіх? Як на мене, цілком собі успіх. Це важливотому повторюся — якщо ви втрачаєте хоча б один із цих файлів — втрачаєте весь бекап.

Якщо подивитися в журналі інформацію про бекап, що виводиться за допомогою Trace Flag 3213 і 3605, то можна звернути увагу, що при бекапі в кілька пристроїв збільшується, як мінімум, кількість BUFFERCOUNT. Напевно, можна спробувати підібрати найоптимальніші параметри і для BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, але в мене відразу не вийшло, а проводити таке тестування ще раз, але для різної кількості файлів я полінувався. Та й диски шкода. Якщо ви бажаєте організувати таке тестування у себе, скрипт переробити не складно.

Насамкінець поговоримо про ціну. Якщо бекап знімається паралельно з роботою користувачів - потрібно дуже відповідально підійти до тестування, оскільки якщо бекап знімається швидше - диски напружуються сильніше, навантаження на процесор зростає (треба це все ще й стискати нальоту), відповідно, знижується загальна чуйність системи.

Жартами-жартами, але чудово розумію, що жодних одкровень не зробив. Те, що написано вище, — це просто демонстрація того, як можна підібрати оптимальні параметри для зняття бекапів.

Пам'ятайте, що все, що ви робите, ви робите на свій страх і ризик. Перевіряйте свої резервні копії та не забувайте про DBCC CHECKDB.

Джерело: habr.com