🥇MS SQL Server: BACKUP ho li-steroids

Ema! Ema! Ke 'nete, sena ha se sengoloa se seng mabapi le mefuta ea li-backups tsa SQL Server. Ha ke na ho bua ka phapang pakeng tsa mehlala ea ho hlaphoheloa le mokhoa oa ho sebetsana le log e ntseng e hōla.

Mohlomong (mohlomong), ka mor'a ho bala poso ena, u tla khona ho etsa bonnete ba hore bekapo e tlositsoeng ho uena ka mekhoa e tloaelehileng e tla tlosoa hosane bosiu, hantle, makhetlo a 1.5 ka potlako. 'Me feela ka lebaka la hore u sebelisa li-parameter tse ling tsa BACKUP DATABASE.

Haeba litaba tsa poso li ne li hlakile ho uena, ke masoabi. Ke balile ntho e 'ngoe le e' ngoe eo Google e e fumaneng bakeng sa poleloana "habr sql server backup", 'me ha ho sengoloa se le seng moo ke fumaneng ho buuoa ka taba ea hore nako ea ho boloka nako e ka susumetsoa ka tsela e itseng ho sebelisa li-parameter.

Hang-hang ke tla lebisa tlhokomelo ea hau ho maikutlo a Alexander Gladchenko (@mssqlhelp):

Le ka mohla u se ke ua fetola liparamente tsa BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE tlhahiso. Li etselitsoe feela ho ngola lingoliloeng tse joalo. Ka ts'ebetso, u tla tlosa mathata a ho hopola kapele.

Ehlile, ho ka ba monate ho ba litaba tse bohlale ka ho fetesisa le tse ikhethileng, empa, ka bomalimabe, ha ho joalo. Ho na le lingoliloeng / lingoliloeng tsa puo ea Senyesemane le Serussia (ke lula ke ferekanngoa hore na ke li bitsa eng ka nepo) tse fanoeng sehloohong sena. Tse ling tsa tseo ke ileng ka kopana le tsona ke tsena: linako, два, tse tharo (ho sql.ru).

Kahoo, ho qala, ke tla hokela syntax ea BACKUP e theoletsoeng hanyane ho tloha ho MSDN (ka tsela, ke ngotse ka holimo ka BACKUP DATABASE, empa sena sohle se sebetsa ho li-backup tsa transaction log le backup ea phapang, empa mohlomong ka phello e sa bonahaleng):

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

<…> - ho bolela hore ho ne ho e-na le ho hong moo, empa ke e tlositse hobane joale ha e amane le sehlooho.

Hangata u nka bekapo? Ba "ruta" joang mokhoa oa ho nka li-backups ho libilione tsa lingoliloeng? Ka kakaretso, haeba ke hloka ho etsa bekapo ea nako e le 'ngoe ea database e seng kholo haholo, ke tla ngola ntho e kang ena ka bo eona:

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

'Me, ka kakaretso, mohlomong 75-90% ea li-parameter tsohle tse atisang ho boleloa lihloohong tse mabapi le li-backups li thathamisitsoe mona. Ho joalo, ho boetse ho na le INIT, SKIP. A na u kile oa etela MSDN? Na u bone hore ho na le likhetho bakeng sa skrine e le 'ngoe le halofo? Ke boetse ke bone ...

Mohlomong u se u hlokometse hore ho ea pele re tla bua ka li-parameter tse tharo tse ileng tsa sala sebakeng sa pele sa khoutu - BLOCKSIZE, BUFFERCOUNT le MAXTRANSFERSIZE. Mona ke litlhaloso tsa bona ho tsoa ho MSDN:

THIBELLA MOKHOA = { blocksize | @ blocksize_e feto-fetohang } - e bonts'a boholo ba boloko ba 'mele ka li-byte. Boholo bo tšehetsoeng ke 512, 1024, 2048, 4096, 8192, 16, 384, le 32 byte (768 KB). Boleng ba kamehla ke 65 bakeng sa lisebelisoa tsa theipi le 536 bakeng sa lisebelisoa tse ling. Ka tloaelo paramethara ena ha e hlokehe hobane polelo ea BACKUP e ikhethela ka bo eona boholo bo loketseng ba block bakeng sa sesebelisoa. Ho seta saese ea boloko ka ho hlaka ho tlola khetho ea boholo ba block ka mokhoa o iketsang.

BUFFERCOUNT = { buffercount | @ buffercount_variable } - E hlalosa palo eohle ea li-buffer tsa I/O tse tla sebelisoa bakeng sa ts'ebetso ea bekapo. O ka hlakisa boleng bofe kapa bofe bo nepahetseng ba palo e felletseng, empa palo e kholo ea li-buffers e ka baka phoso e sa lebaleheng ka lebaka la sebaka se sengata sa aterese ts'ebetsong ea Sqlservr.exe.

Kakaretso ea sebaka se sebelisoang ke li-buffers e khethoa ka mokhoa o latelang: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } e totobatsa boholo bo boholo ba pakete ea data, ka li-byte, e ka fapanyetsanoang lipakeng tsa SQL Server le media media. Li-byte tse ngata tsa 65 (536 KB) ho fihla ho 64 byte (4 MB) lia tšehetsoa.

Kea hlapanya - ke balile sena pele, empa ha ho mohla ho kileng ha etsahala ho 'na hore na ba ka ba le tšusumetso e kae tlhahisong. Ho feta moo, kamoo ho bonahalang kateng, ke hloka ho etsa mofuta oa "ho tsoa" le ho lumela hore le hona joale ha ke utloisise ka botlalo hore na hantle-ntle ba etsa eng. Mohlomong ke hloka ho bala ho eketsehileng ka I/O ea buffered le ho sebetsa ka hard drive. Ka letsatsi le leng ke tla etsa sena, empa hajoale nka ngola sengoloa se tla hlahloba hore na litekanyetso tsena li ama lebelo leo bekapo e nkuoang ka lona.

Ke entse database e nyane, e ka bang 10 GB ka boholo, ka e beha ho SSD, mme ka beha bukana ea li-backups ho HDD.

Ke theha tafole ea nakoana ho boloka liphetho (ha ke na nako ea nakoana, kahoo nka cheka liphetho ka botlalo, empa u iketsetsa qeto):

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

Molao-motheo oa mongolo o bonolo - li-loops tse entsoeng ka sehlaha, e 'ngoe le e' ngoe e fetola boleng ba parameter e le 'ngoe, kenya litekanyo tsena ka taelo ea BACKUP, boloka tlaleho ea ho qetela le histori ho tloha msdb.dbo.backupset, hlakola faele ea ho boloka le ho pheta-pheta ho latelang. . Kaha data ea ts'ebetso ea "backup" e nkiloe ho "backupset", ho nepahala ho batla ho lahlehile (ha ho na likaroloana tsa metsotsoana), empa re tla pholoha sena.

Pele o hloka ho nolofalletsa xp_cmdshell ho hlakola li-backups (ebe u se ke oa lebala ho e thibela haeba u sa e hloke):

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

Ha e le hantle:

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

Haeba ka tšohanyetso u hloka ho hlakisoa ka se etsahalang mona, ngola litlhaloso kapa PM. Hajoale, ke tla u joetsa feela ka liparamente tseo ke li kentseng ho BACKUP DATABASE.

Bakeng sa BLOCKSIZE re na le lethathamo le "koetsoeng" la boleng, 'me ha kea etsa bekapo ka BLOCKSIZE <4KB. MAXTRANSFERSIZE nomoro efe kapa efe e mengata ea 64KB - ho tloha 64KB ho isa 4MB. Ntho e sa fetoheng ho sistimi ea ka ke 1024KB, ke nkile 512 - 1024 - 2048 - 4096.

Ho ne ho le thata le ho feta ka BUFFERCOUNT - e ka ba nomoro efe kapa efe e nepahetseng, empa sehokelo se re e baloa joang ho BACKUP DATABASE mme ke hobaneng ha litekanyetso tse kholo li le kotsi?. E boetse e re mokhoa oa ho fumana tlhahisoleseding mabapi le hore na BUFFERCOUNT bekapo e hlile e entsoe ka eona - bakeng sa ka ke 7. Ho ne ho se na thuso ea ho e fokotsa, 'me moeli o ka holimo o ile oa fumanoa ka liteko - ka BUFFERCOUNT = 896 le MAXTRANSFERSIZE = 4194304 bekapo e ile ea oela le phoso (e ngotsoeng mabapi le sehokelo se kaholimo):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE e kgaotsa ka tsela e sa tlwaelehang.

Msg 701, Level 17, State 123, Line 7 Ho na le memori e sa lekaneng ea sistimi 'default' ea ho tsamaisa potso ena.

Ha ke bapisa, ke tla qala ka ho bonts'a liphetho tsa ho boloka bekapo ntle le ho hlakisa maemo ho hang:

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

Hantle, bekapo le bekapo:

E sebetsitsoe maqephe a 1070072 bakeng sa database 'bt', faele 'bt' faeleng ea 1.

Maqephe a 2 a sebetsitsoeng bakeng sa database 'bt', file 'bt_log' faeleng ea 1.

BAKA DATABASE e sebelitse ka katleho maqephe a 1070074 ka metsotsoana e 53.171 (157.227 MB/sec).

Script ka boeona, e lekang liparamente, e sebelitse ka lihora tse 'maloa, litekanyo tsohle li ne li le teng google spreadsheet. Mona ke khetho ea liphetho tse nang le linako tse tharo tse ntle ka ho fetisisa tsa ho bolaoa (ke lekile ho etsa kerafo e ntle, empa posong ke tla tlameha ho etsa le tafole, le litlhalosong. @motsoako kenyellelitsoe Grafiken e ntle haholo).

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;

Tlhokomeliso, ntlha ea bohlokoa haholo e tsoang ho @motsoako ho tswa tlhaloso:

Re ka bua ka kholiseho hore kamano pakeng tsa liparamente le lebelo la "backup" ka har'a mekhahlelo ena ea boleng ke ntho e sa reroang, ha ho na mohlala. Empa ho tloha ho li-parameter tse hahiloeng ho hlakile hore ho bile le phello e ntle sephethong

Tseo. Ke feela ka ho laola maemo a tloaelehileng a BACKUP e bile phaello ea makhetlo a 2 nakong ea ho tlosa bekapo: metsotsoana e 26, khahlano le 53 qalong. Seo ha se mpe, na? Empa re hloka ho bona se etsahalang ka tsosoloso. Ho thoe'ng haeba hona joale ho nka nako e telele ea 4 ho hlaphoheloa?

Taba ea pele, a re lekanye hore na ho nka nako e kae ho khutlisa backup ka li-setting tsa kamehla:

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

Che, uena ka bouena ua tseba hore, litsela li teng, ho khutlisa ha ho nkeloe sebaka, ho hlaphoheloa ha se ho hlaphoheloa. 'Me ke e etsa ka tsela ena:

E sebetsitsoe maqephe a 1070072 bakeng sa database 'bt', faele 'bt' faeleng ea 1.

Maqephe a 2 a sebetsitsoeng bakeng sa database 'bt', file 'bt_log' faeleng ea 1.

RESTORE DATABASE e sebelitse ka katleho maqephe a 1070074 ka metsotsoana e 40.752 (205.141 MB/sec).

Joale ke tla leka ho khutlisetsa li-backups tse nkiloeng ka BLOCKSIZE e fetotsoeng, BUFFERCOUNT le MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE e sebelitse ka katleho maqephe a 1070074 ka metsotsoana e 32.283 (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE e sebelitse ka katleho maqephe a 1070074 ka metsotsoana e 32.682 (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE e sebelitse ka katleho maqephe a 1070074 ka metsotsoana e 32.091 (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE e sebelitse ka katleho maqephe a 1070074 ka metsotsoana e 32.401 (258.015 MB/sec).

Polelo ea RESTORE DATABASE ha e fetohe nakong ea ho hlaphoheloa; mekhahlelo ena ha e hlalosoe ho eona; SQL Server ka boeona e li khetha ho tsoa ho bekapo. 'Me ho hlakile hore esita le ka ho hlaphoheloa ho ka ba le phaello - hoo e ka bang 20% ka potlako (Ho bua 'nete, ha kea ka ka qeta nako e ngata ke hlaphoheloa, ke ile ka feta har'a li-backups tse "potlako" mme ka etsa bonnete ba hore ha ho na ho senyeha.).

Ha ho ka etsahala, e re ke hlakise hore tsena ha se li-parameter tse loketseng motho e mong le e mong. U ka iphumanela li-parameter tse u loketseng ka ho etsa liteko. Ke fumane liphetho tsena, u tla fumana tse fapaneng. Empa oa bona hore o ka "tune" li-backups tsa hau mme li ka theha le ho tsamaisa kapele.

Ke boetse ke khothaletsa ka matla hore u bale litokomane ka botlalo, hobane ho ka 'na ha e-ba le li-nuances tse tobileng tsamaisong ea hau.

Ho tloha ha ke qala ho ngola ka li-backups, ke batla ho ngola hang-hang ka "optimization" e le 'ngoe, e tloaelehileng ho feta "tuning" parameters (ho ea kamoo ke utloisisang kateng, e sebelisoa bonyane lisebelisoa tse ling tsa "backup", mohlomong hammoho le li-parameter. e hlalositsoe pejana), empa ha e e-so hlalosoe ho Habré le eona.

Haeba re sheba mola oa bobeli litokomaneng, hantle tlas'a BACKUP DATABASE, moo rea bona:

TO <backup_device> [ ,...n ]

U nahana hore ho tla etsahala'ng haeba u bolela lisebelisoa tse 'maloa tsa backup_devices? Syntax e e lumella. Mme ho tla etsahala ntho e khahlisang haholo - "backup" e tla "hasoa" lisebelisoa tse 'maloa. Tseo. "sesebelisoa" se seng le se seng ka bonngoe se tla hloka thuso, se lahleheloe, se lahleheloe ke bekapo eohle. Empa ho tlotsa ho joalo ho tla ama lebelo la ho boloka chelete joang?

Ha re leke ho etsa "backup" ho "lisebelisoa" tse peli tse fumanehang ka lehlakore foldareng e le 'ngoe:

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

Bo-ntate ba lefatše, ke hobane'ng ha see se etsoa?

E sebetsitsoe maqephe a 1070072 bakeng sa database 'bt', faele 'bt' faeleng ea 1.

Maqephe a 2 a hlophisitsoeng bakeng sa database 'bt', file 'bt'log' faeleng ea 1.

BAKA DATABASE e sebelitse ka katleho maqephe a 1070074 ka metsotsoana e 40.092 (208.519 MB/sec).

Na bekapo e bile 25% ka potlako ho feta tekano? Ho thoe'ng haeba re eketsa lisebelisoa tse ling tse 'maloa?

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

BAKA DATABASE e sebelitse ka katleho maqephe a 1070074 ka metsotsoana e 34.234 (244.200 MB/sec).

Ka kakaretso, phaello e ka bang 35% ea nako ea ho nka bekapo feela ka lebaka la hore bekapo e ngotsoe ho lifaele tse 4 ho disk e le 'ngoe hang-hang. Ke hlahlobile palo e kholoanyane - ha ho na phaello ho laptop ea ka, hantle - lisebelisoa tse 4. Bakeng sa hau - ha ke tsebe, u hloka ho hlahloba. Hantle, ka tsela, haeba u na le lisebelisoa tsena - tsena ke li-disk tse fapaneng, liteboho, phaello e lokela ho ba ea bohlokoa le ho feta.

Joale a re ke re bue ka mokhoa oa ho tsosolosa thabo ena. Ho etsa sena, o tla tlameha ho fetola taelo ea ho hlaphoheloa mme o thathamise lisebelisoa tsohle:

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 e sebelitse ka katleho maqephe a 1070074 ka metsotsoana e 38.027 (219.842 MB/sec).

Ka potlako e nyane, empa kae-kae e haufi, eseng ea bohlokoa. Ka kakaretso, bekapo e tlosoa ka potlako, empa e tsosolositsoe ka tsela e ts'oanang - katleho? Ha e le 'na, ke katleho e kholo. Sena ke ea bohlokoa, kahoo ke pheta - haeba u haeba u lahleheloa ke bonyane e 'ngoe ea lifaele tsena, u lahleheloa ke bekapo eohle.

Haeba u sheba ka har'a log ho tlhahisoleseling e bonts'itsoeng ho sebelisoa Trace Flags 3213 le 3605, u tla hlokomela hore ha u boloka lisebelisoa tse 'maloa, bonyane palo ea BUFFERCOUNT e ea eketseha. Mohlomong, o ka leka ho khetha li-parameter tse ling tse nepahetseng bakeng sa BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, empa ha kea ka ka atleha hang-hang, 'me ke ne ke le botsoa haholo ho etsa liteko tse joalo hape, empa bakeng sa palo e fapaneng ea lifaele. Mme ke dihlong ka mabili. Haeba u batla ho hlophisa tlhahlobo e joalo lapeng, ha ho thata ho etsa script hape.

Qetellong, a re bue ka theko. Haeba "backup" e tlosoa ka tsela e ts'oanang le mosebetsi oa basebelisi, o hloka ho nka mokhoa o ikarabellang oa tlhahlobo, hobane haeba backup e tlosoa ka potlako, li-disk li hatelloa le ho feta, mojaro ho processor oa eketseha (o ntse o tlameha ho hatella). e fofa), 'me ka hona, karabelo e akaretsang ea sistimi e ea fokotseha.

Kea soasoa, empa ke utloisisa hantle hore ha kea etsa tšenolo leha e le efe. Se ngotsoeng ka holimo ke pontšo feela ea hore na u ka khetha li-parameter tse nepahetseng joang bakeng sa ho nka li-backups.

Hopola hore ntho e 'ngoe le e 'ngoe eo u e etsang e etsoa ka kotsi ea hau le kotsi ea hau. Sheba li-backups tsa hau 'me u seke oa lebala ka DBCC CHECKDB.

Source: www.habr.com

MS SQL Server: BACKUP ka li-steroids

Eketsa ka tlhaloso hlakola karabo