Изчакайте! Изчакайте! Всъщност, това не е поредната статия за типовете бекъпи на SQL Server. Дори няма да ви разказвам за разликите между моделите на възстановяване и как да се справите с нарасналия "лог".
Възможно е (само възможно), след като прочетете тази публикация, да успеете да направите така, че бекъпът, който се създава с вашите стандартни средства, утре вечер да бъде направен с около 1.5 пъти по-бързо. И само заради това, че използвате малко повече параметри за BACKUP DATABASE.
Ако съдържанието на публикацията е било очевидно за вас — извинете. Прочетох всичко, до което успях да стигна в Google по фразата "habr sql server backup", и в нито една статия не намерих споменаване за това, че по време на бекъпа можете да повлияете по някакъв начин с параметрите.
Веднага ще обърна вашето внимание на коментара на Александър Гладченко ():
Никога не променяйте параметрите BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE на продукция. Те са създадени само за написване на подобни статии. На практика ще изпитате проблеми с паметта.
Би било наистина страхотно да се окажа най-умният и да публикувам ексклузивно съдържание, но за съжаление това не е така. Има статии/публикации на английски и руски език (винаги се бъркам как да ги нарека) посветени на тази тема. Ето част от тези, които ми попаднаха: , , .
И така, за начало ще приложа малко намален синтаксис на BACKUP от (между другото, по-горе писах за BACKUP DATABASE, но всичко това е приложимо и към бекъпа на дневника на транзакциите, и към диференциалния бекъп, но, може би, с по-малко очевиден ефект):
BACKUP DATABASE { database_name | @database_name_var }
TO [ ,...n ]
[ WITH {
| [ ,...n ] } ]
[;]
[ ,...n ]::=
--Опции за комплект медии
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Опции за прехвърляне на данни
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 | @ blocksize_variable } указва размера на физическия блок в байтове. Поддържат се размери 512, 1024, 2048, 4096, 8192, 16 384, 32 768 и 65 536 байта (64 КБ). Стойността по подразбиране е 65 536 за лентови устройства и 512 за други устройства. Обикновено в този параметър няма нужда, тъй като командата BACKUP автоматично избира размера на блока, съответстващ на устройството. Явното задаване на размера на блока преодолява автоматичния избор на размера на блока.
BUFFERCOUNT = { buffercount | @ buffercount_variable } определя общия брой входно-изходни буфери, които ще се използват за операцията по резервно копиране. Може да се зададе всяко положително цяло число, но голям брой буфери може да предизвика грешка за недостатъчност на паметта поради прекомерно виртуално адресно пространство в процеса Sqlservr.exe.
Общият обем пространство, използван от буферите, се определя по следната формула:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } посочва най-голямото количество пакет данни в байтове за обмен на данни между SQL Server и носителя на резервния набор. Поддържат се стойности, кратни на 65 536 байта (64 КБ), до 4 194 304 байта (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, изтривам файла на резервното копие и извършвам следващата итерация. Понеже данните за изпълнението на резервното копие се взимат от 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, Level 16, State 1, Line 7 BACKUP DATABASE is terminating abnormally.
Msg 701, Level 17, State 123, Line 7 There is insufficient system memory in resource pool ‘default’ to run this query.
За сравнение, първо ще покажа резултатите от изпълнението на резервната копия без указване на параметри изобщо:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Е, резервна копия и резервна копия:
Обработени 1070072 страници за база данни ‘bt’, файл ‘bt’ на файл 1.
Обработени 2 страници за база данни ‘bt’, файл ‘bt_log’ на файл 1.
BACKUP DATABASE successfully processed 1070074 pages in 53.171 seconds (157.227 MB/sec).
Самият скрипт, тествал параметрите, работеше за около два часа, всички измервания в А ето и извадка от резултатите, които имат три най-добри времена на изпълнение (опитах се да направя красив график, но в поста ще се наложи да се ограничим с таблица, а в коментарите добави ).
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;Е, това вие и сами знаете, пътища там, replace-не replace, recovery-не 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/сек).
Командата ВЪЗСТАНОВЯВАНЕ НА БАЗА ДАННИ при възстановяване не се променя, не се посочват тези параметри, SQL Server сам ги определя от резервното копие. И е видно, че дори при възстановяване може да има печалба — практически с 20% по-бързо (честно казано, не отделих много време на възстановяването, пробвах няколко от най-"бързите" резервни копия и се уверих, че няма влошаване.).
И за всеки случай уточнявам — тук не са описани някакви оптимални за всички параметри. Оптимални параметри за вас можете да получите само чрез тестване. Аз получих такива резултати, вие ще получите други. Но виждате, че резервните ви копия могат да бъдат "потюнени" и те наистина могат да се създават и възстановяват по-бързо.
Също така настоятелно препоръчвам да прочетете документацията изцяло, защото за вашата система могат да има нюанси.
След като започнах да пиша за резервните копия, искам веднага да спомена и за една "оптимизация", която се среща по-често, отколкото "настройването" на параметри (тя, доколкото разбирам, се използва поне от част от утилитите за архивиране, възможно е и с параметрите описани по-рано), но на Хабра тя все още не е била описана.
Ако погледнем на втория ред в документацията, веднага под BACKUP DATABASE, виждаме:
TO [, ...n]Как мислите, какво ще се случи, ако посочите няколко backup_device? Синтаксисът позволява. Но ще се случи нещо много интересно — резервното копие просто ще се "разпрасне" на няколко устройства. То есть, всяко "устройство" поотделно ще бъде безполезно, загубите ли едно, губите цялото резервно копие. Но как ще се отрази такова разпределение на скоростта за архивиране?
Ще опитам да направя резервно копие на два "устройства", които са разположени близо едно до друго в една папка:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Боже мой, какво се случва тук?
Обработени 1070072 страници за база данни ‘bt’, файл ‘bt’ на файл 1.
Обработени са 2 страници за базата данни ‘bt’, файл ‘btлог’ от файл 1.
BACKUP DATABASE успешно обработи 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;BACKUP DATABASE успешно обработи 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;RESTORE DATABASE успешно обработи 1070074 страници за 38.027 секунди (219.842 MB/сек).
Малко по-бързо, но някъде наблизо, не е съществено. Общо взето, бекапът се прави по-бързо, а възстановяването е също толкова — успех? Според мен — напълно успешен. Това е важно, затова ще повторя — ако вие изгубите поне един от тези файлове — губите целия бекап..
Ако погледнете информацията в журнала за бекапа, изведена чрез Trace Flag 3213 и 3605, можете да забележите, че при архивиране на няколко устройства, се увеличава, поне количеството BUFFERCOUNT. Вероятно, може да пробвате да подберете по-оптимални параметри и за BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, но на мен не ми се получи от раз, а провеждането на такова тестване отново, но за различен брой файлове, ми беше мързеливо. И дисковете ми бяха жалко. Ако искате да организирате такова тестване при вас, скриптът не е труден за редактиране.
На края да поговорим за цената. Ако бекапът се прави паралелно с работата на потребителите — трябва да подходим много отговорно към тестването, тъй като ако бекапът се прави по-бързо — дисковете са под по-голямо напрежение, натоварването на процесора нараства (все пак трябва и да се компресира на лето), съответно, общата отзивчивост на системата се намалява.
Шегите шегите, но разбирам, че не съм направил никакви открития. Това, което е написано по-горе, е просто демонстрация на това как може да се подберат оптималните параметри за създаване на резервни копия.
Помнете, че всичко, което правите, го правите на свой риск. Проверявайте своите резервни копия и не забравяйте за DBCC CHECKDB.
Източник: habr.com
