I-ProHoster > Блог > Ukuphatha > Iseva ye-MS SQL: ISIPELE kuma-steroids

Iseva ye-MS SQL: ISIPELE kuma-steroids

Linda! Linda! Yiqiniso, lesi akusona esinye isihloko mayelana nezinhlobo zezipele ze-SQL Server. Ngeke ngize ngikhulume ngomehluko phakathi kwamamodeli okubuyisela kanye nendlela yokubhekana nelogi egcwele.

Mhlawumbe (mhlawumbe nje), ngemva kokufunda lokhu okuthunyelwe, uzokwazi ukwenza isiqiniseko sokuthi isipele esisusiwe kuwe usebenzisa izindlela ezijwayelekile sizosuswa kusasa ebusuku, kahle, izikhathi ezingu-1.5 ngokushesha. Futhi kuphela ngenxa yokuthi usebenzisa amapharamitha we-BACKUP DATABASE ngaphezulu.

Uma okuqukethwe kokuthunyelwe bekusobala kuwe, ngiyaxolisa. Ngifunde yonke into i-Google eyafika kukho mayelana nenkulumo ethi "isipele seseva ye-habr sql", futhi akukho esihlokweni esisodwa lapho ngithola noma yikuphi okushiwo iqiniso lokuthi isikhathi sokulondoloza singathonywa ngandlela thile kusetshenziswa imingcele.

Ngizodonsela ukunaka kwakho ngokushesha kumazwana ka-Alexander Gladchenko (@mssqlhelp):

Ungalokothi uguqule amapharamitha angu-BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE ekukhiqizeni. Zenzelwe kuphela ukubhala izihloko ezinjalo. Ngokwenza, uzosusa izinkinga zenkumbulo ngokushesha.

Yiqiniso, kungaba kuhle ukuba okuqukethwe okuhlakaniphe kakhulu nokuthumela okukhethekile, kodwa, ngeshwa, akunjalo. Kukhona kokubili izindatshana/okuthunyelwe kolimi lwesiNgisi nolimi lwesiRashiya (Ngihlala ngididekile ukuthi ngingakubiza ngani ngendlela efanele) okunikezwe kulesi sihloko. Nanka amanye engihlangane nawo: izikhathi, два, ezintathu (ku-sql.ru).

Ngakho-ke, okokuqala, ngizonamathisela i-syntax Eyisipele esuswe kancane ukusuka kuyo MSDN (ngendlela, ngibhale ngenhla mayelana neDATABASE Eyisipele, kodwa konke lokhu kusebenza kukho kokubili isipele selogi yokwenziwe kanye nesipele esihlukile, kodwa mhlawumbe ngomphumela osobala kancane):

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

<…> - kusho ukuthi bekunokuthile lapho, kodwa ngikususile ngoba manje akuhambisani nesihloko.

Uvamise ukuthatha kanjani ikhophi yasenqolobaneni? "Bafundisa" kanjani ukuthi bangathatha kanjani ama-backups ezigidini zendatshana? Ngokuvamile, uma ngidinga ukwenza ikhophi yasenqolobaneni yesikhathi esisodwa sesizindalwazi esingesikhulu kakhulu, ngizobhala ngokuzenzakalelayo into efana nale:

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

Futhi, ngokuvamile, cishe ama-75-90% awo wonke amapharamitha avame ukushiwo ezihlokweni ezimayelana nezipele abhalwe lapha. Nokho, kukhona ne-INIT, YEQA. Ingabe uvakashele i-MSDN? Usubonile ukuthi kunezinketho zesikrini esisodwa nesigamu? Ngabona futhi...

Cishe usubonile ukuthi ngokuqhubekayo sizokhuluma ngamapharamitha amathathu asele kubhulokhi yokuqala yekhodi - BLOCKSIZE, BUFFERCOUNT kanye ne-MAXTRANSFERSIZE. Nazi izincazelo zabo ezivela ku-MSDN:

BLOCKSIZE = { blocksize | @ blocksize_variable } - ikhombisa usayizi webhulokhi ebonakalayo ngamabhayithi. Osayizi abasekelwayo bangu-512, 1024, 2048, 4096, 8192, 16, 384, kanye namabhayithi angu-32 (768 KB). Inani elizenzakalelayo ngu-65 lamadivayisi we-tape kanye no-536 kwamanye amadivaysi. Ngokuvamile le pharamitha ayidingeki ngenxa yokuthi isitatimende EYISIPELE sikhetha ngokuzenzakalelayo usayizi webhulokhi ofanele wedivayisi. Ukusetha usayizi webhulokhi kudlula ngokusobala ukukhethwa kosayizi webhulokhi okuzenzakalelayo.

BUFFERCOUNT = { i-buffercount | @ i-buffercount_variable } - Ichaza inani eliphelele lamabhafa e-I/O azosetshenziselwa ukwenza isipele. Ungacacisa noma yiliphi inani eliyinombolo ephozithivu, kodwa inani elikhulu lamabhafa lingabangela iphutha elingaphandle kwenkumbulo ngenxa yesikhala sekheli esibonakalayo eseqile kunqubo ye-Sqlservr.exe.

Inani eliphelele lesikhala esisetshenziswa amabhafa linqunywa ifomula elandelayo: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } icacisa usayizi wephakethe ledatha omkhulu kunawo wonke, ngamabhayithi, ozoshintshwa phakathi kwe-SQL Server nesethi yemidiya eyisipele. Amabhayithi amaningi angu-65 (536 KB) kufika ku-64 bytes (4 MB) asekelwe.

Ngiyafunga - ngike ngakufunda lokhu ngaphambilini, kodwa akuzange kungifikele ukuthi ungakanani umthelela abangaba nawo ekukhiqizeni. Ngaphezu kwalokho, ngokusobala, ngidinga ukwenza uhlobo "lokuphuma" futhi ngivume ukuthi ngisho namanje angiqondi ngokugcwele ukuthi benzani ngempela. Mhlawumbe ngidinga ukufunda okwengeziwe nge-I/O egcinwe ku-buffer kanye nokusebenza nge-hard drive. Ngelinye ilanga ngizokwenza lokhu, kodwa okwamanje ngingavele ngibhale iskripthi esizobheka ukuthi la manani athinta kanjani isivinini okuthathwa ngaso isipele.

Ngenza i-database encane, engaba ngu-10 GB ngosayizi, ngayibeka ku-SSD, futhi ngafaka uhla lwemibhalo lwezipele ku-HDD.

Ngakha ithebula lesikhashana ukuze ngigcine imiphumela (anginayo eyesikhashana, ngakho-ke ngingakwazi ukumba emiphumeleni ngokuningiliziwe, kodwa uzinqumela wena):

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

Umgomo weskripthi ulula - ama-loops afakwe esidlekeni, ngalinye elishintsha inani lepharamitha eyodwa, faka le mingcele kumyalo WE-BACKUP, gcina irekhodi lokugcina elinomlando kusuka ku-msdb.dbo.backupset, susa ifayela eliyisipele kanye nokuphindaphinda okulandelayo. . Njengoba idatha yokwenza ikhophi yasenqolobaneni ithathwe ku-backupset, ukunemba kulahleka (azikho izingxenyana zemizuzwana), kodwa sizosinda kulokhu.

Okokuqala udinga ukunika amandla i-xp_cmdshell ukususa izipele (bese ungakhohlwa ukuyikhubaza uma ungayidingi): 

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

Yebo, empeleni:

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

Uma ngokuzumayo udinga ukucaciselwa ngokwenzekayo lapha, bhala kumazwana noma ku-PM. Okwamanje, ngizokutshela kuphela ngamapharamitha engiwafake KUSIKELELE SEDATABASE.

Ku-BLOCKSIZE sinohlu "oluvaliwe" lwamanani, futhi angizange ngenze ikhophi yasenqolobaneni nge-BLOCKSIZE < 4KB. MAXTRANSFERSIZE noma iyiphi inombolo ephindaphindwe ka-64KB - isuka ku-64KB iye ku-4MB. Okuzenzakalelayo kusistimu yami ngu-1024KB, ngithathe 512 - 1024 - 2048 - 4096.

Bekunzima kakhulu nge-BUFFERCOUNT - kungaba noma iyiphi inombolo ephozithivu, kodwa isixhumanisi sithi ibalwa kanjani kokuthi BACKUP DATABASE futhi kungani amanani amakhulu eyingozi?. Iphinde ithi indlela yokuthola ulwazi mayelana nokuthi iyiphi i-BUFFERCOUNT isipele esenziwe ngayo ngempela - kimina ingu-7. Kwakungekho phuzu ekunciphiseni, futhi umkhawulo ophezulu watholakala ngokuhlola - nge-BUFFERCOUNT = 896 kanye ne-MAXTRANSFERSIZE = 4194304 isipele sawela iphutha (elibhalwe mayelana nalo kusixhumanisi esingenhla):

I-Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE inqanyulwa ngendlela engavamile.

Msg 701, Level 17, State 123, Line 7 Ayikho inkumbulo yesistimu eyanele 'okuzenzakalelayo' ukuze uqalise lo mbuzo.

Ukuze uqhathanise, ngizoqala ngibonise imiphumela yokusebenzisa ikhophi yasenqolobaneni ngaphandle kokucacisa noma yimiphi imingcele nhlobo:

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

Hhayi-ke, ikhophi yasenqolobaneni nekhophi yasenqolobaneni:

Kucutshungulwe amakhasi angu-1070072 esizindalwazi esithi 'bt', ifayela elithi 'bt' kufayela elingu-1.

Kucutshungulwe amakhasi angu-2 esizindalwazi esithi 'bt', ifayela elithi 'bt_log' efayelini 1.

IDATHABASE Eyisipele icutshungulwe ngempumelelo amakhasi angu-1070074 kumasekhondi angu-53.171 (157.227 MB/sec).

Iskripthi ngokwaso, sihlola amapharamitha, sisebenze emahoreni ambalwa, zonke izilinganiso zazingaphakathi google spreadsheet. Futhi nansi ukukhethwa kwemiphumela enezikhathi ezintathu ezinhle kakhulu zokubulawa (ngizamile ukwenza igrafu enhle, kodwa kokuthunyelwe kuzodingeka ngenze ngetafula, nakumazwana @mixsture kungeziwe ihluzo cool 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;

Iseva ye-MS SQL: ISIPELE kuma-steroids

Ukunakwa, inothi elibaluleke kakhulu elivela @mixsture kusuka ku ukuhlaziya:

Singasho ngokuqiniseka ukuthi ubudlelwano phakathi kwamapharamitha nesivinini sokusekelayo ngaphakathi kwalawa mabanga amanani akwenzeki, alikho iphethini. Kodwa ukuhamba kude nemingcele eyakhelwe ngaphakathi ngokusobala kube nomphumela omuhle kumphumela

Labo. Kuphela ngokuphatha amapharamitha e-ISIPELEKO ajwayelekile kube yinzuzo ephindwe ka-2 ngesikhathi sokususwa kwekhophi yasenqolobaneni: amasekhondi angu-26, uma kuqhathaniswa nama-53 ekuqaleni. Lokho akukubi, akunjalo? Kodwa sidinga ukubona ukuthi kwenzekani ngokubuyiselwa. Kuthiwani uma manje kuthatha isikhathi esiphindwe ka-4 ukululama?

Okokuqala, ake silinganise ukuthi kuthatha isikhathi esingakanani ukubuyisela isipele ngezilungiselelo ezizenzakalelayo:

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

Hhayi-ke, wena ngokwakho uyazi ukuthi, izindlela zikhona, ukushintshwa akuphindi, ukululama akukona ukululama. Futhi ngikwenza kanje:

Kucutshungulwe amakhasi angu-1070072 esizindalwazi esithi 'bt', ifayela elithi 'bt' kufayela elingu-1.

Kucutshungulwe amakhasi angu-2 esizindalwazi esithi 'bt', ifayela elithi 'bt_log' efayelini 1.

BUYISELA IDATABASE icutshungulwe ngempumelelo amakhasi angu-1070074 kumasekhondi angu-40.752 (205.141 MB/isekhondi).

Manje ngizozama ukubuyisela izipele ezithathwe nge-BLOCKSIZE eshintshiwe, BUFFERCOUNT kanye ne-MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

BUYISELA IDATABASE icutshungulwe ngempumelelo amakhasi angu-1070074 kumasekhondi angu-32.283 (258.958 MB/isekhondi).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

BUYISELA IDATABASE icutshungulwe ngempumelelo amakhasi angu-1070074 kumasekhondi angu-32.682 (255.796 MB/isekhondi).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

BUYISELA IDATABASE icutshungulwe ngempumelelo amakhasi angu-1070074 kumasekhondi angu-32.091 (260.507 MB/isekhondi).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

BUYISELA IDATABASE icutshungulwe ngempumelelo amakhasi angu-1070074 kumasekhondi angu-32.401 (258.015 MB/isekhondi).

Isitatimende se-RESTORE DATABASE asishintshi ngesikhathi sokululama; le mingcele ayicaciswanga kuyo; Iseva ye-SQL ngokwayo inquma ukuthi ingakanani kusukela kusipele. Futhi kuyacaca ukuthi ngisho nokululama kungaba nenzuzo - cishe ngama-20% ngokushesha (Uma ngikhuluma iqiniso, angizange ngichithe isikhathi esiningi ngilulama, ngidlule kumakhophi ambalwa “ashesha kakhulu” futhi ngenza isiqiniseko sokuthi akukho ukuwohloka.).

Uma kwenzeka, ake ngicacise ukuthi lawa akuwona amapharamitha alungele wonke umuntu. Ungazitholela amapharamitha alungile ngokuhlola. Ngithole le miphumela, uzothola ehlukene. Kodwa uyabona ukuthi ungakwazi "ukushuna" izipele zakho futhi empeleni zingakha futhi zisebenzise ngokushesha.

Futhi ngincoma ngokuqinile ukuthi ufunde amadokhumenti wonke, ngoba kungase kube nama-nuances aqondile ohlelweni lwakho.

Selokhu ngaqala ukubhala ngama-backups, ngifuna ukubhala ngokushesha mayelana “nokunethezeka” okukodwa, okuvame kakhulu kunemingcele “yokulungisa” (ngokuqonda kwami, isetshenziswa okungenani izinsiza ezithile eziyisipele, mhlawumbe kanye nemingcele. echazwe ngaphambili), kodwa ayikachazwa ku-Habré futhi.

Uma sibheka umugqa wesibili emibhalweni, khona kanye ngaphansi kweDATABASE YESIPELE, lapho sibona:

TO <backup_device> [ ,...n ]

Ucabanga ukuthi kuzokwenzekani uma ucacisa ama-backup_devices ambalwa? I-syntax iyakuvumela. Futhi into ethakazelisa kakhulu izokwenzeka - isipele sizomane "satshalaliswe" kumadivayisi ambalwa. Labo. "idivayisi" ngayinye iyodwa ngeke isebenzise, ​​ilahleke eyodwa, ilahlekelwe sonke isipele. Kodwa ukugcoba okunjalo kuzothinta kanjani isivinini sokulondoloza?

Ake sizame ukwenza ikhophi yasenqolobaneni “kumadivayisi” amabili abekwe eceleni kufolda efanayo:

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

Bobaba bezwe kwenziwa yini lokhu?

Kucutshungulwe amakhasi angu-1070072 esizindalwazi esithi 'bt', ifayela elithi 'bt' kufayela elingu-1.

Kucutshungulwe amakhasi ama-2 esizindalwazi 'bt', ifayela 'bt'log' kufayela elingu-1.

IDATHABASE Eyisipele icutshungulwe ngempumelelo amakhasi angu-1070074 kumasekhondi angu-40.092 (208.519 MB/sec).

Ingabe ikhophi yasenqolobaneni ibe ngokushesha ngo-25% ngaphandle nje kokuluhlaza? Kuthiwani uma sengeza amanye amadivayisi ambalwa?

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

IDATHABASE Eyisipele icutshungulwe ngempumelelo amakhasi angu-1070074 kumasekhondi angu-34.234 (244.200 MB/sec).

Sekukonke, inzuzo cishe i-35% yesikhathi sokuthatha isipele kuphela ngenxa yokuthi isipele sibhalwe kumafayela angu-4 kudiski eyodwa ngesikhathi esisodwa. Ngihlole inombolo enkulu - akukho nzuzo kukhompuyutha yami ephathekayo, kahle - amadivaysi ama-4. Okwakho - angazi, udinga ukuhlola. Nokho, ngendlela, uma unalawa madivaysi - lawa amadiski ahlukene ngempela, siyakuhalalisela, inzuzo kufanele ibe ebaluleke nakakhulu.

Manje ake sikhulume ngendlela yokubuyisela le njabulo. Ukuze wenze lokhu, kuzodingeka ushintshe umyalo wokutakula futhi ubhale wonke amadivayisi:

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 icutshungulwe ngempumelelo amakhasi angu-1070074 kumasekhondi angu-38.027 (219.842 MB/isekhondi).

Ngokushesha kancane, kodwa kwenye indawo eduze, hhayi okubalulekile. Ngokuvamile, isipele sisuswa ngokushesha futhi sibuyiselwe ngendlela efanayo - impumelelo? Mina-ke, kuyimpumelelo impela. Lokhu kubalulekile, ngakho ngiyaphinda - uma wena uma ulahlekelwa okungenani elilodwa lalawa mafayela, ulahlekelwa isipele sonke.

Uma ubheka kulogi ulwazi oluyisipele oluboniswa kusetshenziswa i-Trace Flags 3213 kanye ne-3605, uzoqaphela ukuthi uma wenza ikhophi yasenqolobaneni kumadivayisi ambalwa, okungenani inani le-BUFFERCOUNT liyakhuphuka. Mhlawumbe, ungazama ukukhetha amapharamitha angcono kakhulu we-BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, kodwa angizange ngiphumelele ngaso leso sikhathi, futhi ngangivilapha kakhulu ukwenza ukuhlola okunjalo futhi, kodwa ngenombolo ehlukile yamafayela. Futhi kuyihlazo mayelana namasondo. Uma ufuna ukuhlela ukuhlola okunjalo ekhaya, akunzima ukwenza kabusha iskripthi.

Ekugcineni, ake sikhulume ngentengo. Uma ikhophi yasenqolobaneni isusiwe ngokuhambisana nomsebenzi wabasebenzisi, udinga ukuthatha indlela enomthwalo wemfanelo wokuhlola, ngoba uma isipele sisuswa ngokushesha, ama-disks ahlukunyezwa kakhulu, umthwalo ku-processor uyanda (kusadingeka ucindezele it on the fly), futhi ngokufanele, ukusabela jikelele kohlelo kuncipha.

Ngiyadlala nje, kodwa ngiqonda kahle ukuthi angenzanga noma yiziphi izambulo. Okubhalwe ngenhla kuwukukhombisa nje ukuthi ungakhetha kanjani imingcele efanelekile yokuthatha ama-backups.

Khumbula ukuthi konke okwenzayo uzenzela wena ngokwakho futhi uzifaka engozini. Hlola izipele zakho futhi ungakhohlwa nge-DBCC CHECKDB.

Source: www.habr.com

Engeza amazwana