ProHoster > blog > Utawala > Seva ya MS SQL: HUDUMA kwenye steroids

Seva ya MS SQL: HUDUMA kwenye steroids

Subiri! Subiri! Kweli, hii sio nakala nyingine kuhusu aina za chelezo za Seva ya SQL. Sitazungumza hata juu ya tofauti kati ya mifano ya uokoaji na jinsi ya kukabiliana na logi iliyokua.

Labda (labda tu), baada ya kusoma chapisho hili, utaweza kuhakikisha kuwa nakala rudufu ambayo imeondolewa kwako kwa kutumia njia za kawaida itaondolewa kesho usiku, vizuri, mara 1.5 haraka. Na tu kutokana na ukweli kwamba unatumia vigezo zaidi vya BACKUP DATABASE.

Ikiwa maudhui ya chapisho yalikuwa dhahiri kwako, samahani. Nilisoma kila kitu ambacho Google ilipata kwa kifungu "hifadhi nakala ya seva ya habr sql", na katika nakala moja sikupata kutajwa kwa ukweli kwamba wakati wa kuhifadhi unaweza kuathiriwa kwa njia fulani kwa kutumia vigezo.

Mara moja nitatoa mawazo yako kwa maoni ya Alexander Gladchenko (@mssqlmsaada):

Usiwahi kubadilisha vigezo vya BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE katika toleo la umma. Zinatengenezwa tu kwa kuandika nakala kama hizo. Kwa mazoezi, utaondoa shida za kumbukumbu kwa muda mfupi.

Ingekuwa, bila shaka, kuwa baridi kuwa maudhui smartest na baada ya kipekee, lakini, kwa bahati mbaya, hii si kesi. Kuna makala/chapisho za lugha ya Kiingereza na Kirusi (mimi huchanganyikiwa kila mara kuhusu nini cha kuziita kwa usahihi) zinazotolewa kwa mada hii. Haya ni baadhi ya niliyokutana nayo: wakati, Π΄Π²Π°, tatu (kwenye sql.ru).

Kwa hivyo, kwa kuanzia, nitaambatisha sintaksia iliyovuliwa kidogo ya HIFADHI kutoka MSDN (kwa njia, niliandika hapo juu juu ya DATABASE YA BACKUP, lakini yote haya yanatumika kwa nakala rudufu ya logi ya ununuzi na chelezo tofauti, lakini labda na athari isiyo wazi):

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

<…> - inamaanisha kuwa kulikuwa na kitu hapo, lakini niliiondoa kwa sababu sasa haihusiani na mada.

Je, unachukuaje nakala rudufu? Je, "wanafundisha" jinsi ya kuchukua nakala rudufu katika mabilioni ya nakala? Kwa ujumla, ikiwa ninahitaji kufanya nakala rudufu ya wakati mmoja ya hifadhidata isiyo kubwa sana, nitaandika kiotomatiki kitu kama hiki:

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

Na, kwa ujumla, labda 75-90% ya vigezo vyote ambavyo kawaida hutajwa katika nakala kuhusu chelezo zimeorodheshwa hapa. Kweli, pia kuna INIT, ruka. Je, umetembelea MSDN? Umeona kuwa kuna chaguzi za skrini moja na nusu? Pia niliona...

Labda tayari umegundua kuwa zaidi tutazungumza juu ya vigezo vitatu vilivyobaki kwenye kizuizi cha kwanza cha nambari - BLOCKSIZE, BUFFERCOUNT na MAXTRANSFERSIZE. Hapa kuna maelezo yao kutoka kwa MSDN:

ZUIA = { kuzuia ukubwa | @ blocksize_variable } - inaonyesha ukubwa wa kizuizi cha kimwili katika baiti. Ukubwa unaotumika ni 512, 1024, 2048, 4096, 8192, 16, 384, na baiti 32 (KB 768). Thamani chaguo-msingi ni 65 kwa vifaa vya tepi na 536 kwa vifaa vingine. Kwa kawaida kigezo hiki si lazima kwa sababu taarifa ya HUDUMA huchagua kiotomati ukubwa wa kuzuia kifaa. Kuweka ukubwa wa kizuizi kunabatilisha kwa uwazi uteuzi wa ukubwa wa kizuizi kiotomatiki.

BUFFERCOUNT = { idadi ya buffer | @ buffercount_variable } - Inafafanua jumla ya idadi ya vibafa vya I/O ambavyo vitatumika kwa utendakazi wa chelezo. Unaweza kubainisha thamani kamili kamili, lakini idadi kubwa ya vihifadhi inaweza kusababisha hitilafu ya nje ya kumbukumbu kutokana na nafasi nyingi za anwani pepe katika mchakato wa Sqlservr.exe.

Jumla ya nafasi inayotumiwa na buffers imedhamiriwa na fomula ifuatayo: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } inabainisha saizi kubwa zaidi ya pakiti ya data, katika baiti, ili kubadilishana kati ya Seva ya SQL na seti ya hifadhi ya midia. Anuwai za baiti 65 (536 KB) hadi baiti 64 (MB 4) zinatumika.

Ninaapa - Nimesoma hii hapo awali, lakini haikunijia ni kiasi gani wangeweza kuwa na athari kwenye tija. Zaidi ya hayo, inaonekana, ninahitaji kufanya aina ya "kutoka" na kukubali kwamba hata sasa sielewi kikamilifu ni nini hasa wanachofanya. Labda ninahitaji kusoma zaidi juu ya I/O iliyohifadhiwa na kufanya kazi na gari ngumu. Siku moja nitafanya hivi, lakini kwa sasa ninaweza tu kuandika hati ambayo itaangalia jinsi maadili haya yanavyoathiri kasi ambayo nakala rudufu inachukuliwa.

Nilifanya hifadhidata ndogo, karibu GB 10 kwa saizi, kuiweka kwenye SSD, na kuweka saraka ya chelezo kwenye HDD.

Ninaunda meza ya muda ili kuhifadhi matokeo (Sina muda mfupi, ili niweze kuchimba matokeo kwa undani zaidi, lakini unaamua mwenyewe):

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

Kanuni ya script ni rahisi - vitanzi vilivyowekwa, ambayo kila mmoja hubadilisha thamani ya parameter moja, ingiza vigezo hivi kwenye amri ya BACKUP, uhifadhi rekodi ya mwisho na historia kutoka kwa msdb.dbo.backupset, futa faili ya chelezo na iteration inayofuata. . Kwa kuwa data ya utekelezaji wa chelezo inachukuliwa kutoka kwa chelezo, usahihi umepotea (hakuna sehemu za sekunde), lakini tutaishi hii.

Kwanza unahitaji kuwezesha xp_cmdshell kufuta nakala rudufu (basi usisahau kuizima ikiwa hauitaji): 

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

Kweli, kwa kweli:

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

Ikiwa ghafla unahitaji ufafanuzi juu ya kile kinachotokea hapa, andika kwenye maoni au PM. Kwa sasa, nitakuambia tu kuhusu vigezo ambavyo nimeweka kwenye DATABASE YA BACKUP.

Kwa BLOCKSIZE tuna orodha "iliyofungwa" ya thamani, na sikuhifadhi nakala kwa BLOCKSIZE <4KB. MAXTRANSFERSIZE nambari yoyote ambayo ni kizidishio cha 64KB - kutoka 64KB hadi 4MB. Chaguo-msingi kwenye mfumo wangu ni 1024KB, nilichukua 512 - 1024 - 2048 - 4096.

Ilikuwa ngumu zaidi na BUFFERCOUNT - inaweza kuwa nambari yoyote chanya, lakini kiungo kinasema inakokotolewa vipi katika HUDUMA YA DATABASE na kwa nini maadili makubwa ni hatari?. Pia inasema jinsi ya kupata habari kuhusu ambayo BUFFERCOUNT chelezo imefanywa kwa kweli - kwangu ni 7. Hakukuwa na maana katika kuipunguza, na kikomo cha juu kiligunduliwa kwa majaribio - na BUFFERCOUNT = 896 na MAXTRANSFERSIZE = 4194304 chelezo ilianguka na hitilafu (ambayo imeandikwa kwenye kiungo hapo juu):

Msg 3013, Level 16, State 1, Line 7 DATABASE HIFADHI inaisha isivyo kawaida.

Msg 701, Kiwango cha 17, Jimbo la 123, Mstari wa 7 Hakuna kumbukumbu ya kutosha ya mfumo katika 'chaguo-msingi' ya hifadhi ya rasilimali ili kutekeleza hoja hii.

Kwa kulinganisha, kwanza nitaonyesha matokeo ya kuendesha chelezo bila kutaja vigezo vyovyote:

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

Kweli, chelezo na chelezo:

Imechakatwa kurasa 1070072 kwa hifadhidata 'bt', faili 'bt' kwenye faili 1.

Imechakatwa kurasa 2 za hifadhidata 'bt', faili 'bt_log' kwenye faili 1.

HUDUMA YA DATABASE ilichakata kurasa 1070074 kwa sekunde 53.171 (157.227 MB/sekunde).

Nakala yenyewe, ikijaribu vigezo, ilifanya kazi kwa masaa kadhaa, vipimo vyote vilikuwa ndani lahajedwali ya google. Na hapa kuna uteuzi wa matokeo na nyakati tatu bora za utekelezaji (nilijaribu kutengeneza grafu nzuri, lakini kwenye chapisho itabidi nifanye na meza, na kwenye maoni. @mchanganyiko aliongeza graphics baridi sana).

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;

Seva ya MS SQL: HUDUMA kwenye steroids

Tahadhari, kumbuka muhimu sana kutoka @mchanganyiko ya maoni:

Tunaweza kusema kwa ujasiri kwamba uhusiano kati ya vigezo na kasi ya chelezo ndani ya safu hizi za maadili ni nasibu, hakuna muundo. Lakini kuhama kutoka kwa vigezo vilivyojengwa ni wazi kulikuwa na athari nzuri kwenye matokeo

Wale. Ni kwa kudhibiti tu vigezo vya kawaida vya HIFADHI ilikuwa faida mara 2 katika muda wa uondoaji wa chelezo: sekunde 26, dhidi ya 53 mwanzoni. Hiyo si mbaya, sawa? Lakini tunahitaji kuona nini kinatokea na urejesho. Je, ikiwa sasa inachukua mara 4 zaidi kurejesha?

Kwanza, hebu tupime inachukua muda gani kurejesha nakala rudufu na mipangilio chaguo-msingi:

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

Kweli, wewe mwenyewe unajua kuwa, njia zipo, badala sio nafasi, kupona sio kupona. Na mimi hufanya kama hii:

Imechakatwa kurasa 1070072 kwa hifadhidata 'bt', faili 'bt' kwenye faili 1.

Imechakatwa kurasa 2 za hifadhidata 'bt', faili 'bt_log' kwenye faili 1.

REJESHA DATABASE ilichakata kurasa 1070074 kwa sekunde 40.752 (205.141 MB/sekunde).

Sasa nitajaribu kurejesha nakala zilizochukuliwa na BLOCKSIZE iliyobadilishwa, BUFFERCOUNT na MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

REJESHA DATABASE ilichakata kurasa 1070074 kwa sekunde 32.283 (258.958 MB/sekunde).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

REJESHA DATABASE ilichakata kurasa 1070074 kwa sekunde 32.682 (255.796 MB/sekunde).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

REJESHA DATABASE ilichakata kurasa 1070074 kwa sekunde 32.091 (260.507 MB/sekunde).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

REJESHA DATABASE ilichakata kurasa 1070074 kwa sekunde 32.401 (258.015 MB/sekunde).

Taarifa ya RESTORE DATABASE haibadiliki wakati wa urejeshaji; vigezo hivi havijabainishwa ndani yake; Seva ya SQL yenyewe huamua kutoka kwa chelezo. Na ni wazi kuwa hata kwa kupona kunaweza kuwa na faida - karibu 20% haraka (Kuwa waaminifu, sikutumia muda mwingi kurejesha, nilipitia nakala kadhaa za "haraka" na kuhakikisha kuwa hakuna uharibifu.).

Ikiwezekana, wacha nifafanue kuwa hizi sio vigezo ambavyo ni sawa kwa kila mtu. Unaweza tu kupata vigezo bora kwako kwa kujaribu. Nimepata matokeo haya, utapata tofauti. Lakini unaona kwamba unaweza "kurekebisha" chelezo zako na zinaweza kuunda na kupeleka haraka.

Pia ninapendekeza sana kwamba usome nyaraka kwa ukamilifu, kwa sababu kunaweza kuwa na nuances maalum kwa mfumo wako.

Tangu nianze kuandika juu ya chelezo, nataka kuandika mara moja juu ya "optimization" moja zaidi, ambayo ni ya kawaida zaidi kuliko vigezo vya "kurekebisha" (kwa kadiri ninavyoelewa, inatumiwa na angalau huduma zingine za chelezo, labda pamoja na vigezo. ilivyoelezwa hapo awali), lakini bado haijaelezewa kuhusu Habre pia.

Ikiwa tutaangalia mstari wa pili kwenye nyaraka, chini ya DATABASE YA BACKUP, hapo tunaona:

TO <backup_device> [ ,...n ]

Je, unadhani nini kitatokea ikiwa utabainisha backup_devices kadhaa? Syntax inaruhusu. Na jambo la kufurahisha sana litatokea - nakala rudufu "itaenea" kwenye vifaa kadhaa. Wale. kila "kifaa" kivyake hakitakuwa na maana, kimepotea kimoja, kimepoteza chelezo nzima. Lakini upakaji kama huo utaathiri vipi kasi ya chelezo?

Wacha tujaribu kufanya nakala rudufu kwenye "vifaa" viwili ambavyo viko kando kwenye folda moja:

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

Akina baba wa dunia, kwa nini haya yanafanyika?

Imechakatwa kurasa 1070072 kwa hifadhidata 'bt', faili 'bt' kwenye faili 1.

Imechakatwa kurasa 2 za hifadhidata 'bt', faili 'bt'log' kwenye faili 1.

HUDUMA YA DATABASE ilichakata kurasa 1070074 kwa sekunde 40.092 (208.519 MB/sekunde).

Je, hifadhi rudufu iliharakishwa kwa 25% tu nje ya bluu? Je, ikiwa tutaongeza vifaa kadhaa zaidi?

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

HUDUMA YA DATABASE ilichakata kurasa 1070074 kwa sekunde 34.234 (244.200 MB/sekunde).

Kwa jumla, faida ni karibu 35% ya wakati wa kuchukua chelezo tu kwa sababu ya ukweli kwamba nakala rudufu imeandikwa kwa faili 4 kwenye diski moja mara moja. Niliangalia nambari kubwa - hakuna faida kwenye kompyuta yangu ya mbali, vyema - vifaa 4. Kwa ajili yako - sijui, unahitaji kuangalia. Kweli, kwa njia, ikiwa una vifaa hivi - hizi ni diski tofauti kabisa, pongezi, faida inapaswa kuwa muhimu zaidi.

Sasa hebu tuzungumze juu ya jinsi ya kurejesha furaha hii. Ili kufanya hivyo, utahitaji kubadilisha amri ya kurejesha na kuorodhesha vifaa vyote:

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

REJESHA DATABASE ilichakata kurasa 1070074 kwa sekunde 38.027 (219.842 MB/sekunde).

Haraka kidogo, lakini mahali fulani karibu, sio muhimu. Kwa ujumla, chelezo huondolewa kwa kasi na kurejeshwa kwa njia ile ile - mafanikio? Kama mimi, ni mafanikio kabisa. Hii ni muhimu, kwa hivyo narudia - ikiwa wewe ukipoteza angalau faili moja kati ya hizi, utapoteza chelezo nzima.

Ukiangalia katika logi taarifa ya hifadhi rudufu inayoonyeshwa kwa kutumia Alama za Kufuatilia 3213 na 3605, utagundua kuwa unapohifadhi nakala kwenye vifaa kadhaa, angalau idadi ya BUFFERCOUNT huongezeka. Labda, unaweza kujaribu kuchagua vigezo bora zaidi vya BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, lakini sikufanikiwa mara moja, na nilikuwa mvivu sana kufanya majaribio kama haya tena, lakini kwa idadi tofauti ya faili. Na ni aibu juu ya magurudumu. Ikiwa unataka kupanga upimaji huo nyumbani, si vigumu kufanya upya hati.

Hatimaye, hebu tuzungumze kuhusu bei. Ikiwa nakala rudufu imeondolewa sambamba na kazi ya watumiaji, unahitaji kuchukua njia ya kuwajibika sana ya upimaji, kwa sababu ikiwa nakala rudufu imeondolewa haraka, diski zinachujwa zaidi, mzigo kwenye processor huongezeka (bado lazima ushinike). juu ya kuruka), na ipasavyo, mwitikio wa jumla wa mfumo hupungua.

Ninatania tu, lakini ninaelewa vizuri kwamba sikutoa ufunuo wowote. Kilichoandikwa hapo juu ni onyesho tu la jinsi unaweza kuchagua vigezo bora vya kuchukua chelezo.

Kumbuka kwamba kila kitu unachofanya kinafanywa kwa hatari na hatari yako mwenyewe. Angalia nakala zako na usisahau kuhusu DBCC CHECKDB.

Chanzo: mapenzi.com

Kuongeza maoni