Várjon! Várjon! Valóban nem ez egy újabb cikk az SQL Server biztonsági mentési típusairól. Még a helyreállítási modellek különbségeiről sem fogok beszélni, és arról, hogyan kell kezelni a megnövekedett „naplót”.
Lehet, hogy (csak lehet), hogy ennek a bejegyzésnek az elolvasása után sikerül úgy beállítania, hogy a biztonsági mentés, amit a standard eszközökkel végez, holnap este körülbelül 1,5-szer gyorsabban készüljön el. Csak annyival, hogy egy kicsit több BACKUP DATABASE paramétert használ.
Ha az írás tartalma nyilvánvaló volt számodra – sajnálom. Minden, ami a „habr sql server backup” kifejezésre keresve a Google-ban megjelent, elolvastam, és egyetlen cikkben sem találtam utalást arra, hogy a biztonsági mentés ideje alatt valamilyen módon befolyásolhatók lennének a paraméterek.
Azonnal felhívom a figyelmet Alexander Gladchenko hozzászólására ():
Soha ne változtassa meg a BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE paramétereket éles környezetben. Ezeket csak ilyen cikkek írására találták ki. A gyakorlatban problémái lesznek a memóriával.
Természetesen nagyszerű lenne a legokosabbnak lenni és exkluzív tartalmat közölni, de sajnos ez nem így van. Léteznek angol és orosz nyelvű cikkek/bejegyzések (mindig összezavarodom, hogy hogyan kell őket helyesen hívni), amelyek e témának vannak szentelve. Íme néhány, amelyek rám akadtak: , , .
Tehát először csatolok néhány megcsonkított BACKUP szintaxist a (egyébként fentebb említettem a BACKUP DATABASE-t, de ez mind a tranzakciós napló biztonsági mentésére, mind a differenciális biztonsági mentésre vonatkozik, de valószínűleg kevésbé nyilvánvaló hatással):
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 }— azt jelenti, hogy ott volt valami, de ezt eltávolítottam, mert jelenleg nem kapcsolódik a témához.
Hogyan készíted általában a biztonsági mentést? Hogyan „tanítják” meg a biztonsági mentést végezni a milliárdnyi cikkben? Valójában, ha egyszeri alkalommal kell készítenem egy nem túl nagy adatbázist, automatikusan valami ilyesmit írnék:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--na jó, a CHECKSUM-ot csak azért írtam, hogy okosabbnak tűnjekÉs alapvetően itt felsorolva van valószínűleg a paraméterek 75-90%-a, amelyeket általában a biztonsági másolatokkal kapcsolatos cikkekben említenek. Ott van még az INIT, SKIP is. Jártál már az MSDN-en? Láttad, hogy ott másfél képernyőnyi opció van? Én is láttam…
Valószínűleg már rájöttél, hogy most a kód első blokkában maradt három paraméterről lesz szó — BLOCKSIZE, BUFFERCOUNT és MAXTRANSFERSIZE. Itt vannak az MSDN-ből származó leírásaik:
BLOCKSIZE = { blocksize | @ blocksize_variable } — megadja a fizikai blokk méretét byte-ban. Támogatott méretek 512, 1024, 2048, 4096, 8192, 16 384, 32 768 és 65 536 byte (64 KB). A default érték 65 536 a szalagos eszközök esetén és 512 más eszközök esetén. Általában nem szükséges ezt a paramétert megadni, mivel a BACKUP utasítás automatikusan választja ki a blokk méretét, amely megfelel az eszköznek. A blokk méretének explicit megadása felülírja az automatikus méretválasztást.
BUFFERCOUNT = { buffercount | @ buffercount_variable } — meghatározza a biztonsági másolat készítésekor használandó bemeneti-kimeneti pufferek összes számát. Bármilyen pozitív egész szám megadható, azonban a túl nagy pufferek száma memóriahiányt okozhat a Sqlservr.exe folyamatban a túlzott virtuális címterület miatt.
A pufferek által használt összterület a következő képlet alapján határozható meg:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } — megadja a legnagyobb adatcsomag méretét byte-ban az SQL Server és a biztonsági másolat médiája közötti adatcseréhez. Olyan értékek támogatottak, amelyek 65 536 byte (64 KB) többszörösei, egészen 4 194 304 byte (4 MB) méretig.
Megesküszöm — olvastam ezt korábban, de nem is jutott eszembe, milyen hatással lehetnek a teljesítményre. Sőt, láthatóan meg kell tennem a „coming out”-et, és be kell vallanom, hogy még most sem értem teljesen, hogy mit csinálnak pontosan. Valószínűleg többet kellene olvasnom a pufferekkel történő I/O és a merevlemezek működéséről. Egyszer majd megteszem, de most egyszerűen írok egy szkriptet, ami ellenőrzi, hogy ezek az értékek milyen hatással vannak a biztonsági másolat készítésének sebességére.
Készítettem egy kis adatbázist, körülbelül 10 GB méretűt, amelyet SSD-re helyeztem, és a biztonsági másolatok számára a könyvtárt HDD-re helyeztem.
Létrehozok egy ideiglenes táblát az eredmények tárolására (nálam nem ideiglenes, hogy alaposabban meg tudjam nézni az eredményeket, de döntse el, ahogy szeretné):
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
);A szkript működése egyszerű — beágyazott ciklusok, amelyek mindegyike egy paraméter értékét változtatja, ezeket a paramétereket beillesztem a BACKUP parancsba, elmentem az utolsó bejegyzést a msdb.dbo.backupset-ből, törlöm a mentési fájlt, és következik a következő iteráció. Mivel a mentés végrehajtásáról szóló adatok a backupset-ből származnak, a pontosság kissé elveszik (ott nincsenek másodperc törték), de ezen túljutunk.
Először engedélyezni kell az xp_cmdshell használatát, hogy törölhessem a mentéseket (ne felejtse el letiltani, ha nincs rá szüksége):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GONos, tehát:
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;
ENDHa bármi magyarázat szükséges arra, ami itt történik — írjon kommentet vagy privát üzenetet. Jelenleg csak a BACKUP DATABASE-be beillesztett paraméterekről fogok beszélni.
A BLOCKSIZE esetében van egy „zárt” lista az értékekről, azonban nálam nem futott le a biztonsági másolat BLOCKSIZE < 4KB értékkel. A MAXTRANSFERSIZE bármilyen, 64KB osztó szám — 64KB-tól 4MB-ig terjedően. Alapértelmezés szerint a rendszeremen 1024KB van, én 512 — 1024 — 2048 — 4096-ot választottam.
Bonyolultabb volt a BUFFERCOUNT — ez bármilyen pozitív szám lehet, de a hivatkozás szerint . Ott van írva az is, hogyan lehet információt szerezni arról, hogy melyik BUFFERCOUNT-tal készül a biztonsági másolat — nekem ez 7. Csökkenteni nem volt értelme, a felső határt tapasztalati úton állapították meg — BUFFERCOUNT = 896 és MAXTRANSFERSIZE = 4194304 esetén a biztonsági másolat hibával leállt (erről a hivatkozásban van szó):
Msg 3013, Level 16, State 1, Line 7 A BACKUP DATABASE rendellenesen végződik.
Msg 701, Level 17, State 123, Line 7 Nincs elegendő rendszer memória a ‚default‘ erőforrás csoportban a lekérdezés végrehajtásához.
Összehasonlításként először megmutatom a biztonsági másolat végrehajtásának eredményeit, amikor egyáltalán nem adtam meg paramétereket:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Nos, biztonsági másolat és kész:
1070072 oldal feldolgozva a ‚bt‘ adatbázis számára, fájl ‚bt‘ az 1. fájlon.
2 oldal feldolgozva a ‚bt‘ adatbázis számára, fájl ‚bt_log‘ az 1. fájlon.
A BACKUP DATABASE sikeresen feldolgozott 1070074 oldalt 53.171 másodperc alatt (157.227 MB/sec).
A script, amely teszteli a paramétereket, pár óra alatt futott le, minden mérés a . Itt van az eredmények mintavétele, amelyek a legjobb három végrehajtási időt tartalmazzák (próbáltam szép grafikont készíteni, de a posztban el kell hogy intézzem táblázattal, a kommentekben hozzáadva ).
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;
Figyelem, azonnal egy nagyon fontos megjegyzés a :
határozottan elmondható, hogy a paraméterek és a biztonsági másolat gyorsasága közötti kapcsolat ezekben az értéktartományokban véletlenszerű, nincs semmilyen törvényszerűség. De a beépített paraméterek eltérése nyilvánvalóan jótékony hatással volt az eredményre.
Tehát csak a BACKUP szabványos paramétereinek kezelésével 2-szeres időmegtakarítást értem el a biztonsági másolat készítésekor: 26 másodperc, szemben az elején mért 53 másodperccel. Ez nem is rossz, igaz? De meg kell nézni, hogy mi a helyzet a visszaállítással. Elvégre most 4-szer lassabb lesz a visszaállítás?
Először mérjük meg, mennyi idő alatt történik a biztonsági másolat visszaállítása az alapbeállításokkal:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Nos, ezt önök is tudják, ott az utak, replace-nek replace, recovery-nek nem recovery. Én így végzem el:
1070072 oldal feldolgozva a ‚bt‘ adatbázis számára, fájl ‚bt‘ az 1. fájlon.
2 oldal feldolgozva a ‚bt‘ adatbázis számára, fájl ‚bt_log‘ az 1. fájlon.
A RESTORE DATABASE sikeresen feldolgozta a 1070074 oldalt 40.752 másodperc alatt (205.141 MB/s).
Most megpróbálom helyreállítani a módosított BLOCKSIZE, BUFFERCOUNT és MAXTRANSFERSIZE beállítású biztonsági másolatokat.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304A RESTORE DATABASE sikeresen feldolgozta a 1070074 oldalt 32.283 másodperc alatt (258.958 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304A RESTORE DATABASE sikeresen feldolgozta a 1070074 oldalt 32.682 másodperc alatt (255.796 MB/s).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152A RESTORE DATABASE sikeresen feldolgozta a 1070074 oldalt 32.091 másodperc alatt (260.507 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304A RESTORE DATABASE sikeresen feldolgozta a 1070074 oldalt 32.401 másodperc alatt (258.015 MB/s).
A RESTORE DATABASE utasítás helyreállítás során nem változik, nem adja meg ezeket a paramétereket, a SQL Server maga határozza meg őket a biztonsági másolat alapján. És látható, hogy még a helyreállítás során is lehetnek nyerességek — gyakorlatilag 20%-kal gyorsabb.Őszintén szólva nem sok időt szántam a helyreállításra, csak néhány leggyorsabb biztonsági másolatot próbáltam ki, és megbizonyosodtam arról, hogy nincsenek romlások.).
Mindenesetre tisztázni szeretném — itt nem valamilyen mindenki számára optimális paraméterekről van szó. Az optimális beállításokat csak teszteléssel kaphatja meg. Én ilyen eredményeket kaptam, ön másokat fog kapni. De látja, hogy a saját biztonsági másolatait lehet "finomítani", és valóban gyorsabban készülhetnek el és állíthatók helyre.
Még egyszer ajánlom, hogy olvassa el a teljes dokumentációt, mert az ön rendszerére vonatkozóan lehetnek speciális részletek.
Mivel elkezdtem a biztonsági másolatokról írni, azonnal szeretnék még egy "optimalizálásról" is írni, amely gyakrabban fordul elő, mint a beállítások "finomítása" (mivel úgy értem, hogy legalább egy része a biztonsági másolatkészítő eszközöknek ezt használja, talán a korábban leírt paraméterekkel együtt), de a Habrán ezt még nem írták le.
Ha megnézzük a dokumentáció második sorát, közvetlenül a BACKUP DATABASE alatt, ott azt látjuk:
TO [ ,...n ]Mit gondol, mi történik, ha több backup_device-t ad meg? A szintaxis engedi. De nagyon érdekes dolog fog történni - a biztonsági másolat egyszerűen "szétfog" több eszköz között. Tehát minden „eszköz” egyedül haszontalan lesz, ha egyet elveszít, egész a biztonsági másolat elveszik. De hogyan fog hatni a szétosztás a biztonsági másolat készítésének sebességére?
Próbáljunk meg biztonsági másolatot készíteni két "eszközre", amelyek egymás mellett vannak egy mappában:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Atya világ, mi történik itt?
1070072 oldal feldolgozva a ‚bt‘ adatbázis számára, fájl ‚bt‘ az 1. fájlon.
A 2 oldal feldolgozva a ‘bt’ adatbázishoz, fájl ‘btnaplózás fájl 1-en.
A BACKUP DATABASE sikeresen feldolgozta a 1070074 oldalt 40,092 másodperc alatt (208,519 MB/sec).
A biztonsági mentés 25%-kal gyorsabban készült, csak úgy, magától? Mi lenne, ha még pár eszközt hozzáadnánk?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;A BACKUP DATABASE sikeresen feldolgozta a 1070074 oldalt 34,234 másodperc alatt (244,200 MB/sec).
Összességében, körülbelül 35%-os időmegtakarítás a biztonsági mentés rögzítése miatt, mert egyszerre 4 fájlba íródik ugyanazon a lemezen. Nagyobb mennyiséget ellenőriztem — a laptopomon nem volt eredmény, optimalizáltan 4 eszköz. Önöknek — nem tudom, ezt ellenőrizni kell. Ja és ha ezek az eszközök valóban különböző lemezek — gratulálok, a megtakarítás még jelentősebb kell, hogy legyen.
Most beszéljünk arról, hogyan lehet visszaállítani ezt a boldogságot. Ehhez meg kell változtatni a visszaállítási parancsot, és felsorolni az összes eszközt:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;A RESTORE DATABASE sikeresen feldolgozta a 1070074 oldalt 38,027 másodperc alatt (219,842 MB/sec).
Kicsit gyorsabb, de valahol közel, nem lényeges. Összességében a biztonsági mentés gyorsabban készül, a visszaállítás pedig ugyanolyan — siker? Szerintem — teljesen siker. Ez fontos, ezért megismétlem — ha Ön elveszt egyetlen ilyen fájlt — elveszíti az egész biztonsági mentést.
Ha megnézi a biztonsági mentés naplójában a Trace Flag 3213 és 3605 segítségével megjelenített információt, észreveheti, hogy ha több eszközre készül a biztonsági mentés, legalább a BUFFERCOUNT növekszik. Valószínűleg érdemes lenne optimalizálni a BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE paramétereket is, de nekem ez elsőre nem sikerült, és nem volt kedvem újra tesztelni különböző fájlok számára. A lemezeket sajnálom. Ha Ön azt szeretné, hogy ilyen tesztelést szervezzen, a script átalakítása nem nehéz.
A végén beszéljünk az árról. Ha a biztonsági mentés párhuzamosan készül a felhasználók munkájával — nagyon felelősségteljesen kell hozzáállni a teszteléshez, mivel ha a biztonsági mentés gyorsabban készül — a lemezek erősebben feszülnek, a processzor terhelése növekszik (ezt mind még tömöríteni is kell), ennek következtében csökken a rendszer általános válaszideje.
Vicces, vicces, de teljesen értem, hogy semmi különöset nem mondtam. Amit fent írtam, az csak egy bemutató arról, hogyan lehet optimális paramétereket kiválasztani a backupok készítéséhez.
Ne feledje, hogy mindent, amit csinál, saját felelősségére teszi. Ellenőrizze a biztonsági mentéseit, és ne feledkezzen meg a DBCC CHECKDB-ről.
Forrás: habr.com
