MS SQL Server: BWINO pa ma steroids

Dikirani! Dikirani! Zowona, iyi si nkhani ina yokhudza mitundu ya ma backups a SQL Server. Sindidzalankhulanso za kusiyana pakati pa zitsanzo zobwezeretsa komanso momwe mungachitire ndi chipika chokulirapo.

Mwina (mwinamwake), mutawerenga izi, mudzatha kuonetsetsa kuti zosunga zobwezeretsera zomwe zachotsedwa kwa inu pogwiritsa ntchito njira zokhazikika zidzachotsedwa mawa usiku, chabwino, nthawi 1.5 mwachangu. Ndipo kokha chifukwa chakuti mumagwiritsa ntchito magawo a BACKUP DATABASE.

Ngati zomwe zili patsambali zidawonekera kwa inu, pepani. Ndinawerenga zonse zomwe Google idapeza pamawu oti "habr sql seva zosunga zobwezeretsera", ndipo palibe m'nkhani imodzi yomwe ndidapezapo kutchulidwa kuti nthawi yosunga zobwezeretsera imatha kukhudzidwa mwanjira ina pogwiritsa ntchito magawo.

Nthawi yomweyo ndikukopa chidwi chanu ku ndemanga ya Alexander Gladchenko (@mssqlhelp):

Musasinthe magawo a BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE popanga. Amapangidwa kuti azingolemba nkhani zotere. Pochita, mudzachotsa mavuto a kukumbukira posachedwa.

Zingakhale, ndithudi, zabwino kukhala wanzeru kwambiri komanso kutumiza zinthu zokhazokha, koma, mwatsoka, izi siziri choncho. Pali zolemba/zolemba zachingerezi ndi chilankhulo cha Chirasha (nthawi zonse ndimasokonezeka kuti ndizitcha chiyani molondola) zoperekedwa pamutuwu. Nawa ena mwa omwe ndidakumana nawo: nthawi, два, atatu (pa sql.ru).

Chifukwa chake, poyambira, ndilumikiza kalembedwe ka BACKUP yochotsedwa pang'ono kuchokera MSDN (mwa njira, ndidalemba pamwambapa za BACKUP DATABASE, koma zonsezi zikugwira ntchito pazosunga zosunga zobwezeretsera komanso zosunga zosiyanitsa, koma mwina zowoneka bwino):

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

<…> - zikutanthauza kuti panali chinachake pamenepo, koma ndinachichotsa chifukwa tsopano sichikugwirizana ndi mutuwo.

Kodi nthawi zambiri mumasunga bwanji zosunga zobwezeretsera? Kodi "amaphunzitsa" bwanji momwe angatengere zosunga zobwezeretsera mu mabiliyoni a zolemba? Nthawi zambiri, ngati ndikufunika kusungitsa zosunga zobwezeretsera kamodzi kamphindi kakang'ono kwambiri, ndingolemba motere:

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

Ndipo, kawirikawiri, mwina 75-90% ya magawo onse omwe amatchulidwa m'nkhani zosunga zobwezeretsera alembedwa apa. Chabwino, palinso INIT, SKIP. Kodi mudayenderapo MSDN? Kodi mwawona kuti pali zosankha zowonetsera chimodzi ndi theka? Ndinaonanso...

Mwina mwazindikira kale kuti kupitilira apo tikambirana magawo atatu omwe adatsalira pamndandanda woyamba wa code - BLOCKSIZE, BUFFERCOUNT ndi MAXTRANSFERSIZE. Nawa mafotokozedwe awo kuchokera ku MSDN:

BLOCKSIZE = { blocksize | @ blocksize_variable } - ikuwonetsa kukula kwa block mu byte. Makulidwe othandizidwa ndi 512, 1024, 2048, 4096, 8192, 16, 384, ndi 32 bytes (768 KB). Mtengo wosasinthika ndi 65 pazida za tepi ndi 536 pazida zina. Nthawi zambiri chizindikirochi sichofunikira chifukwa mawu a BACKUP amasankha kukula koyenera kwa chipika cha chipangizocho. Kukhazikitsa kukula kwa block kumaposa kusankha kukula kwa block.

BUFFERCOUNT = { chiwerengero cha buffer | @ buffercount_variable } - Imatanthawuza kuchuluka kwa ma buffer a I/O omwe adzagwiritsidwe ntchito posunga zosunga zobwezeretsera. Mutha kufotokoza mtengo uliwonse wabwino, koma kuchuluka kwa ma buffers kungayambitse cholakwika chosakumbukika chifukwa cha kuchuluka kwa ma adilesi munjira ya Sqlservr.exe.

Kuchuluka kwa malo omwe amagwiritsidwa ntchito ndi ma buffers amatsimikiziridwa ndi njira iyi: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } imatchula kukula kwake kwakukulu kwa paketi ya data, mu ma byte, kusinthanitsa pakati pa SQL Server ndi zosunga zobwezeretsera. Ma byte angapo a 65 (536 KB) mpaka 64 byte (4 MB) amathandizidwa.

Ndikulumbirira - Ndinawerengapo kale, koma sizinachitike kwa ine kuti zingakhudze bwanji zokolola. Komanso, mwachiwonekere, ndikufunika kupanga mtundu wa "kutuluka" ndikuvomereza kuti ngakhale tsopano sindikumvetsa bwino zomwe akuchita. Mwina ndiyenera kuwerenga zambiri za I/O yolumikizidwa ndikugwira ntchito ndi hard drive. Tsiku lina ndidzachita izi, koma pakadali pano nditha kulemba script yomwe iwona momwe izi zimakhudzira liwiro lomwe zosunga zobwezeretsera zimatengedwa.

Ndinapanga database yaying'ono, pafupifupi 10 GB kukula kwake, ndikuyiyika pa SSD, ndikuyika bukhu la zosunga zobwezeretsera pa HDD.

Ndimapanga tebulo lakanthawi kuti ndisunge zotsatira (ndilibe nazo kwakanthawi, kotero nditha kukumba muzotsatira mwatsatanetsatane, koma mumasankha nokha):

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

Mfundo ya script ndi yosavuta - malupu okhala ndi zisa, chilichonse chomwe chimasintha mtengo wa parameter imodzi, ikani magawowa mu lamulo la BACKUP, sungani mbiri yomaliza ndi mbiri kuchokera msdb.dbo.backupset, chotsani fayilo yosunga zobwezeretsera ndi kubwereza kotsatira. . Popeza zosunga zobwezeretsera zimatengedwa kuchokera ku zosunga zobwezeretsera, kulondola kumatayika pang'ono (palibe tizigawo ta masekondi), koma tipulumuka izi.

Choyamba muyenera kuloleza xp_cmdshell kufufuta zosunga zobwezeretsera (ndiye musaiwale kuzimitsa ngati simukuzifuna):

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

Chabwino, kwenikweni:

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

Ngati mwadzidzidzi mukusowa kufotokozera pazomwe zikuchitika pano, lembani mu ndemanga kapena PM. Pakalipano, ndikuwuzani za magawo omwe ndayika mu BACKUP DATABASE.

Kwa BLOCKSIZE tili ndi mndandanda wazinthu "zotsekedwa", ndipo sindinasungire zosunga zobwezeretsera ndi BLOCKSIZE <4KB. MAXTRANSFERSIZE nambala iliyonse yomwe ili yochulukitsa 64KB - kuchokera ku 64KB kufika ku 4MB. Zosasintha pamakina anga ndi 1024KB, ndinatenga 512 - 1024 - 2048 - 4096.

Zinali zovuta kwambiri ndi BUFFERCOUNT - ikhoza kukhala nambala iliyonse yabwino, koma ulalo umati amawerengedwa bwanji mu BACKUP DATABASE ndipo chifukwa chiyani zikhulupiriro zili zowopsa?. Imanenanso momwe mungapezere zambiri za zomwe BUFFERCOUNT zosunga zobwezeretsera zimapangidwira - kwa ine ndi 7. Panalibe chifukwa chochepetsera, ndipo malire apamwamba adapezeka moyesera - ndi BUFFERCOUNT = 896 ndi MAXTRANSFERSIZE = 4194304 zosunga zobwezeretsera zidagwa ndi cholakwika (chomwe chalembedwa mu ulalo pamwambapa):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE ikutha mosadziwika bwino.

Msg.

Poyerekeza, ndiwonetsa kaye zotsatira zosunga zosunga zobwezeretsera popanda kutchula magawo aliwonse:

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

Chabwino, zosunga zobwezeretsera ndi zosunga zobwezeretsera:

Masamba okonzedwa a 1070072 a database 'bt', fayilo 'bt' pa fayilo 1.

Masamba 2 osinthidwa a database 'bt', fayilo 'bt_log' pafayilo 1.

BACKUP DATABASE idasinthidwa bwino masamba a 1070074 mumasekondi 53.171 (157.227 MB/sec).

Script yokha, kuyesa magawo, inagwira ntchito mu maola angapo, miyeso yonse inalimo google spreadsheet. Ndipo apa pali zosankha zomwe zili ndi nthawi zitatu zabwino kwambiri zophatikizira (ndinayesera kupanga graph yabwino, koma positi ndiyenera kuchita ndi tebulo, ndi ndemanga. @msanganizo anawonjezera zithunzi zabwino kwambiri).

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;

Chidziwitso, cholemba chofunikira kwambiri kuchokera @msanganizo kuchokera ndemanga:

Titha kunena molimba mtima kuti ubale pakati pa magawo ndi liwiro losunga zosunga zobwezeretsera mkati mwamitundu iyi ndi yachisawawa, palibe chitsanzo. Koma kuchoka pazigawo zomangidwa mwachiwonekere kunali ndi zotsatira zabwino pa zotsatira

Iwo. Pokhapokha poyang'anira magawo a BACKUP wamba kunali kupindula kwa 2 mu nthawi yochotsa zosunga zobwezeretsera: masekondi 26, motsutsana ndi 53 pachiyambi. Izo sizoyipa, chabwino? Koma tiyenera kuwona zomwe zimachitika ndi kubwezeretsa. Nanga bwanji ngati patenga nthawi 4 kuti achire?

Choyamba, tiyeni tiwone kuti zimatenga nthawi yayitali bwanji kubwezeretsa zosunga zobwezeretsera ndi makonda osasintha:

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

Chabwino, inu nokha mukudziwa kuti, njira zilipo, kusintha sikulowa m'malo, kuchira sikuchira. Ndipo ndimachita izi:

Masamba okonzedwa a 1070072 a database 'bt', fayilo 'bt' pa fayilo 1.

Masamba 2 osinthidwa a database 'bt', fayilo 'bt_log' pafayilo 1.

RESTORE DATABASE idasinthidwa bwino masamba a 1070074 mumasekondi 40.752 (205.141 MB/sec).

Tsopano ndiyesera kubwezeretsa zosunga zobwezeretsera zomwe zidasinthidwa ndi BLOCKSIZE, BUFFERCOUNT ndi MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE idasinthidwa bwino masamba a 1070074 mumasekondi 32.283 (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE idasinthidwa bwino masamba a 1070074 mumasekondi 32.682 (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE idasinthidwa bwino masamba a 1070074 mumasekondi 32.091 (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE idasinthidwa bwino masamba a 1070074 mumasekondi 32.401 (258.015 MB/sec).

Mawu a RESTORE DATABASE sasintha panthawi yochira; magawo awa sanatchulidwe mmenemo; SQL Server yokha imawasankha kuchokera ku zosunga zobwezeretsera. Ndipo zikuwonekeratu kuti ngakhale mutachira pangakhale phindu - pafupifupi 20% mofulumira (Kunena zowona, sindinakhale nthawi yayitali ndikuchira, ndidadutsa ma backups angapo "ofulumira" ndikuwonetsetsa kuti palibe kuwonongeka.).

Zikatero, ndiroleni ndifotokoze kuti izi sizinthu zina zomwe zili zoyenera kwa aliyense. Mutha kudzipezera nokha magawo oyenera poyesa. Ndapeza zotsatira izi, mupeza zosiyana. Koma mukuwona kuti mutha "kusintha" ma backups anu ndipo amatha kupanga ndikutumiza mwachangu.

Ndikupangiranso mwamphamvu kuti muwerenge zolemba zonse, chifukwa pakhoza kukhala ma nuances okhudzana ndi dongosolo lanu.

Popeza ndidayamba kulemba za zosunga zobwezeretsera, ndikufuna kulemba nthawi yomweyo za "kukhathamiritsa" kumodzi, komwe kumakhala kofala kwambiri kuposa magawo a "kukonza" (momwe ndikumvetsetsa, kumagwiritsidwa ntchito ndi zida zina zosunga zobwezeretsera, mwina pamodzi ndi magawo. zomwe zafotokozedwa kale), koma sizinafotokozedwenso za Habré.

Ngati tiyang'ana pamzere wachiwiri pazolembedwa, pansi pa BACKUP DATABASE, pamenepo tikuwona:

TO <backup_device> [ ,...n ]

Kodi mukuganiza kuti chingachitike ndi chiyani ngati mungatchule ma backup_devices angapo? Syntax imalola. Ndipo chinthu chochititsa chidwi kwambiri chidzachitika - zosunga zobwezeretsera "zidzafalikira" pazida zingapo. Iwo. "chipangizo" chilichonse payekhapayekha chidzakhala chopanda ntchito, chotayika, chotaya zosunga zobwezeretsera zonse. Koma kupaka koteroko kungakhudze bwanji liwiro losunga zobwezeretsera?

Tiyeni tiyese kupanga zosunga zobwezeretsera pa "zida" ziwiri zomwe zili mbali imodzi mufoda yomweyo:

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

Abambo adziko lapansi, chifukwa chiyani izi zikuchitika?

Masamba okonzedwa a 1070072 a database 'bt', fayilo 'bt' pa fayilo 1.

Masamba awiri osinthidwa a database 'bt', file 'bt'log' pa fayilo 1.

BACKUP DATABASE idasinthidwa bwino masamba a 1070074 mumasekondi 40.092 (208.519 MB/sec).

Kodi zosunga zobwezeretsera zidakhala 25% mwachangu kunja kwa buluu? Nanga bwanji ngati tiwonjezera zida zina zingapo?

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

BACKUP DATABASE idasinthidwa bwino masamba a 1070074 mumasekondi 34.234 (244.200 MB/sec).

Pazonse, phindu ndi pafupifupi 35% ya nthawi yosunga zosunga zobwezeretsera kokha chifukwa choti zosunga zobwezeretsera zimalembedwa ku mafayilo 4 pa disk imodzi nthawi imodzi. Ndinayang'ana nambala yokulirapo - palibe phindu pa laputopu yanga, moyenera - zida 4. Kwa inu - sindikudziwa, muyenera kuyang'ana. Chabwino, mwa njira, ngati muli ndi zipangizozi - awa ndi ma disks osiyana, zikomo, phindu liyenera kukhala lofunika kwambiri.

Tsopano tiyeni tikambirane mmene tingabwezeretse chimwemwe chimenechi. Kuti muchite izi, muyenera kusintha lamulo lobwezeretsa ndikulemba zida zonse:

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 idasinthidwa bwino masamba a 1070074 mumasekondi 38.027 (219.842 MB/sec).

Kuthamanga pang'ono, koma kwinakwake pafupi, osati kwakukulu. Nthawi zambiri, zosunga zobwezeretsera zimachotsedwa mwachangu ndikubwezeretsedwanso chimodzimodzi - kupambana? Kwa ine, ndizopambana. Izi ndizofunikira, kotero ndikubwereza - ngati inu ngati mutataya imodzi mwa mafayilowa, mumataya zosunga zobwezeretsera zonse.

Mukayang'ana mu chipika pazomwe zikuwonetsedwa pogwiritsa ntchito Trace Flags 3213 ndi 3605, mudzazindikira kuti mukasunga zida zingapo, osachepera kuchuluka kwa BUFFERCOUNT kumawonjezeka. Mwinamwake, mungayesere kusankha magawo abwino kwambiri a BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, koma sindinapambane nthawi yomweyo, ndipo ndinali waulesi kuti ndiyesenso kuyesa koteroko, koma kwa chiwerengero chosiyana cha mafayilo. Ndipo ndi zamanyazi za magudumu. Ngati mukufuna kukonza kuyesa koteroko kunyumba, sikovuta kukonzanso script.

Pomaliza, tiyeni tikambirane za mtengo. Ngati zosunga zobwezeretsera zimachotsedwa mogwirizana ndi ntchito ya ogwiritsa ntchito, muyenera kutenga njira yoyeserera kwambiri, chifukwa ngati zosunga zobwezeretsera zimachotsedwa mwachangu, ma disks amasokonekera kwambiri, kuchuluka kwa purosesa kumawonjezeka (muyenera kukanikiza. pa ntchentche), ndipo motero, kuyankha kwathunthu kwadongosolo kumachepa.

Ndikungoseka, koma ndikumvetsetsa bwino kuti sindinaulule chilichonse. Zomwe zalembedwa pamwambapa ndikungowonetsa momwe mungasankhire magawo abwino kwambiri otengera zosunga zobwezeretsera.

Kumbukirani kuti chilichonse chomwe mumachita chimachitika mwakufuna kwanu komanso mwangozi. Onani zosunga zobwezeretsera zanu ndipo musaiwale za DBCC CHECKDB.

Source: www.habr.com