Wpływ parametrów BUFFERCOUNT i MAXTRANSFERSIZE na odtwarzanie bazy danych

Jak często zdarza Ci się odtwarzać kopie zapasową kluczowego systemu swojej firmy? W moim przypadkach jest to rzadkość. Nawet, jeśli jest to przećwiczone, zgodnie z przygotowaną procedurą, kopia zapasowa jest „pewna” to towarzyszy przy tym spory niepokój. Nie zastanawiam się jak długo będzie trwał RESTORE tylko czy w ogóle pójdzie.

Nawet pomijając takie parametry biznesowe jak RTO (recovery time objective) czyli czas, w jakim należy przywrócić bazy danych po wystąpieniu awarii oraz RPO (recovery point objective) czyli akceptowalny poziom utraty danych wyrażony w czasie. W naszych celach jako DBA powinna znaleźć się optymalizacja czasu odtwarzania (jak i tworzenia kopii) baz danych. W tym mogą być pomocne, takie parametry jak BUFFERCOUNT i MAXTRANSFERSIZE.

Co one wnoszą do sprawy?

Parametry te są dostępne zarówno dla polecenia BACKUP jak i dla RESTORE.

Dla wykonywania kopii zapasowych jak i dla odzyskiwania bazy danych mogą mieć wpływ na szybkości tych operacji. Służą do określania ilości pamięci wykorzystywanej podczas tworzenia kopii zapasowych lub przywracania.

BUFFERCOUNT = { buffercount | @buffercount_variable }

Określa całkowitą liczbę buforów I/O przeznaczonych do operacji tworzenia kopii zapasowych. Można określić dowolną dodatnią liczbę całkowitą; jednak duża liczba buforów może powodować błędy „poza pamięcią” z powodu niedostatecznej przestrzeni adresowej wirtualnej w procesie Sqlservr.exe.

MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
Określa największą jednostkę transferu w bajtach, która będzie używana między SQL Server a nośnikiem kopii zapasowej. Możliwe wartości to wielokrotności 65536 bajtów (64 KB) do maksymalnej wartośći 4194304 bajtów (4 MB).

Backup i restore na domyślnych parametrach

Aby sprawdzić, jakie wartości mają parametry BUFFERCOUNT i MAXTRANSFERSIZE wtedy gdy ich nie definiujemy, można posłuzyć się flagą 3213 bądź 3014 która ujawnia informacje diagnostyczne dotyczących operacji tworzenia kopii zapasowych lub przywracania. Dodatkowo użyta flaga 3605 przekieruję te informację do dziennika błędów SQL Server.

Poniżej tylko fragment zwróconych informacji. Jednak niezbędny do omówienia zagadnień tego wpisu.

Backup/Restore buffer configuration parameters
Memory limit:              1535MB
BufferCount:                6
Sets Of Buffers:            2
MaxTransferSize:            1024 KB
Min MaxTransferSize:        64 KB
Total buffer space:         12 MB
Tabular data device count:  1
Fulltext data device count: 0
Filestream device count:    0
TXF device count:           0
Filesystem i/o alignment:   512
Media Buffer count:         6
Media Buffer size:          1024 KB
Encode Buffer count:        6
  • Memory limit to limit pamięci to ilość pamięci wewnętrznej puli buforów, która jest dostępna dla tego procesu tworzenia kopii zapasowych/przywracania.
  • BufferCount jest liczbą buforów, która będzie używana przy tym procesie. Wartość domyślnie jest wyliczana. Szczegółowe wyjaśnienia znajdziesz tutaj
  • Sets Of Buffers jest liczbą zestawów buforów.
  • MaxTransferSize to maksymalna ilość danych, które będą przetwarzane na bufor.
  • Total buffer space to całkowita przestrzeń bufora jest obliczana, jako (buffercount * sets of buffers) * maxtransfersize. Jest to liczba, która nie powinna przekroczyć wartości Memory Limit. Przekroczenie tej wartości poprzez przesadzenie z BUFFERCOUNT i MAXTRANSFERSIZE w najlepszym przypadku może zakończyć się błędem takimi jak poniżej.

Msg 3013, Level 16, State 1, Line 60
RESTORE DATABASE is terminating abnormally.
Msg 701, Level 17, State 17, Line 60
There is insufficient system memory in resource pool ‚default’ to run this query.

Optymalizacja odtworzenia bazy danych za pomocą BUFFERCOUNT i MAXTRANSFERSIZE

W wpisie skupiłem się głównie na odzyskiwaniu kopii zapasowej. W tym celu popełniłem skrypt, który umożliwia wykonanie serie poleceń RESTORE bazy z różnymi parametrami BUFFERCOUNT i MAXTRANSFERSIZE. Skrypt wylicza wartość BUFFERCOUNT  na podstawie wybranej jednostki transferu oraz wskazanej wartości Limit Memory.

Limit Memory oraz Sets of Buffers sprawdziłem już wcześniej wykonując jednorazowy RESTORE bezpośrednio z SSMS. Do testów posłużyłem się bazą AdventureWorks2014, wykonując dla każdego zestawu parametrów 4 powtórzenia z przerwą 120 sekundową pomiędzy kolejnym odtworzeniem.

Wyniki poniżej. Tego typu test powtarzałem kilkukrotnie i obserwowałem pewne wahania pomiędzy wynikami. Jednak szczególnie zarysowała się przewaga parametrów 1500 dla BUFFERCOUNT  oraz 524288 MAXTRANSFERSIZE gdzie średnią prędkość została uzyskana na poziomie 71MB/sec. Prawie 100% szybciej w porównaniu do ustawień domyślnych. Czas na poziomie 3,41 sekundy, co w rezultacie przekłada się na skrócenie czasu o niecałe 45%.

Wyniki odtwarzania bazy danych przy parametrach BufferCount i MaxTransferSize

BufferCount MaxTransferSize TotalLimit Duration [sec] MB/sec Duration [%] MB/sec [%]
6 1048576 12 6,25 37,41  100 100
12000 65536 1500 9,86 23,26 159,82 63,11
1500 524288 1500 3,41 71,04 55,67 193,69
750 1048576 1500 18,34 12,55 296,93 34,03
375 2097152 1500 12,44 18,44 201,24 49,94
188 4194304 1504 8,32 27,58 134,92 74,90

W ten sposób określone wartości dla opcji  BUFFERCOUNT i MAXTRANSFERSIZE używałem na tym serwerze również do odtwarzania baz znacznie większych niż AdventureWorks, gdzie otrzymywałem poprawę na poziomie 40% w stosunku do wartości domyślnych.

Podsumowanie

Krótko mówiąc, odpowiednio strojąc parametry BUFFERCOUNT i MAXTRANSFERSIZE jesteśmy wstanie skrócić czas odzyskiwania. W krytycznej sytuacji tak określone parametry przełożą się na krótszy czas niedostępności danych, systemów w firmie. Natomiast, na co dzień możemy wykorzystać taką wiedzę np. do skrócenia czasu dostarczania środowisk testowych dla zespołów developerskich.

Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.

Leave a Reply

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *