Чекај! Чекај! Точно, ова не е уште една статија за видовите резервни копии на SQL Server. Јас дури и нема да зборувам за разликите помеѓу моделите за обновување и како да се справите со обраснат дневник.
Можеби (само можеби), откако ќе го прочитате овој пост, ќе можете да се уверите дека резервната копија што е отстранета од вас со стандардни средства ќе биде отстранета утре вечер, добро, 1.5 пати побрзо. И само поради фактот што користите малку повеќе параметри на BACKUP DATABASE.
Ако содржината на објавата ви беше очигледна, извинете. Прочитав сè до што дојде Google за фразата „habr sql сервер резервна копија“, и во ниту една статија не најдов никакво спомнување на фактот дека времето за резервна копија може некако да се влијае со користење на параметри.
Веднаш ќе ви го свртам вниманието на коментарот на Александар Гладченко ():
Никогаш не менувајте ги параметрите BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE во производството. Тие се направени само за пишување вакви статии. Во пракса, за кратко време ќе се ослободите од проблемите со меморијата.
Се разбира, би било кул да се биде најпаметен и да објавуваш ексклузивна содржина, но, за жал, тоа не е така. Постојат и написи/написи на англиски и руски јазик (секогаш сум збунет како да ги наречам правилно) посветени на оваа тема. Еве некои од оние на кои наидов: , , .
Така, за почеток, ќе прикачам малку одземена резервна синтакса од (патем, напишав погоре за BACKUP DATABASE, но сето ова важи и за резервна копија од дневникот на трансакции и за диференцијална резервна копија, но можеби со помалку очигледен ефект):
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 }
<...><…> - тоа значи дека имало нешто таму, но го отстранив затоа што сега не е релевантно за темата.
Како обично правите резервна копија? Како тие „учат“ како да прават резервни копии во милијарди статии? Во принцип, ако треба да направам еднократна резервна копија на некоја не многу голема база на податоци, автоматски ќе напишам нешто како ова:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееИ, генерално, веројатно 75-90% од сите параметри кои обично се споменуваат во написите за резервни копии се наведени овде. Па има и INIT, SKIP. Дали сте го посетиле MSDN? Дали сте виделе дека има опции за еден и пол екран? Видов и...
Веројатно веќе сфативте дека понатаму ќе зборуваме за трите параметри кои останаа во првиот блок код - BLOCKSIZE, BUFFERCOUNT и MAXTRANSFERSIZE. Еве ги нивните описи од MSDN:
BLOCKSIZE = { големина на блок | @ blocksize_variable } - ја означува големината на физичкиот блок во бајти. Поддржани големини се 512, 1024, 2048, 4096, 8192, 16, 384 и 32 бајти (768 KB). Стандардната вредност е 65 за уреди со лента и 536 за други уреди. Обично овој параметар не е неопходен бидејќи изјавата BACKUP автоматски ја избира соодветната големина на блок за уредот. Поставувањето на големината на блокот експлицитно го поништува автоматскиот избор на големината на блокот.
BUFFERCOUNT = { тампон број | @ buffercount_variable } - Го дефинира вкупниот број на В/И бафери што ќе се користат за операцијата за резервна копија. Можете да наведете која било позитивна цел број вредност, но голем број на бафери може да предизвикаат грешка без меморија поради прекумерен виртуелен адресен простор во процесот Sqlservr.exe.
Вкупниот износ на простор што го користат баферите се одредува со следнава формула:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } ја одредува најголемата големина на пакет со податоци, во бајти, за размена помеѓу SQL Server и медиумот за резервни сет. Поддржани се повеќекратни 65 бајти (536 KB) до 64 бајти (4 MB).
Се колнам - го прочитав ова и порано, но никогаш не ми падна на памет колку тие би можеле да имаат влијание врз продуктивноста. Згора на тоа, очигледно, треба да направам еден вид „излегување“ и да признаам дека дури и сега не разбирам целосно што точно прават. Веројатно треба да прочитам повеќе за баферскиот влез/излез и работа со хард диск. Еден ден ќе го направам ова, но засега можам само да напишам скрипта што ќе провери како овие вредности влијаат на брзината со која се прави резервната копија.
Направив мала база на податоци, околу 10 GB, ја ставив на SSD и го ставив директориумот за резервни копии на HDD.
Создавам привремена табела за зачувување на резултатите (немам привремена, за да можам подетално да ги истражувам резултатите, но вие сами одлучувате):
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
);Принципот на скриптата е едноставен - вгнездени циклуси, од кои секоја ја менува вредноста на еден параметар, вметнете ги овие параметри во командата BACKUP, зачувајте го последниот запис со историја од msdb.dbo.backupset, избришете ја резервната датотека и следната повторување . Бидејќи податоците за извршување на резервната копија се земени од резервната група, точноста е донекаде изгубена (нема делови од секунди), но ние ќе го преживееме ова.
Прво треба да овозможите xp_cmdshell да ги брише резервните копии (потоа не заборавајте да го оневозможите ако не ви треба):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOПа, всушност:
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;
ENDАко одеднаш ви треба појаснување за тоа што се случува овде, пишете во коментар или PM. Засега ќе ви кажам само за параметрите што ги ставив во BACKUP DATABASE.
За BLOCKSIZE имаме „затворена“ листа на вредности, а јас не направив резервна копија со BLOCKSIZE < 4KB. MAXTRANSFERSIZE било кој број што е множител на 64KB - од 64KB до 4MB. Стандардно на мојот систем е 1024KB, земав 512 - 1024 - 2048 - 4096.
Потешко беше со BUFFERCOUNT - може да биде кој било позитивен број, но на линкот пишува . Исто така, пишува како да се добие информација со кој BUFFERCOUNT е всушност направен резервната - за мене е 7. Немаше смисла да се намали, а горната граница беше откриена експериментално - со BUFFERCOUNT = 896 и MAXTRANSFERSIZE = 4194304 резервната копија падна со грешка (за која е напишана во врската погоре):
Порака 3013, Ниво 16, Состојба 1, Линија 7 РЕЗЕРВНА БАЗА НА ПОДАТОЦИ завршува ненормално.
Порака 701, Ниво 17, состојба 123, линија 7 Нема доволно системска меморија во базенот на ресурси „стандардно“ за да се изврши ова барање.
За споредба, прво ќе ги прикажам резултатите од водење резервна копија без воопшто да специфицирам никакви параметри:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Па, резервна копија и резервна копија:
Обработени 1070072 страници за базата на податоци „bt“, датотеката „bt“ на датотеката 1.
Обработени 2 страници за базата на податоци „bt“, датотеката „bt_log“ на датотеката 1.
РЕЗЕРВНАТА БАЗА НА ПОДАТОЦИ успешно обработи 1070074 страници за 53.171 секунди (157.227 MB/сек).
Самата скрипта, тестирајќи ги параметрите, работеше за неколку часа, сите мерења беа вклучени . И еве избор на резултати со три најдобри времиња на извршување (се обидов да направам убав графикон, но во објавата ќе морам да се задоволам со табела и во коментарите додаде ).
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;
Внимание, многу важна забелешка од на :
Можеме со сигурност да кажеме дека односот помеѓу параметрите и брзината на резервната копија во овие опсези на вредности е случаен, нема шема. Но, оддалечувањето од вградените параметри очигледно имаше добар ефект врз резултатот
Оние. Само со управување со стандардните параметри BACKUP беше 2-кратно зголемување на времето за отстранување на резервната копија: 26 секунди, наспроти 53 на почетокот. Тоа не е лошо, нели? Но, треба да видиме што ќе се случи со реставрацијата. Што ако сега е потребно 4 пати подолго да се опорави?
Прво, да измериме колку време е потребно за да се врати резервната копија со стандардните поставки:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Па, вие самите го знаете тоа, начините се таму, заменете не е замена, закрепнувањето не е закрепнување. И јас го правам тоа вака:
Обработени 1070072 страници за базата на податоци „bt“, датотеката „bt“ на датотеката 1.
Обработени 2 страници за базата на податоци „bt“, датотеката „bt_log“ на датотеката 1.
ВРАТИ БАЗАТА НА ПОДАТОЦИ успешно обработи 1070074 страници за 40.752 секунди (205.141 МБ/сек).
Сега ќе се обидам да ги вратам резервните копии направени со променети BLOCKSIZE, BUFFERCOUNT и MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304ВРАТИ БАЗАТА НА ПОДАТОЦИ успешно обработи 1070074 страници за 32.283 секунди (258.958 МБ/сек).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304ВРАТИ БАЗАТА НА ПОДАТОЦИ успешно обработи 1070074 страници за 32.682 секунди (255.796 МБ/сек).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152ВРАТИ БАЗАТА НА ПОДАТОЦИ успешно обработи 1070074 страници за 32.091 секунди (260.507 МБ/сек).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304ВРАТИ БАЗАТА НА ПОДАТОЦИ успешно обработи 1070074 страници за 32.401 секунди (258.015 МБ/сек).
Изјавата RESTORE DATABASE не се менува за време на обновувањето; овие параметри не се наведени во него; самиот SQL Server ги одредува од резервната копија. И јасно е дека дури и со закрепнување може да има добивка - скоро 20% побрзо (Да бидам искрен, не потрошив многу време на закрепнување, поминав низ неколку „најбрзи“ резервни копии и се уверив дека нема влошување).
За секој случај, да појаснам дека тоа не се некои параметри кои се оптимални за секого. Можете да ги добиете оптималните параметри за себе само со тестирање. Ги добив овие резултати, ќе добиете различни. Но, гледате дека можете да ги „наштимате“ вашите резервни копии и тие всушност можат да се формираат и распоредат побрзо.
Исто така, силно препорачувам да ја прочитате документацијата во целост, бидејќи може да има нијанси специфични за вашиот систем.
Откако почнав да пишувам за резервни копии, сакам веднаш да напишам за уште една „оптимизација“, која е почеста од параметрите за „подесување“ (колку што разбрав, ја користат барем некои резервни алатки, можеби заедно со параметрите опишано претходно), но сè уште не е опишано ниту на Хабре.
Ако ја погледнеме втората линија во документацијата, веднаш под BACKUP DATABASE, таму ќе видиме:
TO <backup_device> [ ,...n ]Што мислите дека ќе се случи ако наведете неколку backup_devices? Синтаксата го дозволува тоа. И ќе се случи многу интересна работа - резервната копија едноставно ќе се „рашири“ на неколку уреди. Оние. секој „уред“ поединечно ќе биде бескорисен, изгубен е еден, изгубен е целиот резервна копија. Но, како таквото размачкување ќе влијае на брзината на резервната копија?
Ајде да се обидеме да направиме резервна копија на два „уреди“ кои се наоѓаат рамо до рамо во истата папка:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Отци на светот, зошто се прави ова?
Обработени 1070072 страници за базата на податоци „bt“, датотеката „bt“ на датотеката 1.
Обработени 2 страници за базата на податоци „bt“, датотека „bt“најави се на датотеката 1.
РЕЗЕРВНАТА БАЗА НА ПОДАТОЦИ успешно обработи 1070074 страници за 40.092 секунди (208.519 MB/сек).
Дали резервната копија стана 25% побрза само од ведро небо? Што ако додадеме уште неколку уреди?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;РЕЗЕРВНАТА БАЗА НА ПОДАТОЦИ успешно обработи 1070074 страници за 34.234 секунди (244.200 MB/сек).
Севкупно, добивката е околу 35% од времето на правење резервна копија само поради фактот што резервната копија е напишана на 4 датотеки на еден диск одеднаш. Проверив поголем број - нема добивка на мојот лаптоп, оптимално - 4 уреди. За вас - не знам, треба да проверите. Па, патем, ако ги имате овие уреди - ова се навистина различни дискови, честитки, добивката треба да биде уште позначајна.
Сега да разговараме за тоа како да ја вратиме оваа среќа. За да го направите ова, ќе треба да ја промените командата за обновување и да ги наведете сите уреди:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;ВРАТИ БАЗАТА НА ПОДАТОЦИ успешно обработи 1070074 страници за 38.027 секунди (219.842 МБ/сек).
Малку побрзо, но некаде блиску, не значајно. Во принцип, резервната копија се отстранува побрзо и се обновува на ист начин - успех? Што се однесува до мене, тоа е доста успех. Ова е важно, па повторувам - ако вие ако изгубите барем една од овие датотеки, ќе ја изгубите целата резервна копија.
Ако во дневникот ги погледнете информациите за резервната копија прикажани со помош на Trace Flags 3213 и 3605, ќе забележите дека кога се прави резервна копија на неколку уреди, барем бројот на BUFFERCOUNT се зголемува. Веројатно, можете да се обидете да изберете пооптимални параметри за BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, но не успеав веднаш и бев премногу мрзлив да спроведам такво тестирање повторно, но за различен број датотеки. И срамота е за тркалата. Ако сакате да организирате такво тестирање дома, не е тешко да го преправите сценариото.
Конечно, ајде да зборуваме за цената. Ако резервната копија се отстранува паралелно со работата на корисниците, треба да имате многу одговорен пристап кон тестирањето, бидејќи ако резервната копија се отстранува побрзо, дисковите се напрегаат повеќе, оптоварувањето на процесорот се зголемува (сеуште треба да компресирате тоа во лет), и соодветно на тоа, се намалува целокупната одзивност на системот.
Се шегувам, но совршено разбирам дека не направив никакви откритија. Она што е напишано погоре е само демонстрација за тоа како можете да ги изберете оптималните параметри за правење резервни копии.
Запомнете дека сè што правите е направено на ваш сопствен ризик и ризик. Проверете ги вашите резервни копии и не заборавајте за DBCC CHECKDB.
Извор: www.habr.com
