Odota! Odota! Totta, tämä ei ole toinen artikkeli SQL Server -varmuuskopioiden tyypeistä. En edes puhu palautusmallien eroista ja siitä, kuinka käsitellä umpeen kasvanutta puuta.
Ehkä (vain kenties) tämän viestin lukemisen jälkeen voit varmistaa, että sinulta tavallisilla keinoilla poistettu varmuuskopio poistetaan huomenna illalla, no, 1.5 kertaa nopeammin. Ja vain siksi, että käytät hieman enemmän BACKUP DATABASE -parametreja.
Jos viestin sisältö oli sinulle ilmeinen, olen pahoillani. Luin kaiken, mitä Google päätyi lauseeseen "habr sql server backup", enkä yhdestäkään artikkelista löytänyt mainintaa siitä, että varmuuskopiointiaikaan voidaan jotenkin vaikuttaa parametreilla.
Kiinnitän heti huomiosi Alexander Gladchenkon kommenttiin ():
Älä koskaan muuta tuotannossa olevia parametreja BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE. Ne on tehty vain tällaisten artikkeleiden kirjoittamista varten. Käytännössä pääset eroon muistiongelmista hetkessä.
Olisi tietysti hienoa olla älykkäin ja julkaista eksklusiivista sisältöä, mutta valitettavasti näin ei ole. Tälle aiheelle on omistettu sekä englannin- että venäjänkielisiä artikkeleita/postauksia (olen aina ymmälläni siitä, miksi niitä pitäisi kutsua oikein). Tässä muutamia, joihin törmäsin: , , .
Joten aluksi liitän hieman riisutun BACKUP-syntaksin (muuten, kirjoitin yllä VARMUUSTIETOKANNASTA, mutta kaikki tämä koskee sekä tapahtumalokin varmuuskopiointia että differentiaalista varmuuskopiointia, mutta ehkä vähemmän ilmeisellä vaikutuksella):
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 }
<...><…> - se tarkoittaa, että siellä oli jotain, mutta poistin sen, koska nyt se ei liity aiheeseen.
Miten yleensä otat varmuuskopion? Kuinka he "opettavat" ottamaan varmuuskopiot miljardeista artikkeleista? Yleensä, jos minun on tehtävä kertaluonteinen varmuuskopio jostain ei kovin suuresta tietokannasta, kirjoitan automaattisesti jotain tällaista:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееJa yleensä, luultavasti 75-90% kaikista parametreista, jotka yleensä mainitaan varmuuskopiointia koskevissa artikkeleissa, on lueteltu tässä. No, siellä on myös INIT, SKIP. Oletko käynyt MSDN:ssä? Oletko nähnyt, että vaihtoehtoja on puolitoista näytölle? Näin myös...
Olet todennäköisesti jo ymmärtänyt, että puhumme edelleen kolmesta parametrista, jotka jäivät ensimmäiseen koodilohkoon - BLOCKSIZE, BUFFERCOUNT ja MAXTRANSFERSIZE. Tässä on heidän kuvaukset MSDN:stä:
BLOCKSIZE = { lohkon koko | @ blocksize_variable } - osoittaa fyysisen lohkon koon tavuina. Tuetut koot ovat 512, 1024, 2048, 4096, 8192, 16 384, 32 768 ja 65 536 tavua (64 kt). Oletusarvo on 65 nauhalaitteille ja 536 muille laitteille. Yleensä tämä parametri ei ole välttämätön, koska BACKUP-käsky valitsee automaattisesti sopivan lohkokoon laitteelle. Lohkon koon asettaminen ohittaa automaattisen lohkokoon valinnan.
BUFFERCOUNT = { puskurimäärä | @ puskurimäärän_muuttuja } - Määrittää varmuuskopiointiin käytettävien I/O-puskurien kokonaismäärän. Voit määrittää minkä tahansa positiivisen kokonaisluvun, mutta suuri määrä puskureita voi aiheuttaa muistin loppumisesta johtuvan virheen Sqlservr.exe-prosessin liiallisesta virtuaalisesta osoiteavaruudesta.
Puskurien käyttämän tilan kokonaismäärä määritetään seuraavalla kaavalla:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { suurin siirtokoko | @ maxtransfersize_variable } määrittää suurimman datapaketin koon tavuina, jotka vaihdetaan SQL Serverin ja varmuuskopiointimedian välillä. Useita 65 536 tavua (64 kt) jopa 4 194 304 tavua (4 Mt) tuetaan.
Vannon - Olen lukenut tämän ennenkin, mutta minulle ei koskaan tullut mieleen, kuinka paljon ne voivat vaikuttaa tuottavuuteen. Lisäksi minun on ilmeisesti tehtävä eräänlainen "ulostulo" ja myönnettävä, että en vieläkään täysin ymmärrä, mitä he tarkalleen tekevät. Minun täytyy luultavasti lukea lisää puskuroidusta I/O:sta ja työskentelystä kiintolevyn kanssa. Jonakin päivänä teen tämän, mutta toistaiseksi voin vain kirjoittaa skriptin, joka tarkistaa, kuinka nämä arvot vaikuttavat varmuuskopion nopeuteen.
Tein pienen, noin 10 Gt:n tietokannan, laitoin sen SSD:lle ja varmuuskopiointihakemiston kiintolevylle.
Luon väliaikaisen taulukon tulosten tallentamiseksi (minulla ei ole väliaikaista, joten voin kaivella tuloksia tarkemmin, mutta päätät itse):
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
);Komentosarjan periaate on yksinkertainen - sisäkkäiset silmukat, joista jokainen muuttaa yhden parametrin arvon, lisää nämä parametrit BACKUP-komentoon, tallenna viimeinen tietue historialla tiedostosta msdb.dbo.backupset, poista varmuuskopiotiedosto ja seuraava iteraatio . Koska varmuuskopion suoritustiedot otetaan varmuuskopiosta, tarkkuus on jonkin verran menetetty (sekuntien murto-osia ei ole), mutta selviämme tästä.
Ensin sinun on otettava xp_cmdshell käyttöön varmuuskopioiden poistamiseksi (älä unohda sitten poistaa sitä käytöstä, jos et tarvitse sitä):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GONo oikeastaan:
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;
ENDJos tarvitset yhtäkkiä selvennystä siitä, mitä täällä tapahtuu, kirjoita kommentteihin tai PM. Toistaiseksi kerron vain parametreista, jotka olen lisännyt VARMUUSTIETOKANNAAN.
BLOCKSIZE:lle meillä on "suljettu" arvoluettelo, enkä tehnyt varmuuskopiota, jos BLOCKSIZE < 4 kt. MAXTRANSFERSIZE mikä tahansa numero, joka on 64 kt:n kerrannainen - 64 kt:sta 4 megatavuun. Järjestelmäni oletusarvo on 1024 kt, otin 512 - 1024 - 2048 - 4096.
Se oli vaikeampaa BUFFERCOUNT kanssa - se voi olla mikä tahansa positiivinen luku, mutta linkki sanoo . Siinä kerrotaan myös, kuinka saada tietoa millä BUFFERCOUNT-luvulla varmuuskopio todella tehdään - minulle se on 7. Ei ollut mitään järkeä pienentää sitä, ja yläraja löydettiin kokeellisesti - BUFFERCOUNT = 896 ja MAXTRANSFERSIZE = 4194304 varmuuskopio putosi virhe (josta kirjoitettu yllä olevassa linkissä):
Viesti 3013, taso 16, tila 1, rivi 7 VARMUUSKOPIOINTITIETOKANTA päättyy epänormaalisti.
Viesti 701, taso 17, tila 123, rivi 7 Resurssivarannossa "oletus" ei ole tarpeeksi järjestelmämuistia tämän kyselyn suorittamiseen.
Vertailun vuoksi näytän ensin varmuuskopion suorittamisen tulokset määrittelemättä mitään parametreja:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;No, varmuuskopiointi ja varmuuskopiointi:
Käsitelty 1070072 sivua tietokannassa "bt", tiedosto "bt" tiedostossa 1.
Käsitelty 2 sivua tietokannassa "bt", tiedosto "bt_log" tiedostossa 1.
BACKUP DATABASE käsitteli onnistuneesti 1070074 sivua 53.171 sekunnissa (157.227 MB/s).
Itse skripti, joka testasi parametreja, toimi parissa tunnissa, kaikki mittaukset olivat mukana . Ja tässä on valikoima tuloksia kolmella parhaalla toteutusajalla (yritin tehdä hienon kaavion, mutta postauksessa minun täytyy tyytyä taulukkoon ja kommentteihin hän lisäsi ).
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;
Huomio, erittäin tärkeä huomautus ja :
Voimme vakuuttavasti sanoa, että parametrien ja varmuuskopioinnin nopeuden välinen suhde näillä arvoalueilla on satunnainen, kaavaa ei ole. Mutta sisäänrakennetuista parametreista pois siirtymisellä oli ilmeisesti hyvä vaikutus tulokseen
Nuo. Ainoastaan standardeja BACKUP-parametreja hallitsemalla varmuuskopion poistoaika kasvoi kaksinkertaiseksi: 2 sekuntia verrattuna 26:een alussa. Se ei ole huono, eihän? Mutta meidän on katsottava, mitä kunnostuksen kanssa tapahtuu. Entä jos toipuminen kestää nyt 53 kertaa kauemmin?
Mittaa ensin, kuinka kauan varmuuskopion palauttaminen oletusasetuksilla kestää:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Tiedät itsekin, että keinot ovat olemassa, korvaaminen ei korvaa, toipuminen ei ole palautumista. Ja minä teen sen näin:
Käsitelty 1070072 sivua tietokannassa "bt", tiedosto "bt" tiedostossa 1.
Käsitelty 2 sivua tietokannassa "bt", tiedosto "bt_log" tiedostossa 1.
RESTORE DATABASE käsitteli onnistuneesti 1070074 sivua 40.752 sekunnissa (205.141 Mt/s).
Nyt yritän palauttaa varmuuskopiot, jotka on otettu muutetuilla BLOCKSIZE-, BUFFERCOUNT- ja MAXTRANSFERSIZE-arvoilla.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE käsitteli onnistuneesti 1070074 sivua 32.283 sekunnissa (258.958 Mt/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE käsitteli onnistuneesti 1070074 sivua 32.682 sekunnissa (255.796 Mt/s).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE käsitteli onnistuneesti 1070074 sivua 32.091 sekunnissa (260.507 Mt/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE käsitteli onnistuneesti 1070074 sivua 32.401 sekunnissa (258.015 Mt/s).
RESTORE DATABASE -käsky ei muutu palautuksen aikana; näitä parametreja ei ole määritetty siinä; SQL Server itse määrittää ne varmuuskopiosta. Ja on selvää, että jopa toipumisen myötä voitto voi olla - melkein 20% nopeampi (Ollakseni rehellinen, en käyttänyt paljon aikaa palautumiseen, kävin läpi useita "nopeimpia" varmuuskopioita ja varmistin, ettei huononemista tapahtunut).
Selvennyksen varalta, että nämä eivät ole kaikkia optimaalisia parametreja kaikille. Optimaaliset parametrit saat itsellesi vain testaamalla. Minä sain nämä tulokset, sinä saat erilaisia. Mutta näet, että voit "virittää" varmuuskopiosi ja ne voivat itse asiassa muodostua ja ottaa käyttöön nopeammin.
Suosittelen myös, että luet dokumentaation kokonaisuudessaan, koska järjestelmässäsi voi olla vivahteita.
Siitä lähtien kun aloin kirjoittaa varmuuskopioista, haluan heti kirjoittaa vielä yhdestä "optimoinnista", joka on yleisempää kuin "parametrien virittäminen" (sikäli kuin ymmärrän, sitä käyttävät ainakin jotkut varmuuskopiointiohjelmat, ehkä yhdessä parametrien kanssa kuvattu aiemmin), mutta sitä ei ole vielä kuvattu myöskään Habrén kohdalla.
Jos katsomme dokumentaation toista riviä, aivan VARMUUSKOPIOINTITIETOKANTA-kohdan alla, näemme siellä:
TO <backup_device> [ ,...n ]Mitä luulet tapahtuvan, jos määrität useita varmuuskopiointilaitteita? Syntaksi sallii sen. Ja erittäin mielenkiintoinen asia tapahtuu - varmuuskopio yksinkertaisesti "levitetään" useille laitteille. Nuo. jokainen "laite" yksitellen on hyödytön, kadonnut yhden, menettänyt koko varmuuskopion. Mutta miten tällainen tahraus vaikuttaa varmuuskopioinnin nopeuteen?
Yritetään tehdä varmuuskopio kahdelle "laitteelle", jotka sijaitsevat vierekkäin samassa kansiossa:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Maailman isät, miksi näin tehdään?
Käsitelty 1070072 sivua tietokannassa "bt", tiedosto "bt" tiedostossa 1.
Käsitelty 2 sivua tietokannassa "bt", tiedosto "bt"kirjaudu sisään tiedostoon 1.
BACKUP DATABASE käsitteli onnistuneesti 1070074 sivua 40.092 sekunnissa (208.519 MB/s).
Tuliko varmuuskopioinnista 25 % nopeampi aivan yllättäen? Mitä jos lisäämme vielä pari laitetta?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE käsitteli onnistuneesti 1070074 sivua 34.234 sekunnissa (244.200 MB/s).
Kaiken kaikkiaan voitto on noin 35 % varmuuskopioinnin ajasta vain siksi, että varmuuskopio kirjoitetaan 4 tiedostoon yhdelle levylle kerralla. Tarkistin suuremman määrän - kannettavassani ei ole vahvistusta, optimaalisesti - 4 laitetta. Sinulle - en tiedä, sinun on tarkistettava. No, muuten, jos sinulla on nämä laitteet - nämä ovat todella erilaisia levyjä, onnittelut, voiton pitäisi olla vieläkin merkittävämpi.
Nyt puhutaan kuinka palauttaa tämä onnellisuus. Tätä varten sinun on muutettava palautuskomentoa ja lueteltava kaikki laitteet:
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 käsitteli onnistuneesti 1070074 sivua 38.027 sekunnissa (219.842 Mt/s).
Hieman nopeampi, mutta jossain lähellä, ei merkittävää. Yleensä varmuuskopio poistetaan nopeammin ja palautetaan samalla tavalla - menestys? Minulle se on melkoinen menestys. Tämä on tärkeää, joten toistan - jos sinä jos menetät ainakin yhden näistä tiedostoista, menetät koko varmuuskopion.
Jos katsot lokista jäljityslippujen 3213 ja 3605 avulla näkyviä varmuuskopiointitietoja, huomaat, että kun varmuuskopioit useille laitteille, ainakin BUFFERCOUNT-määrä kasvaa. Todennäköisesti voit yrittää valita optimaalisempia parametreja kohteille BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, mutta en onnistunut heti, ja olin liian laiska suorittamaan tällaista testausta uudelleen, mutta eri tiedostomäärälle. Ja se on sääli pyörien suhteen. Jos haluat järjestää tällaisen testauksen kotona, käsikirjoituksen uusiminen ei ole vaikeaa.
Lopuksi puhutaan hinnasta. Jos varmuuskopio poistetaan rinnakkain käyttäjien työn kanssa, testaukseen on suhtauduttava erittäin vastuullisesti, koska jos varmuuskopio poistetaan nopeammin, levyt rasituvat enemmän, prosessorin kuormitus kasvaa (joudut silti pakkaamaan se lennossa), ja vastaavasti järjestelmän yleinen reagointikyky heikkenee.
Vitsailen, mutta ymmärrän hyvin, etten tehnyt paljastuksia. Yllä kirjoitettu on yksinkertaisesti osoitus siitä, kuinka voit valita optimaaliset parametrit varmuuskopiointia varten.
Muista, että kaikki tekemäsi tapahtuu omalla riskilläsi ja riskilläsi. Tarkista varmuuskopiot ja älä unohda DBCC CHECKDB:tä.
Lähde: will.com