ProHoster > Blogg > Administration > MS SQL Server: BACKUP pÄ steroider

MS SQL Server: BACKUP pÄ steroider

VÀnta! VÀnta! Visst Àr det hÀr inte Ànnu en artikel om typer av SQL Server-backuper. Jag kommer inte ens att berÀtta om skillnaderna mellan ÄterstÀllningsmodeller eller hur man hanterar en vÀxande "logg".

Kanske (bara kanske), efter att ha lÀst det hÀr inlÀgget, kan du fÄ sÄ att backuperna som görs med dina standardverktyg blir 1,5 gÄnger snabbare imorgon kvÀll. Och det enbart genom att du anvÀnder lite fler parametrar för BACKUP DATABASE.

Om innehĂ„llet i inlĂ€gget var uppenbart för dig – jag ber om ursĂ€kt. Jag lĂ€ste allt jag hittade pĂ„ Google för frasen "habr sql server backup", och i ingen av artiklarna hittade jag nĂ€mningar om hur man kan pĂ„verka tiden för backupen med hjĂ€lp av parametrar.

Jag vill genast pÄpeka kommentaren frÄn Alexander Gladchenko (@mssqlhelp):

Ändra aldrig parametrarna BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE i produktion. De Ă€r gjorda endast för att skriva sĂ„dana artiklar. I praktiken fĂ„r du problem med minnet pĂ„ köpet.

Det skulle förstÄs vara coolt att vara den smartaste och lÀgga upp exklusivt innehÄll, men tyvÀrr Àr det inte sÄ. Det finns bÄde engelska och ryska artiklar/poster (jag har alltid svÄrt att veta vad jag ska kalla dem) som Àgnar sig Ät detta Àmne. HÀr Àr en del av dem som jag stött pÄ: ett, tvÄ, tre (pÄ sql.ru).

SÄ, först ska jag bifoga en nÄgot nedskuren syntax för BACKUP frÄn MSDN (förresten, dÀr ovan skrev jag om BACKUP DATABASE, men allt detta gÀller Àven för transaktionsloggbackuper och differentielle backuper, men kanske med mindre uppenbar effekt):

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

 [ ,...n ]::=

--Media Set Options
 
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

— betyder att det fanns nĂ„got dĂ€r, men jag tog bort det eftersom det just nu inte Ă€r relevant för Ă€mnet.

Hur brukar du utföra en backup? Hur "lÀr" man sig att utföra en backup i miljarders artiklar? Om jag plötsligt behöver göra en backup av en inte alltför stor databas, kommer jag automatiskt att skriva nÄgot som liknar detta:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
-- okej, CHECKSUM skrev jag bara för att verka smartare

Och hÀr nÀmns nog 75-90% av alla parametrar som vanligtvis nÀmns i artiklar om sÀkerhetskopior. Det finns Àven INIT, SKIP och sÄ vidare. Har ni varit inne pÄ MSDN? Har ni sett att det finns alternativ pÄ ett och ett halvt skÀrmar? Det har jag ocksÄ sett


Ni har nog redan förstĂ„tt att vi nu kommer att prata om de tre parametrarna som Ă„terstĂ„r i den första kodblocket — BLOCKSIZE, BUFFERCOUNT och MAXTRANSFERSIZE. HĂ€r Ă€r deras beskrivningar frĂ„n MSDN:

BLOCKSIZE = { blocksize | @ blocksize_variable } — anger storleken pĂ„ den fysiska blocken i byte. Stödda storlekar Ă€r 512, 1024, 2048, 4096, 8192, 16 384, 32 768 och 65 536 byte (64 KB). StandardvĂ€rdet Ă€r 65 536 för bandenheter och 512 för andra enheter. Ofta behöver man inte ange detta vĂ€rde, eftersom BACKUP-kommandot automatiskt vĂ€ljer blockstorlek som passar enheten. En explicit instĂ€llning av blockstorleken Ă„sidosĂ€tter den automatiska valet.

BUFFERCOUNT = { buffercount | @ buffercount_variable } — definierar det totala antalet in- och utdata buffertar som kommer att anvĂ€ndas för sĂ€kerhetskopieringsoperationen. Du kan ange vilket helt positivt vĂ€rde som helst, men ett stort antal buffertar kan orsaka minnesbristfel pĂ„ grund av överdriven virtuell adressrymd under Sqlservr.exe.

Den totala mÀngden utrymme som anvÀnds av buffertarna berÀknas enligt följande formel: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } anger den maximala mÀngden data som kan överföras i byte mellan SQL Server och mediet för sÀkerhetskopian. VÀrden som Àr multiplar av 65 536 byte (64 KB) stöds upp till 4 194 304 byte (4 MB).

Jag svĂ€r — jag har lĂ€st detta tidigare, men jag hade ingen aning om vilken inverkan de kan ha pĂ„ prestandan. Dessutom verkar det som om jag behöver göra en sorts "coming out" och erkĂ€nna att jag inte helt förstĂ„r vad de faktiskt gör. Kanske jag borde lĂ€sa mer om buffrad in- och utdata och hur hĂ„rddisken fungerar. En dag gör jag det, men just nu kan jag bara skriva ett skript som kontrollerar hur dessa vĂ€rden pĂ„verkar hastigheten pĂ„ sĂ€kerhetskopieringen.

Jag har gjort en liten databas, som Àr ungefÀr 10 GB stor, lade den pÄ en SSD, och katalogen för sÀkerhetskopiorna ligger pÄ en HDD.

Jag skapar en temporÀr tabell för att lagra resultaten (den Àr inte temporÀr för mig sÄ att jag kan analysera resultaten mer ingÄende, men det fÄr ni avgöra sjÀlva):

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

Scriptets arbetsprincip Ă€r enkel — nĂ€stlade loopar, var och en av dem Ă€ndrar vĂ€rdet pĂ„ en parameter, jag lĂ€gger in dessa parametrar i BACKUP-kommandot, sparar den senaste posten med historik frĂ„n msdb.dbo.backupset, tar bort backup-filen och gĂ„r vidare till nĂ€sta iteration. Eftersom data om backupens utförande hĂ€mtas frĂ„n backupset, gĂ„r en viss precision förlorad (det finns inga brĂ„kdelar av sekunder), men vi klarar oss.

Först mÄste du tillÄta anvÀndning av xp_cmdshell för att ta bort backuper (glöm inte att stÀnga av det igen om du inte behöver det):

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

Och, egentligen:

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

Om du plötsligt behöver förklaringar om vad som hĂ€nder hĂ€r — skriv en kommentar eller skickar ett meddelande. Jag kommer bara att berĂ€tta om parametrarna som jag anvĂ€nder i BACKUP DATABASE.

För BLOCKSIZE har vi en "sluten" lista med vĂ€rden, och jag kunde inte genomföra en backup med BLOCKSIZE < 4KB. MAXTRANSFERSIZE kan vara valfritt tal som Ă€r ett multipel av 64KB — frĂ„n 64KB till 4MB. Som standard pĂ„ mitt system Ă€r det 1024KB, jag valde 512 — 1024 — 2048 — 4096.

Det var svĂ„rare med BUFFERCOUNT — det kan vara vilket positivt tal som helst, men hĂ€r stĂ„r det skrivet hur det berĂ€knas i BACKUP DATABASE och vilka risker stora vĂ€rden medför. HĂ€r stĂ„r det ocksĂ„ hur man fĂ„r information om vilket BUFFERCOUNT som verkligen anvĂ€nds för backup — för mig Ă€r det 7. Det fanns ingen anledning att minska det, och den övre grĂ€nsen upptĂ€cktes genom erfarenhet — vid BUFFERCOUNT = 896 och MAXTRANSFERSIZE = 4194304 misslyckades backupen med ett fel (som beskrivs i lĂ€nken ovan):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE avslutas onormalt.

Msg 701, Level 17, State 123, Line 7 Det finns otillrÀckligt systemminne i resurspoolen 'standard' för att köra denna frÄga.

För jÀmförelse kommer jag först att visa resultaten av backup-exekveringen utan att ange nÄgra parametrar överhuvudtaget:

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

Ja, backup Àr backup:

Bearbetade 1070072 sidor för databasen 'bt', fil 'bt' pÄ fil 1.

Bearbetade 2 sidor för databasen 'bt', fil 'bt_log' pÄ fil 1.

BACKUP DATABASE har framgÄngsrikt bearbetat 1070074 sidor pÄ 53.171 sekunder (157.227 MB/sek).

SjÀlva skriptet som testar parametrarna tog ett par timmar, alla mÀtningar finns i Google-tabellen. HÀr Àr utdraget av resultaten med de tre bÀsta tiderna för exekvering (jag försökte göra en fin graf, men i inlÀgget fÄr jag nöja mig med en tabell, och i kommentarerna @mixsture lades till vÀldigt coola grafer).

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;

MS SQL Server: BACKUP pÄ steroider

Observera, en mycket viktig anmÀrkning frÄn @mixsture frÄn kommentaren:

kan man med sÀkerhet sÀga att sambandet mellan parametrarna och hastigheten pÄ backupen inom dessa vÀrden Àr slumpmÀssigt, det finns ingen regelbundenhet. Men avvikelsen frÄn de inbyggda parametrarna har uppenbarligen haft en positiv effekt pÄ resultatet

Det vill sĂ€ga, enbart genom att styra de standardiserade BACKUP-parametrarna har man uppnĂ„tt en tidsvinst pĂ„ 2 gĂ„nger vid genomförande av backupen — 26 sekunder, mot 53 i början. Inte illa, eller hur? Men vi mĂ„ste se vad som hĂ€nder med Ă„terstĂ€llningen. Vad hĂ€nder om den nu tar 4 gĂ„nger lĂ€ngre tid?

Först ska vi mÀta hur lÄng tid det tar att ÄterstÀlla backupen med standardinstÀllningar:

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

Ja, detta vet ni sjÀlva, vÀgar dÀr, replace-eller inte replace, recovery-eller inte recovery. Och sÄ hÀr körs det hos mig:

Bearbetade 1070072 sidor för databasen 'bt', fil 'bt' pÄ fil 1.

Bearbetade 2 sidor för databasen 'bt', fil 'bt_log' pÄ fil 1.

ÅTERSTÄLLA DATABAS har framgĂ„ngsrikt bearbetat 1070074 sidor pĂ„ 40,752 sekunder (205,141 MB/s).

Nu ska jag försöka ÄterstÀlla sÀkerhetskopiorna, som togs med Àndrad BLOCKSIZE, BUFFERCOUNT och MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

ÅTERSTÄLLA DATABAS har framgĂ„ngsrikt bearbetat 1070074 sidor pĂ„ 32,283 sekunder (258,958 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

ÅTERSTÄLLA DATABAS har framgĂ„ngsrikt bearbetat 1070074 sidor pĂ„ 32,682 sekunder (255,796 MB/s).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

ÅTERSTÄLLA DATABAS har framgĂ„ngsrikt bearbetat 1070074 sidor pĂ„ 32,091 sekunder (260,507 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

ÅTERSTÄLLA DATABAS har framgĂ„ngsrikt bearbetat 1070074 sidor pĂ„ 32,401 sekunder (258,015 MB/s).

Instruktionen ÅTERSTÄLLA DATABAS Ă€ndras inte vid Ă„terstĂ€llning, dessa parametrar anges inte, SQL Server bestĂ€mmer dem sjĂ€lv frĂ„n sĂ€kerhetskopian. Och det Ă€r tydligt att Ă€ven vid Ă„terstĂ€llning kan det finnas vinster – praktiskt taget 20% snabbare (Ärligt talat, jag har inte spenderat mycket tid pĂ„ Ă„terstĂ€llning, jag testade nĂ„gra av de "snabbaste" sĂ€kerhetskopiorna och försĂ€krade mig om att det inte finns nĂ„gra försĂ€mringar.).

För sĂ€kerhets skull vill jag pĂ„peka – hĂ€r beskrivs inte nĂ„gra optimala parametrar för alla. De optimala parametrarna för dig kan du endast fĂ„ genom testning. Jag har fĂ„tt dessa resultat, du kommer att fĂ„ andra. Men du ser att dina sĂ€kerhetskopior kan "justeras" och de kan verkligen skapas och Ă„terstĂ€llas snabbare.

Jag rekommenderar ocksÄ starkt att du lÀser dokumentationen i sin helhet, eftersom det kan finnas nyanser just för ditt system.

Eftersom jag har börjat skriva om sÀkerhetskopior vill jag genast ocksÄ ta upp en annan "optimering" som oftare förekommer Àn "justering" av parametrar (den anvÀnds, sÄvitt jag förstÄr, av minst en del verktyg för sÀkerhetskopiering, eventuellt tillsammans med de parametrar som beskrivits tidigare), men den har Ànnu inte beskrivits pÄ Habr.

Om vi tittar pÄ den andra raden i dokumentationen, direkt under BACKUP DATABASE, ser vi:

TO  [ ,...n ]

Vad tror ni hĂ€nder om man anger flera backup_device? Syntaxen tillĂ„ter ju det. Det kommer vara en mycket intressant sak – sĂ€kerhetskopian "sprider sig" helt enkelt över flera enheter. D.v.s. varje "enhet" för sig blir vĂ€rdelös, om man tappar en, tappar man hela sĂ€kerhetskopian. Men hur pĂ„verkar denna spridning hastigheten pĂ„ sĂ€kerhetskopieringen?

Vi ska försöka göra en sÀkerhetskopiering pÄ tvÄ "enheter" som ligger bredvid varandra i samma mapp:

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

Herregud, vad Àr det som hÀnder?

Bearbetade 1070072 sidor för databasen 'bt', fil 'bt' pÄ fil 1.

Bearbetade 2 sidor för databas 'bt', fil 'btlog' pÄ fil 1.

BACKUP DATABASE har framgÄngsrikt bearbetat 1070074 sidor pÄ 40,092 sekunder (208,519 MB/s).

Gjorde backupen 25% snabbare utan nÄgon uppenbar anledning? TÀnk om man lÀgger till nÄgra enheter till?

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

BACKUP DATABASE har framgÄngsrikt bearbetat 1070074 sidor pÄ 34,234 sekunder (244,200 MB/s).

Sammanlagt en tidsvinst pĂ„ cirka 35% för att ta backupen, bara för att den skrivs direkt till 4 filer pĂ„ samma disk. Jag har testat med fler enheter — pĂ„ min laptop fanns ingen vinst, optimalt Ă€r 4 enheter. För er — vet inte, det Ă€r nĂ„got ni fĂ„r testa. Och, förresten, om era enheter Ă€r faktiskt olika diskar, grattis, vinsten borde vara Ă€nnu större.

Nu ska vi prata om hur man ÄterstÀller detta. För det behöver vi Àndra ÄterstÀllningskommandot och lista alla enheter:

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 har framgÄngsrikt bearbetat 1070074 sidor pÄ 38,027 sekunder (219,842 MB/s).

Lite snabbare, men pĂ„ nĂ„got sĂ€tt liknande, inte betydligt. Generellt sett, backupen tas snabbare, men Ă„terstĂ€lls pĂ„ samma sĂ€tt — en framgĂ„ng? För mig — det Ă€r definitivt en framgĂ„ng. Det Ă€r viktigt, sĂ„ jag upprepar — om ni misstĂ€nker bara en av dessa filer — förlorar ni hela backupen..

Om man tittar i loggen om backupen, som kan visas med Trace Flag 3213 och 3605, kan man notera att nÀr backupen görs pÄ flera enheter ökar Ätminstone antalet BUFFERCOUNT. Kanske kan man försöka hitta mer optimala parametrar för BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, men jag lyckades inte direkt, och att göra sÄdana tester igen, men för olika antal filer, kÀnde jag mig lat. Och dessutom Àr diskarna vÀrdefulla. Om ni vill organisera sÄdana tester hos er, Àr det inte svÄrt att omarbeta skriptet.

Till sist, lĂ„t oss prata om kostnaden. Om backupen tas parallellt med anvĂ€ndarnas arbete — sĂ„ behöver man verkligen vara noggrann med testerna, eftersom om backupen gĂ„r snabbare — sĂ„ överbelastas diskarna mer, och belastningen pĂ„ processorn ökar (mĂ„ste ju komprimera allt detta i farten), vilket i sin tur sĂ€nker systemets totala respons.

För att skÀmta lite sÄ förstÄr jag att jag inte har kommit med nÄgra avslöjanden. Det som skrev ovan Àr bara en demonstration av hur man kan vÀlja optimala parametrar för att ta sÀkerhetskopior.

Kom ihĂ„g att allt ni gör — görs pĂ„ egen risk. Kontrollera era sĂ€kerhetskopior och glöm inte DBCC CHECKDB.

KĂ€lla: habr.com

Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar đŸ”„ Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar | ProHoster