Čekati! Čekati! Istina, ovo nije još jedan članak o vrstama sigurnosnih kopija SQL Servera. Neću ni govoriti o razlikama između modela oporavka i kako se nositi s zaraslim trupcem.
Možda (samo možda), nakon čitanja ovog posta, moći ćete se uvjeriti da će sigurnosna kopija koja vam je uklonjena standardnim sredstvima biti uklonjena sutra navečer, dobro, 1.5 puta brže. I to samo zbog činjenice da koristite malo više parametara BACKUP DATABASE.
Ako vam je sadržaj posta bio očit, žao mi je. Pročitao sam sve do čega je Google došao za frazu “habr sql server backup”, i niti u jednom članku nisam našao spominjanje činjenice da se na vrijeme backupa može nekako utjecati pomoću parametara.
Odmah ću vam skrenuti pozornost na komentar Aleksandra Gladčenka ():
Nikada ne mijenjajte parametre BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE u proizvodnji. Napravljeni su samo za pisanje takvih članaka. U praksi ćete se u tren oka 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 ih ispravno nazvati) posvećeni ovoj temi. Evo nekih na koje sam naišao: , , .
Dakle, za početak ću priložiti malo skraćenu BACKUP sintaksu iz (usput, gore sam pisao o SIGURNOSNOM KOPIRANJU BAZE PODATAKA, ali sve ovo vrijedi i za sigurnosno kopiranje dnevnika transakcija i za diferencijalno sigurnosno kopiranje, ali možda s manje očiglednim učinkom):
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 pravite sigurnosnu kopiju? Kako oni "podučavaju" kako napraviti sigurnosne kopije u milijardama članaka? Općenito, ako trebam napraviti jednokratnu sigurnosnu kopiju neke ne baš velike baze podataka, automatski ću napisati nešto poput ovoga:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееI općenito, ovdje je navedeno vjerojatno 75-90% svih parametara koji se obično spominju u člancima o sigurnosnim kopijama. Pa tu je i INIT, SKIP. Jeste li posjetili MSDN? Jeste li vidjeli da postoje opcije za jedan i pol ekran? Također sam vidio...
Vjerojatno ste već shvatili da ćemo dalje govoriti o tri parametra koji su ostali u prvom bloku koda - BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE. Evo njihovih opisa s MSDN-a:
BLOKIRAJ = { veličina bloka | @ varijabla veličine_bloka } - 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). Defaultna vrijednost je 65 za ure aje trake i 536 za ostale ure aje. Obično ovaj parametar nije neophodan jer izjava BACKUP automatski odabire odgovarajuću veličinu bloka za uređaj. Postavljanje veličine bloka eksplicitno nadjačava automatski odabir veličine bloka.
BUFFERCOUNT = { međuspremnik | @ varijabla broja_spremnika } - Definira ukupan broj I/O međuspremnika koji će se koristiti za operaciju sigurnosnog kopiranja. Možete navesti bilo koju vrijednost pozitivnog cijelog broja, ali veliki broj međuspremnika može uzrokovati pogrešku nedostatka memorije zbog prekomjernog prostora virtualne adrese u procesu Sqlservr.exe.
Ukupna količina prostora koju koriste međuspremnici određena je sljedećom formulom:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } navodi najveću veličinu paketa podataka, u bajtovima, koji se razmjenjuju između SQL Servera i medija skupa sigurnosnih kopija. Podržani su višestruki od 65 bajtova (536 KB) do 64 bajta (4 MB).
Kunem se - ovo sam već čitao, ali nikad mi nije palo na pamet koliki bi utjecaj mogli imati na produktivnost. Štoviše, očito moram napraviti svojevrsni “coming out” i priznati da ni sada ne razumijem u potpunosti što točno rade. Vjerojatno trebam pročitati više o međuspremniku I/O i radu s tvrdim diskom. Jednog dana ću to učiniti, ali za sada mogu samo napisati skriptu koja će provjeriti kako ove vrijednosti utječu na brzinu izrade sigurnosne kopije.
Napravio sam malu bazu, cca 10 GB, stavio je na SSD, a na HDD direktorij za backup.
Kreiram privremenu tablicu 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 rada skripte je jednostavan - ugniježđene petlje, od kojih svaka mijenja vrijednost jednog parametra, te parametre umetnite u naredbu BACKUP, spremite zadnji zapis s poviješću iz msdb.dbo.backupset, izbrišite datoteku sigurnosne kopije i sljedeća iteracija . Budući da se podaci o izvršavanju sigurnosne kopije preuzimaju iz sigurnosne kopije, točnost je donekle izgubljena (nema djelića sekunde), ali preživjet ćemo ovo.
Prvo morate omogućiti xp_cmdshell za brisanje sigurnosnih kopija (zatim ga nemojte zaboraviti 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;
GOPa 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;
ENDAko vam iznenada treba pojašnjenje o tome što se ovdje događa, pišite u komentarima ili PM. Za sada ću vam reći samo o parametrima koje sam stavio u BACKUP DATABASE.
Za BLOCKSIZE imamo "zatvoreni" popis vrijednosti i nisam napravio sigurnosnu kopiju s BLOCKSIZE < 4KB. MAXTRANSFERSIZE bilo koji broj koji je višekratnik 64KB - od 64KB do 4MB. Zadana vrijednost na mom sustavu je 1024 KB, uzeo sam 512 - 1024 - 2048 - 4096.
Bilo je teže s BUFFERCOUNT - može biti bilo koji pozitivan broj, ali link kaže . Također piše kako dobiti informaciju s kojim BUFFERCOUNT-om se stvarno radi backup - kod mene je to 7. Nije bilo smisla smanjivati ga, a gornja granica je otkrivena eksperimentalno - s BUFFERCOUNT = 896 i MAXTRANSFERSIZE = 4194304 backup je pao s greška (o čemu piše na gornjoj poveznici):
Poruka 3013, razina 16, stanje 1, redak 7. SIGURNOSNA BAZA PODATAKA neuobičajeno završava.
Poruka 701, razina 17, stanje 123, redak 7 Nema dovoljno sistemske memorije u skupu resursa 'zadano' za pokretanje ovog upita.
Za usporedbu, prvo ću pokazati rezultate izvođenja sigurnosne kopije bez navođenja ikakvih parametara:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Pa sigurnosna kopija i sigurnosna kopija:
Obrađeno 1070072 stranica za bazu podataka 'bt', datoteka 'bt' na datoteci 1.
Obrađene 2 stranice za bazu podataka 'bt', datoteka 'bt_log' na datoteci 1.
BAZA PODATAKA SIGURNOSNE KOPIJE uspješno je obradila 1070074 stranica u 53.171 sekundi (157.227 MB/sek).
Sama skripta, testiranje parametara, proradila je za par sati, sva mjerenja su bila unutra . A ovdje je i izbor rezultata s tri najbolja vremena izvršenja (pokušao sam napraviti lijep graf, ali u postu ću se morati zadovoljiti tablicom, au komentarima dodao je ).
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, vrlo važna napomena od od :
Sa sigurnošću možemo reći da je odnos između parametara i brzine sigurnosnog kopiranja unutar ovih raspona vrijednosti slučajan, nema uzorka. Ali odmak od ugrađenih parametara očito je dobro utjecao na rezultat
Oni. Samo upravljanjem standardnim BACKUP parametrima postignut je dvostruki dobitak u vremenu uklanjanja sigurnosne kopije: 2 sekundi, naspram 26 na početku. To nije loše, zar ne? Ali moramo vidjeti što će se dogoditi s restauracijom. Što ako sada oporavak traje 53 puta duže?
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 to i sami znate, načini postoje, zamjena nije zamjena, oporavak nije oporavak. A ja to radim ovako:
Obrađeno 1070072 stranica za bazu podataka 'bt', datoteka 'bt' na datoteci 1.
Obrađene 2 stranice za bazu podataka 'bt', datoteka 'bt_log' na datoteci 1.
OBNAVLJANJE BAZE PODATAKA uspješno je obradilo 1070074 stranice u 40.752 sekunde (205.141 MB/sek).
Sada ću pokušati vratiti sigurnosne kopije snimljene s promijenjenim BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304OBNAVLJANJE BAZE PODATAKA uspješno je obradilo 1070074 stranice u 32.283 sekunde (258.958 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304OBNAVLJANJE BAZE PODATAKA uspješno je obradilo 1070074 stranice u 32.682 sekunde (255.796 MB/sek).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152OBNAVLJANJE BAZE PODATAKA uspješno je obradilo 1070074 stranice u 32.091 sekunde (260.507 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304OBNAVLJANJE BAZE PODATAKA uspješno je obradilo 1070074 stranice u 32.401 sekunde (258.015 MB/sek).
Naredba RESTORE DATABASE ne mijenja se tijekom oporavka; ovi parametri u njoj nisu navedeni; sam SQL Server ih određuje iz sigurnosne kopije. I jasno je da čak i uz oporavak može doći do dobitka - gotovo 20% brže (Da budem iskren, nisam potroš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. Samo testiranjem možete dobiti optimalne parametre za sebe. Ja sam dobio ove rezultate, vi ćete dobiti drugačije. Ali vidite da možete "podešavati" svoje sigurnosne kopije i one se zapravo mogu brže formirati i implementirati.
Također vam toplo preporučujem da pročitate dokumentaciju u cijelosti, jer mogu postojati nijanse specifične za vaš sustav.
Budući da sam počeo pisati o sigurnosnim kopijama, želim odmah pisati o još jednoj "optimizaciji", koja je češća od "podešavanja" parametara (koliko sam shvatio, koriste je barem neki pomoćni programi za sigurnosno kopiranje, možda zajedno s parametrima ranije opisan), ali još nije opisan ni na Habréu.
Ako pogledamo drugi redak u dokumentaciji, odmah ispod BACKUP DATABASE, tamo vidimo:
TO <backup_device> [ ,...n ]Što mislite da će se dogoditi ako navedete nekoliko backup_devices? Sintaksa to dopušta. I dogodit će se vrlo zanimljiva stvar - sigurnosna kopija će se jednostavno "rasprostrijeti" na nekoliko uređaja. Oni. svaki "uređaj" pojedinačno bit će beskoristan, izgubljen jedan, izgubljena cijela sigurnosna kopija. Ali kako će takvo razmazivanje utjecati na brzinu sigurnosnog kopiranja?
Pokušajmo napraviti sigurnosnu kopiju na dva “uređaja” koji se nalaze jedan pored drugog u istoj mapi:
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', datoteka 'bt' na datoteci 1.
Obrađene 2 stranice za bazu podataka 'bt', datoteka 'bt'prijavite se u datoteku 1.
BAZA PODATAKA SIGURNOSNE KOPIJE uspješno je obradila 1070074 stranica u 40.092 sekundi (208.519 MB/sek).
Je li sigurnosna kopija iznenada postala 25% brža? Što ako dodamo još nekoliko uređaja?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BAZA PODATAKA SIGURNOSNE KOPIJE uspješno je obradila 1070074 stranica u 34.234 sekundi (244.200 MB/sek).
Ukupno, dobitak je oko 35% vremena izrade sigurnosne kopije samo zbog činjenice da se sigurnosna kopija zapisuje u 4 datoteke na jednom disku odjednom. Provjerio sam veći broj - na mom laptopu nema dobitka, optimalno - 4 uređaja. Za vas - ne znam, morate provjeriti. Pa, usput, ako imate ove uređaje - to su stvarno različiti diskovi, čestitamo, dobitak bi trebao biti još značajniji.
Sada razgovarajmo o tome kako vratiti tu sreću. Da biste to učinili, morat ćete promijeniti naredbu za oporavak i popisati 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;OBNAVLJANJE BAZE PODATAKA uspješno je obradilo 1070074 stranice u 38.027 sekunde (219.842 MB/sek).
Malo brže, ali negdje blizu, ne značajno. Općenito, sigurnosna kopija se brže uklanja i vraća na isti način - uspjeh? Što se mene tiče, to je veliki uspjeh. Ovaj je važno, pa ponavljam - ako ti ako izgubite barem jednu od ovih datoteka, izgubit ćete cijelu sigurnosnu kopiju.
Ako u dnevniku pogledate podatke o sigurnosnom kopiranju prikazane pomoću zastavica praćenja 3213 i 3605, primijetit ćete da se kod sigurnosnog kopiranja na nekoliko uređaja povećava barem broj BUFFERCOUNT. Vjerojatno možete pokušati odabrati optimalnije parametre za BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ali nisam odmah uspio i bio sam previše lijen da ponovno provedem takvo testiranje, ali za drugi broj datoteka. A šteta za kotače. Ako želite organizirati takvo testiranje kod kuće, nije teško preraditi skriptu.
Na kraju, razgovarajmo o cijeni. Ako se sigurnosna kopija uklanja paralelno s radom korisnika, morate vrlo odgovorno pristupiti testiranju, jer ako se sigurnosna kopija brže uklanja, diskovi se više opterećuju, povećava se opterećenje procesora (i dalje morate komprimirati u hodu), i sukladno tome, smanjuje se ukupna odzivnost sustava.
Šalim se, ali savršeno dobro razumijem da nisam dao nikakva otkrića. Gore napisano samo je demonstracija kako možete odabrati optimalne parametre za izradu sigurnosnih kopija.
Upamtite da sve što radite radite na vlastitu odgovornost i rizik. Provjerite sigurnosne kopije i ne zaboravite na DBCC CHECKDB.
Izvor: www.habr.com