Gözləmək! Gözləmək! Düzdür, bu SQL Server ehtiyat nüsxələrinin növləri haqqında başqa məqalə deyil. Mən hətta bərpa modelləri arasındakı fərqlər və böyümüş bir logla necə davranmaq barədə danışmayacağam.
Bəlkə də (bəlkə də), bu yazını oxuduqdan sonra standart vasitələrdən istifadə edərək sizdən silinmiş ehtiyat nüsxəsinin sabah gecə, yaxşı, 1.5 dəfə daha sürətli silinəcəyinə əmin ola bilərsiniz. Və yalnız bir az daha YEDƏKLƏMƏ BAZASI parametrlərindən istifadə etdiyinizə görə.
Yazının məzmunu sizə aydın idisə, üzr istəyirəm. Mən Google-un “habr sql server backup” ifadəsi üçün əldə etdiyi hər şeyi oxudum və heç bir məqalədə ehtiyat nüsxə vaxtının parametrlərdən istifadə edərək hər hansı bir şəkildə təsir edə biləcəyinə dair heç bir qeyd tapmadım.
Dərhal diqqətinizi Aleksandr Qladçenkonun şərhinə cəlb edəcəm ():
İstehsalda BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE parametrlərini heç vaxt dəyişməyin. Onlar yalnız belə məqalələr yazmaq üçün hazırlanır. Praktikada yaddaş problemlərindən qısa zamanda xilas olacaqsınız.
Ən ağıllı olmaq və eksklüziv məzmun göndərmək əlbəttə ki, gözəl olardı, lakin təəssüf ki, belə deyil. Bu mövzuya həsr olunmuş həm ingilis, həm də rus dilli məqalələr/yazılar (onları düzgün adlandırmaq məsələsində həmişə çaşıb qalıram) var. Budur rastlaşdığım bəziləri: , , .
Beləliklə, başlamaq üçün, mən bir qədər cılız YEDƏK sintaksisini əlavə edəcəyəm (yeri gəlmişkən, mən yuxarıda BACKUP DATABASE haqqında yazdım, lakin bütün bunlar həm əməliyyat jurnalının ehtiyat nüsxəsinə, həm də diferensial ehtiyat nüsxəsinə aiddir, lakin bəlkə də daha az açıq effektlə):
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 o deməkdir ki, orada nəsə var idi, amma indi mövzuya aidiyyatı olmadığı üçün onu sildim.
Adətən ehtiyat nüsxəsini necə götürürsən? Onlar milyardlarla məqalədə ehtiyat nüsxələri götürməyi necə “öyrətirlər”? Ümumiyyətlə, çox böyük olmayan bəzi verilənlər bazasının birdəfəlik ehtiyat nüsxəsini çıxarmalıyamsa, avtomatik olaraq belə bir şey yazacağam:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееVə ümumiyyətlə, ehtiyat nüsxələri haqqında məqalələrdə adətən qeyd olunan bütün parametrlərin 75-90% -i burada verilmişdir. Yaxşı, INIT, SKIP də var. MSDN-ə baş çəkmisiniz? Bir yarım ekran üçün seçimlərin olduğunu gördünüzmü? Mən də gördüm...
Yəqin ki, artıq başa düşdünüz ki, bundan sonra biz kodun birinci blokunda qalan üç parametrdən - BLOCKSIZE, BUFFERCOUNT və MAXTRANSFERSIZE haqqında danışacağıq. MSDN-dən onların təsvirləri bunlardır:
BLOK ÖLÇÜSÜ = { blok ölçüsü | @ blok ölçüsü_dəyişən } - baytlarda fiziki blok ölçüsünü göstərir. Dəstəklənən ölçülər 512, 1024, 2048, 4096, 8192, 16, 384 və 32 baytdır (768 KB). Varsayılan dəyər lent cihazları üçün 65 və digər cihazlar üçün 536-dir. Adətən bu parametr lazım deyil, çünki BACKUP bəyanatı avtomatik olaraq cihaz üçün uyğun blok ölçüsünü seçir. Blok ölçüsünün təyin edilməsi avtomatik blok ölçüsü seçimini açıq şəkildə ləğv edir.
BUFFERCOUNT = { bufer sayı | @ bufersayı_dəyişən } - Ehtiyat əməliyyatı üçün istifadə olunacaq I/O buferlərinin ümumi sayını müəyyən edir. İstənilən müsbət tam dəyər təyin edə bilərsiniz, lakin çox sayda bufer Sqlservr.exe prosesində həddindən artıq virtual ünvan sahəsinə görə yaddaşdankənar xətaya səbəb ola bilər.
Buferlərin istifadə etdiyi ümumi boşluq miqdarı aşağıdakı düsturla müəyyən edilir:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSİZE = { maxtransfersize | @ maxtransfersize_dəyişən } SQL Server və ehtiyat dəst mediası arasında mübadilə etmək üçün baytlarda ən böyük məlumat paketinin ölçüsünü müəyyən edir. 65 baytdan (536 KB) 64 bayta (4 MB) qədər olan çoxluqlar dəstəklənir.
And içirəm - mən bunu əvvəllər oxumuşam, lakin onların məhsuldarlığa nə qədər təsir göstərə biləcəyi heç ağlıma da gəlməzdi. Üstəlik, görünür, bir növ "çıxış" etməliyəm və etiraf etməliyəm ki, indi də tam olaraq nə etdiklərini başa düşmürəm. Mən yəqin ki, buferləşdirilmiş I/O və sərt disklə işləmək haqqında daha çox oxumalıyam. Bir gün mən bunu edəcəm, amma hələlik bu dəyərlərin ehtiyat nüsxəsinin götürülmə sürətinə necə təsir etdiyini yoxlayacaq bir skript yaza bilərəm.
Təxminən 10 GB ölçüsündə kiçik bir verilənlər bazası yaratdım, onu SSD-yə yerləşdirdim və ehtiyat nüsxələri üçün kataloqu HDD-yə qoydum.
Nəticələri saxlamaq üçün müvəqqəti cədvəl yaradıram (müvəqqəti yoxdur, ona görə də nəticələri daha ətraflı öyrənə bilərəm, ancaq özünüz qərar verin):
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
);Skriptin prinsipi sadədir - hər biri bir parametrin dəyərini dəyişdirən iç içə döngələr, bu parametrləri BACKUP əmrinə daxil edin, msdb.dbo.backupset saytından tarixlə sonuncu qeydi qeyd edin, ehtiyat faylı və növbəti iterasiyanı silin. . Yedək icra məlumatları ehtiyat nüsxədən götürüldüyü üçün dəqiqlik bir qədər itirilir (saniyələrin fraksiyaları yoxdur), lakin biz bundan sağ çıxacağıq.
Əvvəlcə ehtiyat nüsxələrini silmək üçün xp_cmdshell-i aktiv etməlisiniz (sonra ehtiyacınız yoxdursa, onu söndürməyi unutmayın):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOYaxşı, əslində:
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;
ENDBirdən burada baş verənlərlə bağlı aydınlığa ehtiyacınız varsa, şərhlərdə və ya PM-də yazın. Hələlik sizə yalnız YEDEK VERİLƏNİŞ BAZASI-na qoyduğum parametrlər haqqında məlumat verəcəyəm.
BLOCKSIZE üçün bizdə "qapalı" dəyərlər siyahısı var və mən BLOCKSIZE < 4KB ilə ehtiyat nüsxəsini çıxarmadım. MAXTRANSFERSIZE 64KB-ə çox olan istənilən nömrəni - 64KB-dan 4MB-a qədər. Sistemimdə standart 1024KB, mən 512 - 1024 - 2048 - 4096 götürdüm.
BUFFERCOUNT ilə daha çətin idi - bu istənilən müsbət rəqəm ola bilər, lakin linkdə deyilir . O, həmçinin ehtiyat nüsxəsinin əslində hansı BUFFERCOUNT ilə edildiyi barədə məlumatı necə əldə edəcəyini deyir - mənim üçün bu, 7-dir. Onu azaltmağın mənası yox idi və yuxarı həddi eksperimental olaraq aşkar edildi - BUFFERCOUNT = 896 və MAXTRANSFERSIZE = 4194304 ilə ehtiyat nüsxə ilə düşdü. xəta (bu barədə yuxarıdakı linkdə yazılıb):
Mesaj 3013, Səviyyə 16, Dövlət 1, Sətir 7 YEDEK VERİLƏNİŞ BAZASI anormal şəkildə dayandırılır.
Mesaj 701, Səviyyə 17, Dövlət 123, Sətir 7 Bu sorğunu yerinə yetirmək üçün "defolt" resurs hovuzunda kifayət qədər sistem yaddaşı yoxdur.
Müqayisə üçün, ilk növbədə heç bir parametr göstərmədən ehtiyat nüsxəsini işlətməyin nəticələrini göstərəcəyəm:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Yaxşı, ehtiyat və ehtiyat nüsxə:
'bt' verilənlər bazası üçün 1070072 səhifə işlənmişdir, fayl 1-də 'bt' faylı.
'bt' verilənlər bazası, 2-ci faylda 'bt_log' faylı üçün 1 səhifə işlənmişdir.
BACKUP DATABASE 1070074 səhifəni 53.171 saniyəyə (157.227 MB/san) uğurla emal etdi.
Parametrləri sınayan skriptin özü bir neçə saat ərzində işlədi, bütün ölçmələr aparıldı . Və burada üç ən yaxşı icra müddəti ilə nəticələrin seçimi var (gözəl bir qrafik yaratmağa çalışdım, amma yazıda bir cədvəllə və şərhlərdə işləməli olacağam. əlavə etdi ).
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;
Diqqət, çox əhəmiyyətli bir qeyd haqqında :
Əminliklə deyə bilərik ki, bu dəyərlər diapazonunda parametrlər və ehtiyat sürəti arasındakı əlaqə təsadüfi, heç bir nümunə yoxdur. Ancaq daxili parametrlərdən uzaqlaşmaq nəticəyə yaxşı təsir etdi
Bunlar. Yalnız standart YEDƏKLƏMƏ parametrlərini idarə etməklə ehtiyat nüsxəsinin silinmə müddətində 2 dəfə artım əldə edildi: başlanğıcda 26 saniyəyə qarşı 53 saniyə. Bu pis deyil, hə? Ancaq bərpa ilə nə baş verdiyini görməliyik. Bəs bərpa etmək üçün indi 4 dəfə daha çox vaxt lazımdırsa necə?
Əvvəlcə standart parametrlərlə ehtiyat nüsxəni bərpa etmək üçün nə qədər vaxt lazım olduğunu ölçək:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Yaxşı, özünüz də bilirsiniz ki, yollar var, əvəz etmək əvəz etmək deyil, bərpa etmək bərpa deyil. Və mən bunu belə edirəm:
'bt' verilənlər bazası üçün 1070072 səhifə işlənmişdir, fayl 1-də 'bt' faylı.
'bt' verilənlər bazası, 2-ci faylda 'bt_log' faylı üçün 1 səhifə işlənmişdir.
MƏLUMAT BAZASI BƏRPA 1070074 səhifəni 40.752 saniyəyə (205.141 MB/san) uğurla emal etdi.
İndi BLOCKSIZE, BUFFERCOUNT və MAXTRANSFERSIZE dəyişdirilmiş ehtiyat nüsxələrini bərpa etməyə çalışacağam.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304MƏLUMAT BAZASI BƏRPA 1070074 səhifəni 32.283 saniyəyə (258.958 MB/san) uğurla emal etdi.
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304MƏLUMAT BAZASI BƏRPA 1070074 səhifəni 32.682 saniyəyə (255.796 MB/san) uğurla emal etdi.
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152MƏLUMAT BAZASI BƏRPA 1070074 səhifəni 32.091 saniyəyə (260.507 MB/san) uğurla emal etdi.
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304MƏLUMAT BAZASI BƏRPA 1070074 səhifəni 32.401 saniyəyə (258.015 MB/san) uğurla emal etdi.
RESTORE DATABASE bəyanatı bərpa zamanı dəyişmir, bu parametrlər orada göstərilmir; SQL Server özü onları ehtiyat nüsxədən müəyyən edir. Və aydındır ki, hətta sağalma ilə də qazanc ola bilər - demək olar ki, 20% daha sürətli (Düzünü desəm, bərpa etmək üçün çox vaxt sərf etmədim, bir neçə "ən sürətli" ehtiyat nüsxəsini nəzərdən keçirdim və heç bir pisləşmə olmadığına əmin oldum.).
Hər halda, aydınlaşdırım ki, bunlar hər kəs üçün optimal olan bəzi parametrlər deyil. Yalnız özünüz üçün optimal parametrləri sınaqdan keçirərək əldə edə bilərsiniz. Bu nəticələri aldım, siz fərqli nəticələr əldə edəcəksiniz. Ancaq görürsünüz ki, ehtiyat nüsxələrinizi "tənzimləyə" bilərsiniz və onlar həqiqətən daha sürətli formalaşıb yerləşdirə bilərlər.
Həm də sənədləri tam oxumağınızı şiddətlə tövsiyə edirəm, çünki sisteminizə xas olan nüanslar ola bilər.
Ehtiyat nüsxələri haqqında yazmağa başladığım üçün dərhal "tənzimləmə" parametrlərindən daha çox yayılmış daha bir "optimallaşdırma" haqqında yazmaq istəyirəm (başa düşdüyüm kimi, ən azı bəzi ehtiyat nüsxə proqramları tərəfindən istifadə olunur, bəlkə də parametrlərlə birlikdə. daha əvvəl təsvir edilmişdir), lakin Habré-də hələ təsvir edilməmişdir.
Sənədlərdə ikinci sətirə baxsaq, BACKUP DATABASE altında, orada görürük:
TO <backup_device> [ ,...n ]Sizcə, bir neçə backup_cihazı göstərsəniz nə baş verəcək? Sintaksis buna imkan verir. Və çox maraqlı bir şey baş verəcək - ehtiyat nüsxə sadəcə bir neçə cihaz üzərində "yayılacaq". Bunlar. hər bir "cihaz" fərdi olaraq yararsız olacaq, birini itirəcək, bütün ehtiyat nüsxəsini itirəcək. Bəs bu cür ləkələmə ehtiyat sürətə necə təsir edəcək?
Eyni qovluqda yan-yana yerləşən iki "cihazda" ehtiyat nüsxəsini çıxarmağa çalışaq:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Dünya ataları, bu niyə edilir?
'bt' verilənlər bazası üçün 1070072 səhifə işlənmişdir, fayl 1-də 'bt' faylı.
'bt' verilənlər bazası, 'bt' faylı üçün 2 səhifə işlənmişdir1 faylında log'.
BACKUP DATABASE 1070074 səhifəni 40.092 saniyəyə (208.519 MB/san) uğurla emal etdi.
Yedəkləmə 25% daha sürətli oldu? Bir neçə cihaz əlavə etsək nə olacaq?
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 səhifəni 34.234 saniyəyə (244.200 MB/san) uğurla emal etdi.
Ümumilikdə qazanc ehtiyat nüsxəsini götürmə vaxtının təxminən 35% -ni təşkil edir, çünki ehtiyat nüsxə bir anda bir diskdə 4 fayla yazılır. Daha böyük rəqəmi yoxladım - laptopumda heç bir qazanc yoxdur, optimal olaraq - 4 cihaz. Sizin üçün - bilmirəm, yoxlamaq lazımdır. Yaxşı, yeri gəlmişkən, bu cihazlarınız varsa - bunlar həqiqətən fərqli disklərdir, təbrik edirəm, qazanc daha da əhəmiyyətli olmalıdır.
İndi bu xoşbəxtliyi necə bərpa etmək barədə danışaq. Bunu etmək üçün bərpa əmrini dəyişdirməli və bütün cihazları siyahıya almalı olacaqsınız:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;MƏLUMAT BAZASI BƏRPA 1070074 səhifəni 38.027 saniyəyə (219.842 MB/san) uğurla emal etdi.
Bir az daha sürətli, lakin yaxın bir yerdə, əhəmiyyətli deyil. Ümumiyyətlə, ehtiyat nüsxə daha sürətli çıxarılır və eyni şəkildə bərpa olunur - uğur? Mənə gəlincə, bu, kifayət qədər uğurdur. Bu vacibdir, mən təkrar edirəm - əgər bu fayllardan ən azı birini itirsəniz, bütün ehtiyat nüsxəni itirmiş olursunuz.
Trace Flags 3213 və 3605-dən istifadə etməklə nümayiş olunan ehtiyat məlumatların jurnalına baxsanız, bir neçə cihaza ehtiyat nüsxə çıxararkən ən azı BUFFERCOUNT sayının artdığını görəcəksiniz. Yəqin ki, siz BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE üçün daha optimal parametrləri seçməyə cəhd edə bilərsiniz, lakin mən dərhal müvəffəq olmadım və yenidən belə bir sınaq keçirməyə çox tənbəl oldum, lakin fərqli sayda fayl üçün. Və təkərlər haqqında ayıbdır. Evdə belə bir test təşkil etmək istəyirsinizsə, ssenarini yenidən hazırlamaq çətin deyil.
Nəhayət, qiymətdən danışaq. Əgər ehtiyat nüsxə istifadəçilərin işi ilə paralel olaraq silinirsə, testə çox məsuliyyətli yanaşmaq lazımdır, çünki ehtiyat nüsxə daha tez çıxarılarsa, disklər daha çox gərginləşir, prosessorun yükü artır (hələ də sıxılmalısan) tez) və müvafiq olaraq sistemin ümumi reaksiyası azalır.
Sadəcə zarafat edirəm, amma heç bir açıqlama vermədiyimi çox yaxşı başa düşürəm. Yuxarıda yazılanlar sadəcə olaraq ehtiyat nüsxələrinin çıxarılması üçün optimal parametrləri necə seçə biləcəyinizi nümayiş etdirir.
Unutmayın ki, etdiyiniz hər şey öz təhlükəniz və riskinizlə edilir. Yedəkləmələrinizi yoxlayın və DBCC CHECKDB haqqında unutmayın.
Mənbə: www.habr.com