Изчакайте! Изчакайте! Вярно е, че това не е поредната статия за видовете резервни копия на SQL Server. Дори няма да говоря за разликите между моделите за възстановяване и как да се справяте с обрасъл труп.
Може би (само може би), след като прочетете тази публикация, ще можете да се уверите, че архивът, който е премахнат от вас с помощта на стандартни средства, ще бъде премахнат утре вечер, добре, 1.5 пъти по-бързо. И само поради факта, че използвате малко повече параметри на BACKUP DATABASE.
Ако съдържанието на публикацията ви е било очевидно, съжалявам. Прочетох всичко, до което Google стигна за фразата „habr sql server backup“ и в нито една статия не намерих споменаване на факта, че времето за архивиране може да бъде повлияно по някакъв начин с помощта на параметри.
Веднага ще обърна внимание на коментара на Александър Гладченко ():
Никога не променяйте параметрите BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE в производството. Те са направени само за писане на такива статии. На практика ще се отървете от проблемите с паметта за нула време.
Разбира се, би било страхотно да сте най-умните и да публикувате ексклузивно съдържание, но, за съжаление, това не е така. Има както англоезични, така и рускоезични статии/публикации (винаги съм объркан как да ги нарека правилно), посветени на тази тема. Ето някои от тези, на които попаднах: , , .
Така че, като начало, ще прикача леко съкратен синтаксис на BACKUP от (между другото, писах по-горе за РЕЗЕРВНАТА БАЗА ДАННИ, но всичко това се отнася както за архивирането на регистрационния файл на транзакциите, така и за диференциалното архивиране, но може би с по-малко очевиден ефект):
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_variable } - показва размера на физическия блок в байтове. Поддържаните размери са 512, 1024, 2048, 4096, 8192, 16 384, 32 768 и 65 536 байта (64 KB). Стойността по подразбиране е 65 за лентови устройства и 536 за други устройства. Обикновено този параметър не е необходим, тъй като командата BACKUP автоматично избира подходящия размер на блока за устройството. Задаването на размера на блока изрично отменя автоматичния избор на размер на блока.
BUFFERCOUNT = { буферен брой | @ буферна_променлива } - Дефинира общия брой I/O буфери, които ще бъдат използвани за операцията по архивиране. Можете да зададете произволна стойност на положително цяло число, но голям брой буфери може да причинят грешка при недостиг на памет поради прекомерно виртуално адресно пространство в процеса Sqlservr.exe.
Общото пространство, използвано от буферите, се определя по следната формула:
BUFFERCOUNT * MAXTRANSFERSIZE.
МАКСИМАЛЕН ТРАНСФЕРЕН РАЗМЕР = { maxtransfersize | @ maxtransfersize_variable } указва най-големия размер на пакета данни, в байтове, за обмен между SQL Server и носителя на архивния набор. Поддържат се кратни от 65 536 байта (64 KB) до 4 194 304 байта (4 MB).
Кълна се - чел съм това и преди, но никога не ми е хрумвало колко голямо влияние могат да имат върху производителността. Освен това, очевидно, трябва да направя нещо като „излизане“ и да призная, че дори сега не разбирам напълно какво точно правят. Вероятно трябва да прочета повече за буферирания I/O и работата с твърд диск. Някой ден ще направя това, но засега мога просто да напиша скрипт, който ще провери как тези стойности влияят на скоростта, с която се прави архивирането.
Направих малка база данни, около 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Ако изведнъж имате нужда от разяснение какво се случва тук, пишете в коментарите или ЛС. Засега ще ви разкажа само за параметрите, които поставих в BACKUP DATABASE.
За BLOCKSIZE имаме „затворен“ списък със стойности и аз не извърших архивиране с BLOCKSIZE < 4KB. MAXTRANSFERSIZE всяко число, което е кратно на 64KB - от 64KB до 4MB. По подразбиране в моята система е 1024KB, аз взех 512 - 1024 - 2048 - 4096.
По-трудно беше с BUFFERCOUNT - може да е всяко положително число, но връзката го казва . Пише и как да получа информация с кой BUFFERCOUNT всъщност е направен бекъпа - при мен е 7. Нямаше смисъл да се намалява, а горната граница беше открита експериментално - при BUFFERCOUNT = 896 и MAXTRANSFERSIZE = 4194304 бекъпът падаше с грешка (за която е написано в линка по-горе):
Msg 3013, ниво 16, състояние 1, ред 7 РЕЗЕРВНА БАЗА ДАННИ прекратява необичайно.
Msg 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;
Внимание, много важна бележка от на :
Можем уверено да кажем, че връзката между параметрите и скоростта на архивиране в рамките на тези диапазони от стойности е произволна, няма модел. Но отдалечаването от вградените параметри явно се отрази добре на резултата
Тези. Само чрез управление на стандартните параметри за РЕЗЕРВНО КОПИРАНЕ беше постигната двукратна печалба във времето за премахване на архивиране: 2 секунди, срещу 26 в началото. Това не е лошо, нали? Но трябва да видим какво ще стане с реставрацията. Ами ако възстановяването отнема 53 пъти повече време?
Първо, нека измерим колко време отнема възстановяването на архив с настройките по подразбиране:
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 MB/сек).
Сега ще се опитам да възстановя резервни копия, направени с променени BLOCKSIZE, BUFFERCOUNT и MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304ВЪЗСТАНОВЯВАНЕТО НА БАЗАТА ДАННИ успешно обработи 1070074 страници за 32.283 секунди (258.958 MB/сек).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304ВЪЗСТАНОВЯВАНЕТО НА БАЗАТА ДАННИ успешно обработи 1070074 страници за 32.682 секунди (255.796 MB/сек).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152ВЪЗСТАНОВЯВАНЕТО НА БАЗАТА ДАННИ успешно обработи 1070074 страници за 32.091 секунди (260.507 MB/сек).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304ВЪЗСТАНОВЯВАНЕТО НА БАЗАТА ДАННИ успешно обработи 1070074 страници за 32.401 секунди (258.015 MB/сек).
Изявлението 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 MB/сек).
Малко по-бързо, но някъде близо, незначително. Като цяло бекъпът се премахва по-бързо и се възстановява по същия начин - успех? Що се отнася до мен, това е голям успех. Това важно, така че повтарям - ако вие ако загубите поне един от тези файлове, губите целия архив.
Ако погледнете в дневника информацията за архивиране, показана с помощта на флагове за проследяване 3213 и 3605, ще забележите, че когато архивирате на няколко устройства, поне броят на BUFFERCOUNT се увеличава. Вероятно можете да опитате да изберете по-оптимални параметри за BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, но не успях веднага и ме мързеше да проведа отново такова тестване, но за различен брой файлове. И е жалко за колелата. Ако искате да организирате такова тестване у дома, не е трудно да преработите сценария.
И накрая, нека поговорим за цената. Ако архивирането се премахва успоредно с работата на потребителите, трябва да подходите много отговорно към тестването, защото ако архивирането се премахва по-бързо, дисковете се натоварват повече, натоварването на процесора се увеличава (все още трябва да компресирате в движение) и съответно общата отзивчивост на системата намалява.
Шегувам се, но много добре разбирам, че не съм направил никакви разкрития. Написаното по-горе е просто демонстрация как можете да изберете оптималните параметри за архивиране.
Не забравяйте, че всичко, което правите, е на ваша собствена отговорност и риск. Проверете резервните си копия и не забравяйте за DBCC CHECKDB.
Източник: www.habr.com