Laukti! Laukti! Tiesa, tai ne kitas straipsnis apie SQL Server atsarginių kopijų tipus. Net nekalbėsiu apie atkūrimo modelių skirtumus ir kaip elgtis su peraugusiu rąstu.
Galbūt (tik galbūt), perskaitę šį įrašą, galėsite įsitikinti, kad atsarginė kopija, kuri iš jūsų pašalinama naudojant standartines priemones, bus pašalinta rytoj vakare, na, 1.5 karto greičiau. Ir tik dėl to, kad naudojate šiek tiek daugiau BACKUP DATABASE parametrų.
Jei įrašo turinys jums buvo akivaizdus, atsiprašau. Perskaičiau viską, ką Google gavo dėl frazės „habr sql serverio atsarginė kopija“, ir ne viename straipsnyje neradau paminėjimo apie tai, kad atsarginės kopijos trukmę galima kažkaip paveikti naudojant parametrus.
Nedelsdamas atkreipsiu jūsų dėmesį į Aleksandro Gladčenkos komentarą ():
Gamyboje niekada nekeiskite parametrų BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE. Jie skirti tik tokiems straipsniams rašyti. Praktiškai atminties problemų atsikratysite akimirksniu.
Žinoma, būtų puiku būti protingiausiam ir skelbti išskirtinį turinį, bet, deja, taip nėra. Šiai temai yra ir anglų, ir rusų kalbų straipsnių/įrašų (man visada neaišku, kaip teisingai juos pavadinti). Štai keletas, su kuriais susidūriau: , , .
Taigi, pirmiausia pridėsiu šiek tiek sumažintą ATSARGINĖS KOPĖS sintaksę iš (beje, aukščiau rašiau apie ATSARGINĖS DUOMENŲ BAZĘ, bet visa tai galioja tiek operacijų žurnalo, tiek diferencinės atsarginės kopijos kūrimui, bet galbūt su mažiau akivaizdžiu efektu):
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 }
<...><…> - tai reiškia, kad ten kažkas buvo, bet aš jį pašalinau, nes dabar tai nesusiję su tema.
Kaip dažniausiai darote atsarginę kopiją? Kaip jie „moko“, kaip daryti atsargines milijardų straipsnių kopijas? Apskritai, jei man reikia padaryti vienkartinę ne itin didelės duomenų bazės atsarginę kopiją, automatiškai parašysiu maždaug taip:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееIr apskritai čia surašyta turbūt 75-90% visų parametrų, kurie dažniausiai minimi straipsniuose apie atsargines kopijas. Na, dar yra INIT, SKIP. Ar lankėtės MSDN? Ar matėte, kad yra pusantro ekrano variantų? as irgi maciau...
Tikriausiai jau supratote, kad toliau kalbėsime apie tris parametrus, kurie liko pirmame kodo bloke - BLOCKSIZE, BUFFERCOUNT ir MAXTRANSFERSIZE. Štai jų aprašymai iš MSDN:
BLOKO DYDIS = { bloko dydis | @ blocksize_kintamasis } – nurodo fizinį bloko dydį baitais. Palaikomi dydžiai yra 512, 1024, 2048, 4096, 8192, 16 384, 32 768 ir 65 536 baitai (64 KB). Numatytoji reikšmė yra 65 juostiniams įrenginiams ir 536 kitiems įrenginiams. Paprastai šis parametras nėra būtinas, nes BACKUP sakinys automatiškai parenka atitinkamą įrenginio bloko dydį. Bloko dydžio nustatymas aiškiai pakeičia automatinį bloko dydžio pasirinkimą.
BUFERIŲ SKAIČIUS = { buferinis skaičius | @ buferio skaičius_kintamasis } – apibrėžiamas bendras įvesties/išvesties buferių skaičius, kuris bus naudojamas kuriant atsarginę kopiją. Galite nurodyti bet kokią teigiamą sveikojo skaičiaus reikšmę, tačiau dėl didelio buferių skaičiaus gali atsirasti atminties trūkumo klaida dėl pernelyg didelės virtualios adresų vietos Sqlservr.exe procese.
Bendras buferių naudojamos vietos kiekis nustatomas pagal šią formulę:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maksimalus perkėlimas | @ maxtransfersize_kintamasis } nurodo didžiausią duomenų paketo dydį baitais, kuriuo keičiamasi tarp SQL serverio ir atsarginės kopijos rinkinio laikmenos. Palaikomi keli 65 536 baitų (64 KB) iki 4 194 304 baitų (4 MB).
Prisiekiu – skaičiau tai anksčiau, bet nė į galvą neatėjo, kokią įtaką jos gali turėti produktyvumui. Be to, matyt, reikia padaryti savotišką „išėjimą“ ir pripažinti, kad net ir dabar iki galo nesuprantu, ką jie daro. Tikriausiai man reikia daugiau paskaityti apie buferinį I/O ir darbą su kietuoju disku. Kada nors tai padarysiu, bet kol kas galiu tiesiog parašyti scenarijų, kuris patikrins, kaip šios reikšmės įtakoja atsarginės kopijos kūrimo greitį.
Sukūriau nedidelę duomenų bazę, apie 10 GB dydžio, įdėjau į SSD, o atsarginių kopijų katalogą įdėjau į HDD.
Sukuriu laikiną lentelę rezultatams saugoti (neturiu jos laikinos, todėl galiu pasigilinti į rezultatus išsamiau, bet jūs nuspręsite patys):
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
);Scenarijaus veikimo principas paprastas – įdėtos kilpos, kurių kiekvienas pakeičia vieno parametro reikšmę, įterpiate šiuos parametrus į komandą BACKUP, išsaugokite paskutinį įrašą su istorija iš msdb.dbo.backupset, ištrinkite atsarginės kopijos failą ir kitą iteraciją . Kadangi atsarginės kopijos vykdymo duomenys yra paimti iš atsarginės kopijos, tikslumas šiek tiek prarandamas (sekundžių nėra), bet mes tai išgyvensime.
Pirmiausia turite įjungti xp_cmdshell, kad ištrintumėte atsargines kopijas (tada nepamirškite jos išjungti, jei jums to nereikia):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GONa, iš tikrųjų:
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;
ENDJei staiga reikia paaiškinimo, kas čia vyksta, rašykite komentaruose arba PM. Kol kas papasakosiu tik apie parametrus, kuriuos įdėjau ATSARGINĖJE DUOMENŲ BAZĖJE.
BLOCKSIZE turime „uždarą“ verčių sąrašą, o atsarginės kopijos nedariau, kai BLOCKSIZE < 4 KB. MAXTRANSFERSIZE bet kokį skaičių, kuris yra 64 KB kartotinis – nuo 64 KB iki 4 MB. Numatytasis mano sistemos dydis yra 1024 KB, aš paėmiau 512 - 1024 - 2048 - 4096.
Su BUFFERCOUNT buvo sunkiau – tai gali būti bet koks teigiamas skaičius, bet nuoroda sako . Taip pat parašyta, kaip gauti informaciją, su kuriuo BUFFERCOUNT iš tikrųjų daroma atsarginė kopija – man tai 7. Mažinti nebuvo prasmės, o viršutinė riba buvo atrasta eksperimentiškai – esant BUFFERCOUNT = 896 ir MAXTRANSFERSIZE = 4194304 atsarginė kopija nukrito su klaida (apie kurią parašyta aukščiau esančioje nuorodoje):
Pranešimas 3013, 16 lygis, 1 būsena, 7 eilutė ATSARGINĖS DUOMENŲ BAZĖS nutraukimas neįprastai.
701 žinutė, 17 lygis, 123 būsena, 7 eilutė Nepakanka sistemos atminties išteklių telkinyje „numatytasis“, kad būtų galima vykdyti šią užklausą.
Palyginimui pirmiausia parodysiu atsarginės kopijos vykdymo rezultatus, nenurodydamas jokių parametrų:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Na, atsarginė kopija ir atsarginė kopija:
Apdoroti 1070072 duomenų bazės „bt“ puslapiai, 1 failo failas „bt“.
Apdoroti 2 duomenų bazės „bt“ puslapiai, 1 failo failas „bt_log“.
ATSARGINĖ DUOMENŲ BAZĖ sėkmingai apdorojo 1070074 puslapius per 53.171 sekundės (157.227 MB/sek).
Pats scenarijus, testuojant parametrus, suveikė per porą valandų, visi matavimai buvo įvesti . O čia yra atrinkti rezultatai su trimis geriausiais vykdymo laikais (bandžiau padaryti gražų grafiką, bet įraše teks tenkintis su lentele, o komentaruose pridėta ).
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;
Dėmesio, labai svarbi pastaba iš iš :
Galime drąsiai teigti, kad ryšys tarp parametrų ir atsarginio greičio šiuose reikšmių diapazonuose yra atsitiktinis, nėra jokio modelio. Tačiau atitolimas nuo įmontuotų parametrų akivaizdžiai turėjo gerą poveikį rezultatui
Tie. Tik valdant standartinius BACKUP parametrus atsarginės kopijos pašalinimo laikas padidėjo 2 kartus: 26 sekundės, palyginti su 53 pradžioje. Tai nėra blogai, tiesa? Bet mes turime pamatyti, kas atsitiks su atkūrimu. O jei dabar atsigauti prireiks 4 kartus ilgiau?
Pirmiausia išmatuokite, kiek laiko reikia atkurti atsarginę kopiją naudojant numatytuosius nustatymus:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Na, jūs patys tai žinote, būdų yra, pakeisti nėra pakeisti, pasveikimas nėra pasveikimas. Ir darau taip:
Apdoroti 1070072 duomenų bazės „bt“ puslapiai, 1 failo failas „bt“.
Apdoroti 2 duomenų bazės „bt“ puslapiai, 1 failo failas „bt_log“.
RESTORE DATABASE sėkmingai apdorojo 1070074 puslapius per 40.752 sekundes (205.141 MB/sek).
Dabar pabandysiu atkurti atsargines kopijas, padarytas su pakeistais BLOCKSIZE, BUFFERCOUNT ir MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE sėkmingai apdorojo 1070074 puslapius per 32.283 sekundes (258.958 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE sėkmingai apdorojo 1070074 puslapius per 32.682 sekundes (255.796 MB/sek).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE sėkmingai apdorojo 1070074 puslapius per 32.091 sekundes (260.507 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE sėkmingai apdorojo 1070074 puslapius per 32.401 sekundes (258.015 MB/sek).
Atkūrimo metu sakinys RESTORE DATABASE nesikeičia, šie parametrai jame nenurodyti, pats SQL Server juos nustato iš atsarginės kopijos. Ir aišku, kad net atsigavus galima gauti naudos - beveik 20% greičiau (Tiesą sakant, aš neskyriau daug laiko atkūrimui, perėjau kelias „greičiausias“ atsargines kopijas ir įsitikinau, kad nėra pablogėjimo).
Tik tuo atveju leiskite paaiškinti, kad tai nėra kai kurie parametrai, kurie yra optimalūs visiems. Tik testuodami galite gauti sau optimalius parametrus. Aš gavau tokius rezultatus, o jūs gausite kitokius. Tačiau matote, kad galite „sureguliuoti“ savo atsargines kopijas ir jos iš tikrųjų gali būti sukurtos ir įdiegtos greičiau.
Taip pat primygtinai rekomenduoju perskaityti visą dokumentaciją, nes gali būti niuansų, būdingų jūsų sistemai.
Kadangi pradėjau rašyti apie atsargines kopijas, iš karto noriu parašyti dar vieną "optimizavimą", kuris yra labiau paplitęs nei parametrų "tiuningas" (kiek suprantu jį naudoja bent kai kurios atsarginės kopijos, galbūt kartu su parametrais aprašyta anksčiau), tačiau ji dar nebuvo aprašyta ir Habré.
Jei pažvelgsime į antrąją dokumentacijos eilutę, esančią tiesiai po ATSARGINĖS KOPĖJIMO DUOMENŲ BAZĖS, pamatysime:
TO <backup_device> [ ,...n ]Kaip manote, kas nutiks, jei nurodysite kelis atsarginius_įrenginius? Sintaksė tai leidžia. Ir atsitiks labai įdomus dalykas - atsarginė kopija bus tiesiog „paskleista“ keliuose įrenginiuose. Tie. kiekvienas „įrenginys“ atskirai bus nenaudingas, pamestas vienas, prarasta visa atsarginė kopija. Tačiau kaip toks sutepimas paveiks atsarginio kopijavimo greitį?
Pabandykime sukurti atsarginę kopiją dviejuose „įrenginiuose“, kurie yra vienas šalia kito tame pačiame aplanke:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Pasaulio tėvai, kodėl tai daroma?
Apdoroti 1070072 duomenų bazės „bt“ puslapiai, 1 failo failas „bt“.
Apdoroti 2 duomenų bazės „bt“ puslapiai, failas „bt“prisijungti prie 1 failo.
ATSARGINĖ DUOMENŲ BAZĖ sėkmingai apdorojo 1070074 puslapius per 40.092 sekundės (208.519 MB/sek).
Ar atsarginė kopija tapo 25 % greitesnė tiesiog netikėtai? Ką daryti, jei pridėsime dar porą įrenginių?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;ATSARGINĖ DUOMENŲ BAZĖ sėkmingai apdorojo 1070074 puslapius per 34.234 sekundės (244.200 MB/sek).
Iš viso pelnas yra apie 35% atsarginės kopijos darymo laiko vien dėl to, kad atsarginė kopija įrašoma į 4 failus viename diske vienu metu. Patikrinau didesnį skaičių – nešiojamajame kompiuteryje prieaugio nėra, optimaliai – 4 įrenginiai. Jums - aš nežinau, jums reikia patikrinti. Na, beje, jei turite šiuos įrenginius - tai tikrai skirtingi diskai, sveikiname, pelnas turėtų būti dar reikšmingesnis.
Dabar pakalbėkime apie tai, kaip atkurti šią laimę. Norėdami tai padaryti, turėsite pakeisti atkūrimo komandą ir išvardyti visus įrenginius:
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 sėkmingai apdorojo 1070074 puslapius per 38.027 sekundes (219.842 MB/sek).
Šiek tiek greičiau, bet kažkur arti, nereikšminga. Apskritai atsarginė kopija pašalinama greičiau ir atkuriama taip pat – sėkmės? Kalbant apie mane, tai gana sėkminga. Tai svarbus, todėl kartoju – jei tu jei prarasite bent vieną iš šių failų, prarasite visą atsarginę kopiją.
Jei žurnale pažvelgsite į atsarginės kopijos informaciją, rodomą naudojant Trace Flags 3213 ir 3605, pastebėsite, kad kuriant atsargines kopijas keliuose įrenginiuose, bent jau padidėja BUFFERCOUNT skaičius. Tikriausiai galite pabandyti pasirinkti optimalesnius parametrus BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, bet man nepavyko iš karto ir tingėjau pakartoti tokį testavimą, bet skirtingą failų skaičių. O dėl ratų gaila. Jei norite tokį testavimą organizuoti namuose, scenarijų perdaryti nėra sunku.
Galiausiai pakalbėkime apie kainą. Jei atsarginė kopija pašalinama lygiagrečiai su vartotojų darbu, reikia labai atsakingai žiūrėti į testavimą, nes jei atsarginė kopija pašalinama greičiau, diskai labiau įsitempia, padidėja procesoriaus apkrova (vis tiek reikia suspausti tai skrendant), ir atitinkamai mažėja bendras sistemos reagavimas.
Juokauju, bet puikiai suprantu, kad nepadariau jokių apreiškimų. Tai, kas parašyta aukščiau, yra tiesiog demonstravimas, kaip galite pasirinkti optimalius parametrus atsarginėms kopijoms kurti.
Atminkite, kad viskas, ką darote, daroma jūsų pačių rizika ir rizika. Patikrinkite atsargines kopijas ir nepamirškite apie DBCC CHECKDB.
Šaltinis: www.habr.com