Așteptați! Așteptați! Adevărul este că aceasta nu este încă o altă articol despre tipurile de backup-uri SQL Server. Nici măcar nu voi vorbi despre diferențele între modelele de recuperare și cum să gestionăm un „log” crescut.
Poate (doar poate), după ce veți citi acest post, veți putea face ca backup-ul care se realizează cu uneltele standard să fie efectuat, să zicem, cu 1,5 ori mai repede în noaptea de mâine. Și aceasta doar prin utilizarea câtorva parametri în plus pentru BACKUP DATABASE.
Dacă pentru voi conținutul postului a fost evident — îmi pare rău. Am citit tot ce mi-a ieșit în cale prin Google căutând „habr sql server backup”, iar în niciun articol nu am găsit mențiunea că, în timpul backup-ului, ar putea fi influențat cumva prin parametri.
Imediat atrag atenția asupra comentariului lui Alexander Gladchenko ():
Nu modificați niciodată parametrii BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE în producție. Acestea sunt făcute doar pentru a scrie astfel de articole. În practică, veți avea probleme cu memoria.
Ar fi, desigur, grozav să fiu cel mai inteligent și să public conținut exclusiv, dar, din păcate, nu este chiar așa. Există articole/postări atât în limba engleză, cât și în limba rusă (întotdeauna mă confuz cum să le numesc corect) dedicate acestei teme. Iată o parte dintre cele ce mi-au ieșit în cale: , , .
Așadar, mai întâi voi atașa un sintax simplificat al BACKUP din (de altfel, acolo sus am menționat BACKUP DATABASE, dar tot ce se aplică și pentru backup-ul jurnalului de tranzacții, și pentru backup-ul diferențial, deși poate cu un efect mai puțin evident):
BACKUP DATABASE { database_name | @database_name_var }
TO [ ,...n ]
[ WITH {
| [ ,...n ] } ]
[;]
[ ,...n ]::=
--Opțiuni Set Media
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Opțiuni Transfer Date
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }— înseamnă că acolo a fost ceva, dar am scos pentru că acum nu este relevant pentru subiect.
Cum realizați de obicei backup-ul? Cum „învăță” să se realizeze backup-uri în miliardele de articole? De fapt, dacă va fi nevoie să fac un backup rapid pentru o bază de date nu foarte mare, voi scrie automat ceva de genul:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmsth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
-- bine, CHECKSUM l-am scris doar ca să par mai deșteptȘi, în general, aici sunt enumerate, probabil, 75-90% din toți parametrii care sunt menționați de obicei în articolele despre backup-uri. De exemplu, INIT, SKIP etc. Ați mers la MSDN? Ați văzut câte opțiuni sunt acolo, cam cât o pagină și jumătate? Eu am văzut…
Probabil că ați înțeles deja că mai departe va fi vorba despre cei trei parametri care au rămas în primul bloc de cod — BLOCKSIZE, BUFFERCOUNT și MAXTRANSFERSIZE. Iată descrierile lor din MSDN:
BLOCKSIZE = { blocksize | @ blocksize_variable } — indică dimensiunea blocului fizic în octeți. Sunt acceptate dimensiuni de 512, 1024, 2048, 4096, 8192, 16 384, 32 768 și 65 536 octeți (64 KB). Valoarea prestabilită este de 65 536 pentru dispozitivele de tip bandă și 512 pentru celelalte dispozitive. De obicei, nu este necesară această parametru, întrucât comanda BACKUP selectează automat dimensiunea blocului care corespunde dispozitivului. Stabilirea explicită a dimensiunii blocului va suprascrie selecția automată.
BUFFERCOUNT = { buffercount | @ buffercount_variable } — determină numărul total de bufferi de intrare și ieșire care vor fi utilizați pentru operația de backup. Poate fi specificată orice valoare întreagă pozitivă, totuși un număr mare de bufferi poate provoca o eroare de insuficiență de memorie din cauza unui spațiu virtual adresat excesiv în procesul Sqlservr.exe.
Volumul total de spațiu utilizat de buffere este definit prin următoarea formulă:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } indică volumul maxim de pachet de date în octeți pentru transferul de date între SQL Server și suportul de backup. Sunt acceptate valori multiple de 65 536 octeți (64 KB), până la 4 194 304 octeți (4 MB).
Jur că am citit asta mai devreme, dar nu mi-a trecut prin minte ce influență ar putea avea asupra performanței. Mai mult decât atât, se pare că trebuie să fac un fel de „coming out” și să recunosc că nici acum nu înțeleg complet ce anume fac ele. Probabil că ar trebui să citesc mai multe despre intrarea și ieșirea bufferizată și lucrul cu hard disk-ul. Cândva voi face asta, iar acum pot să scriu pur și simplu un script care să verifice cum aceste valori influențează viteza cu care se face backup-ul.
Am creat o mică bază, de aproximativ 10 GB, și am pus-o pe SSD, iar catalogul pentru backup-uri l-am plasat pe HDD.
Creez o tabelă temporară pentru a stoca rezultatele (pentru mine nu este temporară, pentru a putea explora rezultatele mai în detaliu, dar decideți singuri):
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
);Principiul de funcționare al scriptului este simplu — cicluri încastrate, fiecare dintre acestea schimbând valoarea unui parametru, introduc acești parametri în comanda BACKUP, salvez ultima înregistrare cu istoria din msdb.dbo.backupset, șterg fișierul de backup și trec la iterația următoare. Deoarece datele referitoare la execuția backup-ului sunt preluate din backupset, precizia este oarecum pierdută (nu există fracțiuni de secundă acolo), dar vom face față.
Mai întâi, trebuie să permit utilizarea xp_cmdshell pentru a șterge backup-urile (apoi nu uitați să o dezactivați dacă nu aveți nevoie de ea):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOȘi, în esență:
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);
n /* VALORI BLOCKSIZE */
DECLARE @bs int = 4096,
@max_bs int = 65536;
n /* VALORI BUFFERCOUNT */
DECLARE @bc int = 7,
@min_bc int = 7,
@max_bc int = 800;
n /* VALORI MAXTRANSFERSIZE */
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;
ENDDacă aveți nevoie de explicații cu privire la ceea ce se întâmplă aici — scrieți în comentarii sau în privat. Până atunci, voi vorbi doar despre parametrii pe care îi introduc în BACKUP DATABASE.
Pentru BLOCKSIZE avem o listă "închisă" de valori, iar eu nu am reușit să fac un backup cu BLOCKSIZE < 4KB. MAXTRANSFERSIZE poate fi orice număr multiplu de 64KB — de la 64KB la 4MB. Implicit, pe sistemul meu este 1024KB, eu am ales 512 — 1024 — 2048 — 4096.
Mai complicat a fost cu BUFFERCOUNT — acesta poate fi orice număr pozitiv, iar pe link scrie . Acolo, de asemenea, este explicat cum să obții informații despre cu ce BUFFERCOUNT se face efectiv backupul — la mine este 7. Nu avea rost să-l reduc, iar limita superioară a fost descoperită empiric — cu BUFFERCOUNT = 896 și MAXTRANSFERSIZE = 4194304, backupul a căzut cu o eroare (despre care este menționat pe linkul de mai sus):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE se termină anormal.
Msg 701, Level 17, State 123, Line 7 Nu există suficientă memorie de sistem în pool-ul de resurse 'default' pentru a rula această interogare.
Pentru comparație, mai întâi voi arăta rezultatele efectuării backupului fără a specifica parametrii deloc:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Ei bine, backup și backup:
Am procesat 1070072 pagini pentru baza de date ‘bt’, fișier ‘bt’ pe fișier 1.
Am procesat 2 pagini pentru baza de date ‘bt’, fișier ‘bt_log’ pe fișier 1.
BACKUP DATABASE a procesat cu succes 1070074 pagini în 53.171 secunde (157.227 MB/sec).
Întreg scriptul, care testează parametrii, a durat câteva ore, toate măsurătorile în . Iată o selecție a rezultatelor, având cele mai bune trei timpi de execuție (am încercat să fac un grafic frumos, dar în post, va trebui să ne mulțumim cu un tabel, iar în comentarii am adăugat ).
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;
Atenție, imediat o observație foarte importantă de la din :
se poate spune cu încredere că legătura între parametri și viteza de backup în aceste limite de valori este randomizată, nu există nicio regularitate. Dar abaterile de la parametrii încorporați, evident, au influențat pozitiv rezultat.
Adică, doar prin gestionarea parametrilor standard BACKUP s-a obținut un câștig în timpul realizării backupului de 2 ori: 26 de secunde, comparativ cu 53 la început. Și nu e rău, nu-i așa? Dar trebuie să vedem ce se întâmplă cu restaurarea. Ce dacă acum restaurarea va dura de 4 ori mai mult?
Pentru început, să măsurăm cât durează restaurarea backupului cu setările implicite:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Ei bine, asta știți și voi, căile acolo, replace- nu replace, recovery- nu recovery. Și eu execut asta așa:
Am procesat 1070072 pagini pentru baza de date ‘bt’, fișier ‘bt’ pe fișier 1.
Am procesat 2 pagini pentru baza de date ‘bt’, fișier ‘bt_log’ pe fișier 1.
RESTORE DATABASE a procesat cu succes 1070074 pagini în 40.752 secunde (205.141 MB/sec).
Acum voi încerca să restaurez backup-urile efectuate cu BLOCKSIZE, BUFFERCOUNT și MAXTRANSFERSIZE modificate.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE a procesat cu succes 1070074 pagini în 32.283 secunde (258.958 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE a procesat cu succes 1070074 pagini în 32.682 secunde (255.796 MB/sec).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE a procesat cu succes 1070074 pagini în 32.091 secunde (260.507 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE a procesat cu succes 1070074 pagini în 32.401 secunde (258.015 MB/sec).
Instrucțiunea RESTORE DATABASE nu se modifică la restaurare, nu se specifică aceste parametru, SQL Server le determină singur din backup. Și se observă că, chiar și la restaurare, se poate câștiga — aproape cu 20% mai repede (sincer, nu am dedicat mult timp restabilirii, am testat câteva dintre cele mai „rapide” backup-uri și m-am convins că nu există degradări).
Din precauție, voi sublinia — aici sunt descrise niște parametrii care nu sunt optimi pentru toți. Parametrii optimi pentru tine îi poți obține doar prin testare. Eu am obținut aceste rezultate, tu vei obține altele. Dar vezi că backup-urile tale pot fi „optimizarte” și se pot crea și restaura într-un mod real mai repede.
De asemenea, recomand cu tărie să citești documentația în întregime, deoarece pot exista nuanțe specifice sistemului tău.
Deoarece am început să scriu despre backup-uri, vreau imediat să menționez și o altă „optimizare” care apare mai des decât „tuning”-ul parametrilor (aceasta, din câte știu, este folosită de cel puțin o parte dintre utilitarele de backup, posibil împreună cu parametrii descriși anterior), dar pe Habr nu a fost încă descrisă.
Dacă ne uităm la al doilea rând din documentație, imediat sub BACKUP DATABASE, vedem:
TO [, ...n]Ce credeți că se va întâmpla dacă specificați mai multe backup_device-uri? Sintaxa permite. Și va fi un lucru foarte interesant — backup-ul se va „întinde” pe mai multe dispozitive. Adică fiecare „dispozitiv” separat va fi inutil, dacă pierzi unul, pierzi tot backup-ul. Dar cum va afecta această întindere viteza de backup?
Să încercăm să facem un backup pe două „dispozitive” care se află aproape unul de altul într-un singur dosar:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Doamne, ce se întâmplă aici?
Am procesat 1070072 pagini pentru baza de date ‘bt’, fișier ‘bt’ pe fișier 1.
S-au procesat 2 pagini pentru baza de date ‘bt’, fișier ‘btlog’ în fișierul 1.
BACKUP DATABASE a procesat cu succes 1070074 pagini în 40.092 de secunde (208.519 MB/sec).
Backup-ul s-a realizat cu 25% mai repede fără niciun motiv aparent? Și dacă mai adăugăm alte câteva dispozitive?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE a procesat cu succes 1070074 pagini în 34.234 de secunde (244.200 MB/sec).
În total, câștigul este de aproximativ 35% din timpul necesar pentru backup, doar pentru că backup-ul este scris simultan în 4 fișiere pe același disc. Am verificat cu un număr mai mare — pe laptopul meu câștigul a fost absent, optim este 4 dispozitive. Nu știu pentru voi — trebuie să verificați. Și, de fapt, dacă aveți aceste dispozitive — sunt discuri diferite, vă felicit, câștigul ar trebui să fie și mai semnificativ.
Acum să discutăm despre cum se poate restaura acest backup. Pentru aceasta va trebui să modificăm comanda de restaurare și să enumerăm toate dispozitivele:
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 a procesat cu succes 1070074 pagini în 38.027 de secunde (219.842 MB/sec).
Puțin mai repede, dar undeva în apropiere, nu semnificativ. În general, backup-ul se realizează mai rapid, iar restaurarea se face la fel — este un succes? Din punctul meu de vedere — cu siguranță este un succes. Este important, așa că voi repeta — dacă pierdeți măcar unul dintre aceste fișiere — pierdeți tot backup-ul Dacă verificăm informațiile din jurnalul backup-ului, afișate cu ajutorul Trace Flag 3213 și 3605, putem observa că, atunci când backup-ul este realizat pe mai multe dispozitive, se crește, cel puțin, numărul BUFFERCOUNT. Probabil, ar trebui să încercăm să ajustăm parametrii pentru BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, dar eu nu am reușit rapid, iar să efectuez din nou astfel de teste cu un număr diferit de fișiere am fost prea leneș. De asemenea, îmi este milă de discuri. Dacă doriți să organizați un astfel de test la voi, nu este greu să adaptați scriptul..
La final, să discutăm despre preț. Dacă backup-ul se realizează în paralel cu activitatea utilizatorilor — trebuie să abordăm testarea cu maximă responsabilitate, deoarece, dacă backup-ul se face mai repede — discurile se solicită mai mult, încărcătura pe procesor crește (trebuie să comprimați totul în timp real), prin urmare, reacția generală a sistemului scade.
La final vom discuta despre preț. Dacă backup-ul este realizat în paralel cu activitatea utilizatorilor, este necesar să abordăm cu multă responsabilitate testarea, deoarece dacă backup-ul se face mai repede, unitățile de stocare sunt mai solicitate, iar sarcina pe procesor crește (trebuie să comprimăm totul în timp real), ceea ce duce la o scădere a capacității generale de răspuns a sistemului.
Glumele sunt glume, dar înțeleg perfect că nu am adus nicio descoperire revelatoare. Ceea ce am scris mai sus este pur și simplu o demonstrație a modului în care se pot selecta parametrii optimi pentru realizarea copiilor de rezervă.
Amintiți-vă că tot ceea ce faceți – faceți pe propria răspundere. Verificați copiile de rezervă și nu uitați de DBCC CHECKDB.
Sursa: habr.com
