Arhoswch! Arhoswch! Yn wir, nid yw hon yn erthygl arall am fathau o gopïau wrth gefn SQL Server. Ni fyddaf hyd yn oed yn siarad am y gwahaniaethau rhwng modelau adfer a sut i ddelio â log sydd wedi gordyfu.
Efallai (dim ond efallai), ar ôl darllen y post hwn, y byddwch chi'n gallu sicrhau bod y copi wrth gefn sy'n cael ei dynnu oddi wrthych gan ddefnyddio dulliau safonol yn cael ei ddileu nos yfory, wel, 1.5 gwaith yn gyflymach. A dim ond oherwydd y ffaith eich bod yn defnyddio ychydig mwy o baramedrau CRONFA DDATA WRTH GEFN.
Os oedd cynnwys y post yn amlwg i chi, mae'n ddrwg gen i. Darllenais bopeth a gyrhaeddodd Google ar gyfer yr ymadrodd “habr sql server backup”, ac mewn nid un erthygl wnes i ddod o hyd i unrhyw sôn am y ffaith y gellir dylanwadu rywsut ar yr amser wrth gefn gan ddefnyddio paramedrau.
Tynnaf eich sylw ar unwaith at sylw Alexander Gladchenko ():
Peidiwch byth â newid y paramedrau BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE wrth gynhyrchu. Fe'u gwneir ar gyfer ysgrifennu erthyglau o'r fath yn unig. Yn ymarferol, byddwch yn cael gwared ar broblemau cof mewn dim o amser.
Byddai'n cŵl, wrth gwrs, bod y cynnwys craffaf a phostio unigryw, ond, yn anffodus, nid yw hyn yn wir. Mae yna erthyglau / postiadau Saesneg a Rwsieg (rwyf bob amser wedi drysu ynghylch beth i'w galw'n gywir) wedi'u neilltuo i'r pwnc hwn. Dyma rai o'r rhai y des i ar eu traws: , , .
Felly, i ddechrau, byddaf yn atodi cystrawen BACKUP sydd wedi'i thynnu i lawr ychydig o (gyda llaw, ysgrifennais uchod am CRONFA DDATA WRTH GEFN, ond mae hyn i gyd yn berthnasol i wrth gefn log trafodion a chopi wrth gefn gwahaniaethol, ond efallai gydag effaith llai amlwg):
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 }
<...><…> - mae'n golygu bod rhywbeth yno, ond fe wnes i ei ddileu oherwydd nawr nid yw'n berthnasol i'r pwnc.
Sut ydych chi fel arfer yn cymryd copi wrth gefn? Sut maen nhw'n “dysgu” sut i gymryd copïau wrth gefn mewn biliynau o erthyglau? Yn gyffredinol, os bydd angen i mi wneud copi wrth gefn un-amser o rai cronfa ddata nad yw'n fawr iawn, byddaf yn ysgrifennu rhywbeth fel hyn yn awtomatig:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееAc, yn gyffredinol, mae'n debyg bod 75-90% o'r holl baramedrau a grybwyllir fel arfer mewn erthyglau am gopïau wrth gefn wedi'u rhestru yma. Wel, mae yna hefyd INIT, SKIP. Ydych chi wedi ymweld â MSDN? Ydych chi wedi gweld bod opsiynau ar gyfer sgriniau un a hanner? Gwelais hefyd...
Mae'n debyg eich bod eisoes wedi sylweddoli y byddwn yn siarad ymhellach am y tri pharamedr a arhosodd yn y bloc cyntaf o god - BLOCKSIZE, BUFFERCOUNT a MAXTRANSFERSIZE. Dyma eu disgrifiadau o MSDN:
BLOCKSIZE = { bloc maint | @ blocksize_variable } - yn nodi maint y bloc ffisegol mewn beit. Y meintiau a gefnogir yw 512, 1024, 2048, 4096, 8192, 16, 384, a 32 beit (768 KB). Y gwerth rhagosodedig yw 65 ar gyfer dyfeisiau tâp a 536 ar gyfer dyfeisiau eraill. Yn nodweddiadol nid yw'r paramedr hwn yn angenrheidiol oherwydd bod y datganiad BACKUP yn dewis y maint bloc priodol ar gyfer y ddyfais yn awtomatig. Mae gosod maint y bloc yn benodol yn drech na'r dewis maint bloc awtomatig.
CYFRIF BUFFER = { cyfrif byffer | @ cyfrif byffer_variable } - Yn diffinio cyfanswm nifer y byfferau I/O a fydd yn cael eu defnyddio ar gyfer y gweithrediad wrth gefn. Gallwch nodi unrhyw werth cyfanrif positif, ond gall nifer fawr o glustogau achosi gwall y tu allan i'r cof oherwydd gormod o le rhith-gyfeiriad yn y broses Sqlservr.exe.
Mae cyfanswm y gofod a ddefnyddir gan glustogau yn cael ei bennu gan y fformiwla ganlynol:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } yn pennu maint y pecyn data mwyaf, mewn bytes, i'w gyfnewid rhwng SQL Server a'r cyfryngau set wrth gefn. Cefnogir lluosrifau o 65 beit (536 KB) hyd at 64 beit (4 MB).
Rwy'n rhegi - rwyf wedi darllen hwn o'r blaen, ond ni ddigwyddodd erioed i mi faint o effaith y gallent ei chael ar gynhyrchiant. Ar ben hynny, mae'n debyg, mae angen i mi wneud rhyw fath o “ddod allan” a chyfaddef nad wyf hyd yn oed nawr yn deall yn iawn beth yn union maen nhw'n ei wneud. Mae'n debyg bod angen i mi ddarllen mwy am I/O byffer a gweithio gyda gyriant caled. Rhyw ddydd fe wnaf hyn, ond am y tro gallaf ysgrifennu sgript a fydd yn gwirio sut mae'r gwerthoedd hyn yn effeithio ar y cyflymder y cymerir y copi wrth gefn.
Fe wnes i gronfa ddata fach, tua 10 GB o faint, ei roi ar yr SSD, a rhoi'r cyfeiriadur ar gyfer copïau wrth gefn ar yr HDD.
Rwy'n creu tabl dros dro i storio'r canlyniadau (nid oes gennyf dros dro, felly gallaf gloddio i mewn i'r canlyniadau yn fwy manwl, ond rydych chi'n penderfynu drosoch eich hun):
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
);Mae egwyddor y sgript yn syml - dolenni nythu, pob un ohonynt yn newid gwerth un paramedr, mewnosodwch y paramedrau hyn yn y gorchymyn BACKUP, arbedwch y cofnod olaf gyda hanes o msdb.dbo.backupset, dilëwch y ffeil wrth gefn a'r iteriad nesaf . Gan fod y data gweithredu wrth gefn yn cael ei gymryd o'r set wrth gefn, mae'r cywirdeb wedi'i golli rhywfaint (nid oes ffracsiynau o eiliadau), ond byddwn yn goroesi hyn.
Yn gyntaf mae angen i chi alluogi xp_cmdshell i ddileu copïau wrth gefn (yna peidiwch ag anghofio ei analluogi os nad oes ei angen arnoch):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOWel, mewn gwirionedd:
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;
ENDOs oes angen eglurhad arnoch yn sydyn ar yr hyn sy'n digwydd yma, ysgrifennwch y sylwadau neu'r PM. Am y tro, ni fyddaf ond yn dweud wrthych am y paramedrau a roddais yn CRONFA DDATA WRTH GEFN.
Ar gyfer BLOCKSIZE mae gennym restr “gaeedig” o werthoedd, ac ni pherfformiais wrth gefn gyda BLOCKSIZE < 4KB. MWYAFTREISIO unrhyw rif sy'n lluosrif o 64KB - o 64KB i 4MB. Y rhagosodiad ar fy system yw 1024KB, cymerais 512 - 1024 - 2048 - 4096.
Roedd yn anoddach gyda BUFFERCOUNT - gall fod yn unrhyw rif positif, ond dywed y ddolen . Mae hefyd yn dweud sut i gael gwybodaeth am ba BUFFERCOUNT y gwneir y copi wrth gefn mewn gwirionedd ag ef - i mi mae'n 7. Nid oedd unrhyw bwynt i'w leihau, a darganfuwyd y terfyn uchaf yn arbrofol - gyda BUFFERCOUNT = 896 a MAXTRANSFERSIZE = 4194304 syrthiodd y copi wrth gefn gyda gwall (am yr hwn a ysgrifennwyd yn y ddolen uchod):
Mae Msg 3013, Lefel 16, Cyflwr 1, Llinell 7 CRONFA DDATA WRTH GEFN yn dod i ben yn annormal.
Msg 701, Lefel 17, Cyflwr 123, Llinell 7 Nid oes digon o gof system yn y gronfa adnoddau 'diofyn' i redeg yr ymholiad hwn.
Er mwyn cymharu, byddaf yn gyntaf yn dangos canlyniadau rhedeg copi wrth gefn heb nodi unrhyw baramedrau o gwbl:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Wel, copi wrth gefn a gwneud copi wrth gefn:
Wedi prosesu 1070072 o dudalennau ar gyfer cronfa ddata 'bt', ffeil 'bt' ar ffeil 1.
Wedi prosesu 2 dudalen ar gyfer cronfa ddata 'bt', ffeil 'bt_log' ar ffeil 1.
CRONFA DDATA WRTH GEFN Prosesu 1070074 tudalen yn llwyddiannus mewn 53.171 eiliad (157.227 MB/eiliad).
Roedd y sgript ei hun, yn profi'r paramedrau, yn gweithio mewn cwpl o oriau, roedd yr holl fesuriadau i mewn . A dyma ddetholiad o ganlyniadau gyda'r tair amser gweithredu gorau (ceisiais wneud graff neis, ond yn y post bydd yn rhaid i mi ymwneud â thabl, ac yn y sylwadau ychwanegodd ).
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;
Sylw, nodyn pwysig iawn gan o :
Gallwn ddweud yn hyderus bod y berthynas rhwng y paramedrau a chyflymder wrth gefn o fewn yr ystodau hyn o werthoedd yn hap, nid oes patrwm. Ond roedd symud i ffwrdd o'r paramedrau adeiledig yn amlwg wedi cael effaith dda ar y canlyniad
Y rhai. Dim ond trwy reoli'r paramedrau BACKUP safonol y cafwyd cynnydd deublyg mewn amser tynnu copi wrth gefn: 2 eiliad, yn erbyn 26 ar y dechrau. Nid yw hynny'n ddrwg, iawn? Ond mae angen inni weld beth sy'n digwydd gyda'r gwaith adfer. Beth os yw nawr yn cymryd 53 gwaith yn hirach i wella?
Yn gyntaf, gadewch i ni fesur pa mor hir y mae'n ei gymryd i adfer copi wrth gefn gyda gosodiadau diofyn:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Wel, rydych chi'ch hun yn gwybod hynny, mae'r ffyrdd yno, nid yw disodli yn cymryd lle, nid adferiad yw adferiad. Ac rwy'n ei wneud fel hyn:
Wedi prosesu 1070072 o dudalennau ar gyfer cronfa ddata 'bt', ffeil 'bt' ar ffeil 1.
Wedi prosesu 2 dudalen ar gyfer cronfa ddata 'bt', ffeil 'bt_log' ar ffeil 1.
Llwyddodd ADFER CRONFA DDATA i brosesu 1070074 o dudalennau mewn 40.752 eiliad (205.141 MB/eiliad).
Nawr byddaf yn ceisio adfer copïau wrth gefn a gymerwyd gyda newid BLOCKSIZE, BUFFERCOUNT a MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304Llwyddodd ADFER CRONFA DDATA i brosesu 1070074 o dudalennau mewn 32.283 eiliad (258.958 MB/eiliad).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304Llwyddodd ADFER CRONFA DDATA i brosesu 1070074 o dudalennau mewn 32.682 eiliad (255.796 MB/eiliad).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152Llwyddodd ADFER CRONFA DDATA i brosesu 1070074 o dudalennau mewn 32.091 eiliad (260.507 MB/eiliad).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304Llwyddodd ADFER CRONFA DDATA i brosesu 1070074 o dudalennau mewn 32.401 eiliad (258.015 MB/eiliad).
Nid yw'r datganiad RESTORE DATACASE yn newid yn ystod adferiad; nid yw'r paramedrau hyn wedi'u nodi ynddo; Mae SQL Server ei hun yn eu pennu o'r copi wrth gefn. Ac mae'n amlwg, hyd yn oed gydag adferiad, y gall fod enillion - bron i 20% yn gyflymach (A bod yn onest, wnes i ddim treulio llawer o amser ar adferiad, rhedais trwy sawl un o'r copïau wrth gefn “cyflymaf” a gwneud yn siŵr nad oedd unrhyw ddirywiad).
Rhag ofn, gadewch imi egluro nad yw'r rhain yn rhai paramedrau sydd orau i bawb. Dim ond trwy brofi y gallwch chi gael y paramedrau gorau posibl i chi'ch hun. Cefais y canlyniadau hyn, byddwch yn cael rhai gwahanol. Ond fe welwch y gallwch chi “diwnio” eich copïau wrth gefn a gallant ffurfio a defnyddio'n gyflymach mewn gwirionedd.
Rwyf hefyd yn argymell yn gryf eich bod yn darllen y ddogfennaeth yn ei chyfanrwydd, oherwydd efallai y bydd naws sy'n benodol i'ch system.
Ers i mi ddechrau ysgrifennu am gopïau wrth gefn, rwyf am ysgrifennu ar unwaith am un “optimeiddio” arall, sy'n fwy cyffredin na pharamedrau “tiwnio” (cyn belled ag y deallaf, fe'i defnyddir gan o leiaf rai cyfleustodau wrth gefn, efallai ynghyd â'r paramedrau a ddisgrifiwyd yn gynharach), ond nid yw wedi'i ddisgrifio eto ar Habré ychwaith.
Os edrychwn ar yr ail linell yn y ddogfennaeth, yn union o dan CRONFA DDATA WRTH GEFN, fe welwn ni:
TO <backup_device> [ ,...n ]Beth ydych chi'n meddwl fydd yn digwydd os byddwch chi'n nodi sawl dyfais wrth gefn? Mae'r gystrawen yn caniatáu iddo. A bydd peth diddorol iawn yn digwydd - bydd y copi wrth gefn yn cael ei “ledu” dros sawl dyfais. Y rhai. bydd pob “dyfais” yn unigol yn ddiwerth, wedi colli un, wedi colli'r copi wrth gefn cyfan. Ond sut y bydd ceg y groth o'r fath yn effeithio ar gyflymder wrth gefn?
Gadewch i ni geisio gwneud copi wrth gefn ar ddau “ddyfais” sydd wedi'u lleoli ochr yn ochr yn yr un ffolder:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Tadau y byd, paham y gwneir hyn ?
Wedi prosesu 1070072 o dudalennau ar gyfer cronfa ddata 'bt', ffeil 'bt' ar ffeil 1.
Wedi prosesu 2 dudalen ar gyfer cronfa ddata 'bt', ffeil 'bt'log' ar ffeil 1.
CRONFA DDATA WRTH GEFN Prosesu 1070074 tudalen yn llwyddiannus mewn 40.092 eiliad (208.519 MB/eiliad).
A ddaeth y copi wrth gefn 25% yn gyflymach ychydig allan o'r glas? Beth os ydym yn ychwanegu cwpl mwy o ddyfeisiau?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;CRONFA DDATA WRTH GEFN Prosesu 1070074 tudalen yn llwyddiannus mewn 34.234 eiliad (244.200 MB/eiliad).
Yn gyfan gwbl, mae'r cynnydd tua 35% o amser cymryd copi wrth gefn yn unig oherwydd bod y copi wrth gefn wedi'i ysgrifennu i 4 ffeil ar un ddisg ar unwaith. Gwiriais nifer fwy - nid oes unrhyw enillion ar fy ngliniadur, yn optimaidd - 4 dyfais. I chi - wn i ddim, mae angen i chi wirio. Wel, gyda llaw, os oes gennych chi'r dyfeisiau hyn - mae'r rhain yn ddisgiau gwahanol iawn, llongyfarchiadau, dylai'r ennill fod hyd yn oed yn fwy arwyddocaol.
Nawr, gadewch i ni siarad am sut i adfer hapusrwydd hwn. I wneud hyn, bydd yn rhaid i chi newid y gorchymyn adfer a rhestru'r holl ddyfeisiau:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;Llwyddodd ADFER CRONFA DDATA i brosesu 1070074 o dudalennau mewn 38.027 eiliad (219.842 MB/eiliad).
Ychydig yn gyflymach, ond rhywle agos, ddim yn arwyddocaol. Yn gyffredinol, mae'r copi wrth gefn yn cael ei ddileu yn gyflymach a'i adfer yn yr un modd - llwyddiant? O’m rhan i, mae’n dipyn o lwyddiant. hwn yn bwysig, felly yr wyf yn ailadrodd - os ydych os byddwch yn colli o leiaf un o'r ffeiliau hyn, byddwch yn colli'r copi wrth gefn cyfan.
Os edrychwch yn y log ar y wybodaeth wrth gefn sy'n cael ei harddangos gan ddefnyddio Trace Flags 3213 a 3605, byddwch yn sylwi, wrth wneud copi wrth gefn o sawl dyfais, bod o leiaf nifer y BUFFERCOUNT yn cynyddu. Yn ôl pob tebyg, gallwch geisio dewis paramedrau mwy optimaidd ar gyfer BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ond ni lwyddais ar unwaith, ac roeddwn yn rhy ddiog i gynnal profion o'r fath eto, ond ar gyfer nifer wahanol o ffeiliau. Ac mae'n drueni am yr olwynion. Os ydych chi am drefnu profion o'r fath gartref, nid yw'n anodd ail-wneud y sgript.
Yn olaf, gadewch i ni siarad am y pris. Os caiff y copi wrth gefn ei dynnu ochr yn ochr â gwaith y defnyddwyr, mae angen i chi gymryd agwedd gyfrifol iawn at brofi, oherwydd os caiff y copi wrth gefn ei dynnu'n gyflymach, mae'r disgiau'n cael eu straenio'n fwy, mae'r llwyth ar y prosesydd yn cynyddu (mae'n rhaid i chi gywasgu o hyd. yn hedfan), ac yn unol â hynny, mae ymatebolrwydd cyffredinol y system yn lleihau.
Dim ond twyllo, ond rwy'n deall yn iawn na wnes i unrhyw ddatguddiadau. Mae'r hyn sydd wedi'i ysgrifennu uchod yn dangos sut y gallwch ddewis y paramedrau gorau posibl ar gyfer gwneud copïau wrth gefn.
Cofiwch fod popeth a wnewch yn cael ei wneud ar eich perygl a'ch risg eich hun. Gwiriwch eich copïau wrth gefn a pheidiwch ag anghofio am CHECKDB DBCC.
Ffynhonnell: hab.com