Kutmoq! Kutmoq! To'g'ri, bu SQL Server zahiralarining turlari haqida boshqa maqola emas. Qayta tiklash modellari o'rtasidagi farqlar va o'sib chiqqan log bilan qanday kurashish haqida gapirmayman.
Ehtimol (shunchaki, ehtimol), ushbu xabarni o'qib chiqqandan so'ng, siz standart vositalar yordamida sizdan olib tashlangan zahira ertaga tunda, 1.5 baravar tezroq olib tashlanishiga ishonch hosil qilishingiz mumkin. Va faqat bir oz ko'proq BACKUP DATABASE parametrlaridan foydalanganingiz sababli.
Agar xabarning mazmuni sizga tushunarli bo'lsa, uzr so'rayman. Men Google "habr sql server backup" iborasini o'qigan hamma narsani o'qib chiqdim va biron bir maqolada zaxira vaqti parametrlar yordamida qandaydir tarzda ta'sir qilishi mumkinligi haqida hech qanday eslatma topmadim.
Men darhol e'tiboringizni Aleksandr Gladchenkoning sharhiga qarataman ():
Ishlab chiqarishda BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE parametrlarini hech qachon o'zgartirmang. Ular faqat bunday maqolalarni yozish uchun yaratilgan. Amalda siz qisqa vaqt ichida xotira muammolaridan xalos bo'lasiz.
Albatta, eng aqlli va post eksklyuziv kontent bo'lish ajoyib bo'lardi, lekin, afsuski, bunday emas. Ushbu mavzuga bag'ishlangan ingliz va rus tillarida maqolalar/postlar mavjud (men ularni qanday qilib to'g'ri deb atashni bila olmayapman). Mana, men duch kelgan ba'zilari: , , .
Shunday qilib, boshlash uchun men dan biroz qisqartirilgan BACKUP sintaksisini biriktiraman (Aytgancha, men yuqorida BACKUP DATABASE haqida yozgan edim, ammo bularning barchasi tranzaktsiyalar jurnalining zaxira nusxasiga ham, differentsial zahiraga ham tegishli, lekin ehtimol unchalik aniq emas):
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 }
<...><…> - bu u erda nimadir borligini anglatadi, lekin men uni olib tashladim, chunki endi bu mavzuga tegishli emas.
Odatda qanday qilib zaxira nusxasini olasiz? Qanday qilib ular milliardlab maqolalarda zaxira nusxalarini olishni "o'rgatadi"? Umuman olganda, agar men unchalik katta bo'lmagan ma'lumotlar bazasining bir martalik zaxira nusxasini yaratishim kerak bo'lsa, men avtomatik ravishda shunday yozaman:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееVa umuman olganda, zaxira nusxalari haqidagi maqolalarda odatda eslatib o'tilgan barcha parametrlarning 75-90% bu erda keltirilgan. Xo'sh, INIT, SKIP ham bor. MSDN-ga tashrif buyurdingizmi? Bir yarim ekran uchun variantlar mavjudligini ko'rdingizmi? Men ham ko'rdim ...
Siz allaqachon tushungan bo'lsangiz kerak, bundan keyin biz kodning birinchi blokida qolgan uchta parametr - BLOCKSIZE, BUFFERCOUNT va MAXTRANSFERSIZE haqida gaplashamiz. MSDN dan ularning tavsiflari:
BLOKSIZE = { blok hajmi | @ blocksize_variable } - baytlarda jismoniy blok hajmini ko'rsatadi. Qo'llab-quvvatlanadigan o'lchamlar - 512, 1024, 2048, 4096, 8192, 16, 384 va 32 bayt (768 KB). Standart qiymat lenta qurilmalari uchun 65 va boshqa qurilmalar uchun 536. Odatda bu parametr kerak emas, chunki BACKUP bayonoti qurilma uchun mos blok hajmini avtomatik ravishda tanlaydi. Blok o'lchamini belgilash avtomatik blok o'lchamini tanlashni aniq bekor qiladi.
BUFFERCOUNT = { bufer soni | @ buffercount_variable } - Zaxira operatsiyasi uchun foydalaniladigan kiritish/chiqarish buferlarining umumiy sonini belgilaydi. Siz har qanday musbat butun son qiymatini belgilashingiz mumkin, lekin ko'p sonli buferlar Sqlservr.exe jarayonida haddan tashqari virtual manzil maydoni tufayli xotiradan tashqari xatolikka olib kelishi mumkin.
Buferlar tomonidan ishlatiladigan umumiy joy miqdori quyidagi formula bilan aniqlanadi:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } SQL Server va zahiraviy to'plam muhiti o'rtasida almashish uchun baytlarda eng katta ma'lumotlar paketi hajmini belgilaydi. 65 536 bayt (64 KB) dan 4 194 304 baytgacha (4 MB) bir nechta fayllar qo'llab-quvvatlanadi.
Qasam ichaman - men buni avval ham o'qiganman, lekin ular hosildorlikka qanchalik ta'sir qilishi xayolimga ham kelmagan. Bundan tashqari, aftidan, men o'ziga xos "chiqish" qilishim kerak va tan olishim kerakki, hozir ham ular nima qilayotganlarini to'liq tushunmayapman. Men buferlangan kiritish-chiqarish va qattiq disk bilan ishlash haqida ko'proq o'qishim kerak. Bir kun kelib men buni qilaman, lekin hozircha men ushbu qiymatlar zaxira nusxasini olish tezligiga qanday ta'sir qilishini tekshiradigan skript yozishim mumkin.
Men taxminan 10 Gb hajmdagi kichik ma'lumotlar bazasini yaratdim, uni SSD-ga qo'ydim va HDD-ga zaxiralash uchun katalogni qo'ydim.
Natijalarni saqlash uchun vaqtinchalik jadval yarataman (menda bu vaqtinchalik emas, shuning uchun men natijalarni batafsilroq o'rganishim mumkin, lekin o'zingiz qaror qilasiz):
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
);Skriptning ishlash printsipi oddiy - ichki o'rnatilgan tsikllar, ularning har biri bitta parametrning qiymatini o'zgartiradi, bu parametrlarni BACKUP buyrug'iga kiritadi, msdb.dbo.backupset dan tarix bilan oxirgi yozuvni saqlaydi, zaxira faylini va keyingi iteratsiyani o'chiradi. . Zaxira nusxasini bajarish ma'lumotlari zaxira nusxasidan olinganligi sababli, aniqlik biroz yo'qoladi (soniyalarning kasrlari yo'q), ammo biz bundan omon qolamiz.
Zaxira nusxalarini o'chirish uchun avval siz xp_cmdshell-ni yoqishingiz kerak (keyin kerak bo'lmasa, uni o'chirishni unutmang):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOXo'sh, aslida:
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;
ENDAgar sizga to'satdan bu erda nima bo'layotgani haqida tushuntirish kerak bo'lsa, izohlarda yoki PMda yozing. Hozircha men sizga faqat ZAXIRA MA'LUMOTLAR BAZAsiga qo'ygan parametrlar haqida gapirib beraman.
BLOCKSIZE uchun bizda "yopiq" qiymatlar ro'yxati bor va men BLOCKSIZE < 4KB bilan zahira nusxasini amalga oshirmadim. MAXTRANSFERSIZE 64KB ga karrali har qanday raqam - 64KB dan 4MB gacha. Mening tizimimdagi standart 1024KB, men 512 - 1024 - 2048 - 4096 ni oldim.
BUFFERCOUNT bilan bu qiyinroq edi - bu har qanday ijobiy raqam bo'lishi mumkin, lekin havolada aytiladi . Shuningdek, qaysi BUFFERCOUNT bilan zahira nusxasi yaratilgani haqida qanday ma'lumot olish mumkinligi aytiladi - men uchun bu 7. Uni qisqartirishning ma'nosi yo'q edi va yuqori chegara eksperimental ravishda aniqlandi - BUFFERCOUNT = 896 va MAXTRANSFERSIZE = 4194304 bilan zaxira nusxasi tushib ketdi. xato (yuqoridagi havolada yozilgan):
Msg 3013, 16-darajali, 1-holat, 7-qator ZAXIRA MA'LUMOTLAR BAZASI g'ayritabiiy tarzda tugatilmoqda.
Xabar 701, 17-darajali, 123-holat, 7-qator bu so‘rovni bajarish uchun “standart” resurs hovuzida tizim xotirasi yetarli emas.
Taqqoslash uchun, men birinchi navbatda hech qanday parametrni ko'rsatmasdan zaxira nusxasini ishga tushirish natijalarini ko'rsataman:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Xo'sh, zaxira va zaxira:
"bt" ma'lumotlar bazasi uchun 1070072 sahifa, 1-fayldagi "bt" fayli qayta ishlandi.
"bt" ma'lumotlar bazasi uchun 2 sahifa, 1-fayldagi "bt_log" fayli qayta ishlandi.
BACKUP DATABASE 1070074 sahifani 53.171 soniyada (157.227 MB/sek) muvaffaqiyatli qayta ishladi.
Skriptning o'zi, parametrlarni sinab ko'rib, bir necha soat ichida ishladi, barcha o'lchovlar bajarildi . Va bu erda uchta eng yaxshi bajarilish vaqti bilan natijalar tanlovi (men chiroyli grafik yaratishga harakat qildim, lekin postda men jadval bilan va sharhlarda qilishim kerak. qo'shib qo'ydi ).
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;
Diqqat, juda muhim eslatma dan :
Ishonch bilan aytishimiz mumkinki, ushbu qiymatlar oralig'idagi parametrlar va zaxira tezligi o'rtasidagi bog'liqlik tasodifiy, hech qanday naqsh yo'q. Ammo o'rnatilgan parametrlardan uzoqlashish, shubhasiz, natijaga yaxshi ta'sir qildi
Bular. Faqat standart BACKUP parametrlarini boshqarish orqali zahirani olib tashlash vaqti 2 baravar ko'paydi: boshida 26 soniyaga nisbatan 53 soniya. Bu yomon emas, to'g'rimi? Ammo biz qayta tiklash bilan nima sodir bo'lishini ko'rishimiz kerak. Endi tiklanish uchun 4 marta ko'proq vaqt kerak bo'lsa-chi?
Birinchidan, standart sozlamalar bilan zaxira nusxasini tiklash uchun qancha vaqt kerakligini o'lchaymiz:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;O'zingiz bilasizki, yo'llar bor, almashtirish - almashtirish emas, tiklanish - tiklanish emas. Va men buni shunday qilaman:
"bt" ma'lumotlar bazasi uchun 1070072 sahifa, 1-fayldagi "bt" fayli qayta ishlandi.
"bt" ma'lumotlar bazasi uchun 2 sahifa, 1-fayldagi "bt_log" fayli qayta ishlandi.
MA'LUMOTLAR BAZASINI RESTORE 1070074 soniyada (40.752 MB/sek) 205.141 sahifani muvaffaqiyatli qayta ishladi.
Endi men o'zgartirilgan BLOCKSIZE, BUFFERCOUNT va MAXTRANSFERSIZE bilan olingan zaxira nusxalarini tiklashga harakat qilaman.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304MA'LUMOTLAR BAZASINI RESTORE 1070074 soniyada (32.283 MB/sek) 258.958 sahifani muvaffaqiyatli qayta ishladi.
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304MA'LUMOTLAR BAZASINI RESTORE 1070074 soniyada (32.682 MB/sek) 255.796 sahifani muvaffaqiyatli qayta ishladi.
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152MA'LUMOTLAR BAZASINI RESTORE 1070074 soniyada (32.091 MB/sek) 260.507 sahifani muvaffaqiyatli qayta ishladi.
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304MA'LUMOTLAR BAZASINI RESTORE 1070074 soniyada (32.401 MB/sek) 258.015 sahifani muvaffaqiyatli qayta ishladi.
RESTORE DATABASE bayonoti tiklash vaqtida o'zgarmaydi, bu parametrlar unda ko'rsatilmagan; SQL Serverning o'zi ularni zaxiradan aniqlaydi. Va aniqki, tiklanish bilan ham daromad bo'lishi mumkin - deyarli 20% tezroq (Rostini aytsam, tiklanish uchun ko'p vaqt sarflamadim, men bir nechta "tezkor" zaxira nusxalarini ko'rib chiqdim va hech qanday yomonlashuv yo'qligiga ishonch hosil qildim.).
Har holda, bu hamma uchun maqbul bo'lgan ba'zi parametrlar emasligini aniqlab beraman. Siz o'zingiz uchun faqat test orqali optimal parametrlarni olishingiz mumkin. Men bu natijalarni oldim, siz turli xil natijalarga erishasiz. Ammo siz zaxira nusxalaringizni "sozlashingiz" mumkinligini ko'rasiz va ular aslida tezroq shakllantirilishi va joylashishi mumkin.
Shuningdek, hujjatlarni to'liq o'qib chiqishingizni tavsiya qilaman, chunki tizimingizga xos nuanslar bo'lishi mumkin.
Zaxira nusxalari haqida yozishni boshlaganimdan so'ng, men darhol "sozlash" parametrlaridan ko'ra keng tarqalgan yana bitta "optimallashtirish" haqida yozmoqchiman (mening tushunishimcha, u hech bo'lmaganda ba'zi zaxira yordam dasturlari tomonidan ishlatiladi, ehtimol parametrlar bilan birga. ilgari tasvirlangan), lekin u Habré-da ham tasvirlanmagan.
Agar biz hujjatlardagi ikkinchi qatorga qarasak, BACKUP DATABASE ostida, u erda biz quyidagilarni ko'ramiz:
TO <backup_device> [ ,...n ]Agar siz bir nechta backup_devicesni belgilasangiz nima bo'ladi deb o'ylaysiz? Sintaksis bunga imkon beradi. Va juda qiziq narsa bo'ladi - zaxira nusxasi bir nechta qurilmalarga "tarqaladi". Bular. har bir "qurilma" alohida-alohida foydasiz bo'ladi, bittasini yo'qotadi, butun zaxira nusxasini yo'qotadi. Ammo bunday qoralash zaxira tezligiga qanday ta'sir qiladi?
Keling, bitta papkada yonma-yon joylashgan ikkita "qurilma" da zaxira nusxasini yaratishga harakat qilaylik:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Dunyo otalari, nega bunday qilinmoqda?
"bt" ma'lumotlar bazasi uchun 1070072 sahifa, 1-fayldagi "bt" fayli qayta ishlandi.
"bt" ma'lumotlar bazasi, "bt" fayli uchun 2 sahifaga ishlov berildi1-fayldagi log'.
BACKUP DATABASE 1070074 sahifani 40.092 soniyada (208.519 MB/sek) muvaffaqiyatli qayta ishladi.
Zaxira 25% ga tezlashdimi? Yana ikkita qurilma qo'shsak nima bo'ladi?
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 sahifani 34.234 soniyada (244.200 MB/sek) muvaffaqiyatli qayta ishladi.
Hammasi bo'lib, zaxira nusxasini olish vaqtining taxminan 35% ni tashkil qiladi, chunki zaxira bir vaqtning o'zida bitta diskdagi 4 ta faylga yozilgan. Men kattaroq raqamni tekshirdim - noutbukda hech qanday daromad yo'q, optimal - 4 ta qurilma. Siz uchun - bilmayman, tekshirishingiz kerak. Aytgancha, agar sizda ushbu qurilmalar bo'lsa - bu haqiqatan ham turli xil disklar, tabriklar, daromad yanada muhimroq bo'lishi kerak.
Keling, bu baxtni qanday tiklash haqida gapiraylik. Buni amalga oshirish uchun siz tiklash buyrug'ini o'zgartirishingiz va barcha qurilmalarni ro'yxatlashingiz kerak bo'ladi:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;MA'LUMOTLAR BAZASINI RESTORE 1070074 soniyada (38.027 MB/sek) 219.842 sahifani muvaffaqiyatli qayta ishladi.
Bir oz tezroq, lekin yaqin joyda, muhim emas. Umuman olganda, zaxira tezroq olib tashlanadi va xuddi shu tarzda tiklanadi - muvaffaqiyat? Menga kelsak, bu juda muvaffaqiyatli. Bu muhim ahamiyatga ega, shuning uchun takrorlayman - agar siz agar siz ushbu fayllardan kamida bittasini yo'qotsangiz, butun zaxira nusxasini yo'qotasiz.
Agar siz 3213 va 3605 Trace Flags yordamida ko'rsatilgan zahiraviy ma'lumotlarning jurnaliga qarasangiz, bir nechta qurilmalarga zaxiralanganda, hech bo'lmaganda BUFFERCOUNT soni ortib borayotganini sezasiz. Ehtimol, siz BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE uchun maqbulroq parametrlarni tanlashga urinib ko'rishingiz mumkin, lekin men darhol muvaffaqiyatga erisha olmadim va men yana bunday sinovni o'tkazishga dangasa bo'ldim, lekin boshqa sonli fayllar uchun. Va g'ildiraklar uchun afsus. Agar siz uyda bunday testni tashkil qilmoqchi bo'lsangiz, skriptni qayta tiklash qiyin emas.
Va nihoyat, keling, narx haqida gapiraylik. Agar zaxira nusxasi foydalanuvchilarning ishi bilan parallel ravishda olib tashlansa, siz sinovga juda mas'uliyatli yondashishingiz kerak, chunki agar zaxira tezroq olib tashlansa, disklar ko'proq zo'riqadi, protsessorga yuk ko'tariladi (siz hali ham siqishingiz kerak). u tezda) va shunga mos ravishda tizimning umumiy sezgirligi pasayadi.
Hazil, lekin men hech qanday vahiy qilmaganimni juda yaxshi tushunaman. Yuqorida yozilgan narsa shunchaki zaxira nusxalarini olish uchun optimal parametrlarni qanday tanlash mumkinligini ko'rsatishdir.
Esda tutingki, qilayotgan har bir narsa sizning xavf-xataringiz va xavfingiz ostida amalga oshiriladi. Zaxira nusxalaringizni tekshiring va DBCC CHECKDB haqida unutmang.
Manba: www.habr.com