MS SQL Server: BACKUP na sterydach

Czekać! Czekać! To prawda, że ​​​​nie jest to kolejny artykuł na temat typów kopii zapasowych SQL Server. O różnicach między modelami odzyskiwania i o tym, jak sobie radzić z zarośniętą kłodą, nawet nie będę mówił.

Być może (tylko być może) po przeczytaniu tego posta będziesz mógł mieć pewność, że kopia zapasowa usunięta z Ciebie standardowymi sposobami zostanie usunięta jutro wieczorem, no cóż, 1.5 razy szybciej. I tylko dlatego, że używasz trochę więcej parametrów BACKUP DATABASE.

Jeśli treść postu była dla Ciebie oczywista, to przepraszam. Przeczytałem wszystko, co Google znalazł na temat frazy „kopia zapasowa serwera habr sql” i w żadnym artykule nie znalazłem wzmianki o tym, że na czas tworzenia kopii zapasowej można w jakiś sposób wpłynąć za pomocą parametrów.

Od razu zwrócę uwagę na komentarz Aleksandra Gładczenki (@mssqlhelp):

Nigdy nie zmieniaj parametrów BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE w środowisku produkcyjnym. Są one przeznaczone wyłącznie do pisania takich artykułów. W praktyce w mgnieniu oka pozbędziesz się problemów z pamięcią.

Oczywiście fajnie byłoby być najmądrzejszym i publikować ekskluzywne treści, ale niestety tak nie jest. Istnieją artykuły/posty zarówno w języku angielskim, jak i rosyjskim (zawsze nie wiem, jak je poprawnie nazwać) poświęcone temu tematowi. Oto niektóre z nich, na które trafiłem: czas, два, trzy (na sql.ru).

Na początek dołączę nieco okrojoną składnię BACKUP z MSDN (swoją drogą pisałem powyżej o BACKUP DATABASE, ale to wszystko dotyczy zarówno kopii zapasowej dziennika transakcji, jak i kopii różnicowej, ale może z mniej oczywistym skutkiem):

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

<…> - to znaczy, że coś tam było, ale usunąłem, bo teraz nie ma to związku z tematem.

Jak zwykle robisz kopię zapasową? Jak „uczą” robić kopie zapasowe w miliardach artykułów? Ogólnie rzecz biorąc, jeśli będę musiał wykonać jednorazową kopię zapasową jakiejś niezbyt dużej bazy danych, automatycznie napiszę coś takiego:

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

I ogólnie rzecz biorąc, wymieniono tutaj prawdopodobnie 75-90% wszystkich parametrów, które zwykle są wymieniane w artykułach o kopiach zapasowych. Cóż, jest też INIT, SKIP. Czy odwiedziłeś MSDN? Czy widziałeś, że istnieją opcje dla półtora ekranu? Widziałem też...

Prawdopodobnie już zdałeś sobie sprawę, że dalej porozmawiamy o trzech parametrach, które pozostały w pierwszym bloku kodu - BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE. Oto ich opisy z MSDN:

ROZMIAR BLOKU = { rozmiar bloku | @ zmienna_rozmiaru bloku } - wskazuje rozmiar bloku fizycznego w bajtach. Obsługiwane rozmiary to 512, 1024, 2048, 4096, 8192, 16 384, 32 768 i 65 536 bajtów (64 KB). Wartość domyślna to 65 dla urządzeń taśmowych i 536 dla innych urządzeń. Zwykle ten parametr nie jest konieczny, ponieważ instrukcja BACKUP automatycznie wybiera odpowiedni rozmiar bloku dla urządzenia. Ustawienie rozmiaru bloku jawnie zastępuje automatyczny wybór rozmiaru bloku.

LICZBA BUFORÓW = { liczba buforów | @ zmienna_liczbybuforów } - Określa całkowitą liczbę buforów we/wy, które zostaną użyte w operacji tworzenia kopii zapasowej. Można określić dowolną dodatnią wartość całkowitą, ale duża liczba buforów może spowodować błąd braku pamięci z powodu nadmiernej wirtualnej przestrzeni adresowej w procesie Sqlservr.exe.

Całkowitą ilość miejsca zajmowaną przez bufory określa się według następującego wzoru: BUFFERCOUNT * MAXTRANSFERSIZE.

MAKSYMALNY ROZMIAR TRANSFERU = { maksymalny rozmiar transferu | @ maxtransfersize_zmienna } określa największy rozmiar pakietu danych (w bajtach) do wymiany pomiędzy SQL Server a nośnikiem zestawu kopii zapasowych. Obsługiwane są wielokrotności 65 536 bajtów (64 KB) do 4 194 304 bajtów (4 MB).

Przysięgam – czytałem to już wcześniej, ale nigdy nie przyszło mi do głowy, jak duży wpływ mogą mieć na produktywność. Co więcej, najwyraźniej muszę dokonać swego rodzaju „coming outu” i przyznać, że nawet teraz nie do końca rozumiem, co oni właściwie robią. Prawdopodobnie muszę przeczytać więcej o buforowanych wejściach/wyjściach i pracy z dyskiem twardym. Kiedyś to zrobię, ale na razie mogę tylko napisać skrypt, który sprawdzi, jak te wartości wpływają na szybkość wykonywania kopii zapasowej.

Zrobiłem małą bazę danych o wielkości około 10 GB, umieściłem ją na dysku SSD, a na dysku twardym umieściłem katalog do tworzenia kopii zapasowych.

Tworzę tymczasową tabelę do przechowywania wyników (nie mam jej tymczasowej, więc mogę zagłębić się w wyniki bardziej szczegółowo, ale ty decydujesz sam):

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

Zasada działania skryptu jest prosta - zagnieżdżone pętle, z których każda zmienia wartość jednego parametru, wstawia te parametry do komendy BACKUP, zapisuje ostatni rekord z historią z msdb.dbo.backupset, usuwa plik kopii zapasowej i kolejna iteracja . Ponieważ dane wykonania kopii zapasowej są pobierane z zestawu kopii zapasowych, dokładność jest nieco tracona (nie ma ułamków sekund), ale przetrwamy to.

Najpierw musisz włączyć xp_cmdshell, aby usuwać kopie zapasowe (następnie nie zapomnij wyłączyć tej funkcji, jeśli jej nie potrzebujesz):

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;  
GO

Właściwie:

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

Jeśli nagle będziesz potrzebował wyjaśnienia, co się tutaj dzieje, napisz w komentarzach lub na PW. Na razie opowiem tylko o parametrach, które umieściłem w BACKUP DATABASE.

Dla BLOCKSIZE mamy „zamkniętą” listę wartości i nie wykonywałem kopii zapasowej z BLOCKSIZE < 4KB. MAXTRANSFERSIZE dowolna liczba będąca wielokrotnością 64KB - od 64KB do 4MB. Domyślnie w moim systemie jest 1024 KB, wziąłem 512 - 1024 - 2048 - 4096.

Z BUFFERCOUNT było trudniej - może to być dowolna liczba dodatnia, ale link mówi jak to jest obliczane w BACKUP DATABASE i dlaczego duże wartości są niebezpieczne?. Mówi też jak uzyskać informację z jakiego BUFFERCOUNT faktycznie jest tworzona kopia zapasowa - u mnie jest to 7. Nie było sensu jej zmniejszać, a górną granicę odkryto eksperymentalnie - przy BUFFERCOUNT = 896 i MAXTRANSFERSIZE = 4194304 kopia spadła błąd (o którym napisano w linku powyżej):

Msg 3013, poziom 16, stan 1, wiersz 7 BACKUP DATABASE kończy się nieprawidłowo.

Msg 701, poziom 17, stan 123, wiersz 7 W „domyślnej” puli zasobów jest za mało pamięci systemowej, aby uruchomić to zapytanie.

Dla porównania najpierw pokażę wyniki uruchomienia kopii zapasowej bez podawania jakichkolwiek parametrów:

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

Cóż, kopia zapasowa i kopia zapasowa:

Przetworzono 1070072 stron dla bazy danych „bt”, plik „bt” w pliku 1.

Przetworzono 2 strony dla bazy danych „bt”, plik „bt_log” w pliku 1.

BACKUP DATABASE pomyślnie przetworzył 1070074 stron w 53.171 sekund (157.227 MB/s).

Sam skrypt testujący parametry działał w kilka godzin, wszystkie pomiary były zrobione arkusz kalkulacyjny Google. A oto wybór wyników z trzema najlepszymi czasami wykonania (próbowałem zrobić ładny wykres, ale w poście muszę zadowolić się tabelką, a w komentarzach @mieszanka dodano bardzo fajna 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;

Uwaga, bardzo ważna uwaga od @mieszanka z komentarz:

Można śmiało powiedzieć, że zależność pomiędzy parametrami a szybkością tworzenia kopii zapasowych w tych zakresach wartości jest przypadkowa, nie ma żadnej prawidłowości. Ale odejście od wbudowanych parametrów oczywiście miało dobry wpływ na wynik

Te. Tylko dzięki zarządzaniu standardowymi parametrami BACKUP uzyskano dwukrotny wzrost czasu usuwania kopii zapasowych: 2 sekund w porównaniu z 26 na początku. Nie jest źle, prawda? Musimy jednak zobaczyć, co stanie się z odbudową. A co jeśli teraz powrót do zdrowia zajmie 53 razy dłużej?

Najpierw zmierzmy, ile czasu zajmuje przywrócenie kopii zapasowej przy ustawieniach domyślnych:

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

Cóż, sam wiesz, że są na to sposoby: wymiana to nie wymiana, powrót do zdrowia to nie powrót do zdrowia. A robię to tak:

Przetworzono 1070072 stron dla bazy danych „bt”, plik „bt” w pliku 1.

Przetworzono 2 strony dla bazy danych „bt”, plik „bt_log” w pliku 1.

PRZYWRACANIE BAZY DANYCH pomyślnie przetworzyło 1070074 stron w 40.752 sekundy (205.141 MB/s).

Teraz spróbuję przywrócić kopie zapasowe wykonane ze zmienionymi BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

PRZYWRACANIE BAZY DANYCH pomyślnie przetworzyło 1070074 stron w 32.283 sekundy (258.958 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

PRZYWRACANIE BAZY DANYCH pomyślnie przetworzyło 1070074 stron w 32.682 sekundy (255.796 MB/s).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

PRZYWRACANIE BAZY DANYCH pomyślnie przetworzyło 1070074 stron w 32.091 sekundy (260.507 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

PRZYWRACANIE BAZY DANYCH pomyślnie przetworzyło 1070074 stron w 32.401 sekundy (258.015 MB/s).

Instrukcja RESTORE DATABASE nie zmienia się podczas odzyskiwania, parametry te nie są w niej określone, sam SQL Server określa je z kopii zapasowej. I jasne jest, że nawet po regeneracji można zyskać - prawie 20% szybciej (Szczerze mówiąc, nie spędziłem dużo czasu na odzyskiwaniu, przejrzałem kilka „najszybszych” kopii zapasowych i upewniłem się, że nie doszło do pogorszenia).

Na wszelki wypadek wyjaśnię, że nie są to parametry optymalne dla każdego. Optymalne dla siebie parametry można uzyskać jedynie testując. Mam takie wyniki, ty dostaniesz inne. Widzisz jednak, że możesz „dostroić” swoje kopie zapasowe, dzięki czemu będą mogły być tworzone i wdrażane szybciej.

Zdecydowanie zalecam również przeczytanie całej dokumentacji, ponieważ mogą występować niuanse specyficzne dla Twojego systemu.

Skoro zacząłem pisać o kopiach zapasowych, chcę od razu napisać o jeszcze jednej „optymalizacji”, która jest bardziej powszechna niż parametry „dostrajania” (o ile rozumiem, jest ona używana przynajmniej przez niektóre narzędzia do tworzenia kopii zapasowych, być może razem z parametrami opisany wcześniej), ale nie został jeszcze opisany również na Habré.

Jeśli spojrzymy na drugą linię dokumentacji, tuż pod BACKUP DATABASE, zobaczymy tam:

TO <backup_device> [ ,...n ]

Jak myślisz, co się stanie, jeśli określisz kilka urządzeń kopii zapasowych? Składnia na to pozwala. I stanie się bardzo interesująca rzecz - kopia zapasowa zostanie po prostu „rozłożona” na kilka urządzeń. Te. każde „urządzenie” z osobna będzie bezużyteczne, utracone jedno lub cała kopia zapasowa. Ale jak takie rozmazywanie wpłynie na prędkość tworzenia kopii zapasowych?

Spróbujmy wykonać kopię zapasową na dwóch „urządzeniach”, które znajdują się obok siebie w tym samym folderze:

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

Ojcowie świata, dlaczego tak się dzieje?

Przetworzono 1070072 stron dla bazy danych „bt”, plik „bt” w pliku 1.

Przetworzono 2 strony dla bazy danych „bt”, plik „bt”zaloguj” do pliku 1.

BACKUP DATABASE pomyślnie przetworzył 1070074 stron w 40.092 sekund (208.519 MB/s).

Czy tworzenie kopii zapasowych stało się o 25% szybsze niespodziewanie? A co jeśli dodamy jeszcze kilka urządzeń?

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

BACKUP DATABASE pomyślnie przetworzył 1070074 stron w 34.234 sekund (244.200 MB/s).

Łącznie zysk wynosi około 35% czasu wykonywania kopii zapasowej tylko ze względu na fakt, że kopia zapasowa jest zapisywana w 4 plikach na jednym dysku jednocześnie. Sprawdziłem większą liczbę - na moim laptopie nie ma zysku, optymalnie - 4 urządzenia. Dla Ciebie - nie wiem, musisz sprawdzić. A tak na marginesie, jeśli masz te urządzenia - to naprawdę różne dyski, gratulacje, zysk powinien być jeszcze większy.

Porozmawiajmy teraz o tym, jak przywrócić to szczęście. Aby to zrobić, musisz zmienić polecenie odzyskiwania i wyświetlić listę wszystkich urządzeń:

RESTORE DATABASE [bt]
FROM 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;

PRZYWRACANIE BAZY DANYCH pomyślnie przetworzyło 1070074 stron w 38.027 sekundy (219.842 MB/s).

Trochę szybciej, ale gdzieś blisko, nieistotne. Ogólnie rzecz biorąc, kopia zapasowa jest usuwana szybciej i przywracana w ten sam sposób - sukces? Jak dla mnie to całkiem udany. Ten ważny, więc powtarzam - jeśli jeśli utracisz przynajmniej jeden z tych plików, utracisz całą kopię zapasową.

Jeśli spojrzysz w dzienniku na informacje o kopii zapasowej wyświetlane przy użyciu flag Trace 3213 i 3605, zauważysz, że podczas tworzenia kopii zapasowej na kilku urządzeniach wzrasta co najmniej liczba BUFFERCOUNT. Prawdopodobnie można spróbować dobrać bardziej optymalne parametry dla BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ale mi się to nie udało od razu, a byłem zbyt leniwy, aby przeprowadzić takie testowanie jeszcze raz, ale dla innej liczby plików. A szkoda kół. Jeśli chcesz zorganizować takie testy w domu, przerobienie scenariusza nie jest trudne.

Na koniec porozmawiajmy o cenie. Jeśli kopia zapasowa jest usuwana równolegle z pracą użytkowników, należy podejść do testowania bardzo odpowiedzialnie, ponieważ szybsze usuwanie kopii zapasowej powoduje większe obciążenie dysków, zwiększa się obciążenie procesora (trzeba jeszcze skompresować to w locie), w związku z czym ogólna responsywność systemu maleje.

Żartuję, ale doskonale rozumiem, że nie zrobiłem żadnych rewelacji. To, co napisano powyżej, to po prostu demonstracja, w jaki sposób można wybrać optymalne parametry tworzenia kopii zapasowych.

Pamiętaj, że wszystko, co robisz, robisz na własne ryzyko i ryzyko. Sprawdź swoje kopie zapasowe i nie zapomnij o DBCC CHECKDB.

Źródło: www.habr.com