Wachten! Wachten! Toegegeven, dit is niet het zoveelste artikel over typen SQL Server-back-ups. Ik zal het niet eens hebben over de verschillen tussen herstelmodellen en hoe om te gaan met een overgroeide boomstam.
Misschien (heel misschien) kun je er na het lezen van dit bericht voor zorgen dat de back-up die met standaardmiddelen van je wordt verwijderd, morgenavond, nou ja, 1.5 keer sneller wordt verwijderd. En alleen vanwege het feit dat u iets meer BACKUP DATABASE-parameters gebruikt.
Als de inhoud van het bericht voor u duidelijk was, spijt mij dat. Ik heb alles gelezen wat Google te weten kwam over de zinsnede "habr sql server backup", en in geen enkel artikel vond ik enige vermelding van het feit dat de back-uptijd op de een of andere manier kan worden beïnvloed met behulp van parameters.
Ik zal onmiddellijk uw aandacht vestigen op de opmerking van Alexander Gladchenko ():
Wijzig nooit de parameters BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE tijdens de productie. Ze zijn alleen gemaakt voor het schrijven van dergelijke artikelen. In de praktijk ben je binnen no-time van geheugenproblemen af.
Het zou natuurlijk cool zijn om de slimste te zijn en exclusieve inhoud te posten, maar helaas is dit niet het geval. Er zijn zowel Engelstalige als Russischtalige artikelen/posts (ik weet altijd niet hoe ik ze correct moet noemen) gewijd aan dit onderwerp. Hier zijn enkele van degenen die ik tegenkwam: , , .
Dus om te beginnen zal ik een enigszins uitgeklede BACKUP-syntaxis toevoegen aan (Ik schreef hierboven trouwens over BACKUP DATABASE, maar dit alles is van toepassing op zowel de back-up van transactielogboeken als differentiële back-ups, maar misschien met een minder voor de hand liggend effect):
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 }
<...><…> - het betekent dat er iets was, maar ik heb het verwijderd omdat het nu niet relevant is voor het onderwerp.
Hoe maak jij normaal gesproken een back-up? Hoe ‘leren’ ze hoe ze back-ups kunnen maken van miljarden artikelen? Als ik een eenmalige back-up moet maken van een niet erg grote database, zal ik over het algemeen automatisch zoiets als dit schrijven:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееEn over het algemeen worden waarschijnlijk 75-90% van alle parameters die gewoonlijk worden vermeld in artikelen over back-ups hier vermeld. Welnu, er is ook INIT, SKIP. Heb je MSDN bezocht? Heb je gezien dat er opties zijn voor anderhalf scherm? Ik zag ook...
Je hebt je waarschijnlijk al gerealiseerd dat we verder zullen praten over de drie parameters die in het eerste codeblok zijn achtergebleven: BLOCKSIZE, BUFFERCOUNT en MAXTRANSFERSIZE. Hier zijn hun beschrijvingen van MSDN:
BLOKGROOTTE = { blocksize | @ blokgrootte_variabele } - geeft de fysieke blokgrootte in bytes aan. Ondersteunde formaten zijn 512, 1024, 2048, 4096, 8192, 16, 384 en 32 bytes (768 KB). De standaardwaarde is 65 voor bandstations en 536 voor andere apparaten. Normaal gesproken is deze parameter niet nodig omdat de BACKUP-instructie automatisch de juiste blokgrootte voor het apparaat selecteert. Het expliciet instellen van de blokgrootte heeft voorrang op de automatische selectie van de blokgrootte.
BUFFERCOUNT = { buffertelling | @ buffercount_variabele } - Definieert het totale aantal I/O-buffers dat wordt gebruikt voor de back-upbewerking. U kunt elke positieve gehele waarde opgeven, maar een groot aantal buffers kan een fout met onvoldoende geheugen veroorzaken als gevolg van overmatige virtuele adresruimte in het proces Sqlservr.exe.
De totale hoeveelheid ruimte die door buffers wordt gebruikt, wordt bepaald door de volgende formule:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAX.OVERDRACHTSGROOTTE = { maximale overdrachtsgrootte | @ maxtransfergrootte_variabele } specificeert de grootste gegevenspakketgrootte, in bytes, die moet worden uitgewisseld tussen SQL Server en de back-upsetmedia. Veelvouden van 65 bytes (536 KB) tot 64 bytes (4 MB) worden ondersteund.
Ik zweer het, ik heb dit eerder gelezen, maar het is nooit bij me opgekomen hoeveel impact ze op de productiviteit zouden kunnen hebben. Bovendien moet ik blijkbaar een soort 'coming-out' maken en toegeven dat ik zelfs nu niet helemaal begrijp wat ze precies doen. Ik moet waarschijnlijk meer lezen over gebufferde I/O en het werken met een harde schijf. Ooit zal ik dit doen, maar voorlopig kan ik gewoon een script schrijven dat controleert hoe deze waarden de snelheid waarmee de back-up wordt gemaakt beïnvloeden.
Ik heb een kleine database gemaakt, ongeveer 10 GB groot, deze op de SSD geplaatst en de directory voor back-ups op de HDD gezet.
Ik maak een tijdelijke tabel om de resultaten op te slaan (ik heb deze niet tijdelijk, dus ik kan dieper op de resultaten ingaan, maar je beslist zelf):
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
);Het principe van het script is eenvoudig: geneste lussen, die elk de waarde van één parameter veranderen, voeg deze parameters in de BACKUP-opdracht in, sla het laatste record met geschiedenis op van msdb.dbo.backupset, verwijder het back-upbestand en de volgende iteratie . Omdat de back-upuitvoeringsgegevens uit de back-upset worden gehaald, gaat de nauwkeurigheid enigszins verloren (er zijn geen fracties van seconden), maar we zullen dit overleven.
Eerst moet je xp_cmdshell inschakelen om back-ups te verwijderen (vergeet dan niet om het uit te schakelen als je het niet nodig hebt):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOEigenlijk:
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;
ENDAls je plotseling opheldering nodig hebt over wat hier gebeurt, schrijf dan in de reacties of stuur een PM. Voorlopig vertel ik je alleen over de parameters die ik in BACKUP DATABASE heb ingevoerd.
Voor BLOCKSIZE hebben we een “gesloten” lijst met waarden en ik heb geen back-up gemaakt met BLOCKSIZE < 4 KB. MAXTRANSFERSIZE elk nummer dat een veelvoud is van 64 KB - van 64 KB tot 4 MB. De standaard op mijn systeem is 1024 KB, ik nam 512 - 1024 - 2048 - 4096.
Het was moeilijker met BUFFERCOUNT - het kan elk positief getal zijn, maar de link zegt . Er staat ook hoe je informatie kunt krijgen over met welke BUFFERCOUNT de back-up feitelijk is gemaakt - voor mij is het 7. Het had geen zin om het te verkleinen, en de bovengrens werd experimenteel ontdekt - met BUFFERCOUNT = 896 en MAXTRANSFERSIZE = 4194304 viel de back-up met een fout (waarover geschreven in de bovenstaande link):
Bericht 3013, niveau 16, status 1, regel 7 BACKUP DATABASE wordt abnormaal beëindigd.
Bericht 701, niveau 17, status 123, regel 7 Er is onvoldoende systeemgeheugen in de resourcepool 'default' om deze query uit te voeren.
Ter vergelijking laat ik eerst de resultaten zien van het uitvoeren van een back-up zonder enige parameters op te geven:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Nou ja, back-up en back-up:
1070072 pagina's verwerkt voor database 'bt', bestand 'bt' in bestand 1.
2 pagina's verwerkt voor database 'bt', bestand 'bt_log' op bestand 1.
BACKUP DATABASE heeft 1070074 pagina's succesvol verwerkt in 53.171 seconden (157.227 MB/sec).
Het script zelf, dat de parameters testte, werkte binnen een paar uur, alle metingen waren binnen . En hier is een selectie van resultaten met de drie beste uitvoeringstijden (ik heb geprobeerd een mooie grafiek te maken, maar in de post moet ik het doen met een tabel, en in de reacties hij voegde eraan toe ).
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;
Let op, een zeer belangrijke opmerking van van :
We kunnen vol vertrouwen zeggen dat de relatie tussen de parameters en de back-upsnelheid binnen deze waardenbereiken willekeurig is, er is geen patroon. Maar het loslaten van de ingebouwde parameters had duidelijk een goed effect op het resultaat
Die. Alleen door het beheren van de standaard BACKUP-parameters was er een tweevoudige winst in de verwijderingstijd van de back-up: 2 seconden, tegenover 26 in het begin. Dat is niet slecht, toch? Maar we moeten zien wat er met de restauratie gebeurt. Wat als het nu vier keer langer duurt om te herstellen?
Laten we eerst meten hoe lang het duurt om een back-up met standaardinstellingen te herstellen:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Welnu, dat weet u zelf: de manieren zijn er: vervangen is niet vervangen, herstel is geen herstel. En ik doe het zo:
1070072 pagina's verwerkt voor database 'bt', bestand 'bt' in bestand 1.
2 pagina's verwerkt voor database 'bt', bestand 'bt_log' op bestand 1.
RESTORE DATABASE heeft 1070074 pagina's succesvol verwerkt in 40.752 seconden (205.141 MB/sec).
Nu zal ik proberen back-ups te herstellen die zijn gemaakt met gewijzigde BLOCKSIZE, BUFFERCOUNT en MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE heeft 1070074 pagina's succesvol verwerkt in 32.283 seconden (258.958 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE heeft 1070074 pagina's succesvol verwerkt in 32.682 seconden (255.796 MB/sec).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE heeft 1070074 pagina's succesvol verwerkt in 32.091 seconden (260.507 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE heeft 1070074 pagina's succesvol verwerkt in 32.401 seconden (258.015 MB/sec).
De instructie RESTORE DATABASE verandert niet tijdens het herstel; deze parameters zijn er niet in gespecificeerd; SQL Server bepaalt ze zelf vanuit de back-up. En het is duidelijk dat er zelfs met herstel winst kan worden geboekt - bijna 20% sneller (Om eerlijk te zijn, heb ik niet veel tijd aan herstel besteed, ik heb een aantal van de “snelste” back-ups doorgenomen en ervoor gezorgd dat er geen verslechtering was).
Voor het geval dat ik wil verduidelijken dat dit niet enkele parameters zijn die voor iedereen optimaal zijn. Alleen door te testen kunt u de optimale parameters voor uzelf verkrijgen. Ik heb deze resultaten, je krijgt verschillende. Maar u ziet dat u uw back-ups kunt ‘afstemmen’ en dat ze daadwerkelijk sneller kunnen worden gevormd en geïmplementeerd.
Ik raad u ook ten zeerste aan de documentatie in zijn geheel te lezen, omdat er nuances kunnen zijn die specifiek zijn voor uw systeem.
Sinds ik over back-ups begon te schrijven, wil ik meteen schrijven over nog een "optimalisatie", die vaker voorkomt dan het "afstemmen" van parameters (voor zover ik het begrijp, wordt deze door ten minste enkele back-uphulpprogramma's gebruikt, misschien samen met de parameters eerder beschreven), maar ook op Habré is het nog niet beschreven.
Als we naar de tweede regel in de documentatie kijken, direct onder BACKUP DATABASE, zien we daar:
TO <backup_device> [ ,...n ]Wat denkt u dat er zal gebeuren als u meerdere backup_devices opgeeft? De syntaxis laat het toe. En er zal iets heel interessants gebeuren: de back-up wordt eenvoudigweg "verdeeld" over verschillende apparaten. Die. elk “apparaat” afzonderlijk zal nutteloos zijn, er één kwijtraken, de hele back-up kwijtraken. Maar welke invloed heeft dit soort vlekken op de back-upsnelheid?
Laten we proberen een back-up te maken op twee “apparaten” die zich naast elkaar in dezelfde map bevinden:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Vaders van de wereld, waarom wordt dit gedaan?
1070072 pagina's verwerkt voor database 'bt', bestand 'bt' in bestand 1.
2 pagina's verwerkt voor database 'bt', bestand 'bt'log' in bestand 1.
BACKUP DATABASE heeft 1070074 pagina's succesvol verwerkt in 40.092 seconden (208.519 MB/sec).
Is de back-up zomaar uit het niets 25% sneller geworden? Wat als we nog een paar apparaten toevoegen?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE heeft 1070074 pagina's succesvol verwerkt in 34.234 seconden (244.200 MB/sec).
In totaal bedraagt de winst ongeveer 35% van de tijd die u besteedt aan het maken van een back-up, alleen al omdat de back-up naar vier bestanden tegelijk op één schijf wordt geschreven. Ik heb een groter aantal gecontroleerd - er is geen winst op mijn laptop, optimaal - 4 apparaten. Voor jou - ik weet het niet, je moet het controleren. Trouwens, als je deze apparaten hebt - dit zijn echt verschillende schijven, gefeliciteerd, de winst zou nog groter moeten zijn.
Laten we het nu hebben over hoe we dit geluk kunnen herstellen. Om dit te doen, moet u de herstelopdracht wijzigen en alle apparaten vermelden:
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 heeft 1070074 pagina's succesvol verwerkt in 38.027 seconden (219.842 MB/sec).
Iets sneller, maar ergens in de buurt, niet significant. Over het algemeen wordt de back-up sneller verwijderd en op dezelfde manier hersteld - succes? Wat mij betreft is het een behoorlijk succes. Dit важно, dus ik herhaal - als jij als u ten minste één van deze bestanden verliest, verliest u de volledige back-up.
Als u in het logboek naar de back-upinformatie kijkt die wordt weergegeven met behulp van Trace Flags 3213 en 3605, zult u merken dat bij het maken van een back-up naar meerdere apparaten, in ieder geval het aantal BUFFERCOUNT toeneemt. Waarschijnlijk kun je proberen meer optimale parameters te selecteren voor BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, maar dat lukte niet meteen, en ik was te lui om dergelijke tests opnieuw uit te voeren, maar voor een ander aantal bestanden. En het is jammer van de wielen. Als je dergelijke tests thuis wilt organiseren, is het niet moeilijk om het script opnieuw te maken.
Laten we het tenslotte over de prijs hebben. Als de back-up parallel met het werk van de gebruikers wordt verwijderd, moet u een zeer verantwoorde testaanpak hanteren, want als de back-up sneller wordt verwijderd, worden de schijven zwaarder belast en neemt de belasting van de processor toe (u moet nog steeds comprimeren dit meteen gebeurt), en dienovereenkomstig neemt de algehele responsiviteit van het systeem af.
Grapje, maar ik begrijp heel goed dat ik geen onthullingen heb gedaan. Wat hierboven is geschreven, is slechts een demonstratie van hoe u de optimale parameters voor het maken van back-ups kunt selecteren.
Vergeet niet dat alles wat u doet, op eigen risico en risico gebeurt. Controleer uw back-ups en vergeet DBCC CHECKDB niet.
Bron: www.habr.com