Atendu! Atendu! Vere, ĉi tio ne estas alia artikolo pri specoj de sekurkopioj de SQL Server. Mi eĉ ne parolos pri la diferencoj inter reakiraj modeloj kaj kiel trakti trokreskitan ŝtipon.
Eble (nur eble), post legado de ĉi tiu afiŝo, vi povos certigi, ke la sekurkopio, kiu estas forigita de vi per normaj rimedoj, estos forigita morgaŭ nokte, nu, 1.5 fojojn pli rapide. Kaj nur pro tio, ke vi uzas iom pli da parametroj de BACKUP DATUBAZO.
Se la enhavo de la afiŝo estis evidenta al vi, mi bedaŭras. Mi legis ĉion, kion Guglo atingis por la frazo "habr sql server backup", kaj en eĉ ne unu artikolo mi trovis ian mencion pri tio, ke la rezerva tempo povas esti iel influita per parametroj.
Mi tuj atentigos vin pri la komento de Aleksandr Gladĉenko ():
Neniam ŝanĝu la parametrojn BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE en produktado. Ili estas faritaj nur por verki tiajn artikolojn. En la praktiko, vi forigos memorajn problemojn baldaŭ.
Kompreneble estus bone esti la plej inteligenta kaj afiŝi ekskluzivan enhavon, sed, bedaŭrinde, ĉi tio ne estas la kazo. Estas kaj anglalingvaj kaj ruslingvaj artikoloj/afiŝoj (mi ĉiam konfuzas kiel ĝuste nomi ilin) dediĉitaj al ĉi tiu temo. Jen kelkaj el tiuj, kiujn mi renkontis: , , .
Do, por komenci, mi aldonos iomete malkonstruitan BACKUP sintakson de (Cetere, mi skribis supre pri RESARKODA DATUMARKO, sed ĉio ĉi validas por kaj transakcia protokolo-rezervo kaj diferenciga sekurkopio, sed eble kun malpli evidenta efiko):
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 }
<...><…> - signifas ke estis io tie, sed mi forigis ĝin ĉar nun ĝi ne rilatas al la temo.
Kiel vi kutime prenas sekurkopion? Kiel ili "instruas" kiel fari sekurkopiojn en miliardoj da artikoloj? Ĝenerale, se mi bezonas fari unufojan sekurkopion de iu ne tre granda datumbazo, mi aŭtomate skribos ion tian:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееKaj, ĝenerale, verŝajne 75-90% de ĉiuj parametroj, kiuj estas kutime menciitaj en artikoloj pri sekurkopioj, estas listigitaj ĉi tie. Nu, ekzistas ankaŭ INIT, SKIP. Ĉu vi vizitis MSDN? Ĉu vi vidis, ke ekzistas ebloj por unu kaj duono ekranoj? Mi ankaŭ vidis...
Vi verŝajne jam rimarkis, ke plue ni parolos pri la tri parametroj kiuj restis en la unua bloko de kodo - BLOCKSIZE, BUFFERCOUNT kaj MAXTRANSFERSIZE. Jen iliaj priskriboj de MSDN:
BLOCKSIZE = { blokgrandeco | @ bloksize_variable } - indikas la fizikan blokgrandecon en bajtoj. Grandoj subtenataj estas 512, 1024, 2048, 4096, 8192, 16, 384 kaj 32 bajtoj (768 KB). La defaŭlta valoro estas 65 por bendaj aparatoj kaj 536 por aliaj aparatoj. Tipe ĉi tiu parametro ne estas necesa ĉar la BACKUP deklaro aŭtomate elektas la taŭgan blokgrandecon por la aparato. Agordi la blokgrandecon eksplicite superregas la aŭtomatan blokgrandelekton.
BUFFERCOUNT = { bufrokalkulo | @ buffercount_variable } - Difinas la totalan nombron de I/O-bufroj, kiuj estos uzataj por la rezerva operacio. Vi povas specifi ajnan pozitivan entjeran valoron, sed granda nombro da bufroj povas kaŭzi senmemoran eraron pro troa virtuala adresspaco en la procezo Sqlservr.exe.
La totala kvanto de spaco uzata de bufroj estas determinita per la sekva formulo:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } specifas la plej grandan datumpakaĵon, en bajtoj, por interŝanĝi inter SQL-Servilo kaj la sekurkopio-aro. Multobloj de 65 bajtoj (536 KB) ĝis 64 bajtoj (4 MB) estas subtenataj.
Mi ĵuras - mi jam legis ĉi tion, sed neniam venis en la kapon al mi kiom multe da efiko ili povus havi sur produktiveco. Cetere, ŝajne, mi devas fari ian "eliro" kaj konfesi, ke eĉ nun mi ne plene komprenas, kion precize ili faras. Mi verŝajne bezonas legi pli pri bufro-I/O kaj pri laboro kun malmola disko. Iam mi faros tion, sed nuntempe mi povas simple skribi skripton, kiu kontrolos kiel ĉi tiuj valoroj influas la rapidecon, kun kiu la sekurkopio estas prenita.
Mi faris malgrandan datumbazon, ĉirkaŭ 10 GB en grandeco, metis ĝin sur la SSD, kaj metis la dosierujon por sekurkopioj sur la HDD.
Mi kreas provizoran tabelon por konservi la rezultojn (mi ne havas ĝin provizore, do mi povas pli detale enfosi la rezultojn, sed vi mem decidas):
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
);La principo de la skripto estas simpla - nestitaj bukloj, ĉiu el kiuj ŝanĝas la valoron de unu parametro, enigu ĉi tiujn parametrojn en la komandon BACKUP, konservu la lastan rekordon kun historio de msdb.dbo.backupset, forigu la rezervan dosieron kaj la sekvan ripeton. . Ĉar la rezervaj ekzekutdatenoj estas prenitaj de la sekurkopio, la precizeco estas iom perdita (ne ekzistas frakcioj de sekundoj), sed ni postvivos ĉi tion.
Unue vi devas ebligi xp_cmdshell por forigi sekurkopiojn (tiam ne forgesu malŝalti ĝin se vi ne bezonas ĝin):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GONu, fakte:
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;
ENDSe vi subite bezonas klarigon pri tio, kio okazas ĉi tie, skribu en la komentoj aŭ PM. Nuntempe, mi nur rakontos al vi pri la parametroj, kiujn mi enmetas en RESKORPDA DATUBO.
Por BLOCKSIZE ni havas "fermitan" liston de valoroj, kaj mi ne faris sekurkopion kun BLOCKSIZE < 4KB. MAXTRANSFERSIZE ajna nombro kiu estas oblo de 64KB - de 64KB ĝis 4MB. La defaŭlta en mia sistemo estas 1024KB, mi prenis 512 - 1024 - 2048 - 4096.
Estis pli malfacile kun BUFFERCOUNT - ĝi povas esti ajna pozitiva nombro, sed la ligilo diras . Ĝi ankaŭ diras kiel akiri informojn pri kiu BUFFERCOUNT la sekurkopio estas efektive farita - por mi ĝi estas 7. Ne estis senco redukti ĝin, kaj la supra limo estis malkovrita eksperimente - kun BUFFERCOUNT = 896 kaj MAXTRANSFERSIZE = 4194304 la sekurkopio falis kun eraro (pri kiu skribita en la supra ligilo):
Msg 3013, Nivelo 16, Ŝtato 1, Linio 7 RESARVA DATUBAZO finiĝas nenormale.
Msg 701, Nivelo 17, Ŝtato 123, Linio 7 Estas nesufiĉa sistemmemoro en la rimeda aro "defaŭlte" por ruli ĉi tiun demandon.
Por komparo, mi unue montros la rezultojn de rulado de sekurkopio tute sen specifi iujn ajn parametrojn:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Nu, sekurkopio kaj sekurkopio:
Prilaboritaj 1070072 paĝoj por datumbazo 'bt', dosiero 'bt' en dosiero 1.
Prilaboritaj 2 paĝoj por datumbazo 'bt', dosiero 'bt_log' en dosiero 1.
RESARVA DATUBAZO sukcese prilaboris 1070074 paĝojn en 53.171 sekundoj (157.227 MB/sek).
La skripto mem, testante la parametrojn, funkciis en kelkaj horoj, ĉiuj mezuradoj estis en . Kaj jen elekto de rezultoj kun la tri plej bonaj ekzekuttempoj (mi provis fari belan grafeon, sed en la afiŝo mi devos kontentiĝi kun tabelo, kaj en la komentoj aldonis ).
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;
Atentu, tre grava noto de el :
Ni povas memcerte diri, ke la rilato inter la parametroj kaj rezerva rapido ene de ĉi tiuj intervaloj de valoroj estas hazarda, ne ekzistas ŝablono. Sed malproksimiĝi de la enkonstruitaj parametroj evidente havis bonan efikon al la rezulto
Tiuj. Nur per administrado de la normaj BACKUP-parametroj estis 2-obla gajno en rezerva foriga tempo: 26 sekundoj, kontraŭ 53 ĉe la komenco. Tio ne estas malbona, ĉu ne? Sed ni devas vidi kio okazas kun la restarigo. Kio se ĝi nun bezonas 4 fojojn pli longe por resaniĝi?
Unue, ni mezuru kiom da tempo necesas restarigi sekurkopion kun defaŭltaj agordoj:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Nu, vi mem scias tion, la manieroj estas tie, anstataŭigi ne estas anstataŭigi, reakiro ne estas reakiro. Kaj mi faras ĝin tiel:
Prilaboritaj 1070072 paĝoj por datumbazo 'bt', dosiero 'bt' en dosiero 1.
Prilaboritaj 2 paĝoj por datumbazo 'bt', dosiero 'bt_log' en dosiero 1.
RESTORE DATUBAZO sukcese prilaboris 1070074 paĝojn en 40.752 sekundoj (205.141 MB/sec).
Nun mi provos restarigi sekurkopiojn prenitajn kun ŝanĝitaj BLOCKSIZE, BUFFERCOUNT kaj MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATUBAZO sukcese prilaboris 1070074 paĝojn en 32.283 sekundoj (258.958 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATUBAZO sukcese prilaboris 1070074 paĝojn en 32.682 sekundoj (255.796 MB/sec).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATUBAZO sukcese prilaboris 1070074 paĝojn en 32.091 sekundoj (260.507 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATUBAZO sukcese prilaboris 1070074 paĝojn en 32.401 sekundoj (258.015 MB/sec).
La deklaro RESTORE DATABASE ne ŝanĝiĝas dum reakiro; ĉi tiuj parametroj ne estas specifitaj en ĝi; SQL-Servilo mem determinas ilin el la sekurkopio. Kaj estas klare, ke eĉ kun reakiro povas esti gajno - preskaŭ 20% pli rapide (Por esti honesta, mi ne pasigis multe da tempo por reakiro, mi trakuris plurajn el la "plej rapidaj" sekurkopioj kaj certigis, ke ne estas difekto.).
Por la okazo, mi klarigu, ke ĉi tiuj ne estas iuj parametroj optimumaj por ĉiuj. Vi nur povas akiri la optimumajn parametrojn por vi mem per testado. Mi ricevis ĉi tiujn rezultojn, vi ricevos malsamajn. Sed vi vidas, ke vi povas "agordi" viajn sekurkopiojn kaj ili efektive povas formiĝi kaj disfaldi pli rapide.
Mi ankaŭ forte rekomendas ke vi legu la dokumentaron en ĝia tuteco, ĉar povas esti nuancoj specifaj por via sistemo.
Ĉar mi komencis verki pri sekurkopioj, mi volas tuj skribi pri unu plia "optimumigo", kiu estas pli ofta ol "agordado" parametroj (laŭ mi komprenas, ĝi estas uzata de almenaŭ kelkaj sekurkopioj, eble kune kun la parametroj. priskribita pli frue), sed ĝi ankoraŭ ne estis priskribita ankaŭ pri Habré.
Se ni rigardas la duan linion en la dokumentado, ĝuste sub RESARKOVA DATUBAZO, tie ni vidas:
TO <backup_device> [ ,...n ]Kion laŭ vi okazos se vi specifos plurajn sekurkopiojn? La sintakso permesas ĝin. Kaj tre interesa afero okazos - la sekurkopio simple estos "disvastigita" tra pluraj aparatoj. Tiuj. ĉiu "aparato" individue estos senutila, perdita unu, perdita la tuta sekurkopio. Sed kiel tia ŝmiraĵo influos rezervan rapidon?
Ni provu fari sekurkopion sur du "aparatoj", kiuj troviĝas unu apud la alia en la sama dosierujo:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Patroj de la mondo, kial tio estas farata?
Prilaboritaj 1070072 paĝoj por datumbazo 'bt', dosiero 'bt' en dosiero 1.
Prilaboritaj 2 paĝoj por datumbazo 'bt', dosiero 'bt'log' en la dosiero 1.
RESARVA DATUBAZO sukcese prilaboris 1070074 paĝojn en 40.092 sekundoj (208.519 MB/sek).
Ĉu la sekurkopio fariĝis 25% pli rapida tuj? Kio se ni aldonas kelkajn pliajn aparatojn?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;RESARVA DATUBAZO sukcese prilaboris 1070074 paĝojn en 34.234 sekundoj (244.200 MB/sek).
Entute, la gajno estas ĉirkaŭ 35% de la tempo de sekurkopio nur pro la fakto, ke la sekurkopio estas skribita al 4 dosieroj sur unu disko samtempe. Mi kontrolis pli grandan nombron - ne estas gajno sur mia tekkomputilo, optimume - 4 aparatoj. Por vi - mi ne scias, vi devas kontroli. Nu, cetere, se vi havas ĉi tiujn aparatojn - ĉi tiuj estas vere malsamaj diskoj, gratulojn, la gajno devus esti eĉ pli grava.
Nun ni parolu pri kiel restarigi ĉi tiun feliĉon. Por fari tion, vi devos ŝanĝi la reakiran komandon kaj listigi ĉiujn aparatojn:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;RESTORE DATUBAZO sukcese prilaboris 1070074 paĝojn en 38.027 sekundoj (219.842 MB/sec).
Iom pli rapide, sed ie proksime, ne signifa. Ĝenerale, la sekurkopio estas forigita pli rapide kaj restarigita en la sama maniero - sukceso? Koncerne min, ĝi estas sufiĉe sukceso. Ĉi tio estas grava, do mi ripetas - se vi se vi perdas almenaŭ unu el ĉi tiuj dosieroj, vi perdas la tutan sekurkopion.
Se vi rigardas en la protokolo la sekurkopiajn informojn montritajn uzante Trace Flags 3213 kaj 3605, vi rimarkos, ke dum sekurkopio al pluraj aparatoj, almenaŭ la nombro da BUFFERCOUNT pliiĝas. Verŝajne, vi povas provi elekti pli optimumajn parametrojn por BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, sed mi ne sukcesis tuj, kaj mi estis tro maldiligenta por fari tian testadon denove, sed por malsama nombro da dosieroj. Kaj estas domaĝe pri la radoj. Se vi volas organizi tiajn provojn hejme, ne estas malfacile refari la skripton.
Fine, ni parolu pri prezo. Se la sekurkopio estas forigita paralele kun la laboro de la uzantoj, vi devas preni tre respondecan aliron al testado, ĉar se la sekurkopio estas forigita pli rapide, la diskoj estas pli streĉitaj, la ŝarĝo sur la procesoro pliiĝas (vi ankoraŭ devas kunpremi. ĝi sur la muŝo), kaj sekve, la ĝenerala respondeco de la sistemo malpliiĝas.
Nur ŝercas, sed mi tute bone komprenas, ke mi faris neniujn revelaciojn. Kio estas skribita supre estas simple pruvo pri kiel vi povas elekti la optimumajn parametrojn por preni sekurkopiojn.
Memoru, ke ĉio, kion vi faras, estas farita je via propra risko kaj risko. Kontrolu viajn sekurkopiojn kaj ne forgesu pri DBCC CHECKDB.
fonto: www.habr.com