🥇MS SQL Server: BACKUP on steroids

Sug! Sug! Run, tani maaha maqaal kale oo ku saabsan noocyada kaydinta SQL Server. Xitaa kama hadli doono farqiga u dhexeeya moodooyinka soo kabashada iyo sida loola tacaalo log-korka.

Waxaa laga yaabaa (laga yaabee), ka dib markaad akhrido boostada, waxaad awoodi doontaa inaad hubiso in kaydinta lagaa saaray adiga oo isticmaalaya macnaha caadiga ah laga saari doono habeen dambe, si fiican, 1.5 jeer ka dhakhso badan. Oo kaliya sababtoo ah xaqiiqda ah inaad isticmaashid wax yar oo dheeraad ah oo ku soo kabanaya cabbiraadaha DATABASE.

Haddii nuxurka qoraalkaagu kuu cad yahay, waan ka xumahay. Waxaan akhriyay wax kasta oo Google u helay weedha "habr sql server backup", hal maqaalna kama helin wax sheegid xaqiiqda ah in wakhtiga kaydinta si uun loo saameyn karo iyadoo la adeegsanayo cabbirada.

Waxaan isla markiiba soo jeedin doonaa dareenkaaga faallooyinka Alexander Gladchenko (@mssqlhelp):

Weligaa ha beddelin BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE cabbirrada wax soo saarka. Waxa loo sameeyay oo keliya qorista maqaalladaas. Ficil ahaan, waxaad ka takhalusi doontaa dhibaatooyinka xusuusta waqti yar gudahood.

Dabcan, way fiicnaan lahayd inaad noqoto kan ugu xariifsan oo dhajinta nuxur gaar ah, laakiin, nasiib daro, tani maahan. Waxaa jira maqaallo/posts-luqad-Ingiriisi iyo Ruush-ba (mar walba waan ku wareersanahay waxa loogu yeero si sax ah) oo u go'ay mawduucan. Waa kuwan qaar ka mid ah kuwa aan la kulmay: jeer, два, saddex (sql.ru).

Marka, si aan ku bilaabo, waxaan ku soo lifaaqi doonaa hab-raac waxyar oo hoos loo dhigay oo BACKUP ah MSDN (Sidoo kale, waxaan kor ku qoray wax ku saabsan BACKUP DATABASE, laakiin waxaas oo dhan waxay khuseeyaan labadaba kaydinta kaydinta macaamil ganacsi iyo kayd kala duwan, laakiin laga yaabee inay leedahay saameyn yar oo muuqata):

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

<…> - waxa ay la macno tahay in ay wax jiraan, laakiin waan ka saaray sababta oo ah hadda ma khusayso mawduuca.

Sidee badiyaa u qaataa kayd? Sidee bay u "bareen" sida loogu qaado kaydka balaayiin maqaallo ah? Guud ahaan, haddii aan u baahdo in aan sameeyo kayd hal-mar ah qaar aan aad u weynayn, waxaan si toos ah u qori doonaa wax sidan oo kale ah:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее

Iyo, guud ahaan, malaha 75-90% dhammaan cabbiraadaha inta badan lagu sheego maqaallada ku saabsan kaydinta ayaa halkan ku taxan. Hagaag, waxa kale oo jira INIT, GUDUB. Ma booqatay MSDN? Miyaad aragtay inay jiraan xulashooyin hal shaashad iyo badh ah? Waxaan sidoo kale arkay...

Waxay u badan tahay inaad horeba u garatay inaan ka sii hadli doono saddexda cabbir ee ku hadhay qaybta koodhka - BLOCKSIZE, BUFFERCOUNT iyo MAXTRANSFERSIZE. Waa kuwan sharraxaaddooda MSDN:

BLOCKSIZE = { blocksize | @ blocksize_variable } - waxay muujinaysaa cabbirka xannibaadda jirka ee bytes. Cabbirrada la taageeray waa 512, 1024, 2048, 4096, 8192, 16, 384, iyo 32 bytes (768 KB). Qiimaha caadiga ah waa 65 ee qalabka cajaladaha iyo 536 qalabyada kale. Caadi ahaan halbeegyadan lagama maarmaan ma aha sababtoo ah bayaanka BACKUP wuxuu si toos ah u dooranayaa cabbirka ku habboon aaladda. Dejinta cabbirka xannibaadda ayaa si cad u burinaysa xulashada cabbirka xannibaadda tooska ah.

BUFFERCOUNT = { tirsiga | @ buffercount_variable } - Wuxuu qeexayaa tirada guud ee kaydiyeyaasha I/O ee loo isticmaali doono hawlgalka kaydinta. Waxaad qeexi kartaa qiime kasta oo togan, laakiin tiro badan oo kaydiyayaal ah ayaa laga yaabaa inay keenaan khalad xusuusta ka baxsan sababtoo ah booska ciwaanka farsamada ee xad dhaafka ah ee habka Sqlservr.exe.

Tirada guud ee booska ay adeegsadaan kaydiyayaashu waxa lagu go'aamiyaa qaacidada soo socota: BUFFERCOUNT * MAXTRANSFERSIZE.

BANDHIGGA = { maxtransfersize | @ maxtransfersize_variable } waxa uu qeexayaa cabbirka baakidhka xogta ugu weyn, ee bytes, si loo beddelo inta u dhaxaysa Server-ka SQL iyo warbaahinta kaydinta. Dhowr jeer oo ah 65 bytes (536 KB) ilaa 64 bytes (4 MB) ayaa la taageeray.

Waan ku dhaartaye - tan hore ayaan u akhriyey, laakiin weligeed iguma dhicin inta ay la egtahay saamaynta ay ku yeelan karaan wax soo saarka. Intaa waxaa dheer, sida muuqata, waxaan u baahanahay inaan sameeyo nooc ka mid ah "soo baxay" oo aan qirto in xitaa hadda aanan si buuxda u fahmin waxa dhabta ah ee ay sameynayaan. Waxa ay u badan tahay in aan u baahanahay in aan wax badan ka akhriyo I/O-da la keydiyay iyo ku shaqaynta wadida adag. Maalin ayaan tan samayn doonaa, laakiin hadda waxaan qori karaa qoraal oo hubin doona sida qiyamkani u saameeyaan xawaaraha kaydinta.

Waxaan sameeyay xog-ururin yar, oo cabbirkeedu dhan yahay 10 GB, waxaan dul saaray SSD-ga, oo waxaan dul dhigay buugga kaydinta HDD-ga.

Waxaan abuuraa miis ku meel gaar ah si aan u kaydiyo natiijooyinka (ma haysto ku meel gaadh, markaa waxaan si faahfaahsan u qodi karaa natiijooyinka, laakiin adiga ayaa go'aaminaya naftaada):

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

Mabda'a qoraalku waa mid fudud - wareegyada buulka ah, mid kasta oo ka mid ah wuxuu beddelayaa qiimaha halbeeg, geli xuduudahan amarka BACKUP, ka badbaadi rikoorka ugu dambeeya ee taariikhda msdb.dbo.backupset, tirtir faylka kaydinta iyo soo-celinta soo socota . Maadaama xogta fulinta kaydinta laga soo qaatay kaydka kaydka, saxnimadu xoogaa way luntay (ma jiraan jajab ilbidhiqsi), laakiin waynu ka badbaadi doonnaa tan.

Marka hore waxaad u baahan tahay inaad awood u siiso xp_cmdshell si aad u tirtirto kaydka (ka dibna ha iloobin inaad damiso haddii aadan u baahnayn):

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

Hagaag, dhab ahaantii:

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

Haddii aad si lama filaan ah ugu baahan tahay caddayn ku saabsan waxa halkan ka dhacaya, ku qor faallooyinka ama PM. Hadda, waxaan kaliya kaaga sheegi doonaa wax ku saabsan cabbiraadaha aan ku dhejiyay BACKUP DATABASE.

BLOCKSIZE waxaan ku haynaa liis qiyam ah oo “xiran”, mana aanan samayn gurmad leh BLOCKSIZE <4KB. KOR DHIG tiro kasta oo ah isku dhufashada 64KB - laga bilaabo 64KB ilaa 4MB. Habka caadiga ah ee nidaamkaygu waa 1024KB, waxaan qaatay 512 - 1024 - 2048 - 4096.

Aad bay ugu adkeyd BUFFERCOUNT - waxay noqon kartaa nambar kasta oo togan, laakiin xiriirku wuxuu leeyahay sidee loogu xisaabiyaa BACKUP DATABASE iyo sababta qiimaha waaweyni ay khatar u yihiin?. Waxa kale oo ay leedahay sida loo helo macluumaadka ku saabsan BUFFERCOUNT dhabta ah ee lagu sameeyay - aniga ahaan waa 7. Wax macno ah ma jirin in la dhimo, xadka sarena waxaa la ogaaday si tijaabo ah - BUFFERCOUNT = 896 iyo MAXTRANSFERSIZE = 4194304 kaydku wuxuu ku dhacay qalad (ku saabsan kaas oo ku qoran xiriirka sare):

Msg 3013, Heerka 16, Gobolka 1, Line 7 BACKUP DATABASE ayaa si aan caadi ahayn u dhamaanaysa.

Msg 701, Heerka 17, Gobolka 123, Sadarka 7 Ma jiro hab ku filan xusuusta ku jirta barkada kheyraadka 'default' si loo socodsiiyo weydiintan.

Isbarbardhigga, waxaan marka hore tusi doonaa natiijooyinka socodsiinta kaydinta iyadoon la cayimin haba yaraatee:

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

Hagaag, kayd iyo gurmad:

Waxaa loo habeeyey 1070072 bog xogta 'bt', faylka 'bt' ee faylka 1.

Loo habeeyey 2 bog oo loogu talagalay xogta 'bt', faylka 'bt_log' ee faylka 1.

BACKUP DATABASE waxay si guul leh uga baaraandegtay 1070074 bog 53.171 ilbiriqsi (157.227 MB/sec).

Qoraalka laftiisa, tijaabinta xuduudaha, wuxuu shaqeeyay dhowr saacadood, dhammaan cabbirada ayaa ku jiray xaashida google. Oo halkan waa xulasho natiijooyin leh saddexda jeer ee ugu wanaagsan ee fulinta (Waxaan isku dayay inaan sameeyo garaaf fiican, laakiin boostada waa inaan ku sameeyaa miis, iyo faallooyinka @isku dhafka ku daray sawiro aad u fiican).

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;

Fiiro gaar ah, qoraal aad muhiim u ah oo ka yimid @isku dhafka ka faallo:

Waxaan si kalsooni leh u dhihi karnaa in xiriirka ka dhexeeya xuduudaha iyo xawaaraha kaydinta ee kala duwanaantan qiimaha waa random, ma jiro qaab. Laakin ka guurista halbeegyada la dhisay waxay si cad u saameyn wanaagsan ku yeelatay natiijada

Kuwaas. Kaliya marka la maareeyo cabirrada BACKUP ee caadiga ah waxay ahayd faa'iido 2 laab ah wakhtiga ka saarista kaydinta: 26 ilbiriqsi, marka loo eego 53 bilowga. Ma xuma, sax? Laakiin waxaan u baahanahay inaan aragno waxa ku dhacaya soo celinta. Maxaa dhacaya haddii ay hadda qaadato 4 jeer ka dheer soo kabashada?

Marka hore, aynu cabbirno inta ay qaadanayso in dib loo soo celiyo kaydinta goobaha caadiga ah:

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

Hagaag, adiga laftaadu waad ogtahay in, siyaabaha ay jiraan, beddelaadku maaha beddelka, soo kabashada maaha soo kabasho. Oo waxaan u sameeyaa sidan:

Waxaa loo habeeyey 1070072 bog xogta 'bt', faylka 'bt' ee faylka 1.

Loo habeeyey 2 bog oo loogu talagalay xogta 'bt', faylka 'bt_log' ee faylka 1.

Soo Celinta DATABASE waxay si guul leh uga baaraandegtay 1070074 bog 40.752 ilbiriqsi (205.141 MB/sec).

Hadda waxaan isku dayi doonaa inaan soo celiyo kaydka lagu sameeyay BLOCKSIZE, BUFFERCOUNT iyo MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

Soo Celinta DATABASE waxay si guul leh uga baaraandegtay 1070074 bog 32.283 ilbiriqsi (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

Soo Celinta DATABASE waxay si guul leh uga baaraandegtay 1070074 bog 32.682 ilbiriqsi (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

Soo Celinta DATABASE waxay si guul leh uga baaraandegtay 1070074 bog 32.091 ilbiriqsi (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

Soo Celinta DATABASE waxay si guul leh uga baaraandegtay 1070074 bog 32.401 ilbiriqsi (258.015 MB/sec).

Bayaanka RESTORE DATABASE isma beddelo inta lagu jiro soo kabashada; xuduudahan laguma cayimin dhexdeeda; SQL Server lafteeda ayaa ka go'aamisa kaydinta. Way caddahay in xitaa dib u soo kabashada ay jiri karto faa'iido - ku dhawaad 20% dhakhso ah (Si daacadnimo ah, waqti badan kuma bixin soo kabashada, waxaan ku dhex maray dhowr ka mid ah kaydinta "ugu dhaqsaha badan" oo waxaan hubiyay in aysan jirin wax ka sii daraya.).

Kaliya haddii ay dhacdo, aan caddeeyo in kuwani aysan ahayn qaar ka mid ah xuduudaha u fiican qof kasta. Waxa kaliya oo aad ku heli kartaa cabbirada ugu fiican naftaada marka aad tijaabiso. Waxaan helay natiijooyinkan, waxaad heli doontaa kuwo kala duwan. Laakiin waxaad arkaysaa in aad "habayn karto" kaydintaada oo ay dhab ahaantii samayn karaan oo ay u diri karaan si degdeg ah.

Waxaan sidoo kale si adag kuugula talinayaa inaad akhrido dukumeentiyada oo dhan, sababtoo ah waxaa jiri kara nuances gaar ah nidaamkaaga.

Tan iyo markii aan bilaabay wax ku saabsan kaydinta, waxaan rabaa in aan isla markiiba wax ka qoro hal kale oo "optimization", taas oo ka badan "habaynta" xuduudaha (inta aan fahamsanahay, waxaa isticmaala ugu yaraan qaar ka mid ah yutiilitida kaydinta, laga yaabee inay la socdaan xuduudaha. mar hore lagu sifeeyay), laakiin wali laguma sifayn Habré midkoodna.

Haddi aan eegno xariiqda labaad ee dukumeentiga, ee hoos yimaada BACKUP DATABASE, waxaan ku aragnaa:

TO <backup_device> [ ,...n ]

Maxaad u malaynaysaa inay dhici doonto haddii aad cadayso dhawr qalab oo kayd ah? syntax ayaa u ogolaanaya. Oo wax aad u xiiso badan ayaa dhici doona - kaydinta ayaa si fudud "ku faafin doonta" dhowr qalab. Kuwaas. "qalab" kasta si gaar ah ayaa noqon doona mid aan faa'iido lahayn, mid lumay, lumay kaydkii oo dhan. Laakin sidee bay af-xumada noocaas ahi u saamaynaysaa xawaaraha kaydinta?

Aynu isku dayno inaan ku samayno kayd laba “qalab” kuwaas oo ku yaal dhinac dhinac isla galkii:

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

Aabbayaas adduunyo, maxaa tan loo yeelayaa?

Waxaa loo habeeyey 1070072 bog xogta 'bt', faylka 'bt' ee faylka 1.

Loo habeeyey 2 bog oo loogu talagalay keydka 'bt', faylka 'bt'ku qor faylka 1.

BACKUP DATABASE waxay si guul leh uga baaraandegtay 1070074 bog 40.092 ilbiriqsi (208.519 MB/sec).

Kaabashadu miyay noqotay 25% ka dhakhso badan buluugga? Maxaa dhacaya haddii aan ku darno laba qalab oo dheeraad ah?

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

BACKUP DATABASE waxay si guul leh uga baaraandegtay 1070074 bog 34.234 ilbiriqsi (244.200 MB/sec).

Wadar ahaan, faa'iidada ayaa ku saabsan 35% wakhtiga qaadashada kaydinta kaliya sababtoo ah xaqiiqda ah in kaydinta lagu qoro 4 fayl hal mar disk. Waxaan hubiyay tiro ka badan - ma jiraan wax faa'iido ah oo ku saabsan laptop-kayga, sida ugu fiican - 4 qalab. Adiga - ma garanayo, waxaad u baahan tahay inaad hubiso. Waa hagaag, habka, haddii aad haysato qalabkan - kuwani waa saxanno kala duwan, hambalyo, faa'iidada waa inay noqotaa mid aad u muhiim ah.

Hadda aan ka hadalno sida loo soo celiyo farxaddan. Si tan loo sameeyo, waa inaad beddeshaa amarka soo kabashada oo aad qor dhammaan aaladaha:

RESTORE DATABASE [bt]
FROM 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;

Soo Celinta DATABASE waxay si guul leh uga baaraandegtay 1070074 bog 38.027 ilbiriqsi (219.842 MB/sec).

In yar oo degdeg ah, laakiin meel u dhow, muhiim maaha. Guud ahaan, kaydinta si dhakhso ah ayaa loo saaraa oo loo soo celiyaa si la mid ah - guul? Aniga ahaan, waa guul aad u weyn. Tani waa muhiim, markaa waxaan ku celinayaa - haddii aad Haddii aad lumiso ugu yaraan mid ka mid ah faylashaas, waxaad luminaysaa kaydka oo dhan.

Haddii aad eegto diiwaanka xogta kaydka ah ee lagu soo bandhigay iyadoo la isticmaalayo Trace Flags 3213 iyo 3605, waxaad ogaan doontaa marka aad kaydinayso dhawr qalab, ugu yaraan tirada BUFFERCOUNT ayaa kordhaysa. Malaha, waxaad isku dayi kartaa inaad doorato cabbirro aad u wanaagsan oo loogu talagalay BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, laakiin kuma guuleysan isla markiiba, oo waxaan ahaa caajis inaan mar kale sameeyo tijaabadan oo kale, laakiin tiro ka duwan oo faylasha ah. Oo waa ceeb ku saabsan giraangiraha. Haddii aad rabto in aad guriga ku abaabusho imtixaanada noocaas ah, ma adka inaad dib u samayso qoraalka.

Ugu dambeyntii, aan ka hadalno qiimaha. Haddii kaydinta laga saaro si la mid ah shaqada isticmaalayaasha, waxaad u baahan tahay inaad qaadato hab aad mas'uul u ah tijaabinta, sababtoo ah haddii kaydinta si dhakhso ah loo saaro, saxanadaha ayaa aad u dhib badan, culeyska processor-ka ayaa kordha (wali waa inaad cadaadisaa waxay ku socotaa duullimaad), iyo sidaas awgeed, jawaabta guud ee nidaamka ayaa hoos u dhacaya.

Kaliya kaftan, laakiin waxaan si fiican u fahmay inaanan wax muujin ah samayn. Waxa kor ku qoran waa si fudud muujinta sida aad u dooran karto cabbirrada ugu habboon ee qaadashada kaydinta.

Xusuusnow in wax kasta oo aad samayso lagu sameeyay khatar iyo khatartaada. Hubi kaydkaaga oo ha iloobin DBCC CHECKDB.

Source: www.habr.com

MS SQL Server: BACKUP on steroids

Add a comment cancel reply