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

MS SQL Server: BACKUP pÄ steroider

Vent venligst! Vent venligst! Sandt nok er dette ikke endnu en artikel om typer af SQL Server-backup. Jeg vil endda ikke fortÊlle om forskellene mellem gendannelsesmodeller og hvordan man hÄndterer en voksende 'log'.

MÄske (kun mÄske), efter at have lÊst dette indlÊg, vil du kunne fÄ din backup, som tages med standardvÊrktÞjer, til at blive taget i morgen nat, ja, 1,5 gange hurtigere. Og kun ved at du bruger lidt flere BACKUP DATABASE-parametre.

Hvis indholdet af indlÊgget var Äbenlyst for dig - undskyld. Jeg har lÊst alt, hvad jeg kunne finde pÄ Google ved sÞgningen 'habr sql server backup', og i ingen artikel fandt jeg nÊvnt, at man pÄ en eller anden mÄde kan pÄvirke backup-processen med parametre.

Jeg vil straks henlede din opmÊrksomhed pÄ Alexander Gladchenko's kommentar (@mssqlhelp):

Ændre aldrig parametrene BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE i produktion. De er kun lavet til at skrive sĂ„danne artikler. I praksis vil du fĂ„ problemer med hukommelsen.

Det ville selvfÞlgelig vÊre fedt at vÊre den klogeste og offentliggÞre eksklusivt indhold, men desvÊrre er det ikke sÄdan. Der findes bÄde engelsksprogede og russisk-sprogede artikler/poste (jeg forveksler altid, hvad jeg skal kalde dem), der omhandler dette emne. Her er en del af dem, jeg stÞdte pÄ: en, to, tre (pÄ sql.ru).

SÄ lad mig starte med at vedlÊgge en forkortet syntaks for BACKUP fra MSDN (forresten, jeg nÊvnte ovenfor BACKUP DATABASE, men alt dette gÊlder ogsÄ for backup af transaktionslog og differentiel backup, men muligvis med mindre tydelig 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, at der var noget der, men jeg har fjernet det, fordi det ikke er relevant for emnet nu.

Hvordan tager du normalt en backup? Hvordan 'lÊrer' man at tage backup i milliardvis af artikler? Generelt, hvis jeg skal tage backup af en ikke sÄ stor database en enkelt gang, vil jeg automatisk skrive noget i retning af fÞlgende:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
-- okay, CHECKSUM skrev jeg kun for at se klogere ud

Og generelt, her er sandsynligvis 75-90% af alle parametre, der normalt nÊvnes i artikler om backup. Der er ogsÄ INIT, SKIP og sÄ videre. Har du vÊret i MSDN? Har du set, at der er options, der fylder halvanden skÊrm? Jeg har ogsÄ set det...

Du har sandsynligvis allerede forstĂ„et, at det nĂŠste handler om de tre parametre, der er tilbage i den fĂžrste kodeblok — BLOCKSIZE, BUFFERCOUNT og MAXTRANSFERSIZE. Her er deres beskrivelser fra MSDN:

BLOCKSIZE = { blocksize | @ blocksize_variable } — angiver stĂžrrelsen af den fysiske blok i bytes. StĂžrrelserne 512, 1024, 2048, 4096, 8192, 16 384, 32 768 og 65 536 bytes (64 KB) understĂžttes. StandardvĂŠrdien er 65 536 for bĂ„nddrev og 512 for andre enheder. Normalt er der ikke behov for denne parameter, da BACKUP-instruktionen automatisk vĂŠlger en blokstĂžrrelse, der passer til enheden. En eksplicit indstilling af blokstĂžrrelsen tilsidesĂŠtter den automatiske blokstĂžrrelsesvalg.

BUFFERCOUNT = { buffercount | @ buffercount_variable } — definerer det samlede antal input/output-puffer, der vil blive brugt til backup-operationen. Enhver positiv heltalsvĂŠrdi kan angives, men et stort antal buffere kan forĂ„rsage en hukommelsesfejl pĂ„ grund af overdreven virtuelt adresseomrĂ„de i Sqlservr.exe.

Det samlede rum, der bruges af buffere, bestemmes efter fÞlgende formel: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } angiver den stÞrste mÊngde data i bytes, der udveksles mellem SQL Server og backupmediet. VÊrdier, der er multipler af 65 536 bytes (64 KB), understÞttes op til 4 194 304 bytes (4 MB).

Jeg svĂŠrger — jeg har lĂŠst dette fĂžr, men jeg havde aldrig tĂŠnkt over, hvilken indflydelse de kan have pĂ„ ydelsen. Desuden skal jeg Ă„benbart lave en slags "coming out" og indrĂžmme, at jeg stadig ikke helt forstĂ„r, hvad de gĂžr. MĂ„ske skal jeg lĂŠse mere om buffered input/output og arbejde med harddisken. En dag vil jeg gĂžre det, men lige nu kan jeg bare skrive et script, der kontrollerer, hvordan disse vĂŠrdier pĂ„virker hastigheden, hvormed backupen bliver taget.

Jeg har lavet en lille database, der er omkring 10 GB, lagt den pÄ SSD, og placeringen for backupene er pÄ HDD.

Jeg opretter en midlertidig tabel til at gemme resultaterne (for mig er den ikke midlertidig, sÄ jeg kan undersÞge resultaterne nÊrmere, men I mÄ selv beslutte):

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 princip er simpelt - indlejrede lÞkker, hvor hver Êndrer vÊrdien af én parameter, som jeg indsÊtter i BACKUP-kommandoen, gemmer den seneste post med historik fra msdb.dbo.backupset, sletter backupfilen, og sÄ til nÊste iteration. Da data om backup-udfÞrelse kommer fra backupset, gÄr prÊcisionen delvist tabt (der er ingen brÞkdele af sekunder), men det klarer vi.

FĂžrst skal vi tillade brugen af xp_cmdshell for at kunne slette backups (glem ikke at deaktivere det efterfĂžlgende, hvis du ikke 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

Og sÄ selve:

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 01
DECLARE @bc     int = 7,
        @min_bc int = 7,
        @max_bc int = 800;

01 MAXTRANSFERSIZE values 01
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

Hvis du har brug for forklaringer pĂ„, hvad der foregĂ„r her — skriv en kommentar eller en besked. Indtil videre vil jeg kun fortĂŠlle om parametrene, som jeg indsĂŠtter i BACKUP DATABASE.

For BLOCKSIZE, we have a 'closed' list of values, and I couldn't perform a backup with BLOCKSIZE < 4KB. MAXTRANSFERSIZE can be any number that is a multiple of 64KB—from 64KB to 4MB. By default, on my system, it is 1024KB; I chose 512 – 1024 – 2048 – 4096.

It was more complicated with BUFFERCOUNT—it can be any positive number, but the link states how it is calculated in BACKUP DATABASE and how large values can be dangerous.It also describes how to obtain information about the actual BUFFERCOUNT used for the backup—I had it set to 7. There was no reason to reduce it, and the upper limit was found experimentally—when BUFFERCOUNT = 896 and MAXTRANSFERSIZE = 4194304, the backup failed with an error (described in the link above):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE is terminating abnormally.

Msg 701, Level 17, State 123, Line 7 There is insufficient system memory in resource pool 'default' to run this query.

For comparison, I will first show the results of a backup without specifying parameters at all:

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

Well, a backup is just a backup:

Processed 1070072 pages for database 'bt', file 'bt' on file 1.

Processed 2 pages for database 'bt', file 'bt_log' on file 1.

BACKUP DATABASE successfully processed 1070074 pages in 53.171 seconds (157.227 MB/sec).

The script that tests the parameters ran for a couple of hours; all measurements are in Google Sheets.Here's a selection of results that have the three best execution times (I tried to make a nice graph, but in the post, we will have to settle for a table, while in the comments @mixsture tilfĂžjet very cool graphs.).

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

Attention, here is a very important note from @mixsture af en kommentar:

it can be confidently stated that the relationship between parameters and backup speed in these ranges is random; there is no pattern. However, deviating from the built-in parameters clearly had a positive impact on the result.

That is, by simply managing the standard BACKUP parameters, a time-winning reduction in backup duration was achieved—26 seconds, compared to 53 at the beginning. Not bad, right? But we need to see how recovery goes. What if it now takes four times longer to restore?

To start with, let's measure how long it takes to restore a backup with the default settings:

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

Well, you know it too; the paths are there, replace-or-not-replace, recovery-or-not-recovery. And this is how it executes for me:

Processed 1070072 pages for database 'bt', file 'bt' on file 1.

Processed 2 pages for database 'bt', file 'bt_log' on file 1.

RESTORE DATABASE successfully processed 1070074 pages in 40.752 seconds (205.141 MB/sec).

Nu vil jeg forsĂžge at gendanne backups taget med ĂŠndrede BLOCKSIZE, BUFFERCOUNT og MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE blev succesfuldt behandlet 1070074 sider pÄ 32.283 sekunder (258.958 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE blev succesfuldt behandlet 1070074 sider pÄ 32.682 sekunder (255.796 MB/s).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE blev succesfuldt behandlet 1070074 sider pÄ 32.091 sekunder (260.507 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE blev succesfuldt behandlet 1070074 sider pÄ 32.401 sekunder (258.015 MB/s).

RESTORE DATABASE-instruktionen ĂŠndres ikke under gendannelsen; disse parametre angives ikke, SQL Server bestemmer dem selv ud fra backupen. Og det er tydeligt, at selv under gendannelse kan der vĂŠre en gevinst — nĂŠsten 20% hurtigere (Ærligt talt, jeg brugte ikke meget tid pĂ„ gendannelse, jeg testede nogle af de mest «hurtige» backups og fandt ud af, at der ikke var nogen forringelse.).

For en sikkerheds skyld vil jeg prĂŠcisere — her beskrives ikke nogen optimale parametre for alle. De optimale parametre for dig kan kun opnĂ„s gennem testning. Jeg fik sĂ„danne resultater, du fĂ„r anderledes. Men du ser, at dine backups kan «tunes», og de kan faktisk blive hurtigere at danne og genskabe.

Jeg anbefaler desuden kraftigt at lĂŠse dokumentationen fuldt ud, da der kan vĂŠre detaljer, der er specifikke for dit system.

Da jeg er begyndt at skrive om backups, vil jeg straks nÊvne en anden «optimering», som oftere ses end «tuning» af parametre (den bruges, sÄ vidt jeg forstÄr, af mindst en del vÊrktÞjer til backup, muligvis sammen med de tidligere beskrevne parametre), men den er endnu ikke blevet beskrevet pÄ Habr.

Hvis vi ser pÄ den anden linje i dokumentationen, lige under BACKUP DATABASE, ser vi:

TO  [, ...n]

Hvad tror du der vil ske, hvis du angiver flere backup_device'er? Syntaksen tillader det jo. Men der vil ske en meget interessant ting — backupen vil bare «spredes» over flere enheder. Dvs. hver «enhed» bliver ubrugelig hver for sig; hvis du mister Ă©n, mister du hele backupen. Men hvordan vil sĂ„dan en spredning pĂ„virke hastigheden af backupen?

Lad os prÞve at lave en backup til to «enheder», som ligger tÊt pÄ hinanden i en mappe:

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

Hellige himmel, hvad er det der foregÄr?

Processed 1070072 pages for database 'bt', file 'bt' on file 1.

Behandling af 2 sider for database 'bt', fil 'btlog' pÄ fil 1.

BACKUP DATABASE blev gennemfÞrt med succes og behandlede 1070074 sider pÄ 40,092 sekunder (208,519 MB/sek).

Ble backupen lavet 25% hurtigere bare pÄ en jÊvn plads? Hvad hvis man tilsÊtter et par enheder mere?

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

BACKUP DATABASE blev gennemfÞrt med succes og behandlede 1070074 sider pÄ 34,234 sekunder (244,200 MB/sek).

I alt, en besparelse pĂ„ cirka 35% af tiden for at tage backup kun ved at skrive backupen til 4 filer pĂ„ Ă©n disk. Jeg har testet et stĂžrre antal — pĂ„ min bĂŠrbare er der ikke nogen gevinst, optimalt er 4 enheder. For dig — ved ikke, skal testes. Og forresten, hvis du har disse enheder — at de virkelig er forskellige diske, tillykke, gevinsten bĂžr vĂŠre endnu stĂžrre.

Lad os nu tale om, hvordan man gendanner denne lykke. For dette skal du ĂŠndre gendannelseskommandoen og opregne alle enheder:

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 blev gennemfÞrt med succes og behandlede 1070074 sider pÄ 38,027 sekunder (219,842 MB/sek).

Lidt hurtigere, men et sted i nĂŠrheden, ikke betydeligt. Generelt, tages backup hurtigere, gendannes ogsĂ„ pĂ„ samme mĂ„de — succes? For mig — ganske enkelt succes. Dette er vigtigt, derfor gentager jeg — hvis du mister blot en af disse filer — mister du hele backupen.

Hvis du ser i loggen over backupen, der udskrives ved hjÊlp af Trace Flag 3213 og 3605, kan man bemÊrke, at nÄr backupen foretages pÄ flere enheder, Þges i det mindste antallet af BUFFERCOUNT. MÄske kan man forsÞge at finde mere optimale parametre ogsÄ for BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, men jeg kunne ikke lige gÞre det, og at udfÞre sÄdan testning endnu en gang, men for et forskelligt antal filer, gad jeg ikke. Og diske er kostbare. Hvis du vil organisere sÄdan testning hos dig selv, er det ikke svÊrt at Êndre scriptet.

Til sidst lad os tale om prisen. Hvis backupen tages parallelt med brugernes arbejde — skal man tage meget ansvarligt hensyn til testningen, da hvis backupen tages hurtigere — presser diske hĂ„rdere, og belastningen pĂ„ processoren stiger (det skal jo stadig komprimeres undervejs), hvilket medfĂžrer lavere generel systemresponsivitet.

Jokes aside, I fully understand that I haven’t revealed any great truths. What is written above is merely a demonstration of how to choose the optimal parameters for taking backups.

Husk, at alt hvad du gĂžr — gĂžr du pĂ„ egen risiko. Tjek dine sikkerhedskopier, og glem ikke DBCC CHECKDB.

Kilde: habr.com

KĂžb pĂ„lidelig webhosting med DDoS-beskyttelse, VPS VDS-servere đŸ”„ KĂžb pĂ„lidelig webhosting med DDoS-beskyttelse, VPS VDS-servere | ProHoster