Prisni! Prisni! Vërtetë, ky nuk është një artikull tjetër në lidhje me llojet e kopjeve rezervë të SQL Server. Unë as nuk do të flas për ndryshimet midis modeleve të rikuperimit dhe si të merreni me një regjistër të tejmbushur.
Ndoshta (vetëm ndoshta), pasi të keni lexuar këtë postim, do të jeni në gjendje të siguroheni që kopja rezervë që hiqet nga ju duke përdorur mjete standarde do të hiqet nesër mbrëma, mirë, 1.5 herë më shpejt. Dhe vetëm për faktin se përdorni pak më shumë parametra BACKUP DATABASE.
Nëse përmbajtja e postimit ishte e qartë për ju, më vjen keq. Kam lexuar gjithçka që ka arritur Google për shprehjen "habr sql server backup", dhe në asnjë artikull të vetëm nuk kam gjetur ndonjë përmendje të faktit se koha e rezervimit mund të ndikohet disi duke përdorur parametra.
Unë menjëherë do të tërheq vëmendjen tuaj për komentin e Alexander Gladchenko ():
Asnjëherë mos i ndryshoni parametrat BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE në prodhim. Ato janë krijuar vetëm për të shkruar artikuj të tillë. Në praktikë, ju do të shpëtoni nga problemet e kujtesës në një kohë të shkurtër.
Sigurisht, do të ishte mirë të jesh më i zgjuari dhe të postosh përmbajtje ekskluzive, por, për fat të keq, nuk është kështu. Ka artikuj/postime në gjuhën angleze dhe ruse (gjithmonë jam i hutuar se si t'i quaj saktë) kushtuar kësaj teme. Këtu janë disa nga ato që kam hasur: , , .
Pra, për të filluar, unë do të bashkëngjit një sintaksë BACKUP pak të zhveshur nga (nga rruga, kam shkruar më lart për BAZËN E TË DHËNAVE TË BACKUP, por e gjithë kjo vlen si për rezervimin e regjistrit të transaksioneve ashtu edhe për rezervimin diferencial, por ndoshta me një efekt më pak të dukshëm):
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 }
<...><…> - do të thotë se kishte diçka atje, por e hoqa sepse tani nuk ka lidhje me temën.
Si e bëni zakonisht një kopje rezervë? Si "mësojnë" ata se si të marrin kopje rezervë në miliarda artikuj? Në përgjithësi, nëse më duhet të bëj një kopje rezervë të një baze të dhënash jo shumë të madhe, automatikisht do të shkruaj diçka si kjo:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееDhe, në përgjithësi, ndoshta 75-90% e të gjithë parametrave që zakonisht përmenden në artikujt rreth kopjeve rezervë janë renditur këtu. Epo, ka edhe INIT, SKIP. A e keni vizituar MSDN? A keni parë që ka opsione për një ekran e gjysmë? Unë gjithashtu pashë ...
Ju ndoshta e keni kuptuar tashmë se më tej do të flasim për tre parametrat që mbetën në bllokun e parë të kodit - BLOCKSIZE, BUFFERCOUNT dhe MAXTRANSFERSIZE. Këtu janë përshkrimet e tyre nga MSDN:
BLOCKSIZE = { madhësia e bllokut | @ madhësia e_ndryshueshme e bllokut } - tregon madhësinë fizike të bllokut në bajt. Madhësitë e mbështetura janë 512, 1024, 2048, 4096, 8192, 16, 384 dhe 32 bajt (768 KB). Vlera e paracaktuar është 65 për pajisjet me shirit dhe 536 për pajisjet e tjera. Zakonisht ky parametër nuk është i nevojshëm sepse deklarata BACKUP zgjedh automatikisht madhësinë e duhur të bllokut për pajisjen. Vendosja e madhësisë së bllokut anashkalon në mënyrë eksplicite zgjedhjen automatike të madhësisë së bllokut.
BUFFERCOUNT = { buffercount | @ buffercount_variable } - Përcakton numrin total të buferëve I/O që do të përdoren për operacionin e rezervimit. Mund të specifikoni çdo vlerë të plotë pozitiv, por një numër i madh buferash mund të shkaktojnë një gabim jashtë memorie për shkak të hapësirës së tepërt të adresës virtuale në procesin Sqlservr.exe.
Sasia totale e hapësirës së përdorur nga buferët përcaktohet nga formula e mëposhtme:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZO = { maxtransferoj | @ maxtransfersize_variable } specifikon madhësinë më të madhe të paketës së të dhënave, në bajt, për t'u shkëmbyer ndërmjet SQL Server dhe mediave të grupit rezervë. Mbështeten shumëfisha prej 65 byte (536 KB) deri në 64 byte (4 MB).
Betohem - e kam lexuar këtë më parë, por kurrë nuk më ka shkuar ndërmend se sa ndikim mund të kenë në produktivitet. Për më tepër, me sa duket, duhet të bëj një lloj "coming out" dhe të pranoj që edhe tani nuk e kuptoj plotësisht se çfarë po bëjnë saktësisht. Ndoshta më duhet të lexoj më shumë rreth I/O me bufer dhe punës me një hard disk. Një ditë do ta bëj këtë, por tani për tani mund të shkruaj vetëm një skript që do të kontrollojë se si këto vlera ndikojnë në shpejtësinë me të cilën merret kopja rezervë.
Bëra një bazë të dhënash të vogël, rreth 10 GB në madhësi, e vendosa në SSD dhe vendosa direktorinë për kopjet rezervë në HDD.
Unë krijoj një tabelë të përkohshme për të ruajtur rezultatet (nuk e kam të përkohshme, kështu që mund të gërmoj në rezultatet më në detaje, por ju vendosni vetë):
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
);Parimi i skriptit është i thjeshtë - sythe të mbivendosur, secila prej të cilave ndryshon vlerën e një parametri, futni këto parametra në komandën BACKUP, ruani rekordin e fundit me histori nga msdb.dbo.backupset, fshini skedarin rezervë dhe përsëritjen tjetër. . Meqenëse të dhënat e ekzekutimit të rezervës merren nga grupi rezervë, saktësia humbet disi (nuk ka fraksione sekondash), por ne do t'i mbijetojmë kësaj.
Së pari ju duhet të aktivizoni xp_cmdshell për të fshirë kopjet rezervë (më pas mos harroni ta çaktivizoni nëse nuk ju nevojitet):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOEpo, në fakt:
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;
ENDNëse papritmas keni nevojë për sqarime se çfarë po ndodh këtu, shkruani në komente ose PM. Tani për tani, unë do t'ju tregoj vetëm për parametrat që kam vendosur në BACKUP DATABASE.
Për BLOCKSIZE ne kemi një listë të "mbyllur" vlerash dhe nuk kam kryer një kopje rezervë me BLOCKSIZE < 4KB. MAXTRANSFERIZO çdo numër që është shumëfish i 64 KB - nga 64 KB në 4 MB. Parazgjedhja në sistemin tim është 1024 KB, mora 512 - 1024 - 2048 - 4096.
Ishte më e vështirë me BUFFERCOUNT - mund të jetë çdo numër pozitiv, por lidhja thotë . Ai gjithashtu thotë se si të merrni informacione se me cilin BUFFERCOUNT është bërë në të vërtetë kopja rezervë - për mua është 7. Nuk kishte kuptim ta reduktoja atë dhe kufiri i sipërm u zbulua eksperimentalisht - me BUFFERCOUNT = 896 dhe MAXTRANSFERSIZE = 4194304 rezervimi ra me një gabim (për të cilin është shkruar në lidhjen e mësipërme):
Msg 3013, Niveli 16, Shteti 1, Rreshti 7 BAZA E TË DHËNAVE REZERVUESE po përfundon në mënyrë jonormale.
Msg 701, Niveli 17, Gjendja 123, Rreshti 7 Nuk ka memorie të mjaftueshme të sistemit në grupin e burimeve 'default' për të ekzekutuar këtë pyetje.
Për krahasim, së pari do të tregoj rezultatet e ekzekutimit të një kopje rezervë pa specifikuar asnjë parametër fare:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Epo, kopje rezervë dhe rezervë:
1070072 faqe të përpunuara për bazën e të dhënave 'bt', skedari 'bt' në skedarin 1.
2 faqe të përpunuara për bazën e të dhënave 'bt', skedari 'bt_log' në skedarin 1.
BAZA E TË DHËNAVE BACKUP përpunoi me sukses 1070074 faqe në 53.171 sekonda (157.227 MB/sek).
Vetë skenari, duke testuar parametrat, funksionoi brenda disa orësh, të gjitha matjet ishin brenda . Dhe këtu është një përzgjedhje e rezultateve me tre kohët më të mira të ekzekutimit (u përpoqa të bëj një grafik të bukur, por në postim do të më duhet të mjaftohem me një tabelë dhe në komente ai shtoi ).
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;
Kujdes, një shënim shumë i rëndësishëm nga nga :
Mund të themi me besim se marrëdhënia midis parametrave dhe shpejtësisë rezervë brenda këtyre diapazoneve të vlerave është e rastësishme, nuk ka asnjë model. Por largimi nga parametrat e integruar padyshim që pati një efekt të mirë në rezultat
Ato. Vetëm duke menaxhuar parametrat standardë BACKUP pati një rritje 2-fish në kohën e heqjes së rezervës: 26 sekonda, kundrejt 53 në fillim. Nuk është keq, apo jo? Por ne duhet të shohim se çfarë do të ndodhë me restaurimin. Po sikur tani duhet 4 herë më shumë për t'u rikuperuar?
Së pari, le të matim sa kohë duhet për të rivendosur një kopje rezervë me cilësimet e paracaktuara:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Epo, ju vetë e dini këtë, mënyrat janë atje, zëvendësimi nuk zëvendësohet, rimëkëmbja nuk është rikuperim. Dhe unë e bëj kështu:
1070072 faqe të përpunuara për bazën e të dhënave 'bt', skedari 'bt' në skedarin 1.
2 faqe të përpunuara për bazën e të dhënave 'bt', skedari 'bt_log' në skedarin 1.
RESTORE BAZA E TË DHËNAVE përpunoi me sukses 1070074 faqe në 40.752 sekonda (205.141 MB/sek).
Tani do të përpiqem të rivendos kopjet rezervë të marra me BLOCKSIZE, BUFFERCOUNT dhe MAXTRANSFERSIZE të ndryshuar.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE BAZA E TË DHËNAVE përpunoi me sukses 1070074 faqe në 32.283 sekonda (258.958 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE BAZA E TË DHËNAVE përpunoi me sukses 1070074 faqe në 32.682 sekonda (255.796 MB/sek).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE BAZA E TË DHËNAVE përpunoi me sukses 1070074 faqe në 32.091 sekonda (260.507 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE BAZA E TË DHËNAVE përpunoi me sukses 1070074 faqe në 32.401 sekonda (258.015 MB/sek).
Deklarata RESTORE DATABASE nuk ndryshon gjatë rikuperimit; këto parametra nuk janë të specifikuara në të; vetë SQL Server i përcakton ato nga rezervimi. Dhe është e qartë se edhe me shërim mund të ketë një fitim - pothuajse 20% më shpejt (Për të qenë i sinqertë, nuk kalova shumë kohë për rikuperim, kalova nëpër disa nga rezervat "më të shpejta" dhe u sigurova që të mos kishte përkeqësim).
Për çdo rast, më lejoni të sqaroj se këto nuk janë disa parametra që janë optimale për të gjithë. Ju mund të merrni parametrat optimalë për veten tuaj vetëm duke testuar. Unë kam këto rezultate, ju do të merrni të ndryshme. Por ju shihni se mund të "akordoni" kopjet rezervë dhe ato në fakt mund të formohen dhe vendosen më shpejt.
Unë gjithashtu rekomandoj fuqimisht që të lexoni dokumentacionin në tërësi, sepse mund të ketë nuanca specifike për sistemin tuaj.
Që kur fillova të shkruaj për kopjet rezervë, dua të shkruaj menjëherë për një "optimizim" më shumë, i cili është më i zakonshëm se parametrat e "akordimit" (me sa kuptoj, përdoret nga të paktën disa shërbime rezervë, ndoshta së bashku me parametrat përshkruar më herët), por nuk është përshkruar ende as në Habré.
Nëse shikojmë rreshtin e dytë në dokumentacion, pikërisht nën BAZA E TË DHËNAVE BACKUP, aty shohim:
TO <backup_device> [ ,...n ]Çfarë mendoni se do të ndodhë nëse specifikoni disa pajisje rezervë? Sintaksa e lejon atë. Dhe do të ndodhë një gjë shumë interesante - rezervimi thjesht do të "përhapet" në disa pajisje. Ato. çdo "pajisje" individualisht do të jetë e padobishme, humbi një, humbi të gjithë rezervën. Por si do të ndikojë një njollë e tillë në shpejtësinë e rezervimit?
Le të përpiqemi të bëjmë një kopje rezervë në dy "pajisje" që ndodhen krah për krah në të njëjtën dosje:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Etër të botës, pse po bëhet kjo?
1070072 faqe të përpunuara për bazën e të dhënave 'bt', skedari 'bt' në skedarin 1.
2 faqe të përpunuara për bazën e të dhënave 'bt', skedari 'bt'log' në dosjen 1.
BAZA E TË DHËNAVE BACKUP përpunoi me sukses 1070074 faqe në 40.092 sekonda (208.519 MB/sek).
A u bë kopja rezervë 25% më e shpejtë vetëm jashtë mase? Po sikur të shtojmë disa pajisje të tjera?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BAZA E TË DHËNAVE BACKUP përpunoi me sukses 1070074 faqe në 34.234 sekonda (244.200 MB/sek).
Në total, fitimi është rreth 35% e kohës së marrjes së një kopje rezervë vetëm për faktin se kopja rezervë është shkruar në 4 skedarë në një disk menjëherë. Kontrollova një numër më të madh - nuk ka asnjë fitim në laptopin tim, në mënyrë optimale - 4 pajisje. Për ju - nuk e di, duhet të kontrolloni. Epo, nga rruga, nëse i keni këto pajisje - këto janë disqe vërtet të ndryshëm, urime, fitimi duhet të jetë edhe më i rëndësishëm.
Tani le të flasim se si ta rikthejmë këtë lumturi. Për ta bërë këtë, do të duhet të ndryshoni komandën e rikuperimit dhe të listoni të gjitha pajisjet:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;RESTORE BAZA E TË DHËNAVE përpunoi me sukses 1070074 faqe në 38.027 sekonda (219.842 MB/sek).
Pak më shpejt, por diku afër, jo domethënëse. Në përgjithësi, rezervimi hiqet më shpejt dhe rikthehet në të njëjtën mënyrë - sukses? Sa për mua, është mjaft sukses. Kjo важно, kështu që e përsëris - nëse ju nëse humbni të paktën një nga këta skedarë, ju humbni të gjithë rezervimin.
Nëse shikoni në regjistër informacionin rezervë të shfaqur duke përdorur Trace Flags 3213 dhe 3605, do të vini re se kur rezervoni në disa pajisje, të paktën rritet numri i BUFFERCOUNT. Ndoshta, mund të përpiqeni të zgjidhni parametra më optimalë për BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, por nuk pata sukses menjëherë dhe isha shumë dembel për të kryer përsëri një testim të tillë, por për një numër të ndryshëm skedarësh. Dhe është turp për rrotat. Nëse dëshironi të organizoni një testim të tillë në shtëpi, nuk është e vështirë të ribëni skenarin.
Së fundi, le të flasim për çmimin. Nëse rezervimi hiqet paralelisht me punën e përdoruesve, duhet të keni një qasje shumë të përgjegjshme ndaj testimit, sepse nëse kopja rezervë hiqet më shpejt, disqet sforcohen më shumë, ngarkesa në procesor rritet (ju duhet ende të kompresoni atë në fluturim), dhe në përputhje me rrethanat, reagimi i përgjithshëm i sistemit zvogëlohet.
Bëj shaka, por e kuptoj shumë mirë që nuk kam bërë asnjë zbulim. Ajo që është shkruar më sipër është thjesht një demonstrim se si mund të zgjidhni parametrat optimalë për marrjen e kopjeve rezervë.
Mos harroni se gjithçka që bëni bëhet me rrezikun dhe rrezikun tuaj. Kontrolloni kopjet rezervë dhe mos harroni për DBCC CHECKDB.
Burimi: www.habr.com