Čekajte! Čekajte! Istina, ovo nije još jedan članak o tipovima SQL Server sigurnosnih kopija. Čak neću opisivati razlike između modela oporavka i kako se boriti s prevelikim „logom“.
Moguće (samo moguće), nakon čitanja ovog posta, moći ćete postaviti da se sigurnosna kopija koju izrađujete standardnim sredstvima sutra noću napravi, pa, 1.5 puta brže. I samo zato što koristite malo više parametara BACKUP DATABASE.
Ako vam je sadržaj posta bio očit — izvinjavam se. Pročitao sam sve što sam pronašao na Google-u uz frazu „habr sql server backup“, i nijedan članak nije spominjao da se na vrijeme sigurnosne kopije može nekako utjecati pomoću parametara.
Odmah ću skrenuti vašu pozornost na komentar Aleksandra Gladčenka ():
Nikada ne mijenjajte parametre BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE na proizvodnji. Oni su napravljeni samo za pisanje sličnih članaka. U praksi ćete imati problema s memorijom.
Bilo bi, naravno, sjajno, biti najpametniji i objaviti ekskluzivan sadržaj, ali, nažalost, to nije slučaj. Postoje i članci/postovi na engleskom i ruskom jeziku (uvijek se zbunim kako ih ispravno nazvati) posvećeni ovoj temi. Evo dijela onoga što sam pronašao: , , .
Dakle, za početak ću priložiti nekoliko skraćenih sintakse BACKUP iz (usput, gore sam spomenuo BACKUP DATABASE, ali sve ovo se može primijeniti i na sigurnosnu kopiju dnevnika transakcija, i na diferencijalnu sigurnosnu kopiju, iako možda s manje očitim učinkom):
BACKUP DATABASE { database_name | @database_name_var }
TO [ ,...n ]
[ WITH {
| [ ,...n ] } ]
[;]
[ ,...n ]::=
--Mediijski set opcije
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Opcije prijenosa podataka
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 izrađujete sigurnosne kopije? Kako „podučavaju“ izradu sigurnosne kopije u milijardama članaka? Uglavnom, ako trebate napraviti jednokratnu sigurnosnu kopiju neke ne prevelike baze, automatski ću napisati nešto poput ovoga:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--dobro, CHECKSUM sam napisao samo da bih izgledao pametnijeI, u osnovi, ovdje je navedeno vjerojatno 75-90% svih parametara koji se obično spominju u člancima o backupovima. Pa INIT, SKIP još. Jeste li posjetili MSDN? Vidjeli ste da tamo ima opcija za jedan i pol ekrana? I ja sam to vidio...
Vjerojatno ste već shvatili da će dalje riječ biti o tri parametra koja su ostala u prvom bloku koda — BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE. Evo njihovih opisa iz MSDN:
BLOCKSIZE = { blocksize | @ blocksize_variable } — označava veličinu fizičkog bloka u bajtovima. Podržane veličine su 512, 1024, 2048, 4096, 8192, 16 384, 32 768 i 65 536 bajtova (64 kB). Zadana vrijednost je 65 536 za trake i 512 za ostale uređaje. Obično nije potrebno postavljati ovaj parametar jer instrukcija BACKUP automatski bira veličinu bloka koja odgovara uređaju. Izričito postavljanje veličine bloka nadjačava automatski odabranu veličinu bloka.
BUFFERCOUNT = { buffercount | @ buffercount_variable } — definira ukupni broj ulazno-izlaznih bafera koji će biti korišteni za operaciju rezervnog kopiranja. Može se navesti bilo koja cjelobrojna pozitivna vrijednost, međutim, veliko broj bafera može izazvati grešku nedostatka memorije zbog prekomjernog virtualnog adresnog prostora tijekom procesa Sqlservr.exe.
Ukupni volumen prostora koji koriste baferi određuje se prema sljedećoj formuli:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } označava najveći volumen paketa podataka u bajtovima za razmjenu podataka između SQL Servera i uređaja rezervne kopije. Podržane su vrijednosti koje su višekratne od 65 536 bajtova (64 kB), do 4 194 304 bajta (4 MB).
Kunem se — čitao sam ovo ranije, ali mi nije ni na kraj pameti bilo kakav utjecaj na performanse koji mogu imati. Štoviše, očito je potrebno napraviti svojevrsno „izlaženje” i priznati da čak ni sada ne razumijem u potpunosti što točno rade. Vjerojatno trebam više pročitati o baferiranju ulazno-izlaznih operacija i radu s tvrdim diskom. U nekom trenutku ću to učiniti, a sada mogu samo napisati skriptu koja će provjeriti kako ti vrijednosti utječu na brzinu s kojom se izvodi backup.
Napravio sam malu bazu veličine oko 10 GB, stavio je na SSD, a katalog za backup sam stavio na HDD.
Stvaram privremenu tablicu za pohranu rezultata (za mene nije privremena, kako bih mogao detaljnije pregledati rezultate, ali vi sami odlučite):
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ždene petlje, od kojih svaka mijenja vrijednost jednog parametra, te te parametre uključujem u naredbu BACKUP, pohranjujem posljednji zapis s poviješću iz msdb.dbo.backupset, brišem datoteku sigurnosne kopije i započinjem sljedeću iteraciju. Budući da se podaci o izvršenju sigurnosne kopije temelje na backupsetu, točnost je nešto izgubljena (nema dijelova sekundi), ali to ćemo preživjeti.
Prvo je potrebno omogućiti korištenje xp_cmdshell kako bismo mogli brisati sigurnosne kopije (kasnije ne zaboravite isključiti ako vam nije potreban):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOA sada, u stvari:
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 trebaju objašnjenja o tome što se ovdje događa — slobodno pišite u komentarima ili u privatnim porukama. Za sada ću vam samo reći o parametrima koje uključujem u BACKUP DATABASE.
Za BLOCKSIZE imamo "zatvoren" popis vrijednosti, pri čemu moj backup nije bio izveden s BLOCKSIZE < 4KB. MAXTRANSFERSIZE bilo koji broj višekratnik 64KB — od 64KB do 4MB. Zadano na mom sustavu je 1024KB, ja sam uzeo 512 — 1024 — 2048 — 4096.
Složenije je bilo s BUFFERCOUNT — to može biti bilo koji pozitivan broj, a ovdje piše . Tamo piše kako dobiti informacije o tome s kojim BUFFERCOUNT se stvarno izvodi backup — to je 7 za mene. Nema smisla smanjivati ga, a gornja granica otkrivena je eksperimentalno — pri BUFFERCOUNT = 896 i MAXTRANSFERSIZE = 4194304 backup je pao s greškom (o kojoj piše u prethodnoj vezi):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE se abnormally prekida.
Msg 701, Level 17, State 123, Line 7 Nema dovoljno sistemske memorije u resursnom bazenu ‘default’ za pokretanje ovog upita.
Za usporedbu, prvo ću prikazati rezultate izvođenja backupa bez ikakvih parametara:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Pa, backup je backup:
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 uspješno obradio 1070074 stranice u 53.171 sekundi (157.227 MB/sek).
Sama skripta koja testira parametre radila je nekoliko sati, sve mjere su u . A ovo je uzorak rezultata s tri najbolja vremena izvršenja (pokušao sam napraviti lijep graf, ali, u postu, morat ćemo se zadovoljiti tablicom, a u komentarima dodano ).
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, odmah vrlo važna napomena od iz :
s pouzdanjem se može reći da je veza između parametara i brzine backupa u ovim granicama vrijednosti slučajna, nema nikakve pravilnosti. Ali odstupanje od ugrađenih parametara očito je pozitivno utjecalo na rezultat
Tj. samo upravljanjem standardnim parametrima BACKUP postignut je dobitak u vremenu izvođenja backupa od 2 puta: 26 sekundi, u odnosu na 53 na početku. A to nije loše, zar ne? Ali treba pogledati kako je sa oporavkom. A što ako će sada obnova trajati 4 puta duže?
Prvo, izmjerimo koliko traje obnova backupa s postavkama zadano:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Pa, to već sami znate, putevi, replace-ili ne replace, recovery- ili ne recovery. A ovo se kod mene izvodi 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 je uspješno obradio 1070074 stranice u 40.752 sekundi (205.141 MB/s).
Sada ću pokušati obnoviti sigurnosne kopije napravljene s izmijenjenim BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE je uspješno obradio 1070074 stranice u 32.283 sekunde (258.958 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE je uspješno obradio 1070074 stranice u 32.682 sekundi (255.796 MB/s).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE je uspješno obradio 1070074 stranice u 32.091 sekundi (260.507 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE je uspješno obradio 1070074 stranice u 32.401 sekundi (258.015 MB/s).
Uputa RESTORE DATABASE se ne mijenja pri obnovi, ovi parametri nisu navedeni, SQL Server ih sam određuje prema sigurnosnoj kopiji. I očito je da čak i pri obnovi može biti dobit — gotovo 20% brže (iskreno, nisam posvetio puno vremena obnovi, isprobao sam nekoliko najbržih sigurnosnih kopija i uvjerio se da nema pogoršanja).
Za svaki slučaj ću naglasiti — ovdje nisu opisani neki optimalni parametri za sve. Optimalni parametri za vas možete dobiti samo testiranjem. Ja sam dobio ovakve rezultate, vi ćete dobiti druge. Ali vidite da svoje sigurnosne kopije možete 'podesiti' i da ih stvarno možete brže stvarati i obnavljati.
Također preporučujem da pročitate cijelu dokumentaciju, jer za vaš sustav mogu postojati posebnosti.
Budući da sam počeo pisati o sigurnosnim kopijama, želim odmah pisati i o jednoj 'optimizaciji' koja je česta, više nego 'podešavanje' parametara (barem koliko ja razumijem, koristi je najmanje dio alata za sigurnosno kopiranje, možda zajedno s parametrima iz prethodnog opisa), ali na Habré-u to još nije bilo opisano.
Ako pogledate drugu liniju u dokumentaciji, odmah ispod BACKUP DATABASE, vidimo:
TO [ ,...n ]Što mislite, što će se dogoditi ako navedemo više backup_device-a? Sintaksa to omogućava. A rezultat će biti vrlo zanimljiv — sigurnosna kopija će se samo 'raspršiti' po nekoliko uređaja. To jest, svaki 'uređaj' pojedinačno bit će beskoristan, izgubite jedan, izgubili ste cijelu sigurnosnu kopiju. No, kako će to raspršivanje utjecati na brzinu sigurnosnog kopiranja?
Pokušajmo napraviti sigurnosnu kopiju na dva 'uređaja' koja se nalaze jedan pored drugog u jednoj mapi:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Bože sveti, pa što se ovo događa?
Obrađeno je 1070072 stranica za bazu podataka ‘bt’, datoteka ‘bt’ na datoteci 1.
Obrađene su 2 stranice za bazu podataka 'bt', datoteka 'btlog' na datoteci 1.
BACKUP DATABASE uspješno obrađenih 1070074 stranica u 40.092 sekunde (208.519 MB/s).
Backup je bio 25% brži bez posebnog razloga? 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;BACKUP DATABASE uspješno obrađenih 1070074 stranica u 34.234 sekunde (244.200 MB/s).
Dakle, dobitak od oko 35% vremena za izradu backup-a samo zbog toga što se backup piše odmah u 4 datoteke na jednom disku. Provjerio sam veći broj — na mom laptopu nisam primijetio dobitak, optimalno — 4 uređaja. Za vas — ne znam, treba isprobati. I, usput, ako imate te uređaje — to su stvarno različiti diskovi, čestitam, dobitak bi trebao biti još značajniji.
Sada ćemo razgovarati o tome kako vratiti tu sreću. Da bismo to uradili, morat ćemo promijeniti naredbu za vraćanje 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 uspješno obrađenih 1070074 stranica u 38.027 sekundi (219.842 MB/s).
Malo brže, ali negdje blizu, nije od presudne važnosti. Uglavnom, backup se snima brže, a vraća se jednako — uspjeh? Po mom mišljenju — sasvim dobar uspjeh. To je važno, stoga ću ponoviti — ako izgubite barem jednu od ovih datoteka — gubite cijeli backup.
Ako pogledate informacije o backup-u u dnevniku, ispisane pomoću Trace Flag 3213 i 3605, možete primijetiti da se pri backup-u na nekoliko uređaja povećava, barem, broj BUFFERCOUNT. Možda se mogu pokušati pronaći optimalniji parametri i za BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ali ja to odmah nisam uspio, a raditi takva testiranja ponovno, ali za različit broj datoteka, nisam imao volje. A ni diskovi mi nisu dragi. Ako želite organizirati takvo testiranje kod sebe, prepraviti skriptu nije teško.
Na kraju ćemo razgovarati o cijeni. Ako se backup snima paralelno s radom korisnika — vrlo je važno odgovorno pristupiti testiranju, budući da ako se backup snima brže — diskovi se više opterećuju, opterećenje procesora raste (jer to sve treba još i komprimirati u letu), sukladno tome, smanjuje se ukupna odzivnost sustava.
Šale su šale, ali savršeno razumijem da nisam otkrio ništa novo. Ono što je napisano gore samo je demonstracija kako se mogu odabrati optimalne postavke za izradu sigurnosnih kopija.
Zapamtite da sve što radite radite na svoju odgovornost. Provjeravajte svoje sigurnosne kopije i ne zaboravite na DBCC CHECKDB.
Izvor: habr.com
