MS SQL Server: BACKUP steroides

Oodake! Oodake! Tõsi, see ei ole järjekordne artikkel SQL Serveri varukoopia tüüpide kohta. Ma ei hakka isegi rääkima taastemudelite erinevustest ja sellest, kuidas võidelda suurenenud 'logiga'.

Võimalik (ainult võimalik), et pärast selle postituse lugemist suudate teha nii, et varukoopia, mida te teie standardsed vahendid teevad, toimub homme öösel umbes 1,5 korda kiiremini. Ja ainult seetõttu, et kasutate veidi rohkem BACKUP DATABASE parameetreid.

Kui postituse sisu oli teile ilmne — vabandust. Lugesin kõike, millele Google 'habr sql server backup' fraasi tänu jõudis, ja ükski artikkel ei maininud, et varukoopia ajal saab kuidagi parameetritega mõjutada.

Tahan kohe tähelepanu juhtida Aleksandr Gladšenko kommentaarile (@mssqlhelp):

Ärge kunagi muutke parameetreid BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE tootmises. Need on loodud ainult sarnaste artiklite kirjutamiseks. Tegelikult kohtate mäluprobleemidega võrreldes halbu aegu.

Muidugi oleks lahe olla kõige targem ja jagada eksklusiivset sisu, kuid paraku pole see nii. On olemas nii inglise- kui ka venekeelseid artikleid/postitusi (ma alati segadusse jään, kuidas neid õigesti nimetada), mis käsitlevad seda teemat. Siin on mõned, mis minu ette sattusid: kord, kaks, kolm (sql.ru-s).

Nii et alustuseks jagan mõned kärbitud sintaksit BACKUP-ist MSDN-ilt (muide, seal ma juba kirjutasin BACKUP DATABASE-st, kuid kõik see kehtib ka tehingute ajakirja varundamise ja erineva varukoopia kohta, ehkki vähem ilmsete mõjude korral):

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

 [ ,...n ]::=

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

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

— tähendab, et seal oli midagi, aga ma eemaldasin selle, kuna see ei puuduta hetkel teemat.

Kuidas te tavaliselt varukoopiaid teete? Kuidas «õpetatakse» varukoopiaid tegema miljardites artiklites? Üldiselt, kui peaks olema vaja ühekordselt teha varukoopia mõnest mitte eriti suurest andmebaasist, siis kirjutaksin automaatselt midagi sellist:

VARU TABEL smth
KETTLE = 'D:Backupsmth.bak'
KOOS STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
-- noh, CHECKSUM on kohe kirja pandud ainult selleks, et targem välja näha

Ja tõenäoliselt on siin nimetatud umbes 75-90% kõigist parameetritest, mida tavaliselt mainitakse varukoopiate artiklites. Seal on INIT, SKIP jne. Kas olete MSDN-is käinud? Kas te nägite, et seal on poolteist ekraani valikuid? Mina nägin ka…

Tõenäoliselt olete juba aru saanud, et edasi räägime kolmest parameetrist, mis jäid esimesesse koodiplokki — BLOCKSIZE, BUFFERCOUNT ja MAXTRANSFERSIZE. Siin on nende kirjeldused MSDN-ist:

BLOCKSIZE = { blocksize | @ blocksize_variable } — näitab füüsilise bloki suurust baitides. Toetatakse suurusi 512, 1024, 2048, 4096, 8192, 16 384, 32 768 ja 65 536 baiti (64 KB). Vaikeväärtus on 65 536 lintseadmest ja 512 muude seadmete jaoks. Tavaliselt ei ole selle parameetri seadmine vajalik, kuna BACKUP käsk automaatselt valib seadme jaoks sobiva ploki suuruse. Bloki suuruse selge seadmine ületab automaatse ploki suuruse valimise.

BUFFERCOUNT = { buffercount | @ buffercount_variable } — määrab sisendi ja väljundi puhverfailide koguarvu, mida kasutatakse varundamisoperatsiooni jaoks. Võib märkida mis tahes täisarvu positiivset väärtust, kuid liiga suur puhverfailide arv võib põhjustada mälu puudumise tõrke, kuna Sqlservr.exe protsessis võib tekkida liigne virtuaalne aadressiruum.

Puhvri kasutatud kogus ruumi määratakse järgmise valemi järgi: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } näitab maksimaalset andmepaketi suurust baitides andmete vahetamiseks SQL Serveri ja varundusmedia vahel. Toetatakse väärtusi, mis on jagatavad 65 536 baitiga (64 KB), kuni 4 194 304 baitini (4 MB).

Ma ütlen ausalt, et ma olen seda juba varem lugenud, aga ma ei arvanud kunagi, millist mõju nad jõudlusele avaldada võivad. Veelgi enam, ilmselt tuleb mul teha omamoodi 'välja tulek' ja tunnistada, et isegi praegu ei saa ma täielikult aru, mida täpselt nad teevad. Tõenäoliselt peaksin natuke rohkem lugema puhverdatud sisend-väljundi ja kõvaketta töö kohta. Kunagi teen seda, kuid praegu võin lihtsalt kirjutada skripti, mis testib, kuidas need väärtused mõjutavad varukoopia tegemise kiirus.

Loodud on väike andmebaas, mille suurus on umbes 10 GB, mis on salvestatud SSD-le, ja varukoopiate kataloog on HDD-l.

Loon ajutise tabeli tulemuste salvestamiseks (kuigi mul on see mitte ajutine, et saaksin tulemusi lähemalt uurida, aga 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 — pesas tsüklid, millest igaüks muudab ühe parameetri väärtust, edastan need parameetrid BACKUP käsule, salvestan viimase kirje ajaloost msdb.dbo.backupset, kustutan varukoopia faili ja järgmine iteratsioon. Kuna varukoopia teostamise andmed saadakse backupset'ist, kaotame natuke täpsust (seal ei ole sekundimurde), kuid me saame hakkama.

Esiteks tuleb lubada xp_cmdshelli kasutamine, et varukoopiaid kustutada (ärge unustage hiljem seda välja lülitada, kui see teile ei ole vajalik):

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

Noh ja, tegelikult:

DEKLARE @tmplt AS nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:SQLServerbackupbt.bak''
WITH 
    COMPRESSION,
    BLOCKSIZE = {bs},
    BUFFERCOUNT = {bc},
    MAXTRANSFERSIZE = {ts}';

DEKLARE @sql AS nvarchar(max);

/* BLOCKSIZE väärtused */
DEKLARE @bs     int = 4096, 
        @max_bs int = 65536;

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

/* MAXTRANSFERSIZE väärtused */
DEKLARE @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 lisan BACKUP DATABASE-i.

BLOCKSIZE jaoks on meil 'suletud' väärtuste loetelu, ning minul ei olnud varukoopiat BLOCKSIZE < 4KB. MAXTRANSFERSIZE võib olla ükskõik milline arv, mis on korrutis 64KB — alates 64KB kuni 4MB. Minu süsteemis on vaikimisi 1024KB, mina kasutasin 512 — 1024 — 2048 — 4096.

Raskem oli BUFFERCOUNT'iga — see võib olla ükskõik positiivne number, kuid lingil on kirjas kuidas seda arvutatakse BACKUP DATABASE'is ja millised on suured väärtused ohtlikud. Seal on kirjas, kuidas saada teavet selle kohta, millise BUFFERCOUNT'iga varukoopia tegelikult tehakse — minul on see 7. Seda ei olnud mõtet vähendada, kuna ülemine piir leiti katse-eksituse meetodil — BUFFERCOUNT = 896 ja MAXTRANSFERSIZE = 4194304 korral kukkus varukoopia tõrkega (millest on juttu ülaltoodud lingil):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE lõpetab tavapärase töö.

Msg 701, Level 17, State 123, Line 7 Ressursside paigutuses 'default' ei ole piisavalt süsteemi mälu, et seda päringut käivitada.

Võrdluseks, kõigepealt näitan tulemusi varukoopia tegemisel, ilma parameetreid üldse näitamata:

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

Noh, varukoopia ja varukoopia:

Töötles 1070072 lehte andmebaasi 'bt' jaoks, fail 'bt' failil 1.

Töötles 2 lehte andmebaasi 'bt', fail 'bt_log' failil 1.

BACKUP DATABASE töötles edukalt 1070074 lehte 53.171 sekundiga (157.227 MB/sec).

Kogu skript, mis testib parameetreid, töötas paar tunni jooksul, kõik mõõtmised on Google'i tabelis. Kuid siin on valik kolme parima tööaja tulemuste kohta (ma proovisin teha kena graafiku, kuid postituses tuleb leppida tabeliga, ja kommentaarides) @mixsture lisatud väga ägedad 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ähtis märkusele, kohe väga oluline märkus @mixsture kohast kommentaarist:

saab kindlalt öelda, et seos parameetrite ja varukoopia kiirus vahel nende väärtuste juures on juhuslik, mingit mustrit ei ole. Kuid kõrvalejäämine vaikimisi parameetritest on ilmselt tulemust hästi mõjutanud

St. ainult standardsete BACKUP parameetrite juhtimise tõttu saavutati varukoopia tegemise ajal kokkuhoid 2 korda: 26 sekundit, võrreldes 53-ga alguses. Ja see pole ju halb, eks? Kuid tuleks vaatama, mis taastamisega juhtub. Ja äkki nüüd taastub see 4 korda kauem?

Alustuseks mõõdame, kui kaua varukoopia taastamine toimub vaikimisi seadistustega:

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

Noh, selle teate te ise, seal on tee, replace- ei replace, recovery - ei recovery. Ja minul käib see nii:

Töötles 1070072 lehte andmebaasi 'bt' jaoks, fail 'bt' failil 1.

Töötles 2 lehte andmebaasi 'bt', fail 'bt_log' failil 1.

ANDMEBAASI TAASLOOMINE töötas edukalt 1070074 lehe taastamisega 40,752 sekundiga (205,141 MB/sec).

Nüüd proovin taastada varukoopiaid, mille BLOCKSIZE, BUFFERCOUNT ja MAXTRANSFERSIZE on muudetud.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

ANDMEBAASI TAASLOOMINE töötas edukalt 1070074 lehe taastamisega 32,283 sekundiga (258,958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

ANDMEBAASI TAASLOOMINE töötas edukalt 1070074 lehe taastamisega 32,682 sekundiga (255,796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

ANDMEBAASI TAASLOOMINE töötas edukalt 1070074 lehe taastamisega 32,091 sekundiga (260,507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

ANDMEBAASI TAASLOOMINE töötas edukalt 1070074 lehe taastamisega 32,401 sekundiga (258,015 MB/sec).

ANDMEBAASI TAASLOOMISE juhised ei muutu taastamisel, neid parameetreid ei täpsustata, SQL Server määrab need ise varukoopia põhjal. Ja on näha, et isegi taastamise korral võib olla kasu — praktiliselt 20% kiiremini (ausalt öeldes, ei pööranud taastamiseks palju aega, proovisin lihtsalt mõnda kõige "kiiremat" varukoopiat ja veendusin, et halvenemist ei ole.).

Kuna see kohta on kirjas, siis selgitan, et siin ei ole kirjeldatud kõigile sobivaid optimaalsete parameetreid. Oma parimad parameetrid saate te ainult testimisega. Mina sain sellised tulemused, te saate teised. Aga te näete, et oma varukoopiad võib "häälestada" ja need võivad tõeliselt kiiremini tekkida ja taastuda.

Soovitan tungivalt lugeda kogu dokumentatsiooni, sest just teie süsteemile võivad olla erilised nüansid.

Kuna alustasin varukoopiate kirjutamist, soovin kohe rääkida veel ühest „optimeerimisest”, mis on sagedamini esinev kui parameetrite „häälestamine” (nii nagu ma aru saan, kasutab seda vähemalt osa varundusutiliitidest, võib-olla koos varasemalt kirjeldatud parameetritega), kuid Habré pole sellest veel juttu olnud.

Kui vaadata dokumentatsiooni teist rida, kohe BACKUP DATABASE'i all, näeme me:

TO  [, ...n]

Kuidas arvatakse, et juhtub, kui määrata mitu backup_device'i? Süntees lubab seda. Ja tulemuseks on väga huvitav asi - varukoopia lihtsalt «jagunen» mitme seadme vahel. See tähendab, et iga «seade» eraldi on kasutult, kui kaotame ühe, kaotame kogu varukoopia. Aga kuidas selline jagunemine mõjutab varundamise kiirus?

Proovime varundada kahele «seadmest», mis asuvad üksteise kõrval samas kaustas:

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

Issand jumal, mida see küll teeb?

Töötles 1070072 lehte andmebaasi 'bt' jaoks, fail 'bt' failil 1.

Töödeldud 2 lehte andmebaasist ‘bt’, fail ‘btlog’ failil 1.

BACKUP DATABASE successfully processed 1070074 pages in 40.092 seconds (208.519 MB/sec).

Varukoopia tehti 25% kiiremini lihtsalt tühja koha pealt? Mis juhtub, kui veel paar seadet lisada?

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

BACKUP DATABASE successfully processed 1070074 pages in 34.234 seconds (244.200 MB/sec).

Kokkuvõttes on kasum umbes 35% varukoopia tegemise ajast, kuna varukoopia kirjutatakse kohe nelja faili ühele kettale. Kontrollisin suuremat hulka — minu sülearvutis ei olnud kasu, optimaalne on neli seadet. Teie jaoks — ei tea, tuleb kontrollida. Ja muide, kui need seadmed on tõepoolest erinevad kettad, siis õnnitleme, kasu peaks olema veelgi suurem.

Nüüd räägime, kuidas seda rõõmu taastada. Selleks peab taastamis käsu muutma ja kõik seadmed loetlema:

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 successfully processed 1070074 pages in 38.027 seconds (219.842 MB/sec).

Veidi kiiremini, kuid kuskil kõrval, mitte oluliselt. Üldiselt, varukoopia tehakse kiiremini ja taastatakse samamoodi — kas see on edu? Minu arvates on see täiesti korralik edu. See on oluline, seega kordan veel kord — kui te kaotate vähemalt ühe neist failidest — kaotate kogu varukoopia.

Kui vaadata logis teavet varunduse kohta, mida väljastatakse Trace Flag 3213 ja 3605 abil, siis võib tõdeda, et mitme seadme varundamisel suureneb vähemalt BUFFERCOUNTi arv. Arvatavasti saab proovida leida optimaalsemad seaded ka BUFFERCOUNTi, BLOCKSIZE'i ja MAXTRANSFERSIZE'i jaoks, kuid mul ei õnnestunud seda kohe teha, ja pärast katsetamist erinevate failide arvuga olin laisk. Ja kahju on ketastest. Kui soovite korraldada sellist testimist enda juures, pole skripti ümbertegemine keeruline.

Lõpuks räägime hinnast. Kui varundus toimub paralleelselt kasutajate tööga, tuleb testimisele läheneda väga vastutustundlikult, sest kui varundus toimub kiiremini, koormatakse kettaid rohkem, protsessori koormus suureneb (peab ju seda kõike veel ka jooksvalt kokku suruma), vastavalt väheneb süsteemi üldine reageerimisvõime.

Naerud-naerudeks, aga ma mõistan hästi, et ma ei teinud mingeid ilmutusi. Ülaltoodud on lihtsalt näide sellest, kuidas saab valida optimaalseid seadeid varunduste tegemiseks.

Pidage meeles, et kõik, mida te teete — on teie enda risk. Kontrollige oma varukoopiaid ja ärge unustage DBCC CHECKDB-d.

Allikas: habr.com