Ustawienie BUFFERCOUNT, BLOCKSIZE i MAXTRANSFERSIZE dla polecenia BACKUP

Szukam praktycznych wytycznych dla ustalenia wartości dla BUFFERCOUNT, BLOCKSIZEi MAXTRANSFERSIZEna BACKUProzkaz. Przeprowadziłem trochę badań (patrz poniżej), przeprowadziłem trochę testów i jestem w pełni świadomy, że każda naprawdę cenna odpowiedź rozpocznie się od „Cóż, to zależy ...”. Moje obawy dotyczące testów, które przeprowadziłem i testów pokazanych w dowolnym ze źródeł, które znalazłem (patrz sposób poniżej), polega na tym, że testy są wykonywane w próżni, najprawdopodobniej w systemie bez innego obciążenia.

Jestem ciekawy właściwych wskazówek / najlepszych praktyk dotyczących tych trzech opcji, które opierają się na wieloletnim doświadczeniu: wiele punktów danych w ciągu tygodni lub miesięcy. Nie szukam konkretnych wartości, ponieważ jest to głównie funkcja dostępnego sprzętu, ale chciałbym wiedzieć:

• Jak różne czynniki sprzętowe / obciążenia wpływają na to, co należy zrobić.
• Czy istnieją okoliczności, w których żadna z tych wartości nie powinna zostać zastąpiona?
• Czy są jakieś pułapki, które mogą zastąpić któreś z nich, które nie są od razu oczywiste? Zużywasz za dużo pamięci i / lub dysku we / wy? Komplikujesz operacje przywracania?
• Jeśli mam serwer z wieloma instancjami programu SQL Server (instancja domyślna i dwie instancje nazwane) i jeśli jednocześnie uruchamiam kopie zapasowe wszystkich 3 instancji, czy to wpływa na to, jak ustawiam te wartości poza upewnieniem się, że kolektyw ( BUFFERCOUNT* MAXTRANSFERSIZE) nie przekracza dostępnej pamięci RAM? Możliwa rywalizacja we / wy?
• W tym samym scenariuszu posiadania trzech wystąpień na jednym serwerze i ponownego uruchamiania kopii zapasowych na wszystkich trzech jednocześnie, w jaki sposób jednoczesne uruchamianie kopii zapasowych dla wielu baz danych w każdej instancji wpłynęłoby na ustawienie tych wartości? Oznacza to, że jeśli każda z trzech instancji ma 100 baz danych, z uruchomionymi 2 lub 3 kopiami zapasowymi dla każdej instancji jednocześnie, tak że jednocześnie działa od 6 do 9 kopii zapasowych. (W tej sytuacji mam wiele małych i średnich baz danych, a nie kilka dużych).

Co do tej pory zebrałem:

• BLOCKSIZE:

  • Obsługiwane rozmiary to 512, 1024, 2048, 4096, 8192, 16384, 32768 i 65536 (64 KB). ^[1]
  • Domyślna wartość to 65536 dla urządzeń taśmowych i 512 w przeciwnym razie ^[1]
  • Jeśli wykonujesz kopię zapasową, którą chcesz skopiować i przywrócić z dysku CD-ROM, określ BLOCKSIZE = 2048 ^[1]
  • Gdy piszesz na pojedyncze dyski, domyślnie 512 jest w porządku; jeśli korzystasz z macierzy RAID lub SAN, musisz sprawdzić, czy wartość domyślna lub 65536 jest lepsza. ^{[13 (strona 18)]}

  • Jeśli ustawiasz ręcznie, wartość musi wynosić> = rozmiar bloku użyty do utworzenia pliku (-ów) danych, w przeciwnym razie pojawi się następujący błąd:

    Msg 3272, poziom 16, stan 0, wiersz 3
    Urządzenie „C: \ Program Files \ Microsoft SQL Server \ MSSQL11.MSSQLSERVER \ MSSQL \ Backup \ BackupTest.bak” ma rozmiar sektora sprzętowego 4096, ale parametr rozmiaru bloku określa niekompatybilna wartość zastąpienia 512. Uruchom ponownie instrukcję, używając zgodnego rozmiaru bloku.

• BUFFERCOUNT:

  • Domyślnie ^{[2], [8]} :

    SQL Server 2005 i nowsze wersje:
    (NumberofBackupDevices * [mystery_multiplier]) + NumberofBackupDevices + (2 * NumberofVolumesInvolved)

  • [mystery_multiplier]: Istnieje pewna niespójność w odniesieniu do tej wartości. Widziałem to wyrażone w 3 formach:

    • 3 ^[2]
    • GetSuggestedIoDepth ^[8]
    • GetSuggestedIoDepth + 1 ^[8]

    
    Testy pokazujące mnożnik 3zostały wykonane na SQL Server 2005 SP2 ^[9] .

    Moje testy na SQL Server 2008 R2 i 2012 oraz komentarz użytkownika dotyczący SQL Server 2014 ^[8] pokazują mnożnik 4. Czyli biorąc pod uwagę zgłaszaną wartość GetSuggestedIoDepth(bezpośrednio poniżej):

    • GetSuggestedIoDepthjest teraz 4, lub
    • mnożnik jest teraz GetSuggestedIoDepth + 1
  • GetSuggestedIoDepthzwroty 3dla urządzeń DISK ^[9]
  • Brak ustalonej wartości maksymalnej, ale biorąc pod uwagę, że wymagana pamięć = ( BUFFERCOUNT* MAXTRANSFERSIZE), wydaje się, że praktyczną wartością maksymalną byłoby: BUFFERCOUNT <= (available_memory / MAXTRANSFERSIZE)
• MAXTRANSFERSIZE:
  • Możliwe wartości to wielokrotności 65536 bajtów (64 KB) w zakresie do 4194304 bajtów (4 MB). ^[1]
  • Wartości domyślne: Jeśli urządzenie jest w trybie odczytu (przywracania) lub jest to wersja Desktop lub Express Edition, użyj 64 KB, w przeciwnym razie użyj 1 MB. ^[9]
• Ogólne / Różne:
  • Maksymalny rozmiar, który może być użyty to ( Pula buforów do pamięci fizycznej / 16 ). Zwrócony z wywołania API GlobalMemoryStatusEx (ullTotalPhys). ^[9]
  • Flaga śledzenia 3213generuje parametry konfiguracyjne tworzenia kopii zapasowych / przywracania podczas wykonywania operacji tworzenia kopii zapasowych / przywracania i 3605zrzuca dane wyjściowe do pliku ERRORLOG :DBCC TRACEON (3213, 3605, -1);
  • Możesz użyć DISK = N'NUL:'(odpowiednik DOS / Windows /dev/nullw systemie UNIX) do łatwiejszego testowania niektórych wskaźników (ale nie uzyska dobrego wyczucia całkowitego czasu procesu, ponieważ pomija zapisywanie We / Wy)

Zasoby

1. Strona MSDN dla komendy T-SQL BACKUP
2. KB904804: Podczas tworzenia kopii zapasowej bazy danych w programie SQL Server 2000 występuje niska wydajność
3. Opcje poprawy wydajności tworzenia kopii zapasowych programu SQL Server
4. Kopia zapasowa i przywracanie
5. Optymalizacja tworzenia kopii zapasowych i przywracania programu SQL Server
6. Optymalizacja wydajności tworzenia kopii zapasowych
7. Jak zwiększyć szybkość pełnej kopii zapasowej bazy danych SQL przy użyciu kompresji i dysków półprzewodnikowych
8. Niepoprawna opcja przesyłania danych BufferCount może prowadzić do stanu OOM
9. Jak to działa: W jaki sposób program SQL Server Backup and Restore wybiera rozmiary transferu
10. Jak to działa: SQL Server Backup Buffer Exchange (VDI Focus)
11. SQL Backup dostrajanie dużych baz danych
12. Pamięć programu SQL Server dla bufora kopii zapasowej
13. Studium przypadku: Szybka i niezawodna kopia zapasowa i przywracanie VLDB przez sieć (plik .docx)
14. Ile urządzeń do tworzenia kopii zapasowych jest zalecane, aby poprawić wydajność tworzenia kopii zapasowych?

Testowałem z:

--DBCC TRACEON (3213, 3605, -1);

BACKUP DATABASE [Test] TO
      DISK =  'NUL:'
     --,DISK = 'NUL:'
     -- DISK =  'BackupTest1.bak'
     -- ,DISK =  'BackupTest2.bak'
WITH
    STATS = 5,
    FORMAT,
    CHECKSUM,
    NO_COMPRESSION,
    COPY_ONLY
    --,BUFFERCOUNT = 40
    --,MAXTRANSFERSIZE = 4194304--2097152,
    --,BLOCKSIZE = 16384 

--DBCC TRACEOFF (3213, 3605, -1);

AKTUALIZACJA

Wygląda na to, że czasami zapominam dodać informacje, o które zawsze pytam innych, kiedy odpowiadam na pytanie ;-). Podałem kilka informacji powyżej dotyczących mojej obecnej sytuacji, ale mogę podać więcej szczegółów:

Pracuję dla klienta, który udostępnia aplikację SaaS 24/7 / 365.25. Tak więc użytkownicy mogą być włączeni w dowolnym momencie, ale realistycznie, wszyscy użytkownicy mają siedzibę w USA (na razie) i zwykle pracują głównie w „standardowych” godzinach: 7 rano pacyficznego (tj. 10 rano wschodniego) do 19 wieczorem pacyficznego (tj. 22:00 czasu wschodniego), ale 7 dni w tygodniu, nie tylko od poniedziałku do piątku, chociaż obciążenie weekendowe jest nieco mniejsze.

Są skonfigurowane tak, aby każdy klient miał własną bazę danych. To niszowa branża, więc nie ma dziesiątek tysięcy (lub więcej) potencjalnych klientów. Liczba baz danych klientów różni się w zależności od wystąpienia, przy czym największe wystąpienie ma 206 klientów. Największa DB wynosi ok. 8 GB, ale tylko około 30 DB to ponad 1 GB. Dlatego nie staram się specjalnie maksymalizować wydajności VLDB.

Kiedy zaczynałem od tego klienta, ich kopie zapasowe były zawsze PEŁNE, raz dziennie i nie były tworzone kopie zapasowe LOG. Ustawili także MAXTRANSFERSIZE na 4 MB, a BUFFERCOUNT na 50. Zastąpiłem tę konfigurację nieco dostosowaną wersją skryptu kopii zapasowej bazy danych Oli Hallengren . Nieco dostosowaną częścią jest to, że uruchamia się z narzędzia do wielowątkowości (które napisałem i mam nadzieję, że wkrótce zacznę sprzedawać), które dynamicznie wykrywa bazy danych, gdy łączy się z każdą instancją, i pozwala na dławienie dla każdej instancji (dlatego obecnie uruchamiam trzy instancje jednocześnie, ale bazy danych dla każdej instancji sekwencyjnie, ponieważ nie byłem pewien konsekwencji, jakie ma ich jednoczesne uruchomienie).

Konfiguracja ma teraz wykonać PEŁNĄ kopię zapasową jeden dzień w tygodniu, a kopie zapasowe DIFF w pozostałe dni; Kopie zapasowe LOG są wykonywane co 10 minut. Używam wartości domyślnych dla 3 opcji, o które tutaj pytam. Ale wiedząc, jak zostały ustawione, chciałem się upewnić, że nie cofam optymalizacji (tylko dlatego, że były pewne poważne wady w starym systemie, nie znaczy, że wszystkobyło źle). Obecnie w przypadku 206 baz danych kopie zapasowe PEŁNE (raz w tygodniu) zajmują około 62 minut, a kopie zapasowe DIFF trwają od 7 do 20 minut w pozostałe dni (7 w pierwszym dniu po FULL i 20 w ostatnim dniu przed następny PEŁNY). I to uruchamia je sekwencyjnie (pojedynczy wątek). Całkowity proces tworzenia kopii zapasowej dziennika (wszystkie bazy danych we wszystkich 3 wystąpieniach) zajmuje od 50 do 90 sekund za każdym razem (ponownie co 10 minut).

Zdaję sobie sprawę, że mogę uruchamiać wiele plików na jedną bazę danych, ale a) nie jestem pewien, o ile lepiej będzie to zapewniać wielowątkowość oraz małe i średnie rozmiary baz danych, oraz b) nie chcę komplikować procesu przywracania ( istnieją różne powody, dla których preferowane jest zarządzanie jednym plikiem).

Zdaję sobie również sprawę, że mogłem włączyć kompresję (moje zapytanie testowe celowo ją wyłączyło), i zaleciłem to zespołowi, ale zwrócono mi uwagę, że wbudowana kompresja jest trochę szczęściwa. Częścią starego procesu było skompresowanie każdego pliku do pliku RAR, a ja przeprowadziłem własne testy i stwierdziłem, że tak, wersja RAR jest co najmniej o 50% mniejsza niż wersja natywnie skompresowana. Próbowałem najpierw użyć kompresji natywnej, aby przyspieszyć, a następnie pliki RAR, ale te pliki, choć mniejsze niż te tylko natywnie skompresowane, były nadal nieco większe niż wersja skompresowana tylko w RAR i wystarczająco duża, aby uzasadnić nie używa natywnej kompresji. Proces kompresji kopii zapasowych jest asynchroniczny i uruchamia się co X minut. Jeśli znajdzie .baklub.trnplik, to kompresuje. W ten sposób proces tworzenia kopii zapasowej nie jest spowalniany przez czas potrzebny do skompresowania każdego pliku.

W swoim pytaniu zwróciłeś się do wielu łodzi. Dzięki za bycie tak dokładnym!

Kilka rzeczy, które zauważam z ręki:

• Jak różne czynniki sprzętowe / obciążenia wpływają na to, co należy zrobić.

Czy prowadzisz instancję 24x7? Jakie jest obciążenie przez całą dobę? Zauważyłem, że masz wyłączoną kompresję kopii zapasowych; czy jest to zgodne z projektem testu, czy też z jakiegoś powodu pożądane jest wyłączenie go podczas wprowadzania go do produkcji? Jeśli masz mnóstwo zapasów sprzętowych (procesor / pamięć RAM), a wykonanie kopii zapasowej w jak najkrótszym czasie ma ogromne znaczenie, możesz dostosować te parametry do konkretnego sprzętu, mając na uwadze ten cel. Jeśli chcesz mieć pewność, że obciążenia OLTP są obsługiwane przez całą dobę i nie chcesz, aby tworzenie kopii zapasowych miało na to wpływ, prawdopodobnie będziesz musiał dostroić te parametry na odwrót. Nie określiłeś jednak swoich celów projektowych, ponieważ prosisz o ogólne wskazówki, ponieważ mądrze podajesz „to zależy”.

• Czy istnieją okoliczności, w których żadna z tych wartości nie powinna zostać zastąpiona?

Chcesz zachować ustawienia domyślne, jeśli martwisz się o wsparcie w dalszej części drogi, gdy już nie będziesz utrzymywał instancji i nie masz pewności co do możliwości zastąpienia. Prawdopodobnie zechcesz pozostawić ustawienia domyślne, chyba że masz konkretną potrzebę ich dostrojenia. Niech śpią psy, jak mówią.

• Czy są jakieś pułapki, które mogą zastąpić któreś z nich, które nie są od razu oczywiste? Zużywasz za dużo pamięci i / lub dysku we / wy? Komplikujesz operacje przywracania?

Jak wyraźnie wskazują dokumenty, do których się odwołujesz, zbyt duże podniesienie tych parametrów z pewnością może mieć negatywny wpływ na czas pracy. Podobnie jak w przypadku wszystkich produktów opartych na produkcji, należy je dokładnie przetestować przed wdrożeniem i pozostawić ustawienia w spokoju, chyba że jest to absolutnie konieczne.

• Jeśli mam serwer z wieloma instancjami programu SQL Server (instancja domyślna i dwie instancje nazwane) i jeśli jednocześnie uruchamiam kopie zapasowe wszystkich 3 instancji, czy to wpływa na to, jak ustawiam te wartości poza upewnieniem się, że kolektywne (BUFFERCOUNT * MAXTRANSFERSIZE) nie przekracza dostępnej pamięci RAM? Możliwa rywalizacja we / wy?

Będziesz chciał upewnić się, że pozostawisz dużo pamięci RAM na nieprzewidziane okoliczności. Z pewnością martwiłbym się wykorzystaniem ponad 60% lub 70% dostępnego pamięci RAM do operacji tworzenia kopii zapasowych, chyba że wiedziałbym ze 100% pewnością, że nic więcej się nie wydarzy podczas okna tworzenia kopii zapasowej.

Napisałem post na blogu z kodem, który pokazuje, jak wykonuję testy wydajności tworzenia kopii zapasowych na stronie SQLServerScience.com

^{to może nie być najlepsza odpowiedź, jaką kiedykolwiek napisałem, ale jak powiedział kiedyś Wielki The ™: „tęsknisz za 100% zdjęć, których nie wykonałeś”}