MS SQL Server: Biztonsági mentés szteroidokon

Várjon! Várjon! Igaz, ez nem egy újabb cikk az SQL Server biztonsági másolatainak típusairól. Nem is fogok beszélni a helyreállítási modellek közötti különbségekről és arról, hogyan kell kezelni egy túlnőtt rönköt.

Talán (csak talán), miután elolvasta ezt a bejegyzést, meg tudja majd győződni arról, hogy a szabványos eszközökkel eltávolított biztonsági másolatot holnap este eltávolítják, nos, 1.5-szer gyorsabban. És csak annak köszönhető, hogy egy kicsit több BACKUP DATABASE paramétert használ.

Ha a bejegyzés tartalma nyilvánvaló volt számodra, akkor elnézést kérek. Elolvastam mindent, amihez a Google eljutott a „habr sql server backup” kifejezésre, és egyetlen cikkben sem találtam említést arról, hogy a mentési idő paraméterekkel valahogy befolyásolható.

Azonnal felhívom a figyelmet Alexander Gladchenko megjegyzésére (@mssqlhelp):

A gyártás során soha ne változtassa meg a BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE paramétereket. Csak ilyen cikkek írására készültek. A gyakorlatban pillanatok alatt megszabadul a memóriaproblémáktól.

Természetesen jó lenne a legokosabbnak lenni, és exkluzív tartalmat posztolni, de sajnos ez nem így van. Angol és orosz nyelvű cikkek/bejegyzések is vannak (mindig zavarban vagyok, hogy minek nevezzem őket helyesen) ennek a témának szentelve. Íme néhány, amivel találkoztam: idő, два, három (az sql.ru-n).

Tehát kezdésként csatolok egy kissé lecsupaszított BACKUP szintaxist MSDN (egyébként fentebb írtam a BACKUP ADATBÁZIS-ról, de mindez vonatkozik a tranzakciós napló és a differenciális biztonsági mentésre is, de talán kevésbé szembetűnő hatással):

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 }
<...>

<…> - ez azt jelenti, hogy volt ott valami, de eltávolítottam, mert most nem kapcsolódik a témához.

Általában hogyan készítesz biztonsági másolatot? Hogyan „tanítják meg”, hogyan kell biztonsági másolatot készíteni cikkek milliárdjairól? Általában, ha egyszeri biztonsági másolatot kell készítenem néhány nem túl nagy adatbázisról, akkor automatikusan valami ilyesmit írok:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее

Általánosságban elmondható, hogy a biztonsági mentésekről szóló cikkekben általában említett paraméterek 75-90%-a itt van felsorolva. Nos, van még INIT, SKIP. Járt már az MSDN-n? Láttad, hogy másfél képernyőre van lehetőség? én is láttam...

Valószínűleg már rájött, hogy a továbbiakban a kód első blokkjában maradt három paraméterről fogunk beszélni - BLOCKSIZE, BUFFERCOUNT és MAXTRANSFERSIZE. Íme a leírásuk az MSDN-től:

BLOKKMÉRET = { blokkméret | @ blocksize_variable } – a fizikai blokkméretet jelzi bájtokban. A támogatott méretek: 512, 1024, 2048, 4096, 8192, 16 384, 32 768 és 65 536 bájt (64 KB). Az alapértelmezett érték 65 a szalagos eszközök és 536 a többi eszköz esetében. Ez a paraméter általában nem szükséges, mert a BACKUP utasítás automatikusan kiválasztja az eszköz megfelelő blokkméretét. A blokkméret beállítása kifejezetten felülírja az automatikus blokkméret-választást.

BUFFERSZÁM = { pufferszám | @ pufferszám_változó } – Meghatározza a biztonsági mentési művelethez használt I/O pufferek teljes számát. Bármilyen pozitív egész értéket megadhat, de a nagyszámú puffer memóriakimaradási hibát okozhat az Sqlservr.exe folyamat túlzott virtuális címterülete miatt.

A pufferek által felhasznált teljes terület mennyiségét a következő képlet határozza meg: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { max transzferméret | @ maxtransfersize_variable } az SQL Server és a biztonsági mentési adathordozó között cserélendő legnagyobb adatcsomag méretét adja meg bájtban. 65 536 bájt (64 KB) többszörösei 4 194 304 bájtig (4 MB) támogatottak.

Esküszöm – ezt már olvastam, de eszembe sem jutott, hogy mekkora hatással lehetnek a termelékenységre. Sőt, úgy tűnik, egyfajta „kijövet” kell tennem, és be kell vallanom, hogy még most sem teljesen értem, mit is csinálnak pontosan. Valószínűleg többet kell olvasnom a pufferelt I/O-ról és a merevlemezzel való munkáról. Egyszer megteszem ezt, de egyelőre csak írhatok egy szkriptet, amely ellenőrzi, hogy ezek az értékek hogyan befolyásolják a biztonsági mentés sebességét.

Csináltam egy kis, kb 10 GB-os adatbázist, felraktam az SSD-re, és a HDD-re raktam a mentések könyvtárát.

Létrehozok egy ideiglenes táblázatot az eredmények tárolására (nem áll rendelkezésemre ideiglenes, így részletesebben bele tudok ásni az eredményekbe, de döntsd el magad):

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 elve egyszerű - beágyazott hurkok, amelyek mindegyike megváltoztatja egy paraméter értékét, beilleszti ezeket a paramétereket a BACKUP parancsba, mentse az utolsó rekordot az előzményekkel az msdb.dbo.backupset fájlból, törölje a biztonsági mentési fájlt és a következő iterációt . Mivel a biztonsági mentés végrehajtási adatai a biztonsági mentésből származnak, a pontosság némileg elveszett (nincs a másodperc töredéke), de ezt túl fogjuk élni.

Először engedélyeznie kell az xp_cmdshell-t a biztonsági másolatok törléséhez (majd 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;  
GO

Hát, igazából:

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;
END

Ha hirtelen tisztázásra van szüksége, hogy mi történik itt, írjon kommentben vagy PM-ben. Egyelőre csak azokról a paraméterekről mesélek, amelyeket a BACKUP DATABASE-ba tettem.

A BLOCKSIZE esetében „zárt” értéklistánk van, és nem készítettem biztonsági mentést 4 KB-nál kisebb BLOCKSIZE esetén. MAXTRANSFERSIZE minden olyan számot, amely 64 KB többszöröse – 64 KB-ról 4 MB-ra. A rendszerem alapértelmezett értéke 1024 KB, 512 - 1024 - 2048 - 4096.

Nehezebb volt a BUFFERCOUNT -val - bármilyen pozitív szám lehet, de a link azt írja hogyan számítják ki a BACKUP ADATBÁZISBAN, és miért veszélyesek a nagy értékek?. Azt is leírja, hogyan lehet információt szerezni arról, hogy valójában melyik BUFFERSZÁM-mal készült a biztonsági mentés - nekem ez a 7. Nem volt értelme csökkenteni, és a felső határt kísérletileg fedezték fel - BUFFERCOUNT = 896 és MAXTRANSFERSIZE = 4194304 esetén a biztonsági mentés leesett hiba (amiről a fenti linken írva):

3013. üzenet, 16. szint, 1. állapot, 7. sor BIZTONSÁGI ADATBÁZIS rendellenesen leáll.

701. üzenet, 17. szint, 123. állapot, 7. sor Nincs elegendő rendszermemória az „alapértelmezett” erőforráskészletben a lekérdezés futtatásához.

Összehasonlításképpen először megmutatom a biztonsági mentés futtatásának eredményeit anélkül, hogy paramétereket adnék meg:

BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;

Nos, biztonsági mentés és biztonsági mentés:

1070072 oldal feldolgozva a „bt” adatbázishoz, az 1. fájl „bt” fájljához.

2 oldal feldolgozva a „bt” adatbázishoz, a „bt_log” fájl az 1. fájlban.

A BACKUP DATABASE sikeresen feldolgozott 1070074 oldalt 53.171 másodperc alatt (157.227 MB/s).

Maga a script a paramétereket tesztelve pár óra alatt működött, minden mérés benne volt google táblázat. És itt van egy válogatás a három legjobb végrehajtási idővel rendelkező eredményekből (próbáltam szép grafikont készíteni, de a bejegyzésben be kell érnem egy táblázattal, és a megjegyzésekben @mixsture Hozzátette nagyon klassz grafika).

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, egy nagyon fontos megjegyzés tőle @mixsture A megjegyzések:

Bátran kijelenthetjük, hogy a paraméterek és a mentési sebesség közötti kapcsolat ezekben az értéktartományokban véletlenszerű, nincs minta. De a beépített paraméterektől való távolodás nyilván jó hatással volt az eredményre

Azok. Csak a standard BACKUP paraméterek kezelésével kétszeresére nőtt a biztonsági mentés eltávolítási ideje: 2 másodperc, szemben az 26-mal az elején. Ez nem rossz, igaz? De meg kell néznünk, mi történik a helyreállítással. Mi van, ha most négyszer tovább tart a helyreállítás?

Először mérjük meg, mennyi ideig tart a biztonsági másolat visszaállítása az alapértelmezett beállításokkal:

RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;

Nos, ezt te magad is tudod, a módszerek megvannak, a csere nem helyettesíti, a gyógyulás nem gyógyulás. És én így csinálom:

1070072 oldal feldolgozva a „bt” adatbázishoz, az 1. fájl „bt” fájljához.

2 oldal feldolgozva a „bt” adatbázishoz, a „bt_log” fájl az 1. fájlban.

A RESTORE DATABASE sikeresen feldolgozott 1070074 oldalt 40.752 másodperc alatt (205.141 MB/s).

Most megpróbálom visszaállítani a módosított BLOCKSIZE, BUFFERCOUNT és MAXTRANSFERSIZE értékekkel készített biztonsági másolatokat.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

A RESTORE DATABASE sikeresen feldolgozott 1070074 oldalt 32.283 másodperc alatt (258.958 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

A RESTORE DATABASE sikeresen feldolgozott 1070074 oldalt 32.682 másodperc alatt (255.796 MB/s).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

A RESTORE DATABASE sikeresen feldolgozott 1070074 oldalt 32.091 másodperc alatt (260.507 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

A RESTORE DATABASE sikeresen feldolgozott 1070074 oldalt 32.401 másodperc alatt (258.015 MB/s).

A RESTORE DATABASE utasítás nem változik a helyreállítás során, ezek a paraméterek nincsenek benne megadva, az SQL Server maga határozza meg őket a biztonsági mentésből. És nyilvánvaló, hogy még a felépüléssel is lehet nyereség - majdnem 20%-kal gyorsabb (Hogy őszinte legyek, nem fordítottam sok időt a helyreállításra, átfutottam a „leggyorsabb” mentések közül, és megbizonyosodtam arról, hogy nincs romlás).

Minden esetre tisztázzam, hogy ezek nem olyan paraméterek, amelyek mindenki számára optimálisak. Csak teszteléssel szerezheti meg magának az optimális paramétereket. Én ezeket az eredményeket kaptam, te másokat fogsz kapni. De látja, hogy „hangolhatja” a biztonsági másolatokat, és valójában gyorsabban formálódnak és telepíthetők.

Erősen javaslom továbbá, hogy olvassa el a dokumentációt a maga teljességében, mert előfordulhatnak az Ön rendszerére jellemző árnyalatok.

Mióta a biztonsági mentésekről kezdtem írni, azonnal szeretnék írni még egy „optimalizálásról”, ami gyakoribb, mint a paraméterek „tuningolása” (amennyire én értem, legalább néhány mentési segédprogram használja, talán a paraméterekkel együtt korábban leírtuk), de még nem írták le Habrén sem.

Ha megnézzük a dokumentáció második sorát, közvetlenül a BIZTONSÁGI ADATBÁZIS alatt, azt látjuk:

TO <backup_device> [ ,...n ]

Mit gondol, mi fog történni, ha több backup_devicet ad meg? A szintaxis megengedi. És egy nagyon érdekes dolog fog történni - a biztonsági mentés egyszerűen több eszközön „elterjed”. Azok. minden egyes „eszköz” külön-külön használhatatlan lesz, elveszett egy, a teljes biztonsági másolat elveszett. De hogyan befolyásolja az ilyen elkenődés a biztonsági mentés sebességét?

Próbáljunk meg biztonsági másolatot készíteni két „eszközről”, amelyek ugyanabban a mappában vannak egymás mellett:

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak'   
WITH COMPRESSION;

Világatyák, miért történik ez?

1070072 oldal feldolgozva a „bt” adatbázishoz, az 1. fájl „bt” fájljához.

2 oldal feldolgozva a „bt” adatbázishoz, a „bt” fájlhozjelentkezzen be az 1. fájlba.

A BACKUP DATABASE sikeresen feldolgozott 1070074 oldalt 40.092 másodperc alatt (208.519 MB/s).

A mentés 25%-kal gyorsabb lett hirtelen? Mi van, ha hozzáadunk még néhány eszközt?

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 feldolgozott 1070074 oldalt 34.234 másodperc alatt (244.200 MB/s).

Összességében a nyereség körülbelül 35%-a a biztonsági másolat készítésének idejének, csak annak köszönhető, hogy a mentés 4 fájlba kerül egy lemezre egyszerre. Megnéztem egy nagyobb számot - nincs nyereség a laptopomon, optimális esetben - 4 eszközt. Neked – nem tudom, ellenőrizned kell. Nos, mellesleg, ha rendelkezik ilyen eszközökkel - ezek valóban különböző lemezek, gratulálunk, a nyereségnek még jelentősebbnek kell lennie.

Most beszéljünk arról, hogyan állíthatjuk helyre ezt a boldogságot. Ehhez módosítania kell a helyreállítási parancsot, és listáznia kell 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 feldolgozott 1070074 oldalt 38.027 másodperc alatt (219.842 MB/s).

Kicsit gyorsabb, de valahol közel, nem jelentős. Általánosságban elmondható, hogy a biztonsági másolatot gyorsabban eltávolítják és ugyanúgy visszaállítják - siker? Ami engem illet, ez egy nagy siker. Ez fontos, ezért ismétlem – ha te ha elveszíti legalább az egyik fájlt, akkor a teljes biztonsági másolatot elveszíti.

Ha megnézi a naplóban a 3213-as és 3605-ös nyomkövetési jelzőkkel megjelenített biztonsági mentési információkat, észre fogja venni, hogy több eszközre történő biztonsági mentéskor legalább a BUFFERCOUNT száma nő. Valószínűleg megpróbálhat optimálisabb paramétereket kiválasztani a BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE értékekhez, de nem sikerült azonnal, és lusta voltam, hogy újra elvégezzem az ilyen tesztelést, de más számú fájlhoz. A kerekekért pedig kár. Ha otthon szeretne ilyen tesztelést megszervezni, nem nehéz újra elkészíteni a forgatókönyvet.

Végül beszéljünk az árról. Ha a biztonsági mentést a felhasználók munkájával párhuzamosan távolítják el, akkor nagyon felelősségteljesen kell hozzáállni a teszteléshez, mert ha gyorsabban távolítják el a mentést, akkor jobban megfeszülnek a lemezek, nő a processzor terhelése (még mindig tömöríteni kell menet közben), és ennek megfelelően a rendszer általános válaszkészsége csökken.

Csak viccelek, de tökéletesen megértem, hogy nem tettem semmilyen kinyilatkoztatást. A fent leírtak egyszerűen bemutatják, hogyan lehet kiválasztani az optimális paramétereket a biztonsági mentések készítéséhez.

Ne feledje, hogy mindent a saját veszélyére és kockázatára tesz. Ellenőrizze a biztonsági másolatokat, és ne feledkezzen meg a DBCC CHECKDB-ről.

Forrás: will.com