MS SQL Server: BACKUP steroididel

Oodake! Oodake! Tõeliselt, see ei ole очередný artikli SQL Serveri varundamise tüüpide kohta. Ma ei kavatse isegi rääkida taastamismudelite erinevustest ja sellest, kuidas toime tulla „logi” kasvuga.

Võib-olla (ainult võib-olla), pärast selle postituse lugemist, suudate seadistada, et varundamine, mis toimub teie tavadega, toimuks homme öösel umbes 1,5 korda kiiremini. Ja ainult selle tõttu, et kasutate veidi rohkem BACKUP DATABASE parameetreid.

Kui postituse sisu oli teie jaoks ilmne — vabandust. Lugesin läbi kõik, mis Google'is leidus fraasiga „habr sql server varundamine”, ja ühegi artikli juures ei leidnud mainimist, et varundamise ajal saaks millegagi parameetrite abil mõjutada.

Tõmban kohe teie tähelepanu Aleksandr Gladšenko kommentaarile (@mssqlhelp):

Ärge kunagi muutke parameetreid BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE tootmisüksustes. Need on loodud ainult selliste artiklite kirjutamiseks. Praktikas saate mäluprobleeme.

Oks seonduda selle kõige targemana ja jagada eksklusiivset sisu oleks muidugi äge, aga kahjuks ei ole see nii. On olemas nii ingliskeelseid kui venekeelseid artikleid/postitusi (ma alati segaduses, kuidas neid õigesti nimetada), mis on sellele teemale pühendatud. Siin on osa neist, mis mulle ette jäid: üks, kaks, kolm (sql.ru-s).

Nii et alustuseks jagan paar lühendatud BACKUP süntaksit MSDN (muide, seal üleval rääkisin BACKUP DATABASE-st, kuid kõik see kehtib ka tehingu logi ja erineva varundamise kohta, kuid tõenäoliselt vähem selgete tulemustega):

BACKUP DATABASE { database_name | @database_name_var }
  TO  [ ,...n ]
  
  [ WITH { 
           |  [ ,...n ] } ]
[;]

 [ ,...n ]::=

--Meedia komplekti valikud
 
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Andmete edastamise valikud
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

— tähendab, et seal oli midagi, kuid ma eemaldasin selle, kuna see ei ole hetkel teemasse.

Kuidas te tavaliselt varundate? Kuidas „õpetatakse” varundama tohutute artiklites? Üldiselt, kui peab üksainus kord varundama mõnest mitte väga suurest andmebaasist, kirjutaksin automaatselt midagi sellist:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmt.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--nojah, CHECKSUM-i mainisin ainult, et näida targem

Ja, ja, siin on loetletud ilmselt 75-90% kõigist parameetritest, mida tavaliselt mainitakse varundamise artiklites. Seal on INIT, SKIP jne. Kas te olete MSDN-is käinud? Kas nägite, et seal on poolteist ekraani valikuid? Mina olen ka näinud…

Nagu arvatavasti juba mõistsite, räägime nüüd kolmest parameetrist, mis jäid esimesse koodiblokki — BLOCKSIZE, BUFFERCOUNT ja MAXTRANSFERSIZE. Siin on nende kirjeldused MSDN-ist:

BLOCKSIZE = { blocksize | @ blocksize_variable } määrab füüsilise ploki suuruse baitides. Toetatakse suurusi 512, 1024, 2048, 4096, 8192, 16384, 32768 ja 65536 baiti (64 KB). Vaikimisi väärtus on 65536 lindiseadmete jaoks ja 512 teiste seadmete jaoks. Selle parameetri seadmine ei ole tavaliselt vajalik, kuna BACKUP käsk valib automaatselt seadmele sobiva ploki suuruse. Ploki suuruse selge määramine ületab automaatse ploki suuruse valiku.

BUFFERCOUNT = { buffercount | @ buffercount_variable } määrab kokkuvõttes sisendi-väljundi puhverde arvu, mida varundamise operatsioon kasutab. Saate määrata mis tahes positiivse täisarvu, kuid suur hulk puhreid võib põhjustada mälu nappuse vea, kuna Sqlservr.exe protsessis on liiga suur virtuaalne aadressiruum.

Puhvrite poolt kasutatava ruumi kogus määratakse järgmise valemi abil: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } määrab kõige suurema andmepaketi mahu baitides andmete vahetamiseks SQL Serveri ja varundusseadmestiku vahel. Toetatakse väärtusi, mis on 65 536 baitide (64 KB) kordajad, kuni 4 194 304 baitideni (4 MB).

Ma vandun — ma olen seda varem lugenud, aga mulle ei tulnud pähe, millist mõju need jõudlusele avaldada võivad. Veelgi enam, tundub, et pean tegema teatud «tulemuslikkuse avalduse» ja tunnistama, et isegi praegu ei saa ma täpselt aru, mida nad teevad. Ilmselt peaksin rohkem lugema puhverdatud sisendi-väljundi ja kõvaketta töö kohta. Kord ma teen seda, aga praegu võin lihtsalt kirjutada skripti, mis kontrollib, kuidas need väärtused mõjutavad varundamise kiirus.

Tegin väikese andmebaasi, mille suurus on umbes 10 GB, panin selle SSD-le ja varunduse jaoks mõeldud katalooge HDD-le.

Loon ajutine tabel tulemuste salvestamiseks (mul ei ole see ajutine, et saaksin tulemusi põhjalikumalt uurida, aga eks te otsustage ise):

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

Skripti tööpõhimõte on lihtne — pesastavad tsüklid, millest igaüks muudab ühe parameetri väärtust, edastan need parameetrid BACKUP käsule, salvestan viimase sissekande ajaloost msdb.dbo.backupset, kustutan varundusfaili ja järgneb järgmine iteratsioon. Kuna varunduse teostamise andmed pärinevad backupset'ist, kaotatakse täpsus veidi (seal pole sekundite murdeosi), aga sellest me üle saame.

Esmalt on vaja lubada xp_cmdshelli kasutamine, et varundused kustutada (ärge unustage hiljem keelata, kui seda ei ole vaja):

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

Ja nüüd, tegelikult:

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 väärtused */
DECLARE @bs     int = 4096, 
        @max_bs int = 65536;

/* BUFFERCOUNT väärtused */
DECLARE @bc     int = 7,
        @min_bc int = 7,
        @max_bc int = 800;

/* MAXTRANSFERSIZE väärtused */
DECLARE @ts     int = 524288,   --512KB, vaikimisi = 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

Kui teil on küsimusi selle kohta, mis siin toimub, kirjutage kommentaaridesse või isiklikult. Praegu räägin ainult parameetritest, mida edastan BACKUP DATABASE-le.

BLOCKSIZE jaoks on meil "suletud" väärtuste loetelu, mistõttu ei tehtud minu poolelt varukoopiaid BLOCKSIZE < 4KB. MAXTRANSFERSIZE on igasugune number, mis on jagatav 64KB-ga — vahemikus 64KB kuni 4MB. Oletuslikult on minu süsteemis 1024KB, mina valisin 512 — 1024 — 2048 — 4096.

Raskem oli BUFFERCOUNT — see võib olla igasugune positiivne number, kuid link räägib, kuidas see arvutatakse BACKUP DATABASE puhul ja milliseks on suured väärtused ohtlikud.Seal on kirjas ka, kuidas saada teavet selle kohta, millise BUFFERCOUNT-i korral varukoopia tegelikult tehakse — minul on see 7. Selle vähendamine pole mõistlik, kuid ülemine piir avastati katse-eksituse meetodil — BUFFERCOUNT = 896 ja MAXTRANSFERSIZE = 4194304 korral kukkus varukoopia vea tõttu (millest on juttu ülaltoodud lingil):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE lõppes ebanormaalselt.

Msg 701, Level 17, State 123, Line 7 Süsteemimälu on ressursside grupis 'default' piisav, et seda päringut käitada.

Võrdluseks näitan kõigepealt varukoopia tegemise tulemusi ilma parameetreid määramata:

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

Noh, varukoopia ja varukoopia:

Töödeldi 1070072 lehekülge andmebaasist 'bt', fail 'bt' failis 1.

Töödeldi 2 lehekülge andmebaasist 'bt', fail 'bt_log' failis 1.

BACKUP DATABASE töötles edukalt 1070074 lehekülge 53,171 sekundiga (157,227 MB/sec).

Kogu skript, mis testib parameetreid, töötas paaritunniga, kõik mõõtmised on Google'i tabelis.Kuid siin on tulemuste valik, millel on kolm parimat käitusaega (üritasin teha ilusat graafikut, kuid postituses tuleb leppida tabeliga, ja kommentaarides @mixsture lisatud väga lahedad graafikud.).

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;

Tähelepanu, kohe väga oluline märkus @mixsture API-s kommentaarist:

saab kindlalt öelda, et seos parameetrite ja varukoopia kiiruste vahel nende väärtuste piirides on juhuslik, mingeid seaduspärasusi ei ole. Kuid kõrvalekalle sisseehitatud parameetritest näib, et see on selgelt mõjutanud tulemust.

St. ainult standardsete BACKUP parameetrite haldamise kaudu saavutati varukoopia tegemise ajal kahekordne kasu: 26 sekundit, võrreldes 53-ga alguses. Ja see pole ju halb, eks? Kuid peab uurima, kuidas seal taastamine on. Äkki nüüd taastamine kestab 4 korda kauem?

Esiteks mõõdame, kui kaua kulub vaikeseadete korral varukoopia taastamiseks:

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

Noh, seda te teate, seal on teed, replace- ja recovery-funktsioonid. Ja mul käib see nii:

Töödeldi 1070072 lehekülge andmebaasist 'bt', fail 'bt' failis 1.

Töödeldi 2 lehekülge andmebaasist 'bt', fail 'bt_log' failis 1.

RESTORE DATABASE edukalt 1070074 lehte 40.752 sekundiga (205.141 MB/sek).

Nüüd proovin taastada varukoopiaid, mis on tehtud muudetud BLOCKSIZE, BUFFERCOUNT ja MAXTRANSFERSIZE seadistustega.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE edukalt 1070074 lehte 32.283 sekundiga (258.958 MB/sek).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE edukalt 1070074 lehte 32.682 sekundiga (255.796 MB/sek).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE edukalt 1070074 lehte 32.091 sekundiga (260.507 MB/sek).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE edukalt 1070074 lehte 32.401 sekundiga (258.015 MB/sek).

RESTORE DATABASE juhis taastamisel ei muutu, need parameetrid ei ole seal määratletud, SQL Server määrab need ise varukoopiatest. Ja on näha, et isegi taastamisel võib olla kasu — peaaegu 20% kiiremini (Ausalt öeldes ei kulutanud ma taastamisele palju aega, proovisin vaid mõningaid kõige "kiiremaid" varukoopiaid ja veendusin, et halvenemist ei ole.).

Igaks juhuks täpsustan — siin ei ole kirjeldatud mingisuguseid optimaalseid parameetreid. Optimaalsed parameetrid leiate ainult katsetamise teel. Mina sain sellised tulemused, teie saate teised. Aga näete, et oma varukoopiaid saab "tuunida" ja need võivad tõesti luua ja taastada kiiremini.

Soovitan tungivalt lugeda dokumentatsiooni põhjalikult, kuna teie süsteemi jaoks võivad olla spetsiifilised nüansid.

Kuna alustasin varukoopiate kirjutamisega, tahan kohe mainida veel ühte "optimeerimist", mis esineb sagedamini, kui parameetrite "tüünimine" (nii kasutab vähemalt osa varundusvahendeid, võib-olla koos varem kirjeldatud parameetritega), kuid seda ei ole hetkel Habr's veel kirjeldatud.

Kui vaadata dokumentatsiooni teist rida, kohe pärast BACKUP DATABASE, siis näeme:

TO  [ ,...n ]

Kuidas arvate, mis juhtub, kui määrata mitu backup_device'i? Süntaks ju lubab. Ja juhtub väga huvitav asi — varukoopia lihtsalt "jaotub" mitme seadme vahel. St. Iga "seade" eraldi on kasutuks, kui üks kadus, kadus kogu varukoopia. Kuid kuidas mõjutas see jaotamine varundamise kiirust?

Proovime teha varukoopia kahe "seadmest", mis asuvad lähedal ühes kaustas:

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

Issand, mis siin toimub?

Töödeldi 1070072 lehekülge andmebaasist 'bt', fail 'bt' failis 1.

Käideldi 2 lehte andmebaasis 'bt', fail 'btlog' failil 1.

ANDMEBASE BACKUP edukalt töödeldud 1070074 lehte 40,092 sekundi jooksul (208,519 MB/sek).

Kas varukoopia tehti 25% kiiremini lihtsalt niisama? Ent kui lisada veel paar seadet?

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

ANDMEBASE BACKUP edukalt tööteldi 1070074 lehte 34,234 sekundi jooksul (244,200 MB/sek).

Seega on kokkuvõtteks ajavalitsemise sääst umbes 35% ainult seetõttu, et varukoopia kirjutatakse kohe 4 faili ühte diskile. Olen kontrollinud suuremat arvu — minu läptopis ei olnud kasu, optimaalne on 4 seadet. Teie jaoks — ei tea, tuleb kontrollida. Ja jah, kui need seadmed on tõeliselt erinevad kettad, siis õnnitleme, kasu peaks olema veelgi suurem.

Räägime nüüd, kuidas seda väärtust taastada. Selleks tuleb muuta taastamise käsku ja loetleda kõik seadmed:

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

EDASIMINE ANDMEBASE taastamine edukalt tööteldi 1070074 lehte 38,027 sekundi jooksul (219,842 MB/sek).

Natuke kiirem, aga kuskil lähedal, mitte märgatavalt. Üldiselt, varukoopia võetakse kiiremini, ent taastatakse samamoodi — edu? Minu arvates — on see igati positiivne. See on oluline, seega kordustan — kui te kaotate vähemalt ühe neist failidest — kaotate kogu varukoopia.

Kui vaadata varukoopia teavet ajaloos, mis väljastatakse Trace Flag 3213 ja 3605 kaudu, võib märgata, et mitmes seadmes varukoopia tegemisel suureneb vähemalt BUFFERCOUNT. Arvatavasti võib proovida valida ka paremaid parameetreid BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE jaoks, kuid mul ei õnnestunud ning viitsisin veel ühte tüüpi teste teha erinevate failide arvu jaoks. Ja kettad on kahju. Kui soovite selliseid teste korraldada, pole skripti ümber tegemine keeruline.

Lõpuks räägime hinnast. Kui varukoopia võetakse paralleelselt kasutajate tööga — tuleb testimisele väga tõsiselt läheneda, kuna kui varukoopia võetakse kiiremini — kettad koormavad rohkem, protsessori koormus tõuseb (kuna seda tuleb samuti kärpida), vastavalt sellele väheneb kogu süsteemi reaktsioonikiirus.

Naljapäeva naljad naljadeks, aga ma saan täpselt aru, et ma ei teinud mingit avastust. Ülaltoodud, mis on kirjutatud, on lihtsalt näidis sellest, kuidas saab valida optimaalseid parameetreid varukoopiate tegemiseks.

Pidage meeles, et kõik, mida teete — teete oma riski eest. Kontrollige oma varukoopiaid ja ärge unustage DBCC CHECKDB-d.

Allikas: habr.com