Wacht! Wacht! Eerlijk gezegd is dit geen artikel over de typologie van SQL Server-back-ups. Ik zal zelfs niet ingaan op de verschillen tussen herstelmodellen of hoe om te gaan met een uit de hand gelopen 'log'.
Misschien (maar alleen misschien), na het lezen van deze post, kunt u ervoor zorgen dat de back-up die u met standaardmiddelen maakt, morgenavond ongeveer 1,5 keer sneller draait. En dit alleen maar omdat u net iets meer parameters voor BACKUP DATABASE gebruikt.
Als de inhoud van de post voor u vanzelfsprekend was — mijn excuses. Ik heb alles gelezen wat ik via Google kon vinden met de zoekterm "habr sql server backup", en in geen enkel artikel vond ik de vermelding dat je op een of andere manier invloed kunt uitoefenen op de tijd van de back-up met parameters.
Ik wijs uw aandacht graag op de opmerking van Alexander Gladchenko ():
Verander nooit de parameters BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE in productie. Ze zijn alleen gemaakt voor het schrijven van artikelen zoals deze. In de praktijk zult u geheugenproblemen ondervinden.
Het zou natuurlijk geweldig zijn om de slimste te lijken en exclusieve inhoud te presenteren, maar helaas is dat niet het geval. Er zijn zowel Engelstalige als Ruslandstalige artikelen/posts (ik blijf maar verwarren hoe ik ze correct moet noemen) over dit onderwerp. Dit is een deel van wat ik tegenkwam: , , .
Dus, om te beginnen, voeg ik een beknopte syntaxis van BACKUP toe vanuit (overigens, hierboven heb ik het over BACKUP DATABASE, maar dit is ook van toepassing op het back-uppen van transactielogs en op differentiële back-ups, maar mogelijk met minder duidelijke effecten):
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 }betekent dat er iets was, maar ik heb het verwijderd omdat het momenteel niet relevant is voor het onderwerp.
Hoe maakt u meestal een back-up? Hoe 'leren' miljarden artikelen om een back-up te maken? In het algemeen, als ik eenmalig een back-up van een niet al te grote database moet maken, zou ik automatisch iets als het volgende schrijven:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--goed, CHECKSUM heb ik alleen genoemd om slimmer te lijkenEn dat is dus waarschijnlijk 75-90% van alle parameters die meestal worden genoemd in artikelen over back-ups. Er zijn ook INIT, SKIP, enz. Maar bent u al naar MSDN geweest? Heeft u gezien dat daar opties zijn voor anderhalve pagina? Ik heb dat ook gezien…
U begrijpt waarschijnlijk al dat we verder gaan met drie parameters die over zijn gebleven in het eerste blok code - BLOCKSIZE, BUFFERCOUNT en MAXTRANSFERSIZE. Dit zijn hun beschrijvingen uit MSDN:
BLOCKSIZE = { blocksize | @ blocksize_variable } - geeft de grootte van het fysieke blok in bytes aan. Ondersteunde groottes zijn 512, 1024, 2048, 4096, 8192, 16 384, 32 768 en 65 536 bytes (64 KB). De standaardwaarde is 65 536 voor tape-apparaten en 512 voor andere apparaten. Gewoonlijk is het niet nodig om deze parameter in te stellen, aangezien de BACKUP-instructie automatisch de blokgrootte kiest die overeenkomt met het apparaat. Een expliciete instelling van de blokgrootte overschrijft de automatische keuze.
BUFFERCOUNT = { buffercount | @ buffercount_variable } - bepaalt het totale aantal invoeruitvoerbuffers dat zal worden gebruikt voor de back-upoperatie. U kunt elke positieve gehele waarde opgeven, maar een groot aantal buffers kan leiden tot geheugenfouten door te veel virtuele adressen in het proces Sqlservr.exe.
De totale hoeveelheid ruimte die door buffers wordt gebruikt, wordt bepaald met de volgende formule:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } specificeert de maximale hoeveelheid gegevenspakketten in bytes voor de gegevensuitwisseling tussen SQL Server en het back-uparm. Ondersteunde waarden zijn veelvouden van 65 536 bytes (64 KB), tot 4 194 304 bytes (4 MB).
Ik zweer het - ik heb dit eerder gelezen, maar het is me nooit ter achter gekomen welke invloed ze op de prestaties kunnen hebben. Sterker nog, blijkbaar moet ik een soort 'coming-out' doen en toegeven dat ik zelfs nu nog niet helemaal begrijp wat ze precies doen. Misschien moet ik meer lezen over buffer input-output en hoe harde schijven werken. Een keer zal ik dat doen, maar nu kan ik gewoon een script schrijven dat controleert hoe deze waarden de snelheid beïnvloeden waarmee een back-up wordt gemaakt.
Ik heb een kleine database gemaakt van ongeveer 10 GB, deze heb ik op een SSD geplaatst en de map voor back-ups op een HDD.
Ik maak een tijdelijke tabel aan voor het opslaan van resultaten (voor mij is deze niet tijdelijk, zodat ik de resultaten uitgebreider kan bekijken, maar dat moet je zelf beslissen):
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, waarbij elke lus de waarde van één parameter wijzigt, deze parameters voeg ik toe aan de BACKUP-opdracht, ik sla de laatste invoer met de geschiedenis uit msdb.dbo.backupset op, verwijder het back-upbestand en ga verder met de volgende iteratie. Aangezien de gegevens over de uitvoering van de back-up afkomstig zijn uit backupset, gaat de nauwkeurigheid iets achteruit (er zijn geen fracties van seconden), maar dat overleven we wel.
Eerst moet je het gebruik van xp_cmdshell toestaan om back-ups te verwijderen (vergeet niet om het later 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;
GOEn eigenlijk:
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, standaard = 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 uitleg nodig hebt over wat hier gebeurt — laat het me weten in de reacties of via een privébericht. Voor nu zal ik alleen vertellen over de parameters die ik toevoeg aan BACKUP DATABASE.
Voor BLOCKSIZE hebben we een „gesloten” lijst met waarden, en ik kreeg geen back-up met BLOCKSIZE < 4KB. MAXTRANSFERSIZE kan elk getal zijn dat een veelvoud van 64KB is — van 64KB tot 4MB. Standaard op mijn systeem is het 1024KB, ik heb 512 — 1024 — 2048 — 4096 genomen.
Het was moeilijker met BUFFERCOUNT — dit kan elk positief getal zijn, maar de link geeft aan Daar staat ook hoe je informatie kunt krijgen over de BUFFERCOUNT waarmee de back-up daadwerkelijk wordt gemaakt — voor mij is dat 7. Het had geen zin om het te verlagen, en de bovengrens werd experimenteel vastgesteld — bij BUFFERCOUNT = 896 en MAXTRANSFERSIZE = 4194304 viel de back-up met een fout (die bij de link hierboven staat):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE eindigt abnormaal.
Msg 701, Level 17, State 123, Line 7 Er is onvoldoende systeemgeheugen in resource pool ‘default’ om deze query uit te voeren.
Ter vergelijking, eerst zal ik de resultaten van de back-up zonder enige parameters laten zien:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Nou, een back-up is een back-up:
Verwerkt 1070072 pagina's voor database ‘bt’, bestand ‘bt’ op bestand 1.
Verwerkt 2 pagina's voor database ‘bt’, bestand ‘bt_log’ op bestand 1.
BACKUP DATABASE heeft met succes 1070074 pagina's verwerkt in 53.171 seconden (157.227 MB/sec).
Het script dat de parameters testte, heeft er een paar uur over gedaan, alle metingen staan in . En hier zijn de resultaten, met de drie beste uitvoeringstijden (ik probeerde een mooie grafiek te maken, maar in de post moeten we met een tabel waar het mee doen, en in de opmerkingen toegevoegd ).
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, meteen een zeer belangrijke opmerking van uit :
kan met zekerheid worden gezegd dat de relatie tussen de parameters en de snelheid van de back-up binnen deze waarden willekeurig is, er is geen patroon. Maar een afwijking van de ingebouwde parameters heeft duidelijk een positieve invloed gehad op het resultaat.
Dus alleen door het beheren van de standaardparameters van BACKUP is er een tijdwinst van 2 keer behaald bij het maken van de back-up: 26 seconden, tegen 53 in het begin. Best goed, toch? Maar we moeten kijken hoe het met het herstel gaat. Wat als het nu 4 keer langer duurt om te herstellen?
Laten we eerst meten hoe snel een back-up met standaardinstellingen wordt hersteld:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Nou, dat weet je zelf ook, daar zijn de wegen, replace-nee replace, recovery-nee recovery. En zo voer ik dit uit:
Verwerkt 1070072 pagina's voor database ‘bt’, bestand ‘bt’ op bestand 1.
Verwerkt 2 pagina's voor database ‘bt’, bestand ‘bt_log’ op bestand 1.
RESTORE DATABASE succesvol verwerkt 1070074 pagina's in 40,752 seconden (205,141 MB/sec).
En nu zal ik de back-ups proberen te herstellen die zijn gemaakt met gewijzigde BLOCKSIZE, BUFFERCOUNT en MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE succesvol verwerkt 1070074 pagina's in 32,283 seconden (258,958 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE succesvol verwerkt 1070074 pagina's in 32,682 seconden (255,796 MB/sec).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE succesvol verwerkt 1070074 pagina's in 32,091 seconden (260,507 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE succesvol verwerkt 1070074 pagina's in 32,401 seconden (258,015 MB/sec).
De RESTORE DATABASE-instructie verandert niet tijdens het herstel, deze parameters worden er niet in opgegeven; SQL Server bepaalt ze zelf uit de back-up. En het is duidelijk dat zelfs bij herstel er een winst kan zijn — bijna 20% sneller (eerlijk gezegd heb ik niet veel tijd besteed aan het herstel, ik heb een paar van de 'snelste' back-ups doorlopen en vastgesteld dat er geen verslechtering is.).
Voor de duidelijkheid — hier worden geen optimalen voor iedereen weergegeven. De optimale parameters voor jezelf kun je alleen krijgen door te testen. Ik heb deze resultaten behaald, jij krijgt andere. Maar je ziet dat je je back-ups kunt 'tunen' en ze kunnen daadwerkelijk sneller worden gevormd en uitgerold.
Ik raad sterk aan om de documentatie volledig te lezen, omdat er mogelijk nuances zijn specifiek voor jouw systeem.
Aangezien ik begon met schrijven over back-ups, wil ik meteen een andere 'optimalisatie' beschrijven die vaker voorkomt dan het 'tunen' van parameters (die, voor zover ik begrijp, door minstens een deel van de utiliteiten voor back-ups wordt gebruikt, mogelijk samen met de eerder beschreven parameters), maar die is nog niet beschreven op Habr.
Als we naar de tweede regel in de documentatie kijken, net onder BACKUP DATABASE, zien we:
TO [ ,...n ]Wat denkt u dat er gebeurt als u meerdere backup_device's opgeeft? De syntaxis staat het immers toe. Maar er zal iets heel interessants gebeuren — de back-up wordt gewoon 'uitgesmeerd' over meerdere apparaten. Dat wil zeggen, elk 'apparaat' afzonderlijk zal nutteloos zijn; als je er één verliest, verlies je de hele back-up. Maar wat doet zo'n uitsmering met de snelheid van de back-up?
Laten we een back-up maken naar twee 'apparaten' die naast elkaar in dezelfde map liggen:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Heilige hemel, wat gebeurt hier?
Verwerkt 1070072 pagina's voor database ‘bt’, bestand ‘bt’ op bestand 1.
Verwerkt 2 pagina's voor database ‘bt’, bestand ‘btlog’ op bestand 1.
BACKUP DATABASE succesvol 1070074 pagina's verwerkt in 40,092 seconden (208,519 MB/sec).
Is de back-up 25% sneller gemaakt zonder enige reden? En wat als er nog een paar apparaten worden toegevoegd?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE succesvol 1070074 pagina's verwerkt in 34,234 seconden (244,200 MB/sec).
Al met al, een winst van ongeveer 35% in de tijd van back-up maken doordat deze direct in 4 bestanden op dezelfde schijf wordt weggeschreven. Ik heb een groter aantal getest — op mijn laptop was er geen winst, optimaal zijn 4 apparaten. Voor jou — weet ik niet, dat moet je testen. En trouwens, als je deze apparaten hebt — dat zijn echt verschillende schijven, gefeliciteerd, de winst zou nog aanzienlijker moeten zijn.
Laten we nu bespreken hoe we deze wonderen kunnen herstellen. Hiervoor moet het herstelcommando worden gewijzigd en moeten alle apparaten worden opgesomd:
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 succesvol 1070074 pagina's verwerkt in 38,027 seconden (219,842 MB/sec).
Een beetje sneller, maar ergens dichtbij, niet significant. Kortom, de back-up wordt sneller gemaakt, en het herstel gaat net zo snel — succes? Voor mij is het zeker een succes. Dit is belangrijk, daarom herhaal ik — als je tenminste één van deze bestanden verliest — verlies je de hele back-up.
Als je in het logboek informatie over de back-up bekijkt, weergegeven met behulp van Trace Flag 3213 en 3605, zul je opmerken dat bij een back-up naar meerdere apparaten, het aantal BUFFERCOUNT in ieder geval toeneemt. Misschien kan je proberen om ook de parameters voor BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE beter af te stemmen, maar dat is me niet meteen gelukt, en ik was te lui om dat nogmaals te testen met verschillende aantallen bestanden. En het is zonde van de schijven. Als je zo'n test bij jou wilt organiseren, is het script niet moeilijk om aan te passen.
Aan het einde bespreken we de prijs. Als de back-up parallel met het werk van gebruikers wordt gemaakt — moet je zeer verantwoordelijk omgaan met het testen, omdat als de back-up sneller gaat — de schijven zwaarder belast worden, de belasting op de processor toeneemt (omdat dit allemaal nog on-the-fly moet worden gecomprimeerd), met als gevolg dat de algehele responsiviteit van het systeem afneemt.
Grappen zijn leuk, maar ik begrijp heel goed dat ik geen onthullingen heb gedaan. Wat hierboven is geschreven, is gewoon een demonstratie van hoe je optimale parameters voor het maken van back-ups kunt kiezen.
Vergeet niet dat alles wat je doet, je op eigen risico doet. Controleer je back-ups en vergeet DBCC CHECKDB niet.
Bron: habr.com
