MS SQL Server: BACKUP pe steroizi

Aștepta! Aștepta! Adevărat, acesta nu este un alt articol despre tipurile de copii de rezervă SQL Server. Nici măcar nu voi vorbi despre diferențele dintre modelele de recuperare și despre cum să facem față unui buștean crescut.

Poate (doar poate), după ce ați citit această postare, veți putea să vă asigurați că backup-ul care este eliminat de la dvs. folosind mijloace standard va fi eliminat mâine seară, ei bine, de 1.5 ori mai repede. Și doar datorită faptului că folosești puțin mai mulți parametri BACKUP DATABASE.

Dacă conținutul postării a fost evident pentru tine, îmi pare rău. Am citit tot ce a ajuns Google pentru expresia „habr sql server backup”, și în niciun articol nu am găsit vreo mențiune despre faptul că timpul de backup poate fi influențat cumva folosind parametri.

Vă voi atrage imediat atenția asupra comentariului lui Alexander Gladchenko (@mssqlhelp):

Nu modificați niciodată parametrii BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE în producție. Sunt făcute doar pentru a scrie astfel de articole. În practică, veți scăpa de problemele de memorie în cel mai scurt timp.

Desigur, ar fi cool să fii cel mai inteligent și să postezi conținut exclusiv, dar, din păcate, nu este cazul. Există articole/postări atât în ​​limba engleză, cât și în limba rusă (sunt mereu confuz cu privire la cum să le numesc corect) dedicate acestui subiect. Iată câteva dintre cele pe care le-am întâlnit: timp, два, trei (pe sql.ru).

Deci, pentru început, voi atașa o sintaxă BACKUP ușor redusă de la MSDN (apropo, am scris mai sus despre BACKUP DATABASE, dar toate acestea se aplică atât pentru backup-ul jurnalului de tranzacții, cât și pentru backup-ul diferențial, dar poate cu un efect mai puțin evident):

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

<…> - înseamnă că era ceva acolo, dar l-am eliminat pentru că acum nu este relevant pentru subiect.

Cum faci de obicei o copie de rezervă? Cum „învață” cum să faci copii de rezervă în miliarde de articole? În general, dacă trebuie să fac o singură copie de rezervă a unei baze de date nu foarte mari, voi scrie automat ceva de genul:

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

Și, în general, probabil 75-90% din toți parametrii care sunt de obicei menționați în articolele despre backup-uri sunt enumerați aici. Ei bine, există și INIT, SKIP. Ați vizitat MSDN? Ați văzut că există opțiuni pentru un ecran și jumătate? am vazut si eu...

Probabil v-ați dat deja seama că în continuare vom vorbi despre cei trei parametri care au rămas în primul bloc de cod - BLOCKSIZE, BUFFERCOUNT și MAXTRANSFERSIZE. Iată descrierile lor din MSDN:

DIMENSIUNEA BLOC = { bloc | @ blocksize_variable } - indică dimensiunea blocului fizic în octeți. Dimensiunile acceptate sunt 512, 1024, 2048, 4096, 8192, 16, 384 și 32 de octeți (768 KB). Valoarea implicită este 65 pentru dispozitivele de bandă și 536 pentru alte dispozitive. De obicei, acest parametru nu este necesar deoarece instrucțiunea BACKUP selectează automat dimensiunea de bloc adecvată pentru dispozitiv. Setarea dimensiunii blocului anulează în mod explicit selecția automată a dimensiunii blocului.

NUMĂR TAMPON = { buffercount | @ buffercount_variable } - Definește numărul total de buffer-uri I/O care vor fi utilizate pentru operația de backup. Puteți specifica orice valoare întreagă pozitivă, dar un număr mare de buffer-uri poate cauza o eroare de lipsă de memorie din cauza spațiului excesiv de adrese virtuale în procesul Sqlservr.exe.

Cantitatea totală de spațiu utilizată de buffer-uri este determinată de următoarea formulă: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } specifică cea mai mare dimensiune a pachetului de date, în octeți, de schimbat între SQL Server și mediul setului de rezervă. Sunt acceptați multipli de 65 de octeți (536 KB) până la 64 de octeți (4 MB).

Jur - am mai citit asta, dar nu mi-a trecut prin minte cât de mult ar putea avea un impact asupra productivității. Mai mult, se pare că trebuie să fac un fel de „coming out” și să recunosc că nici acum nu înțeleg pe deplin ce fac ei exact. Probabil că trebuie să citesc mai multe despre I/O tamponat și despre lucrul cu un hard disk. Într-o zi voi face asta, dar deocamdată pot doar să scriu un script care să verifice modul în care aceste valori afectează viteza cu care se realizează backup-ul.

Am făcut o bază de date mică, de aproximativ 10 GB, am pus-o pe SSD și am pus directorul pentru copii de rezervă pe HDD.

Creez un tabel temporar pentru a stoca rezultatele (nu îl am temporar, astfel încât să pot săpa în rezultate mai detaliat, dar tu decideți singur):

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 scriptului este simplu - bucle imbricate, fiecare dintre acestea modificând valoarea unui parametru, inserați acești parametri în comanda BACKUP, salvați ultima înregistrare cu istoric din msdb.dbo.backupset, ștergeți fișierul de rezervă și următoarea iterație . Deoarece datele de execuție a backupului sunt preluate din setul de backup, acuratețea este oarecum pierdută (nu există fracțiuni de secunde), dar vom supraviețui.

Mai întâi trebuie să activați xp_cmdshell pentru a șterge copiile de rezervă (apoi nu uitați să-l dezactivați dacă nu aveți nevoie de el):

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

Pai, de fapt:

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

Dacă brusc aveți nevoie de clarificări cu privire la ceea ce se întâmplă aici, scrieți în comentarii sau PM. Deocamdată, vă voi spune doar despre parametrii pe care i-am pus în BACKUP DATABASE.

Pentru BLOCKSIZE avem o listă de valori „închisă”, iar eu nu am efectuat o copie de rezervă cu BLOCKSIZE < 4KB. MAXTRANSFERSIZE orice număr care este un multiplu de 64KB - de la 64KB la 4MB. Valoarea implicită pe sistemul meu este 1024KB, am luat 512 - 1024 - 2048 - 4096.

A fost mai dificil cu BUFFERCOUNT - poate fi orice număr pozitiv, dar link-ul spune cum se calculează în BACKUP DATABASE și de ce sunt periculoase valorile mari?. Mai spune și cum să obții informații despre ce BUFFERCOUNT se realizează backupul - pentru mine este 7. Nu avea niciun rost să o reduc, iar limita superioară a fost descoperită experimental - cu BUFFERCOUNT = 896 și MAXTRANSFERSIZE = 4194304 backupul a căzut cu o eroare (despre care este scrisă în linkul de mai sus):

Mesajul 3013, nivelul 16, starea 1, linia 7 BACKUP DATABASE se termină anormal.

Mesajul 701, Nivel 17, Stare 123, Linia 7 Nu există memorie de sistem insuficientă în pool-ul de resurse „implicit” pentru a rula această interogare.

Pentru comparație, voi arăta mai întâi rezultatele rulării unei copii de rezervă fără a specifica niciun parametru:

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

Ei bine, backup și backup:

Au fost procesate 1070072 pagini pentru baza de date „bt”, fișierul „bt” din fișierul 1.

S-au procesat 2 pagini pentru baza de date „bt”, fișierul „bt_log” din fișierul 1.

BACKUP DATABASE a procesat cu succes 1070074 pagini în 53.171 secunde (157.227 MB/sec).

Scriptul în sine, testând parametrii, a funcționat în câteva ore, toate măsurătorile au fost în foaie de calcul google. Și iată o selecție de rezultate cu cei mai buni trei timpi de execuție (am încercat să fac un grafic frumos, dar în postare va trebui să mă mulțumesc cu un tabel și în comentarii @mixsture El a adăugat grafica foarte tare).

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;

Atentie, o nota foarte importanta de la @mixsture de comentarii:

Putem spune cu încredere că relația dintre parametri și viteza de backup în aceste intervale de valori este aleatorie, nu există un model. Dar îndepărtarea de parametrii încorporați a avut evident un efect bun asupra rezultatului

Acestea. Numai prin gestionarea parametrilor standard de BACKUP a fost un câștig de 2 ori în timpul de eliminare a backupului: 26 de secunde, față de 53 la început. Nu e rău, nu? Dar trebuie să vedem ce se întâmplă cu restaurarea. Ce se întâmplă dacă acum durează de 4 ori mai mult să se recupereze?

În primul rând, să măsurăm cât timp durează restabilirea unei copii de rezervă cu setările implicite:

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

Ei bine, tu însuți știi că, căile există, înlocuirea nu înseamnă înlocuirea, recuperarea nu este recuperarea. Și o fac așa:

Au fost procesate 1070072 pagini pentru baza de date „bt”, fișierul „bt” din fișierul 1.

S-au procesat 2 pagini pentru baza de date „bt”, fișierul „bt_log” din fișierul 1.

RESTORE DATABASE a procesat cu succes 1070074 pagini în 40.752 secunde (205.141 MB/sec).

Acum voi încerca să restabilesc copiile de rezervă făcute cu BLOCKSIZE, BUFFERCOUNT și MAXTRANSFERSIZE modificate.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE a procesat cu succes 1070074 pagini în 32.283 secunde (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE a procesat cu succes 1070074 pagini în 32.682 secunde (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE a procesat cu succes 1070074 pagini în 32.091 secunde (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE a procesat cu succes 1070074 pagini în 32.401 secunde (258.015 MB/sec).

Instrucțiunea RESTORE DATABASE nu se modifică în timpul recuperării; acești parametri nu sunt specificați în ea; SQL Server însuși îi determină din backup. Și este clar că chiar și cu recuperare poate exista un câștig - aproape 20% mai rapid (Sincer să fiu, nu am petrecut mult timp recuperării, am parcurs câteva dintre cele mai „rapide” copii de rezervă și m-am asigurat că nu există nicio deteriorare).

Pentru orice eventualitate, permiteți-mi să clarific că aceștia nu sunt niște parametri optimi pentru toată lumea. Puteți obține parametrii optimi numai prin testare. Am obținut aceste rezultate, veți obține altele diferite. Dar vedeți că vă puteți „regla” backup-urile și acestea se pot forma și implementa mai repede.

De asemenea, vă recomand cu tărie să citiți documentația în întregime, deoarece pot exista nuanțe specifice sistemului dumneavoastră.

De când am început să scriu despre backup, vreau să scriu imediat despre încă o „optimizare”, care este mai comună decât parametrii de „tuning” (din câte am înțeles, este folosit de cel puțin unele utilitare de backup, poate împreună cu parametrii descris mai devreme), dar nu a fost încă descris nici pe Habré.

Dacă ne uităm la a doua linie din documentație, chiar sub BACKUP DATABASE, acolo vedem:

TO <backup_device> [ ,...n ]

Ce credeți că se va întâmpla dacă specificați mai multe backup_devices? Sintaxa o permite. Și se va întâmpla un lucru foarte interesant - backup-ul va fi pur și simplu „împrăștiat” pe mai multe dispozitive. Acestea. fiecare „dispozitiv” individual va fi inutil, pierdut unul, pierdut întreaga copie de rezervă. Dar cum va afecta astfel de murdare viteza de backup?

Să încercăm să facem o copie de rezervă pe două „dispozitive” care sunt situate unul lângă altul în același folder:

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

Părinți ai lumii, de ce se face asta?

Au fost procesate 1070072 pagini pentru baza de date „bt”, fișierul „bt” din fișierul 1.

Procesate 2 pagini pentru baza de date „bt”, fișierul „bt”log' pe fișierul 1.

BACKUP DATABASE a procesat cu succes 1070074 pagini în 40.092 secunde (208.519 MB/sec).

Backup-ul a devenit din senin cu 25% mai rapid? Ce se întâmplă dacă mai adăugăm 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 secunde (244.200 MB/sec).

În total, câștigul este de aproximativ 35% din timpul realizării unei copii de rezervă doar datorită faptului că backupul este scris pe 4 fișiere de pe un disc deodată. Am verificat un număr mai mare - nu există niciun câștig pe laptopul meu, în mod optim - 4 dispozitive. Pentru tine - nu știu, trebuie să verifici. Ei bine, apropo, dacă aveți aceste dispozitive - acestea sunt discuri cu adevărat diferite, felicitări, câștigul ar trebui să fie și mai semnificativ.

Acum să vorbim despre cum să restabilim această fericire. Pentru a face acest lucru, va trebui să modificați comanda de recuperare și să enumerați 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 secunde (219.842 MB/sec).

Puțin mai rapid, dar undeva aproape, nu semnificativ. În general, backup-ul este eliminat mai repede, dar restaurat în același mod - succes? În ceea ce mă privește, este un succes. Acest este important, așa că repet - dacă tu dacă pierdeți cel puțin unul dintre aceste fișiere, pierdeți întreaga copie de rezervă.

Dacă vă uitați în jurnal la informațiile de backup afișate folosind semnalizatoarele de urmărire 3213 și 3605, veți observa că atunci când faceți copii de rezervă pe mai multe dispozitive, cel puțin numărul de BUFFERCOUNT crește. Probabil, puteți încerca să selectați parametri mai optimi pentru BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, dar nu am reușit imediat și mi-a fost prea lene să efectuez din nou astfel de teste, dar pentru un număr diferit de fișiere. Și este păcat de roți. Dacă doriți să organizați astfel de testare acasă, nu este greu să refaceți scenariul.

În sfârșit, să vorbim despre preț. Dacă backup-ul este eliminat în paralel cu munca utilizatorilor, trebuie să adoptați o abordare foarte responsabilă în ceea ce privește testarea, deoarece dacă backup-ul este eliminat mai repede, discurile sunt încordate mai mult, sarcina procesorului crește (mai trebuie să comprimați este din mers) și, în consecință, capacitatea de răspuns generală a sistemului scade.

Glumesc, dar înțeleg perfect că nu am făcut nicio revelație. Ceea ce este scris mai sus este pur și simplu o demonstrație a modului în care puteți selecta parametrii optimi pentru a face copii de rezervă.

Amintiți-vă că tot ceea ce faceți se face pe propriul risc și risc. Verificați copiile de siguranță și nu uitați de DBCC CHECKDB.

Sursa: www.habr.com