Počkajte! Počkajte! Naozaj, toto nie je ďalší článok o typoch záloh SQL Server. Nebudem ani rozprávať o rozdieloch modelov obnovy a ako bojovať s prehnaným „logom“.
Možno (len možno), po prečítaní tohto príspevku budete schopní zabezpečiť, aby záloha, ktorú robíte štandardnými prostriedkami, zajtra v noci prebehla približne 1,5-krát rýchlejšie. A to len vďaka tomu, že použijete trochu viac parametrov BACKUP DATABASE.
Ak bolo pre vás obsah príspevku zrejmé — ospravedlňujem sa. Prečítal som všetko, čo som našiel na googli pri fráze „habr sql server backup“, a v žiadnom článku som nenašiel zmienku o tom, že na čas zálohy by sa nejako dalo vplyvniť pomocou parametrov.
Okamžite by som chcel upozorniť na komentár Alexandra Gladčenka ():
Nikdy nemente parametre BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE na produkcii. Tieto sú určené iba na písanie podobných článkov. V praxi dostanete problémy s pamäťou na plnej čiare.
Bolo by samozrejme skvelé byť najinteligentnejším a zverejniť exkluzívny obsah, ale to, žiaľ, nie je pravda. Existuje množstvo článkov a príspevkov v angličtine aj ruštine (vždy sa zamieňam, ako ich správne nazvať), ktoré sa tejto téme venujú. Tu je časť z tých, ktoré sa mi dostali do rúk: , , .
Takže na začiatok priložím niekoľko skrátených syntaktických zápisov BACKUP z (mimochodom, vyššie som písal o BACKUP DATABASE, ale všetko toto platí aj pre zálohu transakčného denníka a pre diferenčnú zálohu, aj keď s menej jasným efektom):
BACKUP DATABASE { database_name | @database_name_var }
TO [ ,...n ]
[ WITH {
| [ ,...n ] } ]
[;]
[ ,...n ]::=
--Možnosti súboru médií
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Možnosti prenosu údajov
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }— znamená, že tam niečo bolo, ale ja som to odstránil, pretože sa to teraz téme netýka.
Ako zvyčajne robíte zálohu? Ako „učia“ robiť zálohu v miliardách článkov? Vlastne, ak by bolo potrebné raz urobiť zálohu nejakej nie veľmi veľkej databázy, automaticky napíšem niečo takéto:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--dobre, CHECKSUM som napísal len na to, aby som vyzeral múdrejšieA tu, v podstate, je uvedených asi 75-90% všetkých parametrov, ktoré sa bežne spomínajú v článkoch o zálohovaní. Napríklad INIT, SKIP a ďalšie. Navštívili ste MSDN? Videli ste, že tam je možností na viac ako jeden a pol obrazovky? Ja som to tiež videl…
Asi už chápete, že sa ďalej budeme zaoberať tromi parametrami, ktoré zostali v prvom bloku kódu — BLOCKSIZE, BUFFERCOUNT a MAXTRANSFERSIZE. Tu sú ich popisy z MSDN:
BLOCKSIZE = { blocksize | @ blocksize_variable } — určuje veľkosť fyzického bloku v bajtoch. Podporované veľkosti sú 512, 1024, 2048, 4096, 8192, 16 384, 32 768 a 65 536 bajtov (64 KB). Predvolená hodnota je 65 536 pre páskové zariadenia a 512 pre ostatné zariadenia. Zvyčajne nie je potrebné tento parameter nastavovať, pretože príkaz BACKUP automaticky vyberá veľkosť bloku zodpovedajúcu zariadeniu. Explicitné nastavenie veľkosti bloku prepisuje automatický výber veľkosti bloku.
BUFFERCOUNT = { buffercount | @ buffercount_variable } — určuje celkový počet vstupno-výstupných bufrov, ktoré budú použité pre operáciu zálohovania. Môžete zadať akúkoľvek kladnú celočíselnú hodnotu, avšak veľký počet bufrov môže spôsobiť chybu nedostatku pamäte v dôsledku nadmerného virtuálneho adresného priestoru v procese Sqlservr.exe.
Celkový objem priestoru, ktorý bufre používajú, sa určuje podľa nasledujúcej vzorce:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } udáva najväčší objem balíka údajov v bajtoch na prenos dát medzi SQL Serverom a nosičom zálohovania. Podporované hodnoty sú násobky 65 536 bajtov (64 KB), až do 4 194 304 bajtov (4 MB).
Prísahám, že som to čítal predtým, ale nikdy ma nenapadlo, aký vplyv môžu mať na výkon. Navyše, zjavne musím urobiť akýsi "coming-out" a priznať, že ani teraz úplne nerozumiem, čo presne robia. Možno by som mal viac čítať o buferovanom vstupno-výstupnom prístupe a práci s pevným diskom. Niekedy to určite urobím, ale teraz môžem jednoducho napísať skript, ktorý skontroluje, ako tieto hodnoty ovplyvňujú rýchlosť zálohovania.
Vytvoril som malú databázu s veľkosťou asi 10 GB, uložil som ju na SSD a adresár pre zálohy na HDD.
Vytváram dočasnú tabuľku na uchovávanie výsledkov (pre mňa nie je dočasná, aby som mohol podrobnejšie preskúmať výsledky, ale rozhodnite sa 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
);Zásada fungovania skriptu je jednoduchá — vnorené cykly, z ktorých každý mení hodnotu jedného parametra, tie parametre dávam do príkazu BACKUP, uchovávam posledný záznam z histórie z msdb.dbo.backupset, vymažem súbor zálohy a pokračujem v ďalšej iterácii. Keďže údaje o vykonaní zálohy sa berú z backupset, presnosť je trochu ovplyvnená (neexistujú zlomky sekúnd), ale to prežijeme.
Najprv je potrebné povoliť používanie xp_cmdshell, aby ste mohli mazať zálohy (neskôr nezabudnite vypnúť, ak ho nepotrebujete):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOA vlastne:
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 hodnoty \/
DECLARE @bs int = 4096,
@max_bs int = 65536;
\/
BUFFERCOUNT hodnoty \/
DECLARE @bc int = 7,
@min_bc int = 7,
@max_bc int = 800;
\/
MAXTRANSFERSIZE hodnoty \/
DECLARE @ts int = 524288, --512KB, predvolené = 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;
ENDAk by ste potrebovali vysvetlenia k tomu, čo sa tu deje — píšte do komentárov alebo súkromných správ. Zatiaľ sa zmienim iba o parametroch, ktoré dávam do BACKUP DATABASE.
Pre BLOCKSIZE máme „uzavretý“ zoznam hodnôt, pričom sa mi nevykonal zálohovanie s BLOCKSIZE < 4KB. MAXTRANSFERSIZE môže byť akékoľvek číslo, ktoré je násobkom 64KB — od 64KB do 4MB. Predvolená hodnota na mojej sústave je 1024KB, zobral som 512 — 1024 — 2048 — 4096.
Zložitejšie to bolo s BUFFERCOUNT — môže to byť akékoľvek kladné číslo, ale na odkaze sa píše . Tam sa píše aj o tom, ako získať informácie o tom, s akým BUFFERCOUNT sa skutočne vykonáva zálohovanie — mám to 7. Nemalo zmysel ho znižovať, a horná hranica bola zistená empiricky — pri BUFFERCOUNT = 896 a MAXTRANSFERSIZE = 4194304 zálohovanie zlyhalo s chybou (o ktorej sa píše na hore uvedenom odkaze):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE sa abnormálne ukončuje.
Msg 701, Level 17, State 123, Line 7 V systémovej pamäti v zdroji pool ‚default‘ je nedostatočná kapacita na spustenie tohto dotazu.
Na porovnanie, najprv ukážem výsledky vykonania zálohovania bez udania parametrov vôbec:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;No, zálohovanie a zálohovanie:
Spracovaných 1070072 strán pre databázu ‚bt‘, súbor ‚bt‘ v súbore 1.
Spracovaných 2 stránky pre databázu ‚bt‘, súbor ‚bt_log‘ v súbore 1.
BACKUP DATABASE úspešne spracoval 1070074 strán za 53,171 sekúnd (157,227 MB/sec).
Samotný skript, ktorý testuje parametre, sa vykonal za pár hodín, všetky merania v . A toto je výber výsledkov, ktoré majú tri najlepšie časy vykonania (snažil som sa urobiť pekný graf, ale v príspevku budeme musieť vystačiť s tabuľkou, a v komentároch pridal ).
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, hneď veľmi dôležitá poznámka od z :
môžem s určitosťou povedať, že súvislosť medzi parametrami a rýchlosťou zálohy v týchto hodnotách je náhodná, neexistuje žiadna pravidelnosť. Ale odklon od zabudovaných parametrov mal zjavne dobrý vplyv na výsledok.
T.j. len vďaka správe štandardných parametrov BACKUP sa dosiahol časový zisk pri zálohovaní 2-krát: 26 sekúnd, oproti 53 na začiatku. A nie je to zlé, však? Ale potrebujeme zistiť, čo sa deje so obnovením. A čo ak teraz obnovenie potrvá 4-krát dlhšie?
Na začiatok zmerajme, ako prebieha obnova zálohy s predvolenými nastaveniami:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;No to už iste poznáte, cesty tam, replace-čiže replace, recovery-čiže recovery. A máte to takto:
Spracovaných 1070072 strán pre databázu ‚bt‘, súbor ‚bt‘ v súbore 1.
Spracovaných 2 stránky pre databázu ‚bt‘, súbor ‚bt_log‘ v súbore 1.
RESTORE DATABASE úspešne spracoval 1070074 strán v 40,752 sekundy (205,141 MB/s).
A teraz sa pokúsim obnoviť zálohy, ktoré boli vyhotovené s zmenenými BLOCKSIZE, BUFFERCOUNT a MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE úspešne spracoval 1070074 strán v 32,283 sekundy (258,958 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE úspešne spracoval 1070074 strán v 32,682 sekundy (255,796 MB/s).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE úspešne spracoval 1070074 strán v 32,091 sekundy (260,507 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE úspešne spracoval 1070074 strán v 32,401 sekundy (258,015 MB/s).
Pokyn RESTORE DATABASE pri obnove sa nemení, neuvádza sa v ňom tieto parametre, SQL Server si ich sám určí podľa zálohy. A je vidieť, že aj pri obnove môže byť zisk — prakticky o 20% rýchlejší (úprimne povedané, nevenoval som veľa času obnove, vyskúšal som niekoľko naj «rýchlejších» záloh a presvedčil sa, že zhoršenie nie je).
Pre istotu upresním — nie sú tu popísané žiadne optimálne pre všetky parametre. Optimálne parametre pre seba môžete získať len testovaním. Dostával som takéto výsledky, vy dostanete iné. Ale vidíte, že svoje zálohy môžete «vytuningovať» a skutočne sa môžu vytvárať a obnovovať rýchlejšie.
Ešte dôrazne odporúčam prečítať si celú dokumentáciu, pretože práve pre váš systém môžu byť nuansy.
Keď som začal písať o zálohách, chcem ešte napísať o jednej «optimalizácii», ktorá sa vyskytuje častejšie, než «tuning» parametrov (túto, ako chápem, používa minimálne časť utilít na zálohovanie, možno spolu s parametrami popísanými skôr), ale na Habré ešte nebola opísaná.
Ak sa pozrieme na druhý riadok v dokumentácii, hneď pod BACKUP DATABASE, tam vidíme:
TO [,...n]Čo si myslíte, čo sa stane, ak uvediete niekoľko backup_device’ov? Syntax to predsa umožňuje. A bude to veľmi zaujímavá vec — záloha sa jednoducho «rozptýli» po viacerých zariadeniach. T.j. každý «zariadenie» osobitne bude nepoužiteľné, stratili ste jedno, stratili ste celú zálohu. Ale ako sa takéto rozptýlenie prejaví na rýchlosti zálohovania?
Skúsime urobiť zálohu na dva «zariadenia», ktoré sú vedľa seba v jednej zložke:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Bože môj, čo sa to tu deje?
Spracovaných 1070072 strán pre databázu ‚bt‘, súbor ‚bt‘ v súbore 1.
Spracované 2 stránky pre databázu ‘bt’, súbor ‘btlog’ v súbore 1.
ZÁLOHOVANIE DATABÁZY bolo úspešne spracované, 1070074 stránok za 40,092 sekúnd (208,519 MB/s).
Bola záloha vykonaná o 25 % rýchlejšie bez zjavného dôvodu? A čo ak pridáme ešte pár zariadení?
ZÁLOHOVANIE DATABÁZY [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
S KOMPRESE;ZÁLOHOVANIE DATABÁZY bolo úspešne spracované, 1070074 stránok za 34,234 sekúnd (244,200 MB/s).
Celkovo sa čas zálohy skrátil o približne 35 % len preto, že záloha sa zapisuje okamžite do 4 súborov na jednom disku. Testoval som aj väčší počet — na mojom notebooku sa zisk neprejavil, optimálne — 4 zariadenia. Pre vás — neviem, treba otestovať. A mimochodom, ak máte tieto zariadenia — sú to skutočne rôzne disky, gratulujem, zisk by mal byť ešte výraznejší.
Teraz sa porozprávajme o tom, ako toto šťastie obnoviť. Na to bude potrebné zmeniť príkaz na obnovu a vymenovať všetky zariadenia:
OBNOVIŤ DATABÁZU [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
S NÁHRADOU, OBNOVA;OBNOVA DATABÁZY bola úspešne spracovaná, 1070074 stránok za 38,027 sekúnd (219,842 MB/s).
Trochu rýchlejšie, ale niekde blízko, nie je to podstatné. V zásade sa záloha vykonáva rýchlejšie, ale obnova prebieha rovnako — úspech? Podľa mňa — celkom slušný úspech. Je to dôležité, preto sa opakujem — ak strácate aspoň jeden z týchto súborov — strácate celú zálohu. Ak sa pozrieme do protokolu na informácie o zálohe, ktoré sú získané pomocou Trace Flag 3213 a 3605, môžeme si všimnúť, že pri zálohe na viacero zariadení sa zvyšuje minimálne množstvo BUFFERCOUNT. Možno by bolo dobré vyskúšať prispôsobiť aj optimálne parametre pre BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ale mne sa to hneď nepodarilo, a vykonávať takéto testovanie ešte raz, ale na rôznom počte súborov, sa mi nechcelo. A disky sú mi ľúto. Ak chcete usporiadať takéto testovanie u seba, skript nie je ťažké prepracovať..
Na záver sa porozprávajme o cene. Ak sa záloha vykonáva paralelne s prácou používateľov — je potrebné veľmi zodpovedne pristupovať k testovaniu, pretože ak sa záloha vykonáva rýchlejšie — disky sú viac zaťažené, zaťaženie procesora sa zvyšuje (to všetko treba ešte aj komprimovať na pohyb), a tým sa znižuje celková reakčná doba systému.
Na konci sa porozprávame o cene. Ak sa záloha vykonáva paralelne s prácou používateľov, treba k testovaniu pristupovať veľmi zodpovedne, pretože ak sa záloha vykonáva rýchlejšie, disky sa viac namáhajú a zaťaženie procesora sa zvyšuje (veď to všetko treba ešte aj komprimovať za behu), a teda sa znižuje celková reakčnosť systému.
Vtipy sú vtipmi, ale úplne rozumiem, že som neurobil žiadne veľké odhalenia. To, čo je napísané vyššie, je len ukážka toho, ako je možné zvoliť optimálne parametre pre zálohovanie.
Pamätajte, že všetko, čo robíte, robíte na vlastné riziko. Overte si svoje zálohy a nezabúdajte na DBCC CHECKDB.
Zdroj: habr.com
