Пачакайце! Пачакайце! Праўда, гэта не чарговы артыкул пра тыпы бэкапаў SQL Server. Я нават не буду расказваць пра адрозненні мадэляў аднаўлення і як змагацца з разрослым «логам».
Магчыма (толькі магчыма), пасля прачытання гэтай пасады, вы зможаце зрабіць так, каб бэкап, які здымаецца ў вас стандартнымі сродкамі, заўтра ноччу зняўся, ну, у 1.5 разы хутчэй. І толькі за кошт таго, што вы карыстаецеся крыху больш параметраў BACKUP DATABASE.
Калі для вас змесціва паста было відавочным - прабачце. Я прачытаў усё, да чаго дабраўся гугл па фразе «habr sql server backup», і ні ў адным артыкуле не знайшоў згадкі аб тым, што на час бэкапу можна нейкім чынам паўплываць з дапамогай параметраў.
Адразу ж зверну вашу ўвагу на каментар Аляксандра Гладчанкі ():
Ніколі не мяняйце параметры BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE на продзе. Яны зроблены толькі для напісання падобных артыкулаў. На практыку абрабуеце праблемы з памяццю па спёцы.
Было б, вядома, крута, аказацца самым разумным і выкласці эксклюзіўны кантэнт, але гэта, на жаль, не так. Ёсць і англамоўныя, і рускамоўныя артыкулы/пасты (заўсёды блытаюся як іх правільна называць), прысвечаныя гэтай тэме. Вось частка з тых, што трапіліся мне: , , .
Такім чынам, для пачатку прыкладу некалькі зрэзаны сінтаксіс BACKUP з (дарэчы, там вышэй я пісаў пра 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_variable } - паказвае памер фізічнага блока ў байтах. Падтрымліваюцца памеры 512, 1024, 2048, 4096, 8192, 16, 384 і 32 байт (768 КБ). Значэнне па змаўчанні роўна 65 для істужачных прылад і 536 для іншых прылад. Звычайна ў гэтым параметры няма неабходнасці, бо інструкцыя BACKUP аўтаматычна выбірае памер блока, які адпавядае прыладзе. Відавочная ўстаноўка памеру блока перавызначае аўтаматычны выбар памеру блока.
BUFFERCOUNT = { buffercount | @ buffercount_variable } - вызначае агульны лік буфераў уводу-высновы, якія будуць выкарыстоўвацца для аперацыі рэзервовага капіявання. Можна паказаць любое цэлае дадатнае значэнне, аднак вялікі лік буфераў можа выклікаць памылку недахопу памяці з-за празмернай віртуальнай адраснай прасторы падчас Sqlservr.exe.
Агульны аб'ём прасторы, выкарыстоўванага буферамі, вызначаецца па наступнай формуле:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } паказвае найвялікі аб'ём пакета дадзеных у байтах для абмену дадзенымі паміж SQL Server і носьбітам рэзервовага набору. Падтрымліваюцца значэння, кратныя 65 байтам (536 КБ), аж да 64 байт (4 МБ).
Клянуся - я чытаў гэта раней, але мне і ў галаву не прыходзіла, які ўплыў на прадукцыйнасць яны могуць аказваць. Больш за тое, відаць, трэба зрабіць своеасаблівы «камінг-аўт» і прызнаць, што нават зараз я не да канца разумею, што менавіта яны робяць. Мусіць, трэба пабольш пачытаць пра буферызаваны ўвод-вывад і працу з цвёрдай кружэлкай. Калі-небудзь я гэта зраблю, а зараз магу проста напісаць скрыпт, які праверыць як гэтыя значэнні ўплываюць на хуткасць, з якой здымаецца бэкап.
Зрабіў невялікую базу, памерам каля 10 ГБ, паклаў яе на 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 <4КБ. MAXTRANSFERSIZE любы лік кратнае 64КБ – ад 64КБ да 4МБ. Па-змаўчанні на маёй сістэме 1024КБ, я ўзяў 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 Там ёсць недастатковая сістэма мемуары ў рэсурсе рэсурса "знішчыць" для таго, каб патрапіць.
Для параўнання, спачатку пакажу вынікі выканання бэкапу без указання параметраў наогул:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Ну, бэкап і бэкап:
Выраблена 1070072 pages for database 'bt', file 'bt' on file 1.
Дадзеныя 2 старонкі для 'bt', 'bt_log' file 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 pages for database 'bt', file 'bt' on file 1.
Дадзеныя 2 старонкі для 'bt', 'bt_log' file 1.
RESTORE DATABASE successfully processed 1070074 pages in 40.752 seconds (205.141 MB/sec).
А зараз паспрабую аднавіць бэкапы, знятыя са змененымі BLOCKSIZE, BUFFERCOUNT і MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE successfully processed 1070074 pages in 32.283 seconds (258.958 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE successfully processed 1070074 pages in 32.682 seconds (255.796 MB/sec).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE successfully processed 1070074 pages in 32.091 seconds (260.507 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE successfully processed 1070074 pages in 32.401 seconds (258.015 MB/sec).
Інструкцыя RESTORE DATABASE пры аднаўленні не змяняецца, у ёй не паказваюцца гэтыя параметры, SQL Server сам іх вызначае па бэкапе. І відаць, што нават пры аднаўленні можа быць выйгрыш - практычна на 20% хутчэй (шчыра кажучы, не шмат часу надаў аднаўленню, пратыкаў некалькі самых хуткіх бэкапаў і пераканаўся, што пагаршэння няма).
На ўсякі выпадак удакладню - тут апісаны не нейкія аптымальныя для ўсіх параметры. Аптымальныя параметры для сябе вы можаце атрымаць толькі тэсціраваннем. Я атрымаў такія вынікі, вы атрымаеце іншыя. Але вы бачыце, што свае бэкапы можна «пацюніць» і яны рэальна могуць фармавацца і разгортвацца хутчэй.
Яшчэ настойліва рэкамендую прачытаць-такі дакументацыю цалкам, таму што менавіта для вашай сістэмы могуць быць нюансы.
Раз ужо пачаў пісаць пра бэкапы, хачу адразу напісаць яшчэ пра адну «аптымізацыю», якая сустракаецца часцей, чым «цюнінг» параметраў (яе ж, наколькі я разумею, выкарыстоўвае як мінімум частку ўтыліт для рэзервовага капіявання, магчыма, разам з параметрамі, апісанымі. раней), але на Хабре яна таксама пакуль не была апісана.
Калі паглядзець на другі радок у дакументацыі, адразу пад BACKUP DATABASE, там мы бачым:
TO <backup_device> [ ,...n ]Як вы думаеце, што будзе, калі пазначыць некалькі backup_device'ов? Бо сінтаксіс дазваляе. А будзе вельмі цікавая рэч - бэкап проста «размажацца» па некалькіх дэвайсах. Г.зн. кожны дэвайс паасобку будзе бескарысны, страцілі адзін, страцілі ўвесь бэкап. Але як такое размазванне адаб'ецца на хуткасці рэзервовага капіявання?
Паспрабуем зрабіць бэкап на два «дэвайса», якія ляжаць побач у адной тэчцы:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Бацюшкі-святы, ды што ж гэта робіцца?
Выраблена 1070072 pages for database 'bt', file 'bt' on file 1.
Processed 2 pages for database 'bt', file 'btlog' on file 1.
BACKUP DATABASE successfully processed 1070074 pages in 40.092 seconds (208.519 MB/sec).
Бэкап зрабіўся на 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 successfully processed 1070074 pages in 34.234 seconds (244.200 MB/sec).
Разам, выйгрыш каля 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 successfully processed 1070074 pages in 38.027 seconds (219.842 MB/sec).
Крыху хутчэй, але недзе побач, не істотна. Увогуле, бэкап здымаецца хутчэй, а аднаўляецца гэтак жа - поспех? Як па мне - цалкам сабе поспех. Гэта важна, таму паўтаруся - калі вы губляеце хаця б адзін з гэтых файлаў — губляеце ўвесь бэкап.
Калі паглядзець у часопісе інфармацыю аб бэкапе, якая выводзіцца з дапамогай Trace Flag 3213 і 3605, то можна звярнуць увагу, што пры бэкапе ў некалькі прылад, павялічваецца, прынамсі колькасць BUFFERCOUNT. Напэўна, можна паспрабаваць падабраць больш аптымальныя параметры і для BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, але ў мяне адразу не атрымалася, а праводзіць такое тэсціраванне яшчэ раз, але для рознай колькасці файлаў я заленаваўся. Ды і дыскі шкада. Калі вы жадаеце арганізаваць такое тэставанне ў сябе, скрыпт перарабіць не складана.
У канцы пагаворым аб кошце. Калі бэкап здымаецца паралельна з працай карыстальнікаў - трэба вельмі адказна падысці да тэставання, паколькі калі бэкап здымаецца хутчэй - дыскі напружваюцца мацней, нагрузка на працэсар узрастае (трэба ж гэта ўсё яшчэ і сціскаць налёту), адпаведна, зніжаецца агульная спагадлівасць сістэмы.
Жарты-жартамі, але выдатна разумею, што ніякіх адкрыццяў не зрабіў. Тое, што напісана вышэй - гэта проста дэманстрацыя таго, як можна падабраць аптымальныя параметры для зняцця бэкапаў.
Памятайце, што ўсё, што вы робіце - вы робіце на свой страх і рызыку. Правярайце свае рэзервовыя копіі і не забывайце пра DBCC CHECKDB.
Крыніца: habr.com