MS SQL Server: ZÁLOHA na steroidech

Počkejte! Počkejte! Pravda, toto není další článek o typech záloh SQL Serveru. Nebudu ani mluvit o rozdílech mezi modely obnovy a o tom, jak se vypořádat s přerostlým kmenem.

Možná (jen možná), po přečtení tohoto příspěvku, se budete moci ujistit, že záloha, která je z vás odstraněna pomocí standardních prostředků, bude odstraněna zítra večer, tedy 1.5krát rychleji. A to jen díky tomu, že používáte trochu více parametrů ZÁLOHA DATABÁZE.

Pokud vám byl obsah příspěvku jasný, omlouvám se. Četl jsem vše, k čemu se Google dostal na frázi „habr sql server backup“ a ani v jednom článku jsem nenašel zmínku o tom, že dobu zálohování lze nějak ovlivnit pomocí parametrů.

Okamžitě vás upozorním na komentář Alexandra Gladčenka (@mssqlhelp):

Nikdy neměňte parametry BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE ve výrobě. Jsou vyrobeny pouze pro psaní takových článků. V praxi se tak během chvilky zbavíte problémů s pamětí.

Bylo by samozřejmě skvělé být nejchytřejší a zveřejňovat exkluzivní obsah, ale bohužel tomu tak není. Tomuto tématu se věnují články/příspěvky v anglickém i ruském jazyce (vždy jsem zmatený, jak je správně nazvat). Zde jsou některé z těch, na které jsem narazil: čas, два, tři (na sql.ru).

Pro začátek tedy připojím mírně zkrácenou syntaxi BACKUP from MSDN (mimochodem výše jsem psal o BACKUP DATABASE, ale to vše platí jak pro zálohu transakčního protokolu, tak pro rozdílovou zálohu, ale možná s méně patrným efektem):

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

<…> - to znamená, že tam něco bylo, ale odstranil jsem to, protože to nyní není relevantní k tématu.

Jak obvykle berete zálohu? Jak „učí“, jak zálohovat miliardy článků? Obecně platí, že pokud potřebuji udělat jednorázovou zálohu nějaké nepříliš velké databáze, automaticky napíšu něco takového:

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

A obecně je zde uvedeno pravděpodobně 75-90 % všech parametrů, které jsou obvykle zmíněny v článcích o zálohování. No a je tu také INIT, SKIP. Už jste navštívili MSDN? Viděli jste, že existují možnosti pro jednu a půl obrazovky? Taky jsem viděl...

Pravděpodobně jste si již uvědomili, že dále budeme hovořit o třech parametrech, které zůstaly v prvním bloku kódu - BLOCKSIZE, BUFFERCOUNT a MAXTRANSFERSIZE. Zde jsou jejich popisy z MSDN:

BLOKOVAT VELIKOST = { velikost bloku | @ blockize_variable } - označuje velikost fyzického bloku v bajtech. Podporované velikosti jsou 512, 1024, 2048, 4096, 8192, 16 384, 32 768 a 65 536 bajtů (64 kB). Výchozí hodnota je 65 pro pásková zařízení a 536 pro ostatní zařízení. Tento parametr obvykle není nutný, protože příkaz BACKUP automaticky vybere vhodnou velikost bloku pro zařízení. Nastavení velikosti bloku explicitně přepíše automatický výběr velikosti bloku.

BUFFERCOUNT = { buffercount | @ proměnná_počet_bufferu } - Definuje celkový počet I/O bufferů, které budou použity pro operaci zálohování. Můžete zadat libovolnou kladnou celočíselnou hodnotu, ale velký počet vyrovnávacích pamětí může způsobit chybu nedostatku paměti kvůli nadměrnému virtuálnímu adresnímu prostoru v procesu Sqlservr.exe.

Celkové množství prostoru použitého vyrovnávací paměti je určeno následujícím vzorcem: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } určuje největší velikost datového paketu v bajtech pro výměnu mezi SQL Serverem a médiem zálohovací sady. Podporovány jsou násobky 65 536 bajtů (64 KB) až 4 194 304 bajtů (4 MB).

Přísahám – už jsem to četl, ale nikdy mě nenapadlo, jak velký dopad mohou mít na produktivitu. Navíc zřejmě potřebuji udělat jakýsi „coming out“ a přiznat, že ani teď úplně nechápu, co přesně dělají. Asi si budu muset přečíst více o I/O s vyrovnávací pamětí a práci s pevným diskem. Jednoho dne to udělám, ale zatím mohu napsat skript, který zkontroluje, jak tyto hodnoty ovlivní rychlost zálohování.

Udělal jsem malou databázi o velikosti asi 10 GB, dal ji na SSD a adresář pro zálohy dal na HDD.

Vytvořím dočasnou tabulku pro uložení výsledků (nemám ji dočasnou, takže se mohu ponořit do výsledků podrobněji, ale rozhodnete se 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
);

Princip skriptu je jednoduchý - vnořené smyčky, z nichž každá změní hodnotu jednoho parametru, vloží tyto parametry do příkazu BACKUP, uloží poslední záznam s historií z msdb.dbo.backupset, smaže záložní soubor a další iteraci . Vzhledem k tomu, že data o provedení zálohy jsou převzata ze sady záloh, přesnost se poněkud ztrácí (nejsou zde žádné zlomky sekund), ale přežijeme to.

Nejprve musíte povolit xp_cmdshell pro mazání záloh (pak jej nezapomeňte zakázat, pokud jej nepotřebujete):

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

No, vlastně:

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

Pokud náhle potřebujete objasnit, co se zde děje, napište do komentářů nebo PM. Zatím vám řeknu pouze parametry, které jsem vložil do BACKUP DATABASE.

Pro BLOCKSIZE máme „uzavřený“ seznam hodnot a neprovedl jsem zálohu s BLOCKSIZE < 4 kB. MAXTRANSFERSIZE libovolné číslo, které je násobkem 64KB – od 64KB do 4MB. Výchozí na mém systému je 1024 kB, vzal jsem 512 - 1024 - 2048 - 4096.

S BUFFERCOUNT to bylo složitější - může to být libovolné kladné číslo, ale odkaz říká jak se počítá v BACKUP DATABASE a proč jsou velké hodnoty nebezpečné?. Je tam také napsáno, jak získat informaci o tom, s jakým BUFFERCOUNT se záloha skutečně dělá - pro mě je to 7. Nemělo smysl to snižovat a horní hranice byla objevena experimentálně - s BUFFERCOUNT = 896 a MAXTRANSFERSIZE = 4194304 záloha padla s chyba (o které je napsáno v odkazu výše):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE se nenormálně ukončuje.

Zpráva 701, úroveň 17, stav 123, řádek 7 Ve fondu zdrojů „výchozí“ není dostatek systémové paměti pro spuštění tohoto dotazu.

Pro srovnání nejprve ukážu výsledky spuštění zálohy, aniž bych zadal jakékoli parametry:

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

No, zálohovat a zálohovat:

Zpracováno 1070072 stránek pro databázi 'bt', soubor 'bt' na souboru 1.

Zpracovány 2 stránky pro databázi 'bt', soubor 'bt_log' na souboru 1.

BACKUP DATABASE úspěšně zpracovala 1070074 stránek za 53.171 sekund (157.227 MB/s).

Samotný skript, testující parametry, fungoval za pár hodin, všechna měření byla v pořádku google tabulky. A tady je výběr výsledků se třemi nejlepšími časy provedení (snažil jsem se udělat pěkný graf, ale v příspěvku si budu muset vystačit s tabulkou a v komentářích @směs přidáno velmi povedená grafika).

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;

Pozor, velmi důležitá poznámka od @směs z komentáře:

Můžeme s jistotou říci, že vztah mezi parametry a rychlostí zálohování v těchto rozmezích hodnot je náhodný, neexistuje žádný vzorec. Odklon od vestavěných parametrů se ale na výsledku evidentně dobře podepsal

Tito. Pouze správou standardních parametrů BACKUP bylo dosaženo dvojnásobného prodloužení doby odstranění zálohy: 2 sekund oproti 26 na začátku. To není špatné, že? Ale musíme vidět, co se stane s obnovou. Co když nyní trvá zotavení 53krát déle?

Nejprve změřme, jak dlouho trvá obnovení zálohy s výchozím nastavením:

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

No, sami to víte, způsoby existují, nahradit není nahradit, zotavení není zotavení. A dělám to takto:

Zpracováno 1070072 stránek pro databázi 'bt', soubor 'bt' na souboru 1.

Zpracovány 2 stránky pro databázi 'bt', soubor 'bt_log' na souboru 1.

OBNOVIT DATABÁZI úspěšně zpracovalo 1070074 stránek za 40.752 sekund (205.141 MB/s).

Nyní se pokusím obnovit zálohy pořízené se změněnými BLOCKSIZE, BUFFERCOUNT a MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

OBNOVIT DATABÁZI úspěšně zpracovalo 1070074 stránek za 32.283 sekund (258.958 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

OBNOVIT DATABÁZI úspěšně zpracovalo 1070074 stránek za 32.682 sekund (255.796 MB/s).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

OBNOVIT DATABÁZI úspěšně zpracovalo 1070074 stránek za 32.091 sekund (260.507 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

OBNOVIT DATABÁZI úspěšně zpracovalo 1070074 stránek za 32.401 sekund (258.015 MB/s).

Příkaz RESTORE DATABASE se při obnově nemění, tyto parametry v něm nejsou uvedeny, SQL Server si je určuje sám ze zálohy. A je jasné, že i při zotavení může dojít k zisku – téměř o 20 % rychlejší (Abych byl upřímný, nestrávil jsem mnoho času obnovou, prošel jsem několik „nejrychlejších“ záloh a ujistil se, že nedošlo k žádnému zhoršení).

Pro jistotu upřesním, že to nejsou některé parametry, které jsou optimální pro každého. Optimální parametry pro sebe získáte pouze testováním. Mám tyto výsledky, získáte jiné. Ale vidíte, že můžete své zálohy „vyladit“ a ve skutečnosti se mohou vytvářet a nasazovat rychleji.

Důrazně také doporučuji, abyste si přečetli celou dokumentaci, protože mohou existovat nuance specifické pro váš systém.

Vzhledem k tomu, že jsem začal psát o zálohách, chci hned napsat ještě o jedné „optimalizaci“, která je běžnější než „ladění“ parametrů (pokud jsem pochopil, používají ji alespoň některé zálohovací utility, snad spolu s parametry popsána dříve), ale ani na Habré dosud nebyla popsána.

Pokud se podíváme na druhý řádek v dokumentaci, přímo pod BACKUP DATABASE, vidíme:

TO <backup_device> [ ,...n ]

Co si myslíte, že se stane, když zadáte několik backup_devices? Syntaxe to umožňuje. A stane se velmi zajímavá věc – záloha se jednoduše „rozloží“ na několik zařízení. Tito. každé „zařízení“ jednotlivě bude k ničemu, jedno ztracené, ztracená celá záloha. Jak ale takové rozmazání ovlivní rychlost zálohování?

Zkusme vytvořit zálohu na dvou „zařízeních“, která jsou umístěna vedle sebe ve stejné složce:

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

Otcové světa, proč se to dělá?

Zpracováno 1070072 stránek pro databázi 'bt', soubor 'bt' na souboru 1.

Zpracovány 2 stránky pro databázi 'bt', soubor 'bt'přihlásit se do souboru 1.

BACKUP DATABASE úspěšně zpracovala 1070074 stránek za 40.092 sekund (208.519 MB/s).

Stalo se zálohování o 25 % rychlejší jen tak z ničeho nic? Co když přidáme několik dalších zařízení?

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

BACKUP DATABASE úspěšně zpracovala 1070074 stránek za 34.234 sekund (244.200 MB/s).

Celkem je zisk cca 35% doby pořízení zálohy jen díky tomu, že se záloha zapisuje do 4 souborů na jeden disk najednou. Zkontroloval jsem větší číslo - na mém notebooku není žádný zisk, optimálně - 4 zařízení. Pro vás - nevím, musíte to zkontrolovat. No, mimochodem, pokud máte tato zařízení - jsou to opravdu jiné disky, gratuluji, zisk by měl být ještě výraznější.

Nyní si povíme, jak toto štěstí obnovit. Chcete-li to provést, budete muset změnit příkaz pro obnovení a uvést všechna zařízení:

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

OBNOVIT DATABÁZI úspěšně zpracovalo 1070074 stránek za 38.027 sekund (219.842 MB/s).

Trochu rychlejší, ale někde blízko, nijak výrazné. Obecně platí, že záloha se odstraňuje rychleji a obnovuje se stejným způsobem - úspěch? Pokud jde o mě, je to docela úspěch. Tento důležitý, takže opakuji - pokud ty pokud ztratíte alespoň jeden z těchto souborů, přijdete o celou zálohu.

Pokud se podíváte do protokolu na informace o záloze zobrazené pomocí Trace Flags 3213 a 3605, všimnete si, že při zálohování na několik zařízení se zvyšuje alespoň počet BUFFERCOUNT. Pravděpodobně můžete zkusit vybrat optimálnější parametry pro BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ale to se mi hned nepodařilo a byl jsem příliš líný provést takové testování znovu, ale pro jiný počet souborů. A těch kol je škoda. Pokud si takové testování chcete zorganizovat doma, není těžké scénář předělat.

Nakonec se pojďme bavit o ceně. Pokud se záloha odstraňuje souběžně s prací uživatelů, je potřeba přistupovat k testování velmi zodpovědně, protože při rychlejším odstraňování zálohy se více namáhají disky, zvyšuje se zátěž procesoru (musíte stále komprimovat za běhu) a v souladu s tím se snižuje celková odezva systému.

Dělám si legraci, ale naprosto dobře chápu, že jsem nic neprozradil. To, co je napsáno výše, je pouze ukázkou toho, jak lze vybrat optimální parametry pro pořizování záloh.

Pamatujte, že vše, co děláte, děláte na vlastní nebezpečí a riziko. Zkontrolujte si zálohy a nezapomeňte na DBCC CHECKDB.

Zdroj: www.habr.com