Bíddu! Bíddu! Að vísu er þetta ekki önnur grein um tegundir af afritum af SQL Server. Ég mun ekki einu sinni tala um muninn á batalíkönum og hvernig á að takast á við ofvaxinn tré.
Kannski (bara kannski), eftir að hafa lesið þessa færslu, muntu geta gengið úr skugga um að öryggisafritið sem er fjarlægt frá þér með hefðbundnum hætti verði fjarlægt á morgun, tja, 1.5 sinnum hraðar. Og aðeins vegna þess að þú notar aðeins fleiri færibreytur afritunargagnagrunns.
Ef innihald færslunnar var augljóst fyrir þig, þá þykir mér það leitt. Ég las allt sem Google komst að fyrir setninguna „habr sql server öryggisafrit“ og í ekki einni grein fann ég neitt minnst á þá staðreynd að hægt er að hafa áhrif á afritunartímann með því að nota breytur.
Ég mun strax vekja athygli þína á athugasemd Alexander Gladchenko ():
Aldrei breyta BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE breytunum í framleiðslu. Þær eru eingöngu gerðar til að skrifa slíkar greinar. Í reynd muntu losna við minnisvandamál á skömmum tíma.
Það væri auðvitað töff að vera snjallastur og birta einkarétt efni, en því miður er það ekki raunin. Það eru bæði greinar/færslur á ensku og rússnesku (ég er alltaf að rugla í því hvað ég á að kalla þær rétt) helgaðar þessu efni. Hér eru nokkrar af þeim sem ég rakst á: , , .
Svo, til að byrja með, mun ég hengja örlítið niðurrifna BACKUP setningafræði frá (við the vegur, ég skrifaði hér að ofan um BACKUP DATABASE, en allt þetta á bæði við um öryggisafrit af færsluskrá og mismunaafrit, en kannski með óljósari áhrifum):
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 }
<...><…> - það þýðir að það var eitthvað þarna, en ég fjarlægði það vegna þess að núna er það ekki viðeigandi fyrir efnið.
Hvernig tekur þú venjulega afrit? Hvernig „kenna“ þeir hvernig á að taka afrit í milljörðum greina? Almennt séð, ef ég þarf að taka öryggisafrit af einhverjum ekki mjög stórum gagnagrunni, mun ég sjálfkrafa skrifa eitthvað á þessa leið:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееOg almennt eru líklega 75-90% af öllum breytum sem venjulega eru nefndar í greinum um öryggisafrit skráð hér. Jæja, það er líka INIT, SKIP. Hefur þú heimsótt MSDN? Hefur þú séð að það eru möguleikar fyrir einn og hálfan skjá? Ég sá líka...
Þú hefur líklega þegar áttað þig á því að frekar munum við tala um færibreyturnar þrjár sem voru eftir í fyrsta kóðablokkinni - BLOCKSIZE, BUFFERCOUNT og MAXTRANSFERSIZE. Hér eru lýsingar þeirra frá MSDN:
BLOKSTÆRÐ = { blokkastærð | @ blocksize_variable } - gefur til kynna líkamlega blokkastærð í bætum. Stærðir sem eru studdar eru 512, 1024, 2048, 4096, 8192, 16, 384 og 32 bæti (768 KB). Sjálfgefið gildi er 65 fyrir segulbandstæki og 536 fyrir önnur tæki. Venjulega er þessi færibreyta ekki nauðsynleg vegna þess að BACKUP setningin velur sjálfkrafa viðeigandi blokkastærð fyrir tækið. Að stilla blokkastærð hnekkir beinlínis sjálfvirku vali á blokkastærð.
BUFFERCOUNT = { biðminnifjöldi | @ buffercount_variable } - Skilgreinir heildarfjölda I/O biðminni sem verður notaður fyrir öryggisafritið. Þú getur tilgreint hvaða jákvætt heiltölugildi sem er, en mikill fjöldi biðminni getur valdið villu í minni vegna of mikils sýndarvistfangarýmis í Sqlservr.exe ferlinu.
Heildarmagn pláss sem er notað af biðmunum er ákvarðað með eftirfarandi formúlu:
BUFFERCOUNT * MAXTRANSFERSIZE.
Hámarksflutningsstærð = { hámarksflutningsstærð | @ maxtransfersize_variable } tilgreinir stærstu gagnapakkastærð, í bætum, til að skiptast á milli SQL Server og öryggisafritsmiðilsins. Margfeldi af 65 bætum (536 KB) upp í 64 bæti (4 MB) eru studdir.
Ég sver það - ég hef lesið þetta áður, en mér datt aldrei í hug hversu mikil áhrif þau gætu haft á framleiðni. Þar að auki, greinilega, þarf ég að gera eins konar "koma út" og viðurkenna að jafnvel núna skil ég ekki alveg hvað þeir eru að gera nákvæmlega. Ég þarf líklega að lesa meira um buffered I/O og vinna með harðan disk. Einhvern tíma mun ég gera þetta, en í bili get ég bara skrifað handrit sem mun athuga hvernig þessi gildi hafa áhrif á hraðann sem öryggisafritið er tekið.
Ég bjó til lítinn gagnagrunn, um 10 GB að stærð, setti hann á SSD-diskinn og setti möppuna fyrir öryggisafrit á HDD-inn.
Ég bý til tímabundna töflu til að geyma niðurstöðurnar (ég er ekki með hana tímabundið, svo ég get grafið nánar í niðurstöðurnar, en þú ákveður sjálfur):
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
);Meginreglan um handritið er einföld - hreiður lykkjur, sem hver um sig breytir gildi einnar færibreytu, settu þessar færibreytur inn í BACKUP skipunina, vistaðu síðustu skrána með sögu frá msdb.dbo.backupset, eyða afritaskránni og næsta endurtekning . Þar sem öryggisafritunargögnin eru tekin úr öryggisafritinu tapast nákvæmnin nokkuð (það eru engin sekúndnabrot), en við munum lifa þetta af.
Fyrst þarftu að virkja xp_cmdshell til að eyða afritum (svo ekki gleyma að slökkva á því ef þú þarft það ekki):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOJæja, reyndar:
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;
ENDEf þú þarft skyndilega skýringar á því sem er að gerast hér skaltu skrifa í athugasemdir eða PM. Í bili mun ég aðeins segja þér frá breytunum sem ég setti í BACKUP Gagnagrunnur.
Fyrir BLOCKSIZE höfum við „lokaðan“ lista yfir gildi og ég tók ekki öryggisafrit með BLOCKSIZE < 4KB. MAXTRANSFERSIZE hvaða tölu sem er sem er margfeldi af 64KB - frá 64KB til 4MB. Sjálfgefið á kerfinu mínu er 1024KB, ég tók 512 - 1024 - 2048 - 4096.
Það var erfiðara með BUFFERCOUNT - það getur verið hvaða jákvæð tala sem er, en tengilinn segir . Það segir líka hvernig á að fá upplýsingar um hvaða BUFFERCOUNT öryggisafritið er í raun og veru gert með - fyrir mér er það 7. Það var ekkert vit í að minnka það, og efri mörkin fundust með tilraunum - með BUFFERCOUNT = 896 og MAXTRANSFERSIZE = 4194304 féll öryggisafritið með villa (sem skrifað er um í hlekknum hér að ofan):
Msg 3013, Level 16, State 1, Line 7 VARININGSGAGNABASIS lýkur óeðlilega.
Msg 701, Level 17, State 123, Line 7. Það er ófullnægjandi kerfisminni í auðlindasafni 'sjálfgefið' til að keyra þessa fyrirspurn.
Til samanburðar mun ég fyrst sýna niðurstöðurnar af því að keyra öryggisafrit án þess að tilgreina neinar færibreytur yfirleitt:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Jæja, öryggisafrit og öryggisafrit:
Unnið 1070072 síður fyrir gagnagrunninn 'bt', skrána 'bt' á skrá 1.
Unnið 2 síður fyrir gagnagrunninn 'bt', skrána 'bt_log' á skrá 1.
Öryggisgagnagrunnur vann 1070074 síður á 53.171 sekúndum (157.227 MB/sek).
Handritið sjálft, prófun á breytum, virkaði á nokkrum klukkustundum, allar mælingar voru inni . Og hér er úrval af niðurstöðum með þremur bestu framkvæmdartímunum (ég reyndi að gera fallegt graf, en í færslunni verð ég að láta mér nægja töflu og í athugasemdum bætt við ).
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;
Athygli, mjög mikilvæg athugasemd frá á :
Við getum með öryggi sagt að sambandið milli færibreytna og öryggisafritunarhraða innan þessara gilda sé af handahófi, það er ekkert mynstur. En að fjarlægja innbyggðu færibreyturnar hafði augljóslega góð áhrif á niðurstöðuna
Þeir. Aðeins með því að stjórna stöðluðum BACKUP breytum var 2-faldur ávinningur á tíma til að fjarlægja öryggisafrit: 26 sekúndur, á móti 53 í upphafi. Það er ekki slæmt, ekki satt? En við verðum að sjá hvað gerist með endurreisnina. Hvað ef það tekur nú 4 sinnum lengri tíma að jafna sig?
Í fyrsta lagi skulum við mæla hversu langan tíma það tekur að endurheimta öryggisafrit með sjálfgefnum stillingum:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Jæja, þú veist það sjálfur, leiðirnar eru til staðar, skipta kemur ekki í staðinn, bati er ekki bati. Og ég geri það svona:
Unnið 1070072 síður fyrir gagnagrunninn 'bt', skrána 'bt' á skrá 1.
Unnið 2 síður fyrir gagnagrunninn 'bt', skrána 'bt_log' á skrá 1.
RESTORE DATABASE tókst að vinna úr 1070074 síðum á 40.752 sekúndum (205.141 MB/sek).
Nú mun ég reyna að endurheimta afrit sem tekin eru með breyttri BLOCKSIZE, BUFFERCOUNT og MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE tókst að vinna úr 1070074 síðum á 32.283 sekúndum (258.958 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE tókst að vinna úr 1070074 síðum á 32.682 sekúndum (255.796 MB/sek).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE tókst að vinna úr 1070074 síðum á 32.091 sekúndum (260.507 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE tókst að vinna úr 1070074 síðum á 32.401 sekúndum (258.015 MB/sek).
RESTORE DATABASE setningin breytist ekki við endurheimt; þessar breytur eru ekki tilgreindar í henni; SQL Server sjálfur ákvarðar þær út frá öryggisafritinu. Og það er ljóst að jafnvel með bata getur verið hagnaður - næstum 20% hraðar (Satt að segja eyddi ég ekki miklum tíma í bata, ég fór í gegnum nokkur af „hröðustu“ afritunum og passaði að það væri engin rýrnun).
Bara í tilfelli, leyfðu mér að skýra að þetta eru ekki nokkrar breytur sem eru ákjósanlegar fyrir alla. Þú getur aðeins fengið bestu færibreyturnar fyrir sjálfan þig með því að prófa. Ég fékk þessar niðurstöður, þú munt fá mismunandi. En þú sérð að þú getur „stillt“ afritin þín og þau geta í raun myndast og dreift hraðar.
Ég mæli líka eindregið með því að þú lesir skjölin í heild sinni, vegna þess að það geta verið blæbrigði sem eru sérstök fyrir kerfið þitt.
Þar sem ég byrjaði að skrifa um öryggisafrit, vil ég strax skrifa um eina „hagræðingu“ í viðbót, sem er algengari en „stilling“ færibreytur (eftir því sem mér skilst, er hún notuð af að minnsta kosti sumum öryggisafritunarforritum, kannski ásamt breytunum lýst fyrr), en því hefur ekki enn verið lýst á Habré heldur.
Ef við skoðum seinni línuna í skjölunum, rétt undir Öryggisgagnagrunni, sjáum við:
TO <backup_device> [ ,...n ]Hvað heldurðu að gerist ef þú tilgreinir nokkur backup_devices? Setningafræðin leyfir það. Og mjög áhugavert mun gerast - öryggisafritinu verður einfaldlega „dreift“ yfir nokkur tæki. Þeir. hvert „tæki“ fyrir sig verður ónýtt, glatað eitt, tapað öllu öryggisafritinu. En hvernig mun slík smurning hafa áhrif á öryggisafritunarhraða?
Við skulum reyna að taka öryggisafrit á tveimur „tækjum“ sem eru staðsett hlið við hlið í sömu möppu:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Heimsfeður, hvers vegna er þetta gert?
Unnið 1070072 síður fyrir gagnagrunninn 'bt', skrána 'bt' á skrá 1.
Unnið 2 síður fyrir gagnagrunn 'bt', skrá 'bt'log' á skrá 1.
Öryggisgagnagrunnur vann 1070074 síður á 40.092 sekúndum (208.519 MB/sek).
Var öryggisafritið 25% hraðari bara út í bláinn? Hvað ef við bætum við nokkrum tækjum í viðbót?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;Öryggisgagnagrunnur vann 1070074 síður á 34.234 sekúndum (244.200 MB/sek).
Alls er hagnaðurinn um 35% af þeim tíma sem tekið er afrit eingöngu vegna þess að afritið er skrifað á 4 skrár á einum diski í einu. Ég athugaði stærri fjölda - það er enginn hagnaður á fartölvunni minni, best - 4 tæki. Fyrir þig - ég veit það ekki, þú þarft að athuga. Jæja, við the vegur, ef þú ert með þessi tæki - þetta eru í raun ólíkir diskar, til hamingju, ávinningurinn ætti að vera enn mikilvægari.
Nú skulum við tala um hvernig á að endurheimta þessa hamingju. Til að gera þetta þarftu að breyta endurheimtarskipuninni og skrá öll tæki:
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 tókst að vinna úr 1070074 síðum á 38.027 sekúndum (219.842 MB/sek).
Aðeins hraðar, en einhvers staðar nálægt, ekki marktækt. Almennt er öryggisafritið fjarlægt hraðar, en endurheimt á sama hátt - árangur? Hvað mig varðar, þá er það mjög góður árangur. Þetta er mikilvægt, svo ég endurtek - ef þú ef þú tapar að minnsta kosti einni af þessum skrám taparðu öllu öryggisafritinu.
Ef þú skoðar öryggisafritsupplýsingarnar sem birtar eru með Trace Flags 3213 og 3605 í skránni, muntu taka eftir því að þegar þú tekur öryggisafrit í nokkur tæki eykst að minnsta kosti fjöldi BUFFERCOUNT. Sennilega geturðu reynt að velja ákjósanlegri færibreytur fyrir BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, en mér tókst það ekki strax og ég var of latur til að framkvæma slíkar prófanir aftur, en fyrir mismunandi fjölda skráa. Og það er synd með hjólin. Ef þú vilt skipuleggja slíkar prófanir heima er ekki erfitt að endurgera handritið.
Að lokum skulum við tala um verð. Ef öryggisafritið er fjarlægt samhliða vinnu notenda þarftu að taka mjög ábyrga nálgun við prófun, því ef öryggisafritið er fjarlægt hraðar eru diskarnir þvingaðir meira, álagið á örgjörvan eykst (þú verður samt að þjappa það á flugu), og í samræmi við það minnkar heildarviðbragð kerfisins.
Bara að grínast, en ég skil vel að ég hafi ekki gefið neinar opinberanir. Það sem er skrifað hér að ofan er einfaldlega sýning á því hvernig þú getur valið bestu færibreytur til að taka afrit.
Mundu að allt sem þú gerir er gert á eigin áhættu og áhættu. Athugaðu afritin þín og ekki gleyma DBCC CHECKDB.
Heimild: www.habr.com