Umncedisi we-MS SQL: UGCINE KWI-steroids

Yima! Yima! Enyanisweni, eli ayilolinye inqaku malunga neentlobo ze-SQL Server backups. Andiyi kuthetha malunga nokungafani phakathi kweemodeli zokubuyisela kunye nendlela yokujongana nelogi ekhulayo.

Mhlawumbi (mhlawumbi nje), emva kokufunda le post, uya kukwazi ukuqinisekisa ukuba i-backup ekhutshwe kuwe usebenzisa iindlela eziqhelekileyo ziya kususwa ngomso ebusuku, kakuhle, amaxesha e-1.5 ngokukhawuleza. Kwaye kuphela ngenxa yokuba usebenzisa i parameters EYOGQIBELA YEDATABASE.

Ukuba umxholo weposti wawucacile kuwe, ndiyaxolisa. Ndifunde yonke into uGoogle ayifumanayo kwibinzana elithi "ukugcinwa kweseva ye-habr sql", kwaye akukho nalinye inqaku endilifumeneyo elikhankanyiweyo malunga nokuba ixesha logcino lingaphenjelelwa ngandlela thile usebenzisa iiparamitha.

Ndiza kutsala ingqalelo yakho ngokukhawuleza kumazwana ka-Alexander Gladchenko (@mssqlhelp):

Ungaze utshintshe i-BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE iiparamitha kwimveliso. Zenzelwe kuphela ukubhala amanqaku anjalo. Ngokuziqhelanisa, uya kuphelisa iingxaki zememori ngokukhawuleza.

Kuya kuba, ngokuqinisekileyo, kuya kuba yinto epholileyo ukuba ngowona mxholo ulumkileyo kwaye uthumele kuphela, kodwa, ngelishwa, oku akunjalo. Kukho zombini ulwimi lwesiNgesi kunye nolwimi lwesiRashiya amanqaku / izithuba (ndihlala ndididekile malunga nokuba ndiwabiza ntoni ngokuchanekileyo) anikezelwe kwesi sihloko. Nazi ezinye zezo ndidibene nazo: maxesha, Π΄Π²Π°, ezintathu (kwi-sql.ru).

Ke, ukuqala, ndiza kuqhoboshela i-syntax ehlulwe kancinane BACKUP ukusuka MSDN (ngendlela, ndibhale ngasentla malunga ne-BACKUP DATABASE, kodwa konke oku kusebenza kuzo zombini i-log yentengiselwano kunye ne-backup eyahlukileyo, kodwa mhlawumbi ngesiphumo esicacileyo):

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

<…> - kuthetha ukuba kukho into apho, kodwa ndayisusa kuba ngoku ayihambelani nesihloko.

Udla ngokuthatha njani i-backup? "Bafundisa" njani indlela yokuthatha ii-backups kwiibhiliyoni zamanqaku? Ngokubanzi, ukuba ndifuna ukwenza ugcino lwexesha elinye lwedatha engekho nkulu kakhulu, ndiza kubhala ngokuzenzekelayo into enje:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--Π»Π°Π΄Π½ΠΎ, CHECKSUM я написал Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΠΊΠ°Π·Π°Ρ‚ΡŒΡΡ ΡƒΠΌΠ½Π΅Π΅

Kwaye, ngokubanzi, mhlawumbi i-75-90% yazo zonke iiparameters ezidla ngokukhankanywa kumanqaku malunga nee-backups zidweliswe apha. Ewe, kukwakho INIT, SKIP. Ngaba ukhe wayindwendwela i-MSDN? Ngaba ubonile ukuba kukho iinketho zesikrini esinye nesiqingatha? Ndaphinda ndabona...

Mhlawumbi sele ubonile ukuba ngokubhekele phaya siza kuthetha ngeeparamitha ezintathu ezisele kwibhloko yokuqala yekhowudi - BLOCKSIZE, BUFFERCOUNT kunye ne-MAXTRANSFERSIZE. Nazi iinkcazo zabo ezivela kwi-MSDN:

BIBIKI = { ibhlokhi | @ blocksize_variable } - ibonisa ubungakanani bebhloko ebonakalayo kwii-bytes. Ubungakanani obuxhasiweyo yi-512, 1024, 2048, 4096, 8192, 16, 384, kunye ne-32 bytes (768 KB). Ixabiso elingagqibekanga yi-65 yezixhobo zetape kunye ne-536 kwezinye izixhobo. Ngokuqhelekileyo le parameter ayiyomfuneko kuba ingxelo EPHAMBILI ikhetha ngokuzenzekelayo ubungakanani obufanelekileyo bebhloko yesixhobo. Ukuseta ubungakanani bebhloko ngokucace gca kukhetho lobungakanani bebhloko ezenzekelayo.

BUFFERCOUNT = { inani le-buffer | @ buffercount_variable } - Ichaza inani elipheleleyo le-I/O ye-buffers eza kusetyenziselwa umsebenzi wogcino. Ungakhankanya naliphi na ixabiso elipheleleyo elipheleleyo, kodwa inani elikhulu lee-buffers linokubangela impazamo engaphandle kwenkumbulo ngenxa yesithuba esigqithisileyo sedilesi yenyani kwinkqubo ye-Sqlservr.exe.

Isixa sisonke sesithuba esisetyenziswe zizithinteli simiselwa yile fomula ilandelayo: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } ixela oyena bungakanani bepakethe yedata enkulu, ngeebytes, ekutshintshisweni ngazo phakathi kweSeva yeSQL kunye neseti yogcino lwemidiya. Iibhayithi ezininzi ezingama-65 (536 KB) ukuya kutsho kwi-64 bytes (4 MB) ziyaxhaswa.

Ndiyafunga-ndiyifundile le nto ngaphambili, kodwa ayizange ithi qatha kum ukuba ingaba nefuthe elingakanani kwimveliso. Ngaphezu koko, ngokucacileyo, kufuneka ndenze uhlobo "lokuphuma" kwaye ndivume ukuba kwangoku andiyiqondi kakuhle into abayenzayo. Kuya kufuneka ndifunde ngakumbi malunga ne-I/O ye-buffered kunye nokusebenza nge-hard drive. Ngenye imini ndiza kuyenza le nto, kodwa okwangoku ndingabhala nje iskripthi esiza kukhangela ukuba la maxabiso achaphazela njani isantya esithathwa ngaso isipele.

Ndenze i-database encinci, malunga ne-10 GB ngobukhulu, ndiyibeke kwi-SSD, kwaye ndibeke i-directory ye-backups kwi-HDD.

Ndenza itafile yethutyana ukugcina iziphumo (andinayo okwethutyana, ngoko ke ndiyakwazi ukumba kwiziphumo ngokweenkcukacha ngakumbi, kodwa uzigqibela ngokwakho):

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

Umgaqo weskripthi ulula - i-loops ene-nested, nganye etshintsha ixabiso lepharamitha enye, faka ezi parameters kumyalelo we-BACKUP, gcina irekhodi lokugqibela kunye nembali evela msdb.dbo.backupset, cima ifayile yokugcina kunye nokuphindaphinda okulandelayo. . Ukusukela ukuba idatha yokwenza ugcino ithathwe kwi-backupset, ukuchaneka kulahlekile (akukho maqhezu emizuzwana), kodwa siya kusinda koku.

Okokuqala kufuneka uvule i-xp_cmdshell ukucima i-backups (ke ungalibali ukuyicima ukuba awuyifuni):

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

Ewe, ngokwenene:

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

Ukuba ngokukhawuleza ufuna ukucaciswa kwinto eyenzekayo apha, bhala kwiinkcazo okanye kwi-PM. Okwangoku, ndiza kukuxelela kuphela malunga neeparamitha endizibeke kwi-BACKUP DATABASE.

Kwi-BLOCKSIZE sinoluhlu "luvaliweyo" lwamaxabiso, kwaye andizange ndenze ugcino nge-BLOCKSIZE <4KB. MAXTRANSFERSIZE naliphi na inani eliphindaphindwayo le-64KB - ukusuka kwi-64KB ukuya kwi-4MB. Ukungagqibekanga kwindlela yam ngu 1024KB, ndithathe 512 - 1024 - 2048 - 4096.

Kwakunzima ngakumbi nge-BUFFERCOUNT-inokuba nayiphi na inombolo ephozithivu, kodwa ikhonkco lithi ibalwa njani kwi-BACKUP DATABASE kwaye kutheni amaxabiso amakhulu eyingozi?. Kwakhona ithi indlela yokufumana ulwazi malunga nokuba yeyiphi i-BUFFERCOUNT i-backup eneneni yenziwe ngayo - kum yi-7. Kwakungekho ngongoma ekunciphiseni, kwaye umda ophezulu wafunyanwa ngokuzama - nge BUFFERCOUNT = 896 kunye ne-MAXTRANSFERSIZE = 4194304 i-backup yawa nayo. imposiso (ebhaliweyo malunga nayo kwikhonkco elingentla):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE iphelisa ngokungaqhelekanga.

Msg 701, iNqanaba le-17, iLizwe 123, Umgca wesi-7 Akukho memori yenkqubo yaneleyo kwiqula lemithombo 'emiselweyo' ukuqhuba lo mbuzo.

Ukuthelekisa, ndiza kuqala ndibonise iziphumo zokuqhuba i-backup ngaphandle kokuchaza nayiphi na iparameters:

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

Ewe, i-backup kunye ne-backup:

Esetyenziweyo 1070072 amaphepha esiseko sedatha 'bt', ifayile 'bt' kwifayile 1.

Kuqwalaselwe amaphepha ama-2 esiseko sedatha 'bt', ifayile 'bt_log' kwifayile yoku-1.

I-DATABASE YOKUGQIBELA iqhutywe ngempumelelo amaphepha e-1070074 kwimizuzwana ye-53.171 (157.227 MB/sec).

Isikripthi ngokwaso, ukuvavanya iiparamitha, sisebenze kwiiyure ezimbalwa, yonke imilinganiselo yayingaphakathi google spreadsheet. Kwaye nalu ukhetho lweziphumo kunye nawona maxesha amathathu angcono okubulawa (ndizamile ukwenza igrafu entle, kodwa kwiposti kuya kufuneka ndiyenze ngetafile, nakwizimvo. @mixsture yongezwa imizobo epholileyo kakhulu).

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;

Umncedisi we-MS SQL: UGCINE KWI-steroids

Ingqalelo, inqaku elibaluleke kakhulu ukusuka @mixsture ΠΈΠ· ngcaciso:

Sinokuthi ngokuzithemba ukuba ubudlelwane phakathi kweeparamitha kunye nesantya sokugcina ngaphakathi kolu luhlu lwamaxabiso alukhethi, akukho pateni. Kodwa ukuhamba kude kwiiparamitha ezakhelwe ngaphakathi ngokucacileyo kube nefuthe elihle kwisiphumo

Ezo. Kuphela ngokulawula imilinganiselo ye-BACKUP iparameters yayiyinzuzo ephindwe kabini ngexesha lokususa i-backup: imizuzwana ye-2, ngokumalunga ne-26 ekuqaleni. Akunjalo, akunjalo? Kodwa kufuneka sibone ukuba kwenzeka ntoni ngokubuyiselwa. Kuthekani ukuba ngoku kuthatha amaxesha ama-53 ixesha elide ukuchacha?

Okokuqala, makhe silinganise ukuba kuthatha ixesha elingakanani ukubuyisela i-backup ngoseto olungagqibekanga:

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

Ewe, wena ngokwakho uyazi ukuba, iindlela zikhona, indawo ayithathi ndawo, ukuchacha akubuyi. Kwaye ndenza ngolu hlobo:

Esetyenziweyo 1070072 amaphepha esiseko sedatha 'bt', ifayile 'bt' kwifayile 1.

Kuqwalaselwe amaphepha ama-2 esiseko sedatha 'bt', ifayile 'bt_log' kwifayile yoku-1.

BUYISELA IDATABASE iqhutywe ngempumelelo amaphepha e-1070074 kwimizuzwana ye-40.752 (205.141 MB/sec).

Ngoku ndiza kuzama ukubuyisela ii-backups ezithathiweyo kunye ne-BLOCKSIZE etshintshileyo, i-BUFFERCOUNT kunye ne-MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

BUYISELA IDATABASE iqhutywe ngempumelelo amaphepha e-1070074 kwimizuzwana ye-32.283 (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

BUYISELA IDATABASE iqhutywe ngempumelelo amaphepha e-1070074 kwimizuzwana ye-32.682 (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

BUYISELA IDATABASE iqhutywe ngempumelelo amaphepha e-1070074 kwimizuzwana ye-32.091 (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

BUYISELA IDATABASE iqhutywe ngempumelelo amaphepha e-1070074 kwimizuzwana ye-32.401 (258.015 MB/sec).

Inkcazo ye-RESTORE DATABASE ayitshintshi ngexesha lokubuyisela; ezi parameters azichazwanga kuyo; Umncedisi we-SQL ngokwawo uyazimisela kwi-backup. Kwaye kucacile ukuba nangona ukubuyisela kunokubakho inzuzo - phantse i-20% ngokukhawuleza (Ukunyaniseka, andizange ndichithe ixesha elininzi ekuchacheni, ndiye ndabaleka kwii-backups ezininzi "ezikhawulezayo" kwaye ndaqinisekisa ukuba akukho konakala.).

Ukuba kunokwenzeka, mandicacise ukuba ezi ayizizo iiparameters ezilungele wonke umntu. Ungazifumana kuphela ezona parameters zifanelekileyo ngovavanyo. Ndifumene ezi ziphumo, uya kufumana ezahlukeneyo. Kodwa uyabona ukuba unakho "ukuthunga" ii-backups zakho kwaye zinokwenza kwaye zisebenzise ngokukhawuleza.

Ndikwacebisa ngamandla ukuba ufunde amaxwebhu ngokupheleleyo, kuba kusenokubakho ama-nuances athile kwinkqubo yakho.

Ukusukela ukuba ndiqale ukubhala malunga nee-backups, ndifuna ukubhala kwangoko malunga "nokuphucula" enye, exhaphake kakhulu kuneparamitha "yokulungisa" (ngoko ndiqondayo, isetyenziswa ubuncinci bezinto ezigcinayo, mhlawumbi kunye neeparamitha. echazwe ngaphambili), kodwa ayikachazwa kuHabrΓ© nayo.

Ukuba sijonga umgca wesibini kuxwebhu, kanye phantsi kwe-BACKUP DATABASE, apho sibona:

TO <backup_device> [ ,...n ]

Ucinga ukuba kuya kwenzeka ntoni ukuba ukhankanya ii-backups_devices ezininzi? Isivakalisi siyakuvumela oko. Kwaye into enomdla kakhulu iya kwenzeka - i-backup iya "kusasazwa" kuzo zonke izixhobo ezininzi. Ezo. "isixhobo" ngasinye ngokwaso siya kuba singasebenzi, silahlekile esinye, silahlekelwe sigcino siphela. Kodwa oko kuthambisa kuya kusichaphazela njani isantya sokugcina?

Makhe sizame ukwenza i-backup "kwizixhobo" ezimbini ezibekwe ecaleni kwicala elinye kwifolda enye:

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

Boyise behlabathi kutheni kusenziwa oku?

Esetyenziweyo 1070072 amaphepha esiseko sedatha 'bt', ifayile 'bt' kwifayile 1.

Kuqwalaselwe amaphepha ama-2 esiseko sedatha 'bt', ifayile 'bt'log' kwifayile 1.

I-DATABASE YOKUGQIBELA iqhutywe ngempumelelo amaphepha e-1070074 kwimizuzwana ye-40.092 (208.519 MB/sec).

Ngaba i-backup ibe ngama-25% ngokukhawuleza ngaphandle nje kweblue? Kuthekani ukuba songeza ezinye izixhobo ezimbalwa?

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

I-DATABASE YOKUGQIBELA iqhutywe ngempumelelo amaphepha e-1070074 kwimizuzwana ye-34.234 (244.200 MB/sec).

Iyonke, inzuzo malunga ne-35% yexesha lokuthatha i-backup kuphela ngenxa yokuba i-backup ibhaliwe kwiifayile ze-4 kwidiski enye kanye. Ndijonge inani elikhulu - akukho nzuzo kwilaptop yam, ngokugqibeleleyo - izixhobo ezi-4. Kuwe - andazi, kufuneka ujonge. Ewe, ngendlela, ukuba unazo ezi zixhobo - ezi ziidiski ezahlukeneyo ngokwenene, siyavuyisana, inzuzo kufuneka ibaluleke ngakumbi.

Ngoku makhe sithethe ngendlela yokubuyisela olu lonwabo. Ukwenza oku, kuya kufuneka utshintshe umyalelo wokubuyisela kwaye udwelise zonke izixhobo:

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

BUYISELA IDATABASE iqhutywe ngempumelelo amaphepha e-1070074 kwimizuzwana ye-38.027 (219.842 MB/sec).

Ukukhawuleza kancinci, kodwa kwenye indawo ekufutshane, ayibalulekanga. Ngokubanzi, i-backup isuswa ngokukhawuleza kwaye ibuyiselwe ngendlela efanayo - impumelelo? Mna ke, yimpumelelo enkulu. Oku kubalulekile, ngoko ndiyaphinda-ukuba wena ukuba ulahlekelwe noko enye yezi fayile, ulahlekelwa lugcino lulonke.

Ukuba ujonga kwilog kulwazi logcino oluboniswa kusetyenziswa iiFlegi zeTrace 3213 kunye ne-3605, uya kuqaphela ukuba xa uxhasa izixhobo ezininzi, ubuncinci inani le-BUFFERCOUNT landa. Mhlawumbi, ungazama ukukhetha iiparamitha ezizezona zilungileyo ze-BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, kodwa andizange ndiphumelele kwangoko, kwaye ndonqena ukwenza uvavanyo olunjalo kwakhona, kodwa inani elahlukileyo leefayile. Kwaye kulihlazo malunga namavili. Ukuba ufuna ukucwangcisa uvavanyo olunjalo ekhaya, akunzima ukwenza kwakhona iskripthi.

Ekugqibeleni, makhe sithethe ngexabiso. Ukuba i-backup isusiwe ngokuhambelana nomsebenzi wabasebenzisi, kufuneka uthathe indlela enoxanduva lokuvavanya, kuba ukuba i-backup isusiwe ngokukhawuleza, iidiski zixinzelelwe ngakumbi, umthwalo kwiprosesa uyanda (kusafuneka ucinezele). it on the fly), kwaye ngokufanelekileyo, ukuphendula ngokubanzi kwenkqubo kuyancipha.

Ndiyadlala, kodwa ndiyaqonda kakuhle ukuba andizange ndenze zityhilelo. Okubhaliweyo apha ngasentla kukubonisa nje ukuba ungakhetha njani ezona parameters zokuthatha ii-backups.

Khumbula ukuba yonke into oyenzayo uyenza ngengozi yakho kunye nomngcipheko. Jonga ii-backups zakho kwaye ungalibali nge-DBCC CHECKDB.

umthombo: www.habr.com

Yongeza izimvo