Vänta! Vänta! Det är sant att det här inte är ytterligare en artikel om typer av SQL Server-säkerhetskopior. Jag kommer inte ens att prata om skillnaderna mellan återställningsmodeller och hur man hanterar en övervuxen stock.
Kanske (bara kanske), efter att ha läst det här inlägget, kommer du att kunna försäkra dig om att säkerhetskopian som tas bort från dig med vanliga medel kommer att tas bort i morgon kväll, ja, 1.5 gånger snabbare. Och bara på grund av det faktum att du använder lite fler BACKUP DATABASE parametrar.
Om innehållet i inlägget var uppenbart för dig är jag ledsen. Jag läste allt som Google kom fram till för frasen "habr sql server backup", och i inte en enda artikel hittade jag något omnämnande av det faktum att backuptiden på något sätt kan påverkas med hjälp av parametrar.
Jag kommer omedelbart att uppmärksamma din kommentar från Alexander Gladchenko ():
Ändra aldrig parametrarna BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE i produktionen. De är endast gjorda för att skriva sådana artiklar. I praktiken blir du av med minnesproblem på nolltid.
Det skulle förstås vara coolt att vara smartast och lägga upp exklusivt innehåll, men tyvärr är det inte så. Det finns både engelskspråkiga och ryskspråkiga artiklar/inlägg (jag är alltid förvirrad över vad jag ska kalla dem korrekt) som ägnas åt detta ämne. Här är några av de jag stötte på: , , .
Så till att börja med kommer jag att bifoga en något avskalad BACKUP-syntax från (Jag skrev för övrigt ovan om BACKUP DATABAS, men allt detta gäller både transaktionslogg backup och differentiell backup, men kanske med en mindre uppenbar 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 att det fanns något där, men jag tog bort det eftersom det nu inte är relevant för ämnet.
Hur brukar du ta backup? Hur "lär" de hur man tar säkerhetskopior i miljarder artiklar? I allmänhet, om jag behöver göra en engångssäkerhetskopia av någon inte särskilt stor databas, kommer jag automatiskt att skriva något så här:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееOch generellt sett listas förmodligen 75-90% av alla parametrar som vanligtvis nämns i artiklar om säkerhetskopiering här. Tja, det finns också INIT, SKIP. Har du besökt MSDN? Har du sett att det finns alternativ för en och en halv skärm? Jag såg också...
Du har förmodligen redan insett att vi kommer att prata om de tre parametrarna som fanns kvar i det första kodblocket - BLOCKSIZE, BUFFERCOUNT och MAXTRANSFERSIZE. Här är deras beskrivningar från MSDN:
BLOCK STORLEK = { block storlek | @ blockstorleksvariabel } - indikerar den fysiska blockstorleken i byte. Storlekar som stöds är 512, 1024, 2048, 4096, 8192, 16 384, 32 768 och 65 536 byte (64 KB). Standardvärdet är 65 för bandenheter och 536 för andra enheter. Vanligtvis är denna parameter inte nödvändig eftersom BACKUP-satsen automatiskt väljer lämplig blockstorlek för enheten. Att ställa in blockstorleken åsidosätter uttryckligen det automatiska valet av blockstorlek.
BUFFERTANTAL = { bufferträkning | @ buffercount_variable } - Definierar det totala antalet I/O-buffertar som kommer att användas för säkerhetskopieringen. Du kan ange vilket positivt heltalsvärde som helst, men ett stort antal buffertar kan orsaka ett fel i minnet på grund av för stort virtuellt adressutrymme i Sqlservr.exe-processen.
Den totala mängden utrymme som används av buffertar bestäms av följande formel:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXÖVERFÖRING = { maxtransfersize | @ maxtransfersize_variable } anger den största datapaketstorleken, i byte, som ska utbytas mellan SQL Server och backup-uppsättningsmediet. Multipler på 65 536 byte (64 KB) upp till 4 194 304 byte (4 MB) stöds.
Jag svär - jag har läst det här förut, men det har aldrig fallit mig in hur stor inverkan de kan ha på produktiviteten. Dessutom måste jag tydligen göra ett slags "coming out" och erkänna att jag inte ens nu helt förstår vad exakt de gör. Jag behöver nog läsa mer om buffrad I/O och att arbeta med en hårddisk. Någon gång kommer jag att göra detta, men för tillfället kan jag bara skriva ett skript som kontrollerar hur dessa värden påverkar hastigheten med vilken säkerhetskopieringen tas.
Jag gjorde en liten databas, cirka 10 GB i storlek, satte den på SSD:n och lade katalogen för säkerhetskopiering på hårddisken.
Jag skapar en tillfällig tabell för att lagra resultaten (jag har inte den tillfälligt, så jag kan gräva i resultaten mer detaljerat, men du bestämmer själv):
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
);Principen för skriptet är enkel - kapslade loopar, som var och en ändrar värdet på en parameter, infoga dessa parametrar i kommandot BACKUP, spara den sista posten med historik från msdb.dbo.backupset, radera säkerhetskopian och nästa iteration . Eftersom exekveringsdata för säkerhetskopiering hämtas från säkerhetskopieringssetet går noggrannheten något förlorad (det finns inga bråkdelar av sekunder), men vi kommer att överleva detta.
Först måste du aktivera xp_cmdshell för att ta bort säkerhetskopior (glöm sedan inte att inaktivera det 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;
GOJa faktiskt:
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;
ENDOm du plötsligt behöver ett förtydligande om vad som händer här, skriv i kommentarerna eller PM. För tillfället kommer jag bara att berätta om parametrarna som jag lägger i BACKUP DATABAS.
För BLOCKSIZE har vi en "stängd" lista med värden, och jag gjorde ingen säkerhetskopia med BLOCKSIZE < 4KB. MAXÖVERFÖRA alla tal som är en multipel av 64KB - från 64KB till 4MB. Standard på mitt system är 1024KB, jag tog 512 - 1024 - 2048 - 4096.
Det var svårare med BUFFERCOUNT - det kan vara vilken positiv siffra som helst, men länken säger . Det står också hur man får information om vilken BUFFERCOUNT backupen faktiskt är gjord med - för mig är det 7. Det var ingen idé att minska den, och den övre gränsen upptäcktes experimentellt - med BUFFERCOUNT = 896 och MAXTRANSFERSIZE = 4194304 sjönk backupen med ett fel (som skrevs om i länken ovan):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE avslutas onormalt.
Medd. 701, nivå 17, tillstånd 123, rad 7 Det finns inte tillräckligt med systemminne i resurspoolens "standard" för att köra denna fråga.
Som jämförelse visar jag först resultatet av att köra en säkerhetskopia utan att ange några parametrar alls:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Tja, backup och backup:
Bearbetade 1070072 sidor för databasen 'bt', filen 'bt' på fil 1.
Bearbetade 2 sidor för databasen 'bt', filen 'bt_log' på fil 1.
BACKUP DATABAS bearbetade framgångsrikt 1070074 sidor på 53.171 sekunder (157.227 MB/sek).
Själva skriptet, testning av parametrarna, fungerade på ett par timmar, alla mätningar var inne . Och här är ett urval av resultat med de tre bästa körtiderna (jag försökte göra en snygg graf, men i inlägget får jag nöja mig med en tabell och i kommentarerna han tillade ).
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;
Uppmärksamhet, en mycket viktig anteckning från av :
Vi kan med säkerhet säga att förhållandet mellan parametrarna och säkerhetskopieringshastigheten inom dessa värdeintervall är slumpmässigt, det finns inget mönster. Men att gå bort från de inbyggda parametrarna hade så klart en bra effekt på resultatet
De där. Endast genom att hantera standardparametrarna för BACKUP var en 2-faldig ökning av tiden för backup-borttagning: 26 sekunder, mot 53 i början. Det är inte dåligt, eller hur? Men vi måste se vad som händer med restaureringen. Tänk om det nu tar 4 gånger längre tid att återhämta sig?
Låt oss först mäta hur lång tid det tar att återställa en säkerhetskopia med standardinställningar:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Tja, du själv vet att, vägarna finns, ersätt är inte ersätt, återhämtning är inte återhämtning. Och jag gör så här:
Bearbetade 1070072 sidor för databasen 'bt', filen 'bt' på fil 1.
Bearbetade 2 sidor för databasen 'bt', filen 'bt_log' på fil 1.
ÅTERSTÄLL DATABAS har bearbetat 1070074 sidor på 40.752 sekunder (205.141 MB/sek).
Nu ska jag försöka återställa säkerhetskopior tagna med ändrad BLOCKSIZE, BUFFERCOUNT och MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304ÅTERSTÄLL DATABAS har bearbetat 1070074 sidor på 32.283 sekunder (258.958 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304ÅTERSTÄLL DATABAS har bearbetat 1070074 sidor på 32.682 sekunder (255.796 MB/sek).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152ÅTERSTÄLL DATABAS har bearbetat 1070074 sidor på 32.091 sekunder (260.507 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304ÅTERSTÄLL DATABAS har bearbetat 1070074 sidor på 32.401 sekunder (258.015 MB/sek).
RESTORE DATABASE-satsen ändras inte under återställningen; dessa parametrar anges inte i den; SQL Server själv bestämmer dem från säkerhetskopian. Och det är klart att även med återhämtning kan det bli en vinst - nästan 20% snabbare (För att vara ärlig så spenderade jag inte mycket tid på återställning, jag gick igenom flera av de "snabbaste" säkerhetskopiorna och såg till att det inte blev någon försämring).
För säkerhets skull, låt mig förtydliga att det här inte är några parametrar som är optimala för alla. Du kan bara få de optimala parametrarna för dig själv genom att testa. Jag fick dessa resultat, du kommer att få olika. Men du ser att du kan "justera" dina säkerhetskopior och de kan faktiskt bildas och distribueras snabbare.
Jag rekommenderar också starkt att du läser dokumentationen i sin helhet, eftersom det kan finnas nyanser som är specifika för ditt system.
Sedan jag började skriva om säkerhetskopior vill jag omedelbart skriva om ytterligare en "optimering", vilket är vanligare än "tuning"-parametrar (såvitt jag förstår används det av åtminstone några säkerhetskopieringsverktyg, kanske tillsammans med parametrarna beskrivits tidigare), men den har ännu inte beskrivits på Habré heller.
Om vi tittar på den andra raden i dokumentationen, precis under BACKUP DATABAS, ser vi:
TO <backup_device> [ ,...n ]Vad tror du kommer att hända om du anger flera backup_devices? Syntaxen tillåter det. Och en mycket intressant sak kommer att hända - säkerhetskopian kommer helt enkelt att "spridas" över flera enheter. De där. varje "enhet" individuellt kommer att vara värdelös, förlorade en, förlorade hela säkerhetskopian. Men hur kommer sådan utsmettning att påverka säkerhetskopieringshastigheten?
Låt oss försöka göra en säkerhetskopia på två "enheter" som finns sida vid sida i samma mapp:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Världens fäder, varför görs detta?
Bearbetade 1070072 sidor för databasen 'bt', filen 'bt' på fil 1.
Bearbetade 2 sidor för databasen 'bt', filen 'bt'logga på fil 1.
BACKUP DATABAS bearbetade framgångsrikt 1070074 sidor på 40.092 sekunder (208.519 MB/sek).
Blev säkerhetskopieringen 25 % snabbare helt enkelt? Vad händer om vi lägger till ytterligare ett par enheter?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABAS bearbetade framgångsrikt 1070074 sidor på 34.234 sekunder (244.200 MB/sek).
Totalt är vinsten cirka 35% av tiden för att ta en säkerhetskopia endast på grund av att säkerhetskopian skrivs till 4 filer på en disk samtidigt. Jag kontrollerade ett större antal - det finns ingen vinst på min bärbara dator, optimalt - 4 enheter. För dig - jag vet inte, du måste kolla. Tja, förresten, om du har dessa enheter - det är verkligen olika diskar, grattis, vinsten borde vara ännu mer betydande.
Låt oss nu prata om hur man återställer denna lycka. För att göra detta måste du ä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;ÅTERSTÄLL DATABAS har bearbetat 1070074 sidor på 38.027 sekunder (219.842 MB/sek).
Lite snabbare, men någonstans nära, inte signifikant. I allmänhet tas säkerhetskopian bort snabbare och återställs på samma sätt - framgång? För mig är det en stor succé. Detta är viktigt, så jag upprepar - om du om du förlorar minst en av dessa filer, förlorar du hela säkerhetskopian.
Om du tittar i loggen på säkerhetskopieringsinformationen som visas med Trace Flags 3213 och 3605, kommer du att märka att när du säkerhetskopierar till flera enheter, ökar åtminstone antalet BUFFERCOUNT. Förmodligen kan du försöka välja mer optimala parametrar för BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, men jag lyckades inte direkt, och jag var för lat för att utföra sådana tester igen, men för ett annat antal filer. Och det är synd om hjulen. Om du vill organisera sådana tester hemma är det inte svårt att göra om manuset.
Till sist, låt oss prata om priset. Om säkerhetskopian tas bort parallellt med användarnas arbete måste du ta ett mycket ansvarsfullt tillvägagångssätt för att testa, för om säkerhetskopian tas bort snabbare blir diskarna mer ansträngda, belastningen på processorn ökar (du måste fortfarande komprimera det i farten), och följaktligen minskar systemets övergripande lyhördhet.
Skojar bara, men jag förstår mycket väl att jag inte gjorde några avslöjanden. Det som står ovan är helt enkelt en demonstration av hur du kan välja de optimala parametrarna för att ta säkerhetskopior.
Kom ihåg att allt du gör görs på egen risk och risk. Kontrollera dina säkerhetskopior och glöm inte DBCC CHECKDB.
Källa: will.com