Vente! Vente! Sandt nok er dette ikke endnu en artikel om typer af SQL Server-sikkerhedskopier. Jeg vil ikke engang tale om forskellene mellem genopretningsmodeller og hvordan man håndterer en forvokset log.
Måske (bare måske), efter at have læst dette indlæg, vil du være i stand til at sikre dig, at den sikkerhedskopi, der er fjernet fra dig ved hjælp af standardmidler, vil blive fjernet i morgen aften, ja, 1.5 gange hurtigere. Og kun på grund af det faktum, at du bruger lidt flere BACKUP DATABASE-parametre.
Hvis indholdet af indlægget var indlysende for dig, er jeg ked af det. Jeg læste alt, hvad Google kom til for sætningen "habr sql server backup", og i ikke en eneste artikel fandt jeg nogen omtale af, at backup-tiden på en eller anden måde kan påvirkes ved hjælp af parametre.
Jeg vil straks henlede din opmærksomhed på kommentaren fra Alexander Gladchenko ():
Ændre aldrig BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE parametrene i produktionen. De er kun lavet til at skrive sådanne artikler. I praksis slipper du for hukommelsesproblemer på ingen tid.
Det ville selvfølgelig være fedt at være den smarteste og poste eksklusivt indhold, men det er desværre ikke tilfældet. Der er både engelsksprogede og russisksprogede artikler/indlæg (jeg er altid i tvivl om, hvad jeg skal kalde dem korrekt) om dette emne. Her er nogle af dem jeg stødte på: , , .
Så til at begynde med vil jeg vedhæfte en lidt nedstribet BACKUP-syntaks fra (Jeg skrev i øvrigt ovenfor om BACKUP DATABASE, men alt dette gælder både for transaktionslog backup og differentiel backup, men måske med en mindre indlysende effekt):
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 }
<...><…> - det betyder, at der var noget der, men jeg fjernede det, fordi det nu ikke er relevant for emnet.
Hvordan tager du normalt en backup? Hvordan "lærer" de, hvordan man tager sikkerhedskopier i milliarder af artikler? Generelt, hvis jeg skal lave en engangssikkerhedskopiering af en ikke særlig stor database, vil jeg automatisk skrive noget som dette:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееOg generelt er nok 75-90% af alle parametre, der normalt nævnes i artikler om sikkerhedskopiering, listet her. Nå, der er også INIT, SKIP. Har du besøgt MSDN? Har du set, at der er muligheder for halvanden skærm? Jeg så også...
Du har sikkert allerede indset, at vi yderligere vil tale om de tre parametre, der forblev i den første kodeblok - BLOCKSIZE, BUFFERCOUNT og MAXTRANSFERSIZE. Her er deres beskrivelser fra MSDN:
BLOKSTØRRELSE = { blokstørrelse | @ blokstørrelsesvariabel } - angiver den fysiske blokstørrelse i bytes. Understøttede størrelser er 512, 1024, 2048, 4096, 8192, 16, 384 og 32 bytes (768 KB). Standardværdien er 65 for båndenheder og 536 for andre enheder. Denne parameter er typisk ikke nødvendig, fordi BACKUP-sætningen automatisk vælger den passende blokstørrelse for enheden. Indstilling af blokstørrelsen tilsidesætter eksplicit det automatiske blokstørrelsesvalg.
BUFFERANTAL = { buffertælling | @ buffercount_variable } - Definerer det samlede antal I/O-buffere, der vil blive brugt til sikkerhedskopieringen. Du kan angive en hvilken som helst positiv heltalværdi, men et stort antal buffere kan forårsage en hukommelsesfejl på grund af overdreven virtuel adresseplads i Sqlservr.exe-processen.
Den samlede mængde plads, der bruges af buffere, bestemmes af følgende formel:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAKSOVERFØRSEL = { maxoverførselsstørrelse | @ maxtransfersize_variable } angiver den største datapakkestørrelse, i bytes, der skal udveksles mellem SQL Server og backupsætmediet. Multipler på 65 bytes (536 KB) op til 64 bytes (4 MB) understøttes.
Jeg sværger - jeg har læst dette før, men det faldt mig aldrig ind, hvor stor en indflydelse de kunne have på produktiviteten. Desuden skal jeg tilsyneladende lave en slags "coming out" og indrømme, at jeg selv nu ikke helt forstår, hvad de præcist laver. Jeg skal nok læse mere om buffered I/O og arbejde med en harddisk. En dag vil jeg gøre dette, men indtil videre kan jeg bare skrive et script, der vil kontrollere, hvordan disse værdier påvirker hastigheden, hvormed sikkerhedskopieringen tages.
Jeg lavede en lille database, omkring 10 GB i størrelse, lagde den på SSD'en og lagde mappen til backups på HDD'en.
Jeg opretter en midlertidig tabel til at gemme resultaterne (jeg har den ikke midlertidig, så jeg kan grave i resultaterne mere detaljeret, men du bestemmer selv):
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
);Princippet i scriptet er enkelt - indlejrede loops, som hver ændrer værdien af en parameter, indsæt disse parametre i BACKUP-kommandoen, gem den sidste post med historik fra msdb.dbo.backupset, slet backup-filen og den næste iteration . Da sikkerhedskopieringsudførelsesdataene er taget fra backupsættet, går nøjagtigheden noget tabt (der er ingen brøkdele af sekunder), men vi overlever dette.
Først skal du aktivere xp_cmdshell for at slette sikkerhedskopier (så glem ikke at deaktivere det, hvis du ikke har brug for det):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GONå, faktisk:
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;
ENDHvis du pludselig har brug for en afklaring på, hvad der sker her, så skriv i kommentarfeltet eller PM. Indtil videre vil jeg kun fortælle dig om de parametre, jeg har lagt i BACKUP DATABASE.
For BLOCKSIZE har vi en "lukket" liste over værdier, og jeg lavede ikke en backup med BLOCKSIZE < 4KB. MAXTRANSFERSIZE ethvert tal, der er et multiplum af 64KB - fra 64KB til 4MB. Standarden på mit system er 1024KB, jeg tog 512 - 1024 - 2048 - 4096.
Det var sværere med BUFFERCOUNT - det kan være et hvilket som helst positivt tal, men linket siger . Der står også, hvordan man får information om, hvilken BUFFERCOUNT sikkerhedskopien rent faktisk er lavet med - for mig er det 7. Det nyttede ikke noget at reducere den, og den øvre grænse blev opdaget eksperimentelt - med BUFFERCOUNT = 896 og MAXTRANSFERSIZE = 4194304 faldt sikkerhedskopien med en fejl (som er skrevet i linket ovenfor):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE afsluttes unormalt.
Meddelelse 701, niveau 17, tilstand 123, linje 7. Der er utilstrækkelig systemhukommelse i ressourcepuljen 'standard' til at køre denne forespørgsel.
Til sammenligning vil jeg først vise resultaterne af at køre en sikkerhedskopi uden at specificere nogen parametre overhovedet:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Nå, backup og backup:
Behandlede 1070072 sider for databasen 'bt', filen 'bt' på fil 1.
Behandlede 2 sider for databasen 'bt', filen 'bt_log' på fil 1.
BACKUP DATABASE behandlede 1070074 sider på 53.171 sekunder (157.227 MB/sek.).
Selve scriptet, der testede parametrene, virkede på et par timer, alle målinger var inde . Og her er et udvalg af resultater med de tre bedste eksekveringstider (jeg prøvede at lave en flot graf, men i indlægget må jeg nøjes med en tabel og i kommentarerne Han tilføjede ).
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;
Opmærksomhed, en meget vigtig note fra af :
Vi kan trygt sige, at forholdet mellem parametrene og backuphastigheden inden for disse værdiområder er tilfældigt, der er intet mønster. Men at gå væk fra de indbyggede parametre havde naturligvis en god effekt på resultatet
De der. Kun ved at styre standard BACKUP-parametrene var der en 2-dobbelt forstærkning i fjernelse af backup: 26 sekunder, mod 53 i begyndelsen. Det er ikke dårligt, vel? Men vi må se, hvad der sker med restaureringen. Hvad hvis det nu tager 4 gange længere tid at komme sig?
Lad os først måle, hvor lang tid det tager at gendanne en sikkerhedskopi med standardindstillinger:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Nå, du ved selv, at måderne er der, erstatte er ikke erstatte, recovery er ikke recovery. Og jeg gør det sådan her:
Behandlede 1070072 sider for databasen 'bt', filen 'bt' på fil 1.
Behandlede 2 sider for databasen 'bt', filen 'bt_log' på fil 1.
RESTORE DATABASE behandlede 1070074 sider på 40.752 sekunder (205.141 MB/sek.).
Nu vil jeg prøve at gendanne sikkerhedskopier taget med ændret BLOCKSIZE, BUFFERCOUNT og MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE behandlede 1070074 sider på 32.283 sekunder (258.958 MB/sek.).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE behandlede 1070074 sider på 32.682 sekunder (255.796 MB/sek.).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE behandlede 1070074 sider på 32.091 sekunder (260.507 MB/sek.).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE behandlede 1070074 sider på 32.401 sekunder (258.015 MB/sek.).
RESTORE DATABASE-sætningen ændres ikke under gendannelse; disse parametre er ikke specificeret i den; SQL Server bestemmer selv dem ud fra sikkerhedskopien. Og det er klart, at selv med restitution kan der være en gevinst - næsten 20% hurtigere (For at være ærlig brugte jeg ikke meget tid på gendannelse, jeg kørte gennem flere af de "hurtigste" sikkerhedskopier og sørgede for, at der ikke var nogen forringelse).
For en sikkerheds skyld, så lad mig præcisere, at det ikke er nogle parametre, der er optimale for alle. Du kan kun få de optimale parametre for dig selv ved at teste. Jeg fik disse resultater, du vil få forskellige. Men du kan se, at du kan "tune" dine sikkerhedskopier, og de kan faktisk dannes og implementeres hurtigere.
Jeg anbefaler også stærkt, at du læser dokumentationen i sin helhed, da der kan være nuancer, der er specifikke for dit system.
Siden jeg begyndte at skrive om sikkerhedskopier, vil jeg straks skrive om endnu en “optimering”, som er mere almindelig end “tuning”-parametre (så vidt jeg forstår, bruges den af i hvert fald nogle backup-værktøjer, måske sammen med parametrene beskrevet tidligere), men den er endnu ikke beskrevet på Habré.
Hvis vi ser på den anden linje i dokumentationen, lige under BACKUP DATABASE, ser vi der:
TO <backup_device> [ ,...n ]Hvad tror du vil ske, hvis du angiver flere backup_devices? Syntaksen tillader det. Og en meget interessant ting vil ske - sikkerhedskopien vil simpelthen blive "spredt" på flere enheder. De der. hver "enhed" individuelt vil være ubrugelig, mistet en, mistet hele sikkerhedskopien. Men hvordan vil en sådan udtværing påvirke backuphastigheden?
Lad os prøve at lave en sikkerhedskopi på to "enheder", der er placeret side om side i samme mappe:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Verdens fædre, hvorfor bliver dette gjort?
Behandlede 1070072 sider for databasen 'bt', filen 'bt' på fil 1.
Behandlede 2 sider for databasen 'bt', filen 'bt'log' på fil 1.
BACKUP DATABASE behandlede 1070074 sider på 40.092 sekunder (208.519 MB/sek.).
Blev sikkerhedskopieringen 25 % hurtigere lige ud af det blå? Hvad hvis vi tilføjer et par flere enheder?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE behandlede 1070074 sider på 34.234 sekunder (244.200 MB/sek.).
I alt er gevinsten omkring 35% af tiden for at tage en sikkerhedskopi, kun på grund af det faktum, at sikkerhedskopien skrives til 4 filer på én disk ad gangen. Jeg tjekkede et større antal - der er ingen gevinst på min bærbare computer, optimalt - 4 enheder. For dig - jeg ved det ikke, du skal tjekke. Nå, forresten, hvis du har disse enheder - det er virkelig forskellige diske, tillykke, gevinsten burde være endnu mere betydelig.
Lad os nu tale om, hvordan man genopretter denne lykke. For at gøre dette skal du ændre gendannelseskommandoen og liste 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 behandlede 1070074 sider på 38.027 sekunder (219.842 MB/sek.).
Lidt hurtigere, men et sted tæt på, ikke signifikant. Generelt fjernes sikkerhedskopien hurtigere og gendannes på samme måde - succes? For mit vedkommende er det en ret stor succes. Det her er vigtigt, så jeg gentager - hvis du hvis du mister mindst én af disse filer, mister du hele sikkerhedskopien.
Hvis du ser i loggen på sikkerhedskopieringsoplysningerne, der vises ved hjælp af Trace Flags 3213 og 3605, vil du bemærke, at når du sikkerhedskopierer til flere enheder, stiger i det mindste antallet af BUFFERCOUNT. Sandsynligvis kan du prøve at vælge mere optimale parametre for BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, men det lykkedes ikke med det samme, og jeg var for doven til at udføre en sådan test igen, men for et andet antal filer. Og det er ærgerligt med hjulene. Hvis du vil organisere en sådan test derhjemme, er det ikke svært at lave scriptet om.
Lad os endelig tale om prisen. Hvis sikkerhedskopien fjernes parallelt med brugernes arbejde, skal du tage en meget ansvarlig tilgang til test, for hvis sikkerhedskopien fjernes hurtigere, belastes diskene mere, belastningen på processoren øges (du skal stadig komprimere det på farten), og følgelig falder systemets samlede reaktionsevne.
Bare for sjov, men jeg forstår udmærket, at jeg ikke kom med nogen afsløringer. Det, der er skrevet ovenfor, er blot en demonstration af, hvordan du kan vælge de optimale parametre for at tage backup.
Husk, at alt, hvad du gør, sker på egen risiko og risiko. Tjek dine sikkerhedskopier og glem ikke DBCC CHECKDB.
Kilde: www.habr.com