Czekaj! Czekaj! Naprawdę, to nie jest kolejny artykuł o typach kopii zapasowych SQL Server. Nawet nie będę mówił o różnicach w modelach odzyskiwania ani o tym, jak radzić sobie z rozrośniętym „logiem”.
Możliwe (tylko możliwe), że po przeczytaniu tego wpisu uda się wam sprawić, aby kopia zapasowa robiona standardowymi środkami była jutro w nocy zrobiona, cóż, 1.5 razy szybciej. I to tylko dlatego, że skorzystacie z nieco większej liczby parametrów BACKUP DATABASE.
Jeśli zawartość tego posta była dla was oczywista — przepraszam. Przeczytałem wszystko, do czego dotarłem dzięki Google, wpisując „habr sql server backup”, i w żadnym artykule nie znalazłem wzmianki, że na czas wykonywania kopii zapasowej można w jakiś sposób wpływać za pomocą parametrów.
Od razu zwrócę uwagę na komentarz Aleksandra Gładczuka ():
Nigdy nie zmieniaj parametrów BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE w środowisku produkcyjnym. Są one stworzone tylko po to, aby pisać podobne artykuły. W praktyce napotkasz problemy z pamięcią na pełnej.
Byłoby oczywiście świetnie, móc być najinteligentniejszym i zamieścić ekskluzywną treść, ale niestety tak nie jest. Istnieją zarówno artykuły w języku angielskim, jak i rosyjskim (zawsze mylę, jak je poprawnie nazywać), które dotyczą tego tematu. Oto część z tych, które napotkałem: , , .
Zatem na początek zamieszczam kilka uproszczonych składni BACKUP z (przy okazji, wyżej pisałem o BACKUP DATABASE, ale wszystko to odnosi się również do kopii zapasowych dziennika transakcji i do kopii różnicowych, chociaż być może z mniej zauważalnym efektem):
BACKUP DATABASE { database_name | @database_name_var }
TO [ ,...n ]
[ WITH {
| [ ,...n ] } ]
[;]
[ ,...n ]::=
--Opcje zestawu nośników
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Opcje transferu danych
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }— oznacza, że tam coś było, ale to usunąłem, ponieważ teraz to nie odnosi się do tematu.
Jak zazwyczaj wykonujesz kopię zapasową? Jak „uczą” wykonywać kopię zapasową w miliardach artykułów? W ogóle, jeśli trzeba będzie jednorazowo wykonać kopię zapasową jakiejś niezbyt dużej bazy, automatycznie napiszę coś w tym stylu:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--dobra, CHECKSUM dodałem tylko po to, by wyglądać mądrzejA ogólnie rzec biorąc, wymieniono tutaj chyba 75-90% wszystkich parametrów, które zazwyczaj są wspomniane w artykułach o kopiach zapasowych. No i były tam INIT, SKIP itd. A czy zaglądaliście do MSDN? Widzieliście, że tam opcji jest na półtorej ekranu? Ja też widziałem...
Prawdopodobnie już zrozumieliście, że dalsza mowa będzie o trzech parametrach, które pozostały w pierwszej części kodu — BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE. Oto ich opisy z MSDN:
BLOCKSIZE = { blocksize | @ blocksize_variable } — określa wielkość fizycznego bloku w bajtach. Obsługiwane są rozmiary 512, 1024, 2048, 4096, 8192, 16 384, 32 768 oraz 65 536 bajtów (64 KB). Wartość domyślna to 65 536 dla urządzeń taśmowych i 512 dla innych urządzeń. Zwykle w tym parametrze nie ma potrzeby, ponieważ instrukcja BACKUP automatycznie wybiera rozmiar bloku, odpowiedni do urządzenia. Jawne ustawienie rozmiaru bloku nadpisuje automatyczny wybór rozmiaru bloku.
BUFFERCOUNT = { buffercount | @ buffercount_variable } — określa łączną liczbę buforów wejścia-wyjścia, które będą używane do operacji tworzenia kopii zapasowej. Można podać dowolną dodatnią wartość całkowitą, ale duża liczba buforów może spowodować błąd braku pamięci z powodu nadmiernej przestrzeni adresowej w procesie Sqlservr.exe.
Całkowita ilość miejsca używanego przez bufory określona jest przez następujący wzór:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } określa największy wolumen pakietu danych w bajtach для wymiany danych między SQL Server a nośnikiem kopii zapasowej. Obsługiwane są wartości, które są wielokrotnościami 65 536 bajtów (64 KB), aż do 4 194 304 bajtów (4 MB).
Przysięgam — czytałem to wcześniej, ale nawet nie przyszło mi do głowy, jaki wpływ mogą mieć na wydajność. Co więcej, najwyraźniej muszę zrobić swoisty „coming out” i przyznać, że nawet teraz nie do końca rozumiem, co dokładnie one robią. Prawdopodobnie powinienem poczytać więcej o buforowanym wejściu-wyjściu i pracy z dyskiem twardym. Kiedyś to zrobię, a teraz mogę po prostu napisać skrypt, który sprawdzi jak te wartości wpływają na prędkość, z jaką wykonywana jest kopia zapasowa.
Stworzyłem małą bazę, mającą około 10 GB, położyłem ją na SSD, a katalog na kopie zapasowe umieściłem na HDD.
Tworzę tymczasową tabelę do przechowywania wyników (u mnie nie jest ona tymczasowa, aby można było dokładniej zbadać wyniki, ale to już wasza decyzja):
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, wstawiam te parametry do polecenia BACKUP, zapisuję ostatni rekord z historią z msdb.dbo.backupset, usuwam plik kopii zapasowej i przechodzę do kolejnej iteracji. Ponieważ dane o wykonaniu kopii zapasowej pochodzą z backupset, dokładność jest nieco ograniczona (nie ma tam ułamków sekund), ale to przeżyjemy.
Najpierw trzeba zezwolić na korzystanie z xp_cmdshell, aby móc usuwać kopie zapasowe (później nie zapomnijcie wyłączyć, jeśli nie jest potrzebne):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GONo i, 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);
\/* Wartości BLOCKSIZE *\/
DECLARE @bs int = 4096,
@max_bs int = 65536;
\/ * Wartości BUFFERCOUNT *\/
DECLARE @bc int = 7,
@min_bc int = 7,
@max_bc int = 800;
\/ * Wartości MAXTRANSFERSIZE *\/
DECLARE @ts int = 524288, --512KB, domyślnie = 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;
ENDJeśli potrzebujecie wyjaśnień dotyczących tego, co tutaj się dzieje — piszcie w komentarzach lub wiadomości prywatnej. Na razie opowiem tylko o parametrach, które wstawiam do BACKUP DATABASE.
Dla BLOCKSIZE mamy „zamkniętą” listę wartości, a ja nie przeprowadzałem kopii zapasowej z BLOCKSIZE < 4KB. MAXTRANSFERSIZE to dowolna liczba wielokrotna 64KB — od 64KB do 4MB. Domyślnie na moim systemie to 1024KB, wybrałem 512 — 1024 — 2048 — 4096.
Trudniej było z BUFFERCOUNT — może to być dowolna dodatnia liczba, a jednak w linku napisano, Tam również opisano, jak uzyskać informacje na temat tego, przy jakim BUFFERCOUNT rzeczywiście wykonywana jest kopia zapasowa — u mnie to 7. Nie miało sensu go zmniejszać, a górna granica została odkryta empirycznie — przy BUFFERCOUNT = 896 i MAXTRANSFERSIZE = 4194304 kopia zapasowa zakończyła się błędem (o którym napisano w powyższym linku):
Msg 3013, Poziom 16, Stan 1, Linia 7 BACKUP DATABASE kończy się w sposób nieprawidłowy.
Msg 701, Poziom 17, Stan 123, Linia 7 Brakuje pamięci systemowej w puli zasobów ‚default’, aby uruchomić to zapytanie.
Dla porównania, najpierw pokażę wyniki wykonania kopii zapasowej bez podawania parametrów w ogóle:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Cóż, backup to backup:
Przetworzono 1070072 strony 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 strony w 53.171 sekundy (157.227 MB/sec).
Sam skrypt testujący parametry działał przez kilka godzin, wszystkie pomiary w . A oto zbiór wyników, które mają trzy najlepsze czasy wykonania (starałem się stworzyć ładny wykres, ale w poście muszę zadowolić się tabelką, a w komentarzach dodał ).
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, od razu bardzo ważna uwaga od z :
można śmiało powiedzieć, że związek między parametrami a szybkością backupu w tych zakresach wartości jest losowy, nie widać żadnej zasady. Jednak odstępstwo od wbudowanych parametrów z pewnością dobrze wpłynęło na wynik.
To znaczy, tylko dzięki zarządzaniu standardowymi parametrami BACKUP udało się uzyskać podwójny zysk czasowy w tworzeniu kopii zapasowej: 26 sekund, w porównaniu do 53 na początku. A to już nieźle, prawda? Ale trzeba sprawdzić, jak wygląda przywracanie. A nuż teraz przywracanie zajmie 4 razy dłużej?
Na początek zmierzmy, jak długo trwa przywracanie kopii zapasowej z ustawieniami domyślnymi:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;No, to już sami wiecie, drogi tam, replace-nie replace, recovery-nie recovery. A ja wykonuję to tak:
Przetworzono 1070072 strony dla bazy danych ‚bt’, plik ‚bt’ w pliku 1.
Przetworzono 2 strony dla bazy danych ‚bt’, plik ‚bt_log’ w pliku 1.
PRZYWRÓCENIE BAZY DANYCH zostało pomyślnie przetworzone, 1070074 strony w 40,752 sekundy (205,141 MB/sek).
A teraz spróbuję przywrócić kopie zapasowe, wykonane z zmienionymi BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304PRZYWRÓCENIE BAZY DANYCH zostało pomyślnie przetworzone, 1070074 strony w 32,283 sekundy (258,958 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304PRZYWRÓCENIE BAZY DANYCH zostało pomyślnie przetworzone, 1070074 strony w 32,682 sekundy (255,796 MB/sek).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152PRZYWRÓCENIE BAZY DANYCH zostało pomyślnie przetworzone, 1070074 strony w 32,091 sekundy (260,507 MB/sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304PRZYWRÓCENIE BAZY DANYCH zostało pomyślnie przetworzone, 1070074 strony w 32,401 sekundy (258,015 MB/sek).
Zalecenie dotyczące PRZYWRÓCENIA BAZY DANYCH pozostaje niezmienne; te parametry nie są podawane w instrukcji, SQL Server sam je określa na podstawie kopii zapasowej. Widać, że nawet przy przywracaniu można zyskać — prawie 20% szybciej (szczerze mówiąc, nie poświęciłem dużo czasu na przywracanie, przetestowałem kilka najszybszych kopii zapasowych i upewniłem się, że nie ma pogorszenia.).
Dla pewności dodam — nie są to jakieś optymalne dla wszystkich parametry. Optymalne parametry dla siebie można uzyskać tylko poprzez testowanie. Otrzymałem takie wyniki, wy uzyskacie inne. Ale widzicie, że swoje kopie zapasowe można "dopasować" i rzeczywiście mogą być tworzone i przywracane szybciej.
Zalecam również, aby dokładnie przeczytać dokumentację, ponieważ mogą być niuanse specyficzne dla waszego systemu.
Skoro zacząłem pisać o kopiach zapasowych, chcę od razu napisać również o innej "optymalizacji", która występuje częściej niż "strojenie" parametrów (jak mi się wydaje, przynajmniej część narzędzi do tworzenia kopii zapasowych ją stosuje, być może wraz z parametrami opisanymi wcześniej), ale na Habra nie była jeszcze opisana.
Jeśli spojrzeć na drugi wiersz w dokumentacji, tuż pod BACKUP DATABASE, widzimy:
TO [ ,...n ]Co sądzicie, co się stanie, jeśli wskażemy kilka backup_device'ów? Składnia na to pozwala. A wydarzy się bardzo ciekawa rzecz — kopia zapasowa zostanie po prostu "rozłożona" na kilka urządzeń. Tzn. każde "urządzenie" osobno będzie bezużyteczne, straciliśmy jedno, straciliśmy całą kopię zapasową. Ale jak taka dezintegracja wpłynie na prędkość tworzenia kopii zapasowej?
Spróbujmy wykonać kopię zapasową na dwa "urządzenia", które leżą obok siebie w tym samym folderze:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;O rany, co tu się dzieje?
Przetworzono 1070072 strony dla bazy danych ‚bt’, plik ‚bt’ w pliku 1.
Przetworzono 2 strony dla bazy danych 'bt', plik 'btlog' w pliku 1.
BACKUP DATABASE pomyślnie przetworzył 1070074 strony w 40,092 sekundy (208,519 MB/s).
Czy backup zadziałał 25% szybciej bez żadnego powodu? 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 strony w 34,234 sekundy (244,200 MB/s).
Podsumowując, oszczędność czasu przy wykonywaniu backupu wynosi około 35% tylko dlatego, że backup jest pisany jednocześnie do 4 plików na jednym dysku. Sprawdzałem większą liczbę — na moim laptopie oszczędność nie występuje, optymalnie — 4 urządzenia. Dla was — nie wiem, trzeba sprawdzić. No i, nawiasem mówiąc, jeśli te urządzenia to naprawdę różne dyski, gratulacje, oszczędność powinna być jeszcze większa.
Teraz porozmawiajmy o tym, jak to szczęście przywrócić. W tym celu trzeba będzie zmienić polecenie przywracania i wymienić wszystkie urządzenia:
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 pomyślnie przetworzył 1070074 strony w 38,027 sekundy (219,842 MB/s).
Trochę szybciej, ale gdzieś w pobliżu, nieznacznie. W każdym razie backup jest szybszy, a przywracanie jest takie samo — sukces? Jak dla mnie — jak najbardziej sukces. To ważne, dlatego powtórzę — jeśli stracisz chociaż jeden z tych plików — tracisz cały backup..
Patrząc na dziennik informacji o backupie, wyświetlanej przy pomocy Trace Flag 3213 i 3605, można zauważyć, że przy backupie na kilka urządzeń zwiększa się przynajmniej liczba BUFFERCOUNT. Prawdopodobnie można spróbować dopasować lepsze parametry również dla BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ale ja na szybko tego nie zrobiłem, a aby przeprowadzić takie testowanie jeszcze raz, ale dla różnej liczby plików, nie miałem chęci. I szkoda dysków. Jeśli chcesz zorganizować takie testowanie u siebie, przekształcenie skryptu nie jest trudne.
Na koniec porozmawiajmy o cenie. Jeśli backup jest wykonywany równolegle z pracą użytkowników — trzeba podejść bardzo odpowiedzialnie do testowania, ponieważ jeśli backup jest wykonywany szybciej — dyski są mocniej obciążone, obciążenie procesora wzrasta (trzeba jeszcze to wszystko kompresować na bieżąco), w związku z tym ogólna responsywność systemu maleje.
Żarty na bok, rozumiem, że nie powiedziałem nic odkrywczego. To, co napisano powyżej, to po prostu pokazanie, jak można dobrać optymalne parametry do tworzenia kopii zapasowych.
Pamiętaj, że wszystko, co robisz, robisz na własne ryzyko. Sprawdzaj swoje kopie zapasowe i nie zapominaj o DBCC CHECKDB.
Źródło: habr.com
