MS SQL Server: BACKUP na steroidima

Čekaj! Čekaj! Istina, ovo nije još jedan članak o vrstama sigurnosnih kopija SQL Servera. Neću čak ni govoriti o razlikama između modela oporavka i kako se nositi sa zaraslim dnevnikom.

Možda (samo možda), nakon što pročitate ovaj post, moći ćete se uvjeriti da će sigurnosna kopija koja vam je uklonjena standardnim sredstvima biti uklonjena sutra navečer, pa, 1.5 puta brže. I to samo zbog činjenice da koristite malo više parametara BACKUP DATABASE.

Ako vam je sadržaj objave bio očigledan, žao mi je. Pročitao sam sve što je Gugl došao za frazu “habr sql server backup”, i ni u jednom članku nisam našao da se spominje činjenica da se na vrijeme backup-a može nekako utjecati pomoću parametara.

Odmah ću vam skrenuti pažnju na komentar Aleksandra Gladčenka (@mssqlhelp):

Nikada nemojte mijenjati parametre BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE u proizvodnji. Napravljeni su samo za pisanje ovakvih članaka. U praksi ćete se brzo riješiti problema s pamćenjem.

Bilo bi, naravno, cool biti najpametniji i objavljivati ​​ekskluzivan sadržaj, ali to, nažalost, nije tako. Postoje članci/postovi na engleskom i ruskom jeziku (uvijek sam zbunjen kako da ih ispravno nazovem) posvećeni ovoj temi. Evo nekih od njih na koje sam naišao: puta, два, tri (na sql.ru).

Dakle, za početak ću priložiti malo smanjenu BACKUP sintaksu iz MSDN (usput rečeno, pisao sam gore o BACKUP DATABASE, ali sve ovo se odnosi i na sigurnosnu kopiju dnevnika transakcija i na diferencijalnu sigurnosnu kopiju, ali možda s manje očiglednim efektom):

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

<…> - znači da je tu nešto bilo, ali sam to uklonio jer sada nije relevantno za temu.

Kako obično uzimate rezervnu kopiju? Kako oni "nauče" kako napraviti sigurnosne kopije u milijardama članaka? Generalno, ako treba da napravim jednokratnu rezervnu kopiju neke ne baš velike baze podataka, automatski ću napisati nešto ovako:

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

I, generalno, vjerovatno 75-90% svih parametara koji se obično pominju u člancima o sigurnosnoj kopiji je navedeno ovdje. Pa, tu je i INIT, SKIP. Jeste li posjetili MSDN? Jeste li vidjeli da postoje opcije za jedan i po ekran? Videla sam i...

Vjerovatno ste već shvatili da ćemo dalje govoriti o tri parametra koja su ostala u prvom bloku koda - BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE. Evo njihovih opisa iz MSDN-a:

BLOCKSIZE = { blocksize | @ blocksize_variable } - označava fizičku veličinu bloka u bajtovima. Podržane veličine su 512, 1024, 2048, 4096, 8192, 16, 384 i 32 bajtova (768 KB). Default vrijednost je 65 za ure aje trake i 536 za ostale ure aje. Obično ovaj parametar nije potreban jer izraz BACKUP automatski bira odgovarajuću veličinu bloka za uređaj. Postavljanje veličine bloka eksplicitno poništava automatski odabir veličine bloka.

BUFFERCOUNT = { buffercount | @ buffercount_variable } - Definira ukupan broj I/O bafera koji će se koristiti za operaciju sigurnosnog kopiranja. Možete navesti bilo koju vrijednost pozitivnog cijelog broja, ali veliki broj bafera može uzrokovati grešku nedostatka memorije zbog prevelikog virtualnog adresnog prostora u procesu Sqlservr.exe.

Ukupna količina prostora koju koriste baferi određena je sljedećom formulom: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } specificira najveću veličinu paketa podataka, u bajtovima, za razmjenu između SQL Servera i medija skupa sigurnosne kopije. Podržano je više od 65 bajtova (536 KB) do 64 bajtova (4 MB).

Kunem se – čitao sam ovo ranije, ali nikad mi nije palo na pamet koliki bi uticaj mogli imati na produktivnost. Štaviše, po svemu sudeći, moram napraviti neku vrstu „coming outa“ i priznati da ni sada ne razumijem u potpunosti šta tačno rade. Vjerovatno moram pročitati više o baferovanom ulazu/izlazu i radu sa čvrstim diskom. Jednog dana ću to učiniti, ali za sada mogu samo napisati skriptu koja će provjeriti kako ove vrijednosti utječu na brzinu kojom se pravi backup.

Napravio sam malu bazu podataka, veličine oko 10 GB, stavio je na SSD i stavio direktorij za sigurnosne kopije na HDD.

Napravim privremenu tabelu za pohranjivanje rezultata (nemam je privremenu, pa mogu detaljnije kopati po rezultatima, ali vi odluč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
);

Princip skripte je jednostavan - ugniježđene petlje, od kojih svaka mijenja vrijednost jednog parametra, ubaci ove parametre u naredbu BACKUP, spremi posljednji zapis sa historijom iz msdb.dbo.backupset, izbriše datoteku sigurnosne kopije i sljedeću iteraciju . Budući da su podaci o izvršenju sigurnosne kopije preuzeti iz backupseta, preciznost je donekle izgubljena (nema djelića sekundi), ali to ćemo preživjeti.

Prvo morate omogućiti xp_cmdshell da izbriše sigurnosne kopije (onda ga ne zaboravite onemogućiti ako vam ne treba):

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

Pa, zapravo:

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

Ako vam iznenada zatreba pojašnjenje šta se ovde dešava, pišite u komentare ili PM. Za sada ću vam reći samo o parametrima koje sam stavio u BACKUP DATABASE.

Za BLOCKSIZE imamo “zatvorenu” listu vrijednosti i nisam napravio sigurnosnu kopiju sa BLOCKSIZE < 4KB. MAXTRANSFERSIZE bilo koji broj koji je višestruki od 64KB - od 64KB do 4MB. Zadana vrijednost na mom sistemu je 1024 KB, uzeo sam 512 - 1024 - 2048 - 4096.

Bilo je teže sa BUFFERCOUNT - može biti bilo koji pozitivan broj, ali link kaže kako se izračunava u BACKUP DATABASE i zašto su velike vrijednosti opasne?. Također piše kako dobiti informaciju o tome s kojim BUFFERCOUNT je zapravo napravljena sigurnosna kopija - za mene je to 7. Nije imalo smisla smanjivati ​​je, a gornja granica je otkrivena eksperimentalno - sa BUFFERCOUNT = 896 i MAXTRANSFERSIZE = 4194304 backup je pao sa greška (o kojoj piše na linku iznad):

Poruka 3013, nivo 16, stanje 1, red 7 BACKUP DATABASE se nenormalno završava.

Poruka 701, nivo 17, stanje 123, red 7 Nema dovoljno sistemske memorije u 'podrazumevanom' spremištu resursa za pokretanje ovog upita.

Za usporedbu, prvo ću pokazati rezultate pokretanja sigurnosne kopije bez navođenja ikakvih parametara:

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

Pa, backup i backup:

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

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

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

Sama skripta, testiranjem parametara, proradila je za par sati, sva mjerenja su bila uključena google tabela. A evo i izbora rezultata sa tri najbolja vremena izvršenja (pokušao sam napraviti lijep grafikon, ali u postu ću se morati zadovoljiti tablicom, a u komentarima @mixsture dodano veoma cool 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;

Pažnja, veoma važna napomena od @mixsture из komentar:

Možemo sa sigurnošću reći da je odnos između parametara i rezervne brzine unutar ovih raspona vrijednosti slučajan, nema uzorka. Ali udaljavanje od ugrađenih parametara očito je dobro utjecalo na rezultat

One. Samo upravljanjem standardnim BACKUP parametrima došlo je do dvostrukog povećanja vremena uklanjanja rezervne kopije: 2 sekundi, u odnosu na 26 na početku. To nije loše, zar ne? Ali moramo vidjeti šta će biti sa restauracijom. Šta ako je sada potrebno 53 puta duže da se oporavi?

Prvo, izmjerimo koliko je vremena potrebno za vraćanje sigurnosne kopije sa zadanim postavkama:

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

Pa i sami to znate, postoje načini, zamjena nije zamjena, oporavak nije oporavak. A ja to radim ovako:

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

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

RESTORE DATABASE je uspješno obradio 1070074 stranica za 40.752 sekunde (205.141 MB/sec).

Sada ću pokušati vratiti sigurnosne kopije napravljene sa promijenjenim BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE je uspješno obradio 1070074 stranica za 32.283 sekunde (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE je uspješno obradio 1070074 stranica za 32.682 sekunde (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE je uspješno obradio 1070074 stranica za 32.091 sekunde (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE je uspješno obradio 1070074 stranica za 32.401 sekunde (258.015 MB/sec).

Naredba RESTORE DATABASE se ne mijenja tokom oporavka; ovi parametri nisu navedeni u njemu; SQL Server sam ih određuje iz sigurnosne kopije. I jasno je da čak i uz oporavak može doći do dobitka - skoro 20% brže (Da budem iskren, nisam trošio puno vremena na oporavak, prošao sam kroz nekoliko "najbržih" sigurnosnih kopija i uvjerio se da nema pogoršanja).

Za svaki slučaj da pojasnim da to nisu neki parametri koji su optimalni za sve. Optimalne parametre za sebe možete dobiti samo testiranjem. Ja sam dobio ove rezultate, vi ćete dobiti različite. Ali vidite da možete "podesiti" svoje sigurnosne kopije i one se zapravo mogu brže formirati i rasporediti.

Također vam toplo preporučujem da pročitate dokumentaciju u cijelosti, jer mogu postojati nijanse specifične za vaš sistem.

Pošto sam počeo da pišem o rezervnim kopijama, želim odmah da pišem o još jednoj „optimizaciji“, koja je češća od „podešavanja“ parametara (koliko sam razumeo, koriste je bar neki uslužni programi za pravljenje rezervnih kopija, možda zajedno sa parametrima opisano ranije), ali još nije opisano ni na Habréu.

Ako pogledamo drugi red u dokumentaciji, odmah ispod BACKUP DATABASE, vidimo:

TO <backup_device> [ ,...n ]

Šta mislite da će se dogoditi ako navedete nekoliko backup_devices? Sintaksa to dozvoljava. I dogodit će se vrlo zanimljiva stvar - sigurnosna kopija će se jednostavno "rasprostirati" na nekoliko uređaja. One. svaki "uređaj" pojedinačno će biti beskoristan, izgubljen jedan, izgubljena cela rezervna kopija. Ali kako će takvo zamućivanje utjecati na brzinu sigurnosne kopije?

Pokušajmo napraviti sigurnosnu kopiju na dva "uređaja" koji se nalaze jedan pored drugog u istom folderu:

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

Očevi svijeta, zašto se to radi?

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

Obrađeno 2 stranice za bazu podataka 'bt', fajl 'bt'prijavite se na fajl 1.

BACKUP DATABASE je uspješno obradio 1070074 stranica za 40.092 sekundi (208.519 MB/sec).

Da li je sigurnosna kopija postala 25% brža iz vedra neba? Šta ako dodamo još par uređaja?

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

BACKUP DATABASE je uspješno obradio 1070074 stranica za 34.234 sekundi (244.200 MB/sec).

Ukupno, dobitak je oko 35% vremena pravljenja sigurnosne kopije samo zbog činjenice da se sigurnosna kopija upisuje na 4 datoteke na jednom disku odjednom. Provjerio sam veći broj - nema pojačanja na mom laptopu, optimalno - 4 uređaja. Za vas - ne znam, morate provjeriti. Pa, usput, ako imate ove uređaje - ovo su stvarno različiti diskovi, čestitam, dobitak bi trebao biti još značajniji.

Hajde sada da razgovaramo o tome kako vratiti ovu sreću. Da biste to učinili, morat ćete promijeniti naredbu za oporavak i navesti sve uređaje:

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 je uspješno obradio 1070074 stranica za 38.027 sekunde (219.842 MB/sec).

Malo brže, ali negdje blizu, nebitno. Općenito, sigurnosna kopija se brže uklanja i vraća na isti način - uspjeh? Što se mene tiče, to je prilično uspješan. Ovo je važno, pa ponavljam - ako ti ako izgubite barem jednu od ovih datoteka, izgubit ćete cijelu sigurnosnu kopiju.

Ako u dnevniku pogledate informacije o sigurnosnoj kopiji prikazane pomoću oznaka praćenja 3213 i 3605, primijetit ćete da se prilikom pravljenja sigurnosne kopije na nekoliko uređaja povećava barem broj BUFFERCOUNT. Vjerovatno možete pokušati odabrati optimalnije parametre za BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ali nisam uspio odmah, a bio sam previše lijen da ponovim takvo testiranje, ali za drugačiji broj datoteka. I šteta za točkove. Ako želite organizirati takvo testiranje kod kuće, nije teško prepraviti skriptu.

Na kraju, hajde da pričamo o ceni. Ako se sigurnosna kopija uklanja paralelno s radom korisnika, potrebno je vrlo odgovorno pristupiti testiranju, jer ako se backup ukloni brže, diskovi se više naprežu, povećava se opterećenje procesora (i dalje morate komprimirati to u hodu), i shodno tome, ukupni odziv sistema se smanjuje.

Šalim se, ali savršeno razumijem da nisam napravio nikakva otkrića. Ono što je gore napisano je samo demonstracija kako možete odabrati optimalne parametre za pravljenje rezervnih kopija.

Zapamtite da sve što radite radite na vlastitu odgovornost i rizik. Provjerite svoje sigurnosne kopije i ne zaboravite na DBCC CHECKDB.

izvor: www.habr.com