ProHoster > Blogs > Administrācija > MS SQL Server: rezerves kopija uz steroīdiem

MS SQL Server: rezerves kopija uz steroīdiem

Pagaidi! Pagaidi! Tiesa, Å”is nav vēl viens raksts par SQL Server dublējumu veidiem. Es pat nerunāŔu par atkopÅ”anas modeļu atŔķirÄ«bām un to, kā rÄ«koties ar aizauguÅ”u baļķi.

VarbÅ«t (tikai varbÅ«t) pēc Ŕī ieraksta izlasÄ«Å”anas jÅ«s varēsiet pārliecināties, ka dublējums, kas no jums tiek noņemts, izmantojot standarta lÄ«dzekļus, tiks noņemts rÄ«tvakar, labi, 1.5 reizes ātrāk. Un tikai tāpēc, ka izmantojat nedaudz vairāk BACKUP DATABASE parametru.

Ja ziņas saturs jums bija skaidrs, atvainojiet. IzlasÄ«ju visu, ko Google nokļuva frāzei ā€œhabr sql server backupā€, un nevienā rakstā neatradu pieminētu, ka dublÄ“Å”anas laiku var kaut kā ietekmēt ar parametriem.

Uzreiz vērsÅ”u jÅ«su uzmanÄ«bu uz Aleksandra Gladčenko komentāru (@mssqlhelp):

RažoÅ”anā nekad nemainiet parametrus BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE. Tie ir radÄ«ti tikai Ŕādu rakstu rakstÄ«Å”anai. Praksē jÅ«s ātri atbrÄ«vosities no atmiņas problēmām.

Protams, bÅ«tu forÅ”i bÅ«t gudrākajam un publicēt ekskluzÄ«vu saturu, taču diemžēl tas tā nav. Å ai tēmai ir veltÄ«ti raksti/raksti gan angļu, gan krievu valodā (es vienmēr esmu neizpratnē, kā tos pareizi nosaukt). Å eit ir daži no tiem, ar kuriem es saskāros: laiks, Š“Š²Š°, trÄ«s (vietnē sql.ru).

Tāpēc, lai sāktu, es pievienoÅ”u nedaudz noņemtu REZERVES sintaksi no MSDN (starp citu, es rakstÄ«ju iepriekÅ” par DUBLĒJUMU DATU BĀZI, bet tas viss attiecas gan uz darÄ«jumu žurnāla dublÄ“Å”anu, gan diferenciālo dublÄ“Å”anu, bet varbÅ«t ar mazāk acÄ«mredzamu efektu):

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 }
<...>

<ā€¦> - tas nozÄ«mē, ka tur kaut kas bija, bet es to noņēmu, jo tagad tas neattiecas uz tēmu.

Kā jÅ«s parasti veidojat dublējumu? Kā viņi "māca", kā izveidot rezerves kopijas miljardos rakstu? Kopumā, ja man ir nepiecieÅ”ams veikt vienreizēju kādas ne pārāk lielas datu bāzes dublējumu, es automātiski ierakstÄ«Å”u apmēram Ŕādu:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--Š»Š°Š“Š½Š¾, CHECKSUM я Š½Š°ŠæŠøсŠ°Š» тŠ¾Š»ŃŒŠŗŠ¾ чтŠ¾Š±Ń‹ ŠŗŠ°Š·Š°Ń‚ŃŒŃŃ уŠ¼Š½ŠµŠµ

Un vispār, iespējams, Å”eit ir uzskaitÄ«ti 75-90% no visiem parametriem, kas parasti tiek minēti rakstos par dublÄ“Å”anu. Nu ir arÄ« INIT, SKIP. Vai esat apmeklējis MSDN? Vai esat redzējuÅ”i, ka ir iespējas pusotram ekrānam? Es arÄ« redzēju...

JÅ«s droÅ”i vien jau sapratāt, ka tālāk mēs runāsim par trim parametriem, kas palika pirmajā koda blokā - BLOCKSIZE, BUFFERCOUNT un MAXTRANSFERSIZE. Å eit ir viņu apraksti no MSDN:

BLOKA IZMĒRS = { bloka izmērs | @ blocksize_mainable } ā€” norāda bloka fizisko izmēru baitos. AtbalstÄ«tie izmēri ir 512, 1024, 2048, 4096, 8192, 16 384, 32 768 un 65 536 baiti (64 KB). Noklusējuma vērtÄ«ba ir 65 lentes ierÄ«cēm un 536 citām ierÄ«cēm. Parasti Å”is parametrs nav nepiecieÅ”ams, jo priekÅ”raksts BACKUP automātiski atlasa ierÄ«cei atbilstoÅ”o bloka izmēru. Bloka lieluma iestatÄ«Å”ana nepārprotami ignorē automātisko bloka izmēra izvēli.

BUFERSKAITS = { bufera skaits | @ buferskaita_mainÄ«gais } ā€” definē kopējo I/O buferu skaitu, kas tiks izmantots dublÄ“Å”anas darbÄ«bai. Varat norādÄ«t jebkuru pozitÄ«vu veselu skaitļa vērtÄ«bu, taču liels skaits buferu var izraisÄ«t kļūdu, kas nav atmiņa, jo Sqlservr.exe procesā ir pārāk liela virtuālā adreÅ”u telpa.

Kopējo buferu izmantotās vietas daudzumu nosaka pēc Ŕādas formulas: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maksimālais pārsÅ«tÄ«Å”anas lielums | @ maxtransfersize_variable } norāda lielāko datu paketes lielumu baitos, ar ko apmainÄ«ties starp SQL Server un dublējuma kopas datu nesēju. Tiek atbalstÄ«ti vairāki 65 536 baiti (64 KB) lÄ«dz 4 194 304 baiti (4 MB).

Es zvēru ā€” es to esmu lasÄ«jis iepriekÅ”, bet man nekad nav ienācis prātā, cik liela ietekme tiem varētu bÅ«t uz produktivitāti. Turklāt acÄ«mredzot man ir jāizdara sava veida "iznākÅ”ana" un jāatzÄ«st, ka pat tagad es pilnÄ«bā nesaprotu, ko viņi dara. Man, iespējams, jālasa vairāk par buferizēto I/O un darbu ar cieto disku. Kādreiz es to izdarÄ«Å”u, bet pagaidām varu vienkārÅ”i uzrakstÄ«t skriptu, kas pārbaudÄ«s, kā Ŕīs vērtÄ«bas ietekmē dublÄ“Å”anas ātrumu.

Es izveidoju nelielu datu bāzi, apmēram 10 GB lielu, ievietoju to SSD un ievietoju HDD dublējumkopiju direktoriju.

Es izveidoju pagaidu tabulu, lai saglabātu rezultātus (man tās nav pagaidu, lai es varētu sīkāk izpētīt rezultātus, bet jūs izlemjat pats):

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
);

Skripta darbÄ«bas princips ir vienkārÅ”s ā€“ ligzdotas cilpas, no kurām katra maina viena parametra vērtÄ«bu, ievieto Å”os parametrus komandā BACKUP, saglabā pēdējo ierakstu ar vēsturi no msdb.dbo.backupset, izdzÄ“Å” dublējuma failu un nākamo iterāciju. . Tā kā dublējuma izpildes dati tiek ņemti no dublējuma, precizitāte ir nedaudz zaudēta (nav sekunžu daļas), taču mēs to pārdzÄ«vosim.

Vispirms jums ir jāiespējo xp_cmdshell, lai dzēstu dublējumus (pēc tam neaizmirstiet to atspējot, ja jums tas nav nepiecieÅ”ams): 

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;  
GO

Nu patiesībā:

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

Ja pēkŔņi nepiecieÅ”ams skaidrojums par to, kas Å”eit notiek, rakstiet komentāros vai PM. Pagaidām es jums pastāstÄ«Å”u tikai par parametriem, kurus ievietoju DUBLĒJUMA DATU BĀZĒ.

BLOCKSIZE mums ir ā€œslēgtsā€ vērtÄ«bu saraksts, un es neveicu dublÄ“Å”anu ar BLOCKSIZE < 4 KB. MAXTRANSFERSIZE jebkuru numuru, kas ir 64 KB reizināts ā€” no 64 KB lÄ«dz 4 MB. Manā sistēmā noklusējuma vērtÄ«ba ir 1024 KB, es izmantoju 512ā€“1024ā€“2048ā€“4096.

GrÅ«tāk bija ar BUFFERCOUNT - tas var bÅ«t jebkurÅ” pozitÄ«vs skaitlis, bet saite saka kā tas tiek aprēķināts REZERVES DATU BĀZĒ un kāpēc lielas vērtÄ«bas ir bÄ«stamas?. Tur arÄ« teikts, kā iegÅ«t informāciju par to, ar kuru BUFFERCOUNT faktiski tiek veidots dublējums - man tas ir 7. Nebija jēgas to samazināt, un augŔējā robeža tika atklāta eksperimentāli - ar BUFFERCOUNT = 896 un MAXTRANSFERSIZE = 4194304 dublējums nokrita ar kļūda (par to rakstÄ«ts augstāk esoÅ”ajā saitē):

3013. ziņojums, 16. lÄ«menis, 1. stāvoklis, 7. rindiņa DUBLĒJUMA DATU BĀZES darbÄ«ba tiek pārtraukta neparasti.

Msg 701, Level 17, State 123, Line 7 Resursu pÅ«lā ā€œnoklusējumsā€ nepietiek sistēmas atmiņas, lai izpildÄ«tu Å”o vaicājumu.

SalÄ«dzinājumam es vispirms parādÄ«Å”u dublējuma palaiÅ”anas rezultātus, nenorādot nekādus parametrus:

BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;

Nu, dublēŔana un dublēŔana:

Apstrādātas 1070072 lapas datubāzei ā€œbtā€, fails ā€œbtā€ 1. failā.

Apstrādātas 2 lapas datubāzei ā€œbtā€, fails ā€œbt_logā€ 1. failā.

REZERVES DATU BĀZE veiksmīgi apstrādāja 1070074 lapas 53.171 sekundē (157.227 MB/sek).

Pats skripts, pārbaudot parametrus, nostrādāja pāris stundās, visi mērÄ«jumi bija iekŔā google izklājlapa. Un Å”eit ir rezultātu izlase ar trim labākajiem izpildes laikiem (mēģināju izveidot skaistu grafiku, bet ierakstā bÅ«s jāiztiek ar tabulu un komentāros @mixsture pievienotās ļoti forÅ”a grafika).

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;

MS SQL Server: rezerves kopija uz steroīdiem

Uzmanību, ļoti svarīga piezīme no @mixsture no komentārs:

Varam droÅ”i teikt, ka saikne starp parametriem un dublÄ“Å”anas ātrumu Å”ajos vērtÄ«bu diapazonos ir nejauÅ”a, nav nekāda modeļa. Bet attālināŔanās no iebÅ«vētajiem parametriem acÄ«mredzami labi ietekmēja rezultātu

Tie. Tikai pārvaldot standarta BACKUP parametrus, dublējuma noņemÅ”anas laiks tika palielināts 2 reizes: 26 sekundes, salÄ«dzinot ar 53 sākumā. Tas nav slikti, vai ne? Bet mums jāskatās, kas notiks ar atjaunoÅ”anu. Ko darÄ«t, ja tagad atveseļoÅ”anās prasa 4 reizes ilgāku laiku?

Vispirms izmērÄ«sim, cik ilgs laiks nepiecieÅ”ams, lai atjaunotu dublējumu ar noklusējuma iestatÄ«jumiem.

RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;

Nu, jÅ«s pats to zināt, veidi ir, nomaiņa nav aizstāta, atveseļoÅ”anās nav atveseļoÅ”anās. Un es to daru Ŕādi:

Apstrādātas 1070072 lapas datubāzei ā€œbtā€, fails ā€œbtā€ 1. failā.

Apstrādātas 2 lapas datubāzei ā€œbtā€, fails ā€œbt_logā€ 1. failā.

RESTORE DATABASE veiksmīgi apstrādāja 1070074 lapas 40.752 sekundēs (205.141 MB/sek).

Tagad es mēģināŔu atjaunot dublējumkopijas, kas uzņemtas ar mainÄ«tu BLOCKSIZE, BUFFERCOUNT un MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE veiksmīgi apstrādāja 1070074 lapas 32.283 sekundēs (258.958 MB/sek).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE veiksmīgi apstrādāja 1070074 lapas 32.682 sekundēs (255.796 MB/sek).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE veiksmīgi apstrādāja 1070074 lapas 32.091 sekundēs (260.507 MB/sek).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE veiksmīgi apstrādāja 1070074 lapas 32.401 sekundēs (258.015 MB/sek).

RESTORE DATABASE priekÅ”raksts atkopÅ”anas laikā nemainās; Å”ie parametri tajā nav norādÄ«ti; SQL Server pats tos nosaka no dublējuma. Un ir skaidrs, ka pat ar atveseļoÅ”anos var bÅ«t ieguvums - gandrÄ«z par 20% ātrāk (GodÄ«gi sakot, es netērēju daudz laika atkopÅ”anai, es izgāju vairākas "ātrākās" dublējumkopijas un pārliecinājos, ka nav pasliktināŔanās.).

Katram gadÄ«jumam ļaujiet man precizēt, ka tie nav daži parametri, kas ir optimāli ikvienam. Optimālos parametrus sev var iegÅ«t tikai pārbaudot. Es saņēmu Å”os rezultātus, jÅ«s saņemsiet dažādus rezultātus. Bet jÅ«s redzat, ka varat ā€œnoregulētā€ savus dublējumus, un tos faktiski var izveidot un izvietot ātrāk.

Es arī ļoti iesaku pilnībā izlasīt dokumentāciju, jo tajā var būt jūsu sistēmai raksturīgas nianses.

KopÅ” sāku rakstÄ«t par dublējumkopijām, uzreiz gribu uzrakstÄ«t vēl vienu ā€œoptimizācijuā€, kas ir biežāk sastopama nekā parametru ā€œtuningoÅ”anaā€ (cik saprotu, to izmanto vismaz daži rezerves utilÄ«ti, varbÅ«t kopā ar parametriem aprakstÄ«ts iepriekÅ”), taču tas vēl nav aprakstÄ«ts arÄ« Habrē.

Ja mēs skatāmies uz otro rindiņu dokumentācijā tieÅ”i zem DUBLĒJUMA DATU BĀZES, mēs redzam:

TO <backup_device> [ ,...n ]

Kas, jÅ«suprāt, notiks, ja norādÄ«siet vairākas rezerves_ierÄ«ces? Sintakse to atļauj. Un notiks ļoti interesanta lieta - dublējums vienkārÅ”i tiks ā€œizkliedētsā€ vairākās ierÄ«cēs. Tie. katra ā€œierÄ«ceā€ atseviŔķi bÅ«s bezjēdzÄ«ga, pazaudēta viena, pazaudēta visa dublÄ“Å”ana. Bet kā Ŕāda smērÄ“Å”ana ietekmēs dublÄ“Å”anas ātrumu?

Mēģināsim izveidot dublējumu divās ā€œierÄ«cēsā€, kas atrodas blakus vienā mapē:

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak'   
WITH COMPRESSION;

Pasaules tēvi, kāpēc tas tiek darīts?

Apstrādātas 1070072 lapas datubāzei ā€œbtā€, fails ā€œbtā€ 1. failā.

Apstrādātas 2 lapas datubāzei "bt", failam "bt"piesakieties failā 1.

REZERVES DATU BĀZE veiksmīgi apstrādāja 1070074 lapas 40.092 sekundē (208.519 MB/sek).

Vai dublÄ“Å”ana no zila gaisa kļuva par 25% ātrāka? Ko darÄ«t, ja pievienosim vēl pāris ierÄ«ces?

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;

REZERVES DATU BĀZE veiksmīgi apstrādāja 1070074 lapas 34.234 sekundē (244.200 MB/sek).

Kopumā ieguvums ir aptuveni 35% no dublÄ“Å”anas laika, tikai pateicoties tam, ka dublējums tiek ierakstÄ«ts 4 failos vienā diskā vienlaikus. PārbaudÄ«ju lielāku skaitu - portatÄ«vajā nav pastiprinājuma, optimāli - 4 ierÄ«ces. Jums - es nezinu, jums ir jāpārbauda. Nu, starp citu, ja jums ir Ŕīs ierÄ«ces - tie tieŔām ir dažādi diski, apsveicam, ieguvumam vajadzētu bÅ«t vēl ievērojamākam.

Tagad parunāsim par to, kā atjaunot Ŕo laimi. Lai to izdarītu, jums būs jāmaina atkopŔanas komanda un jāuzskaita visas ierīces:

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 veiksmīgi apstrādāja 1070074 lapas 38.027 sekundēs (219.842 MB/sek).

Nedaudz ātrāk, bet kaut kur tuvu, nenozÄ«mÄ«gi. Kopumā dublējums tiek noņemts ātrāk un atjaunots tāpat - veiksme? Kas attiecas uz mani, tas ir diezgan veiksmÄ«gs. Å is ir svarÄ«gs, tāpēc atkārtoju - ja tu ja pazaudējat vismaz vienu no Å”iem failiem, jÅ«s zaudējat visu dublējumu.

Ja skatāties žurnālā dublējuma informāciju, kas tiek parādÄ«ta, izmantojot Trace Flags 3213 un 3605, jÅ«s ievērosiet, ka, veicot dublÄ“Å”anu vairākās ierÄ«cēs, palielinās vismaz BUFFERCOUNT skaits. DroÅ”i vien varat mēģināt izvēlēties optimālākus parametrus BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, bet man tas uzreiz neizdevās, un man bija slinkums, lai veiktu Ŕādu pārbaudi vēlreiz, bet citam failu skaitam. Un žēl riteņu. Ja vēlaties organizēt Ŕādu testÄ“Å”anu mājās, skriptu nav grÅ«ti pārtaisÄ«t.

Visbeidzot, parunāsim par cenu. Ja dublējums tiek noņemts paralēli lietotāju darbam, testÄ“Å”anai ir jāpieiet ļoti atbildÄ«gi, jo, ja dublējums tiek noņemts ātrāk, diski tiek vairāk noslogoti, palielinās procesora slodze (joprojām ir jāsaspiež to lidojumā), un attiecÄ«gi samazinās sistēmas kopējā reaģētspēja.

Tikai jokoju, bet es lieliski saprotu, ka nekādu atklāsmi neizdaru. IepriekÅ” rakstÄ«tais vienkārÅ”i parāda, kā varat izvēlēties optimālos parametrus dublējumkopiju veidoÅ”anai.

Atcerieties, ka viss, ko darāt, tiek darīts, uzņemoties risku un risku. Pārbaudiet dublējumus un neaizmirstiet par DBCC CHECKDB.

Avots: www.habr.com

Pievieno komentāru