ProHoster > Blog > Administracija > MS SQL Server: BACKUP na steroidih

MS SQL Server: BACKUP na steroidih

Počakajte! Počakajte! Resnično, to ni spet članek o vrstah varnostnih kopij SQL Server. Tudi ne bom razlagal razlik med modeli obnove in kako se spopasti z napihnjenim "logom".

Morda (samo morda), po branju tega prispevka, boste uspeli narediti varnostno kopijo, ki jo vzamete z običajnimi orodji, jutri ponoči 1,5-krat hitreje. In to zgolj zato, ker boste uporabili nekoliko več parametrov BACKUP DATABASE.

Če vam je bil vsebina prispevka očitna — oprostite. Prebral sem vse, kar sem našel na googlu za frazo "habr sql server backup", in v nobenem članku nisem našel omembe, da bi lahko na čas varnostne kopije kako vplivali z uporabo parametrov.

Takoj bom opozoril na komentar Aleksandra Gladčenka (@mssqlhelp):

Nikoli ne spreminjajte parametrov BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE v produkciji. Ti so namenjeni zgolj pisanju takšnih člankov. V praksi se boste srečali s težavami s spominom.

Seveda bi bilo super, da bi bil najpametnejši in objavil ekskluzivno vsebino, vendar to, žal, ni tako. Obstajajo tako angleški kot ruski članki/posts (vedno se zmedem, kako jih pravilno imenovati), posvečeni tej temi. Tukaj je del tistih, ki sem jih našel: jedan, dva, trije (na sql.ru).

Torej, za začetek prilagam nekaj skrajšane sintakse BACKUP iz MSDN (mimogrede, zgoraj sem pisal o BACKUP DATABASE, vendar je vse to aplicirano tudi na varnostno kopiranje transakcijskega dnevnika in na diferencialno varnostno kopijo, vendar morda z manj očitnim učinkom):

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

 [ ,...n ]::=

--Možnost nastavitve medijev
 
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Možnosti prenosa podatkov
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

— pomeni, da je tam bilo nekaj, vendar sem to odstranil, ker trenutno ni v okviru teme.

Kako običajno izvajate varnostno kopijo? Kako "učijo" izvajati varnostno kopijo v milijardah člankov? Na splošno, če bo potrebno enkratno narediti varnostno kopijo neke ne prevelike baze, bom avtomatsko napisal nekaj takega:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--dobro, CHECKSUM sem omenil le, da bi se zdel bolj pameten

In general, this lists probably 75-90% of all parameters that are usually mentioned in articles about backups. There are also INIT, SKIP, and others. Have you checked MSDN? Did you see that there are options spanning one and a half screens? I have seen that too...

You probably already realized that the following discussion will focus on three parameters that remain in the first block of code — BLOCKSIZE, BUFFERCOUNT, and MAXTRANSFERSIZE. Here are their descriptions from MSDN:

BLOCKSIZE = { blocksize | @ blocksize_variable } indicates the size of the physical block in bytes. Supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 bytes (64 KB). The default value is 65536 for tape devices and 512 for other devices. Usually, this parameter does not need to be explicitly set, as the BACKUP command automatically selects a block size appropriate for the device. Explicitly setting the block size overrides the automatic selection.

BUFFERCOUNT = { buffercount | @ buffercount_variable } defines the total number of input/output buffers that will be used for the backup operation. You can specify any positive integer value; however, a large number of buffers may trigger an out-of-memory error due to excessive virtual address space in the Sqlservr.exe process.

The total space used by the buffers is determined by the following formula: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } specifies the largest volume of data packets in bytes for data transfer between SQL Server and the backup media. Supported values are multiples of 65536 bytes (64 KB), up to 4194304 bytes (4 MB).

I swear I read this earlier, but it never occurred to me what impact they might have on performance. Moreover, it seems that I need to make a sort of 'coming out' and admit that even now I do not fully understand what they do. I probably need to read more about buffered input/output and hard disk operations. Someday I'll do this, but for now, I can just write a script to check how these values affect the speed at which the backup is taken.

I created a small database of about 10 GB, placed it on an SSD, and put the backup folder on an HDD.

Ustvarjam začasno tabelo za shranjevanje rezultatov (pri meni ni začasna, da lahko natančneje pregledam rezultate, ampak vi se odločite sami):

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

Načelo delovanja skripta je preprosto — gnezdeni cikli, od katerih vsak spremeni vrednost enega parametra, te parametre posredujem v ukaz BACKUP, shranim zadnji zapis zgodovine iz msdb.dbo.backupset, izbrišem datoteko varnostne kopije in naslednja iteracija. Ker se podatki o izvedbi varnostne kopije pridobivajo iz backupset, natančnost nekoliko izgine (tam ni stotink sekund), a to bomo preživeli.

Najprej je treba omogočiti uporabo xp_cmdshell za brisanje varnostnih kopij (kasneje ne pozabite onemogočiti, če je ne potrebujete):

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

No in, lastno:

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

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

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

/* Vrednosti MAXTRANSFERSIZE */
DECLARE @ts     int = 524288,   --512KB, privzeto = 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

Če potrebujete dodatna pojasnila glede tega, kar tukaj poteka — pišite v komentarje ali v osebna sporočila. Zdaj bom govoril le o parametrih, ki jih posredujem v BACKUP DATABASE.

Za BLOCKSIZE imamo 'zaseban' popis vrednosti, a nijedna rezervna kopija nije izvršena sa BLOCKSIZE < 4KB. MAXTRANSFERSIZE može biti bilo koji broj koji je deljiv sa 64KB — od 64KB do 4MB. Podrazumevano je na mom sistemu 1024KB, uzmem 512 — 1024 — 2048 — 4096.

Teže je bilo sa BUFFERCOUNT — može biti bilo koji pozitivni broj, a ovde se piše kako se izračunava u BACKUP DATABASE i koliko su opasne velike vrednosti. Tamo se takođe navodi kako dobiti informaciju o tome sa kojim BUFFERCOUNT se zapravo pravi rezervna kopija — kod mene je to 7. Nema smisla smanjivati ga, a gornja granica je otkrivena eksperimentalno — pri BUFFERCOUNT = 896 i MAXTRANSFERSIZE = 4194304 rezervna kopija je pala sa greškom (o kojoj se piše u linku iznad):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE se abnomalno prekida.

Msg 701, Level 17, State 123, Line 7 U resursnom skupu 'default' nema dovoljno sistemske memorije da bi se izvršila ova upit.

Za poređenje, prvo ću pokazati rezultate izvršavanja rezervne kopije bez navođenja parametara uopšte:

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

Pa, rezervna kopija i rezervna kopija:

Obrađeno je 1070072 stranica za bazu podataka 'bt', datoteka 'bt' na datoteci 1.

Obrađene su 2 stranice za bazu podataka 'bt', datoteka 'bt_log' na datoteci 1.

BACKUP DATABASE je uspešno obradio 1070074 stranica za 53.171 sekundi (157.227 MB/s).

Sam skript koji testira parametre radio je nekoliko sati, sve merenja su u Google tabeli. A evo odabira rezultata koji ima tri najbolja vremena izvršenja (pokušao sam da napravim lepu grafiku, ali, u postu, moraće da se zadrži na tabeli, a u komentarima @mixsture dodano vrlo sjajne grafike).

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;

MS SQL Server: BACKUP na steroidih

Pažnja, odmah vrlo važno zapažanje od @mixsture iz komentarja:

može se sigurno reći da je veza između parametara i brzine rezervne kopije u ovim granicama vrednosti slučajna, ne postoji nikakva pravilnost. Ali odstupanje od ugrađenih parametara, očigledno, dobro je uticalo na rezultat

Tj. samo zahvaljujući upravljanju standardnim parametrima BACKUP dobijeno je poboljšanje u vremenu pravljenja rezervne kopije za 2 puta: 26 sekundi, u poređenju sa 53 na početku. A nije loše, zar ne? Ali treba videti šta će biti sa obnavljanjem. A šta ako se sada obnova vrši 4 puta sporije?

Za početak, izmerićemo koliko traje obnova rezervne kopije sa podešavanjima podrazumevano:

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

Pa, to vi i sami znate, putevi, replace-ne replace, recovery-ne recovery. I kod mene se izvršava ovako:

Obrađeno je 1070072 stranica za bazu podataka 'bt', datoteka 'bt' na datoteci 1.

Obrađene su 2 stranice za bazu podataka 'bt', datoteka 'bt_log' na datoteci 1.

RESTORE DATABASE успешно обработал 1070074 страниц за 40.752 секунд (205.141 MB/сек).

Теперь попытаюсь восстановить резервные копии, сделанные с изменёнными BLOCKSIZE, BUFFERCOUNT и MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE успешно обработал 1070074 страниц за 32.283 секунды (258.958 MB/сек).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE успешно обработал 1070074 страниц за 32.682 секунды (255.796 MB/сек).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE успешно обработал 1070074 страниц за 32.091 секунды (260.507 MB/сек).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE успешно обработал 1070074 страниц за 32.401 секунды (258.015 MB/сек).

Инструкция RESTORE DATABASE при восстановлении не меняется, в ней не указываются эти параметры, SQL Server сам их определяет по резервной копии. И видно, что даже при восстановлении может быть выигрыш — практически на 20% быстрее (Честно говоря, я не уделил много времени восстановлению, просто протестировал несколько самых «быстрых» резервных копий и убедился, что ухудшений нет.).

На всякий случай уточню — здесь описаны не какие-то оптимальные для всех параметры. Оптимальные параметры вы можете получить только тестированием. Я получил такие результаты, вы получите другие. Но вы видите, что ваши резервные копии можно немного «потюнить», и они действительно могут формироваться и восстанавливаться быстрее.

Ещё настоятельно рекомендую прочитать документацию целиком, потому что для вашей системы могут быть нюансы.

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

Если посмотреть на вторую строчку в документации, сразу под BACKUP DATABASE, там мы видим:

TO  [ ,...n ]

Как вы думаете, что будет, если указать несколько backup_device’ов? Синтаксис ведь позволяет. А произойдёт очень интересная вещь — резервная копия просто «размажется» по нескольким устройствам. То есть каждый «устройство» будет бесполезным отдельно, потеряли одно — потеряли всю резервную копию. Но как такое размазывание скажется на скорости резервного копирования?

Попробуем сделать резервную копию на два «устройства», которые находятся рядом в одной папке:

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

Батюшки-светы, да что же это делается-то?

Obrađeno je 1070072 stranica za bazu podataka 'bt', datoteka 'bt' na datoteci 1.

Обработано 2 страницы для базы данных 'bt', файл 'btлога' в файле 1.

РЕЗЕРВНОЕ КОПИРОВАНИЕ БАЗЫ ДАННЫХ успешно обработано 1070074 страниц за 40.092 секунды (208.519 МБ/сек).

Бэкап стал быстрее на 25% без видимых причин? А если добавить еще пару устройств?

РЕЗЕРВНОЕ КОПИРОВАНИЕ БАЗЫ ДАННЫХ [bt]
В 
    ДИСК = 'D:SQLServerbackupbt1.bak',
    ДИСК = 'D:SQLServerbackupbt2.bak',
    ДИСК = 'D:SQLServerbackupbt3.bak',
    ДИСК = 'D:SQLServerbackupbt4.bak'
С СЖАТОЙ ФОРМАТОМ;

РЕЗЕРВНОЕ КОПИРОВАНИЕ БАЗЫ ДАННЫХ успешно обработано 1070074 страниц за 34.234 секунды (244.200 МБ/сек).

Итак, выигрыш около 35% времени на создание бэкапа всего лишь за счет того, что он пишется сразу в 4 файла на одном диске. Проверял с большим количеством — на моем ноуте выигрыш отсутствует, оптимально — 4 устройства. Для вас — не знаю, нужно проверять. Ну, и, кстати, если у вас эти устройства — это действительно разные диски, поздравляю, выигрыш должен быть еще значительнее.

Теперь давайте поговорим о восстановлении этого счастья. Для этого потребуется изменить команду восстановления и перечислить все устройства:

ВОССТАНОВЛЕНИЕ БАЗЫ ДАННЫХ [bt]
ИЗ 
    ДИСК = 'D:SQLServerbackupbt1.bak',
    ДИСК = 'D:SQLServerbackupbt2.bak',
    ДИСК = 'D:SQLServerbackupbt3.bak',
    ДИСК = 'D:SQLServerbackupbt4.bak'
С ЗАМЕНОЙ, ВОССТАНОВЛЕНИЕ;

ВОССТАНОВЛЕНИЕ БАЗЫ ДАННЫХ успешно обработано 1070074 страниц за 38.027 секунд (219.842 МБ/сек).

Чуть быстрее, но в пределах нормы, не существенно. В общих чертах, резервное копирование происходит быстрее, а восстановление — так же, это успех? Как по мне — вполне неплохой результат. Это важно, поэтому повторюсь — если вы теряете хотя бы один из этих файлов — теряете весь бэкап..

Если посмотреть в журнале информацию о бэкапе, выводимую с помощью Trace Flag 3213 и 3605, можно заметить, что при бэкапе на несколько устройств увеличивается, как минимум, количество BUFFERCOUNT. Наверняка, можно попробовать подобрать более оптимальные параметры и для BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, но у меня сразу это не вышло, а тестировать снова на разном количестве файлов было лень. Да и диски жалко. Если вы хотите организовать такое тестирование у себя, скрипт переделать не сложно.

В конце поговорим о цене. Если резервное копирование происходит одновременно с работой пользователей — нужно очень ответственно подойти к тестированию, поскольку если бэкап становится быстрее — диски значительно нагружаются, нагрузка на процессор возрастает (ведь это еще надо сжимать на лету), следовательно, снижается общая отзывчивость системы.

Šale, šalama, ampak popolnoma razumem, da nisem razkril ničesar novega. To, kar je napisano zgoraj, je le prikaz, kako lahko izberete optimalne parametre za izvajanje varnostnih kopij.

Zapomnite si, da vse, kar počnete, počnete na lastno odgovornost. Preverite svoje varnostnekopije in ne pozabite na DBCC CHECKDB.

Izvor: habr.com

Kupite zanesljivo gostovanje za spletne strani z zaščito pred DDoS, VPS VDS strežniki 🔥 Kupite zanesljivo gostovanje za spletne strani z zaščito pred DDoS, VPS VDS strežniki | ProHoster