Archiwa blogu

Jak sprawić by plik logu transakcyjnego miał tylko 1 VLF

W ramach mojego postu „Jak zmienić ilość/wielkość VLF dla istniejącej bazy danych” przedstawiłem w jaki sposób można zmienić ilość wirtualnych plików dziennika zmniejszając log transakcyjny instrukcjami BACKUP LOG i DBCC SHRINKFILE, a następnie zwiększając jego rozmiar za pomocą ALTER DATABASE … MODIFY FILE. Zmniejszenie logu w przedstawionym przeze mnie przykładzie poskutkowało tym, że plik dziennika zawierał jedynie 2 VLF-y. W tym momencie napisałem, że moglibyśmy posłużyć się kolejną kombinacją kopii logu oraz instrukcji DBCC SHRINKFILE aby pozbyć się jednego z nich. Był to skrót myślowy, którego wyjaśnienie przedstawię poniżej 🙂

Odtwórzmy więc nasz przykład

Zaczynamy się od stworzenia bazy z określoną ilości VLF:

CREATE DATABASE testVLFa
ON PRIMARY (
 NAME = 'testVLF_data',
 FILENAME = 'c:\test\testVLF_data.mdf',
 SIZE = 250
)
LOG ON (
 NAME = 'testVLF_log',
 FILENAME = 'c:\test\testVLF_log.ldf',
 SIZE = 500, -- log będzie zawierał 8 VLF-ów
 FILEGROWTH = 500 --i będzie przyrastał także po 8 VLF
)

Następnie sprawdzamy, czy aby na pewno log transakcyjny bazy składa się z 8  wirtualnych plików:

DBCC LOGINFO('testVLF')

Robimy pełną kopię zapasową bazy:

BACKUP DATABASE testVLF
TO DISK = 'testVLF_FULL_20120407.bak'

Zmniejszamy plik dziennika za pomocą kopi logu transakcji i instrukcji DBCC SHRINKFILE:

BACKUP LOG testVLF
TO DISK = 'testVLF_LOG_20120407.trn'
GO
DBCC SHRINKFILE ('testVLF_log', 1)
GO

Po tym zabiegu nasza plik logu powinien mieć jedynie 2 VLF-y:

DBCC LOGINFO('testVLF')

A co jeśli chcemy mieć jeden plik VLF?

Powtórzmy naszą kombinację:

BACKUP LOG testVLF
TO DISK = 'testVLF_LOG_20120407.trn'
GO
DBCC SHRINKFILE ('testVLF_log', 1)
GO

Sprawdźmy ilość VLF-ów:

DBCC LOGINFO

Plik logu nie zmalał pomimo faktu, że tylko jeden VLF jest aktywny. Dlaczego tak się stało?

Generalnie przeglądając dokumentację, można się natknąć na następującą sentencję:

The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files. 

Stwierdzenie to pochodzi BOOKS ONLINE dla SQL Server 2000, tak więc możne ono już być nieaktualne. W dokumentacji dla SQL Server 2012 nie znalazłem żadnego stwierdzenia na temat minimalnej ilości VLF- ów. Tak czy inaczej – minimalna wielkość logu dla SQL Server 2000 to 512 KB przy dwóch VLF-ach. Czy zatem minimalna ilość wirtualnych plików dziennika dla logu transakcyjnego wynosi 2?

Osobiście zaryzykowałbym tezę, że TAK i limit ten jest nadal obecny w SQL 2012. Jednakże oficjalnej wzmianki na ten temat nie udało mi się znaleźć.

Inną sprawą jest fakt, że możemy skonfigurować plik dziennika transakcji tak aby zawierał on jeden VLF. Aby to zrobić musimy po prostu dodać drugi plik 🙂

ALTER DATABASE testVLF
ADD LOG FILE (
 NAME = 'testVLF_log2',
 FILENAME = 'c:\test\testVLF_log2.ldf',
 SIZE = 500, -- drugi log też będzie zawierał 8 VLF-ów
 FILEGROWTH = 500 --i będzie przyrastał także po 8 VLF
)

Gdy wykonamy instrukcję DBCC LOGIFO zobaczymy ile VLF-ów ma każdy z plików. Na obrazku poniżej widać, że pierwszy z nich ma dwa, a drugi osiem:

Spróbujmy zmniejszyć pierwszy z nich:

BACKUP LOG testVLF
TO DISK = 'testVLF_LOG_20120407.trn'
GO
DBCC SHRINKFILE ('testVLF_log', 1)
GO

Sprawdźmy, czy się udało:

No i mamy jeden VLF dla pierwszego z plików logu. Co więcej dla drugiego pliku też przedstawiona kombinacja zadziała ponieważ nadal będziemy mieć 2 VLF-y dla bazy:

BACKUP LOG testVLF
TO DISK = 'testVLF_LOG_20120407.trn'
GO
DBCC SHRINKFILE ('testVLF_log2', 1)
GO
DBCC LOGINFO
GO

No dobrze, ale czy ten fakt kiedykolwiek mi się przyda?

Generalnie rzecz ujmując – OBY NIE :). Ale jeżeli miałby się przydać, to prawdopodobnie w sytuacji, gdy nasze VLF-y są zbyt duże.

Jeżeli np. każdy z VLF-ów był wielkości 500MB, a my chcielibyśmy by ich wielkość spadła do 64MB, to możemy zmniejszyć log do 2 VLF-ów, dołożyć kolejny plik dziennika i wykonać operację ponownie. W ten sposób sprawimy, że tylko jeden VLF będzie nieodpowiednich rozmiarów. Po wykonaniu tej operacji możemy zwiększyć pierwszy z plików tak by kolejne VLF-y miały po 64MB i na koniec skasować drugi, gdyż nie będzie on nam już potrzebny.

Podsumowanie

Ewidentnie możemy tak skonfigurować nasz plik logu transakcyjnego tak, aby zawierał tylko jeden VLF. Jednakże w tym celu musimy dodać drugi plik dziennika, gdyż SQL Server wymaga minimum dwóch VLF-ów na bazę (co niestety nie jest oficjalnie potwierdzone) 🙂

Reklamy

Ilość pików VLF a kopia logu transakcyjnego w SQL Server

Mój poprzedni wpis na temat plików VLF spowodował pewną dyskusję na temat wydajności przywracania kopii dziennika transakcyjnego a wielkości jego wirtualnych plików. Ponieważ czas wykonania restore logu zależy od jego konfiguracji obiecałem opisać ten problem 🙂

Dla tych z was, którzy są zainteresowani tematem polecam kliknięcie w tag VLF mojego blogu, gdzie można odnaleźć nieco (moim zdaniem) interesujących informacji związanych z wirtualnymi plikami dziennika 🙂

Wielkość plików VLF a zajętość logu transakcyjnego

Pierwszą sprawą na która należy zwrócić uwagę jest fakt, że im mniejsze są nasze plik VLF, tym więcej miejsca potrzebuje SQL Server na przechowywanie dziennika transakcji. Jest to związane z wewnętrzną budową logu oraz tym, że wydzielenie jej logicznej części zabiera nieco miejsca na dysku.

Jako przykład przeanalizujmy następującą sytuację:

Mamy do dyspozycji dwie bazy danych. Obie mają ten sam rozmiar pliku bazy (2500MB) oraz dziennika transakcji (6000MB). Pierwsza z nich, o nazwie TestA ma 24000 VLF-ów, natomiast druga – TestB – jedynie 16. Dla obu wyczyściłem log transakcyjny, tak aby nie zawierał zbędnych wpisów. Dla pewności możemy sprawdzić zajętość logów dla obu baz:

DBCC SQLPERF('LOGSPACE')

Na zrzucie ekranu przedstawionym powyżej można zaobserwować dość interesującą sytuację. Zajętość logu transakcyjnego dla bazy TestA wynosi 12,5%, a wiec dość sporo jak na fakt, że przed chwilą go wyczyściłem poprzez utworzenie kopii zapasowej (dodatkowo dodam, że w bazie nie ma żadnej otwartej transakcji).  Aby zbadać te dziwne zjawisko sprawdźmy aktualny minimalny i maksymalny numer transakcji dla tej bazy:

SELECT MIN([Current LSN]) AS MinLSN, MAX([Current LSN]) AS MaxLSN
FROM fn_dblog(NULL, NULL)

Na obrazku powyżej można zaobserwwać, że aktualnie log transakcyjny zawiera niewiele wpisów, co więcej wszystkie znajdują się w pliku VLF o numerze sekwencyjnym 57612 (E10C w systemie szesnastkowym). Dla pewności użyjmy jeszcze instrukcji DBCC LOGINFO:

DBCC LOGINFO('testA')

Obrazek powyżej potwierdza, że jedynym aktywnym VLF-em w tej bazie jest plik o numerze 57612 (jako jedyny ma status różny od zera). Zajętość logu, którą pokazała instrukcja DCBB SQLPERF(‚LOGSPACE’) wynosząca 12,5% nie wynika z wypełnienia dziennika transakcji przez aktywne wpisy

Sprawdźmy w ile zajmie kopia logu obu baz

Na początek wykonajmy pełną kopię zapasową:

BACKUP DATABASE testA
TO DISK = 'testA_FULL_20120304A.bak'
WITH CHECKSUM, FORMAT

BACKUP DATABASE testB
TO DISK = 'testB_FULL_20120304A.bak'
WITH CHECKSUM, FORMAT

Następnie wykonajmy kopie dzienników transakcji:

BACKUP LOG testA
TO DISK = 'testA_LOG_20120304A.bak'
WITH FORMAT

BACKUP LOG testB
TO DISK = 'testB_LOG_20120304A.bak'
WITH FORMAT

Jeżeli porównamy wielkość stworzonych w ten sposób kopii zauważymy dość sporą różnicę:

Plik kopii logu dla bazy testA zajmuje około 24MB, a wiec dosyć sporo jak na dziennik zawierający jeden aktywny VLF o wielkości 1/4 MB 🙂 Dla porównania zaznaczyłem także kopię dziennika transakcji bazy testB, która zajęła na dysku 97KB.

Sprawdźmy czasy odtworzenia 

Oczywiste jest, że wielkość kopii dziennika transakcji będzie miała wpływ na czas przywrócenia bazy. Chciałbym jednak pokazać jak duże znaczenie ma fakt, ile plików VLF zawierała kopia logu:

SET STATISTICS TIME ON
-- odzyskanie bazy z 24 000 VLF-ów
RESTORE DATABASE TestA_new
FROM DISK = 'testA_LOG_20120304A.bak'
WITH NORECOVERY

Na powyższym obrazku widać, że czas odzyskania dziennika transakcji nie zawierającego prawie żadnych żadnych wpisów związanych z operacjami na bazie wyniósł nieco ponad 128 sekund. Co więcej rozmiar kopii z której odzyskiwaliśmy tę bazę nie jest na tyle duży, aby czekać ponad 2 minuty. Nawet biorąc pod uwagę fakt, że testy były robione na moim komputerze domowym, to wydajność dzisiejszych dysków twardych nie jest aż tak zatrważająca…

Dla porównania przedstawiam wynik dla drugiej z baz:

Czas trwania operacji restore zabrał co najwyżej 0,5 sekundy.

Podsumowanie

Ilość plików VLF ma znaczenie podczas odtwarzania kopii zapasowych logu transakcyjnego. W przypadku gdy log transakcyjny jest podzielony na dużą ilości wirtualnych plików, to rozmiar kopii zapasowej zwiększa się, co ma wpływ na jej czas tworzenia. Odtwarzanie dziennika z takiego backupu również ulega wydłużeniu, przy czym wydłużenie to nie jest adekwatne do rozmiaru samej kopii (jest znacząco większe).  Dla przypomnienia dodam, że w testowanym przeze mnie przypadku restore „prawie pustej” kopii z jednym aktywnym VLF-em zajął ponad 2 minuty 🙂

Jak zmienić ilość/wielkość VLF dla istniejącej bazy danych

SQL Server nie pozwala bezpośrednio określić ilości i wielkości plików VLF, na które będzie podzielony dziennik transakcji. Znana jest natomiast zasada, wg. której SQL Server dobiera ilość wirtualnych plików dziennika podczas zmiany  jego rozmiaru:

1. Dla przyrostów poniżej 64MB – 4 VLF-y

2. Dla przyrostów powyżej 64MB i mniejszych niż 1GB – 8VLF-ów

3. Dla przyrostów powyżej 1GB – 16 VLF-ów

Konfiguracja dla nowej bazy

Jeżeli tworzymy nową bazę danych i chcemy aby jej dziennik posiadał preferowaną przez nas ilość VLF-ów, to powinniśmy już podczas tworzenia bazy określić wielkość logu transakcyjnego:

CREATE DATABASE testVLFa
ON PRIMARY (
 NAME = 'testVLF_data',
 FILENAME = 'c:\test\testVLF_data.mdf',
 SIZE = 250
)
LOG ON (
 NAME = 'testVLF_log',
 FILENAME = 'c:\test\testVLF_log.ldf',
 SIZE = 500, -- log będzie zawierał 8 VLF-ów
 FILEGROWTH = 500 --i będzie przyrastał także po 8 VLF
)

Dla sprawdzenia naszych rachunków możemy posłużyć się instrukcją:

DBCC LOGINFO('testVLF')

W stworzonej bazie zadeklarowaliśmy 8 plików VLF, po około 60 MB każdy.

Konfiguracja dla istniejącej bazy danych

Zmiana wielkości plików VLF istniejącej bazy danych sprowadza się do zmniejszenia wielkości logu transakcyjnego i jego rozszerzenia wg. podanego wcześniej wzoru. W przypadku gdybyśmy chcieli zmienić wielkość VLF-ów stworzonej wcześniej bazy na 120 MB, to musielibyśmy to zrobić w następujący sposób:

1. Kopia logu i zmniejszenie pliku dziennika 

BACKUP LOG testVLF
TO DISK = 'testVLF_LOG_20120226.trn'
GO
DBCC SHRINKFILE ('testVLF_log', 1)
GO

Dla sprawdzenia efektu wykonajmy instrukcję DBCC LOGINFO:

DBCC LOGINFO ('testVLF')

Na obrazku przedstawionym poniżej widać, że zostały dwa pliki VLF. Moglibyśmy posłużyć się dalszą kombinacją kopii logu transakcyjnego i instrukcji DBCC SHRINKFILE aby się pozbyć jednego z nich (Edit: Dokładny opis jak to zrobić zawiera post „Jak sprawić by plik logu transakcyjnego miał tylko 1 VLF”). Niezależnie od tego czy to zrobimy, czy też nie, to jeden plik VLF zawsze zostanie, gdyż każdy dziennik transakcji musi zawierać przynajmniej jeden VLF.

2. Zwiększamy rozmiar dziennika transakcji i tym samym ilość VLF-ów

ALTER DATABASE testVLF
MODIFY FILE  (
    NAME = 'testVLF_log',
    SIZE = 1100 MB -- 2 plik VLF po 60MB + około 980 MB przyrostu
)

Sprawdźmy rezultat:

DBCC LOGINFO ('testVLF')

Na obrazku powyżej zaznaczyłem nowe VLF-y. Jak widać jest ich 8 i mają rozmiar około 120MB.  Pozostałością po dawnych ustawieniach są dwa pierwsze wirtualne pliki dziennika, których wielkość wynosi nieco ponad 60MB.

Podsumowanie

Nie istnieje bezpośredni sposób zadeklarowania ilości wirtualnych plików dziennika transakcji. Można natomiast tak zadeklarować wielkość logu bazy danych, by uzyskać preferowaną przez nas wartość. W przypadku gdy zechcemy przekonfigurować istniejącą bazę danych, tak aby zmienić ilość (lub wielkość) VLF-ów w dzienniku transakcji, to musimy sie liczyć z tym, że zostanie przynajmniej jeden VLF pierwotnej wielkości.

Ilość VLF a wydajność SQL Server cz. 2

W moim pierwszym poście na temat Virtual Log File pokazałem w jak wewnętrzny podział logu na części może mieć wpływ na wydajność instrukcji zapisujących informacje w dzienniku transakcji. W podsumowaniu zaznaczyłem jednak, że duży rozmiar VLF ma także swoje ciemną stronę 🙂

Opowiem w takim razie o minusach …

Istotną wadą VLF o zbyt dużej objętości jest wydłużony czas wykonania zadań, które wymagają czytania logu transakcyjnego przez SQL Server. Jako przykład takiego zadania można przytoczyć tworzenie migawki bazy.

Przygotujmy środowisko do testów

Zaczniemy od małego przypomnienia. Na potrzeby poprzedniego wpisu stworzone zostały dwie bazy testowe… Pierwsza z nich nazywała się testA i jej log składał się z 24000 VLF-ów, druga, o nazwie testB posiadała ich jedynie 16. Obie bazy cechowały się takim samym rozmiarem dziennika transakcji, który wynosi 6000MB. Różniły się jedynie rozmiarem i ilością VLF-ów.

Wpiszmy do nich nieco rekordów:

-- tu jest dużo małych VLF-ów
USE testA
GO
SELECT *
INTO dbo.testLOG
FROM sys.messages
GO
-- a tu mało dużych :)
USE testB
GO
SELECT *
INTO dbo.testLOG
FROM sys.messages
GO

Następnie sprawdźmy ile plików VLF jest w tych bazach używanych lub zużytych. Aby tego dokonać możemy użyć instrukcji DBCC LOGINFO i zbadać zawartość kolumny „Status” dla wyświetlanego przez nią wyniku. Jeżeli jej wartość wynosi 2, to dany VLF jest aktualnie używany lub też został użyty w przeszłości (i nie została zrobiona kopia zapasowa tej części logu). Gdy kolumna „Status” jest równa 0, to dany VLF jest gotowy do ponownego użytku (w szczególności pierwszego).

DBCC LOGINFO ('testA')

DBCC LOGINFO ('testB')

Jak widać na przedstawionych powyżej obrazkach dla bazy testA zostało zużytych wiele VLF-ów, zaś dla bazy testB tylko jeden. Jak będzie wyglądać sytuacja po wykonaniu kopii zapasowej dziennika transakcji?

BACKUP LOG testA
TO DISK = 'testA_LOG_20120209.trn'
WITH FORMAT
BACKUP LOG testB
TO DISK = 'testB_LOG_20120209.trn'
WITH FORMAT
DBCC LOGINFO ('testA')
DBCC LOGINFO ('testB')

Wykonanie kopii zapasowej spowodowało dziennika spowodowało „wyczyszczenie” niepotrzebnych plików VLF. Teraz obie bazy mają po jednym VLF-ie ze statusem równym 2.

Czas na wykonanie migawek 🙂

Skoro przygotowaliśmy już podłoże do testów, to wykonajmy migawki obu baz danych:

CREATE DATABASE [snTestA]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'TestA',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.CTP3\MSSQL\DATA\TestA_snap.mdf'
)
AS SNAPSHOT OF testA

CREATE DATABASE [snTestB]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'TestB',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.CTP3\MSSQL\DATA\TestB_snap.mdf'
)
AS SNAPSHOT OF testB

Na obrazku powyżej można zaobserwować, że czas wykonania migawki dla drugiej z baz jest sporo większy. Stało się tak, gdyż podczas tworzenia snapshotu SQL Server musiał przeczytać odpowiednio większą część logu niż w przypadku bazy testA. Przypomnę, że obie bazy miały po jednym aktywnym VLF-ie, rozmiar wirtualnego pliku logu dla pierwszej z nich to 0,25MB, a dla drugiej około 375 MB 🙂

Dla ścisłości dodam, że SQL Server nie czytał pełnych 375MB dla bazy testB, a jedynie tę część, która była mu potrzebna do otrzymania spójnego obrazu dla migawki. Tak czy inaczej, na pewno przeczytał więcej niż 0,25MB przy bazie testA .

Podsumowanie

Przeprowadzony przeze mnie test potwierdza, że ilość plików VLF wewnątrz logu transakcyjnego ma znaczenie wydajnościowe. Zwiększając rozmiar VLF-ów należy liczyć się ze spowolnieniem zadań, które czytają dziennik transakcji. Dobierając ilość wirtualnych plików logu należy przeanalizować stosunek zysku i strat, tak aby dokonać optymalnych ustawień dla naszych instancji.

W kolejnym wpisie pokażę jak zmienić ilość VLF dla istniejącej bazy danych 🙂

Ilość VLF a wydajność SQL Server cz. 1

Jakiś czas temu na forum portalu wss.pl padło pytanie o optymalny rozmiar VLF (Virtual Log File). Niestety, z uwagi na to, że każde środowisko bazodanowe jest inne, to nie ma uniwersalnej odpowiedzi na tego typu pytanie. W kolejnych wpisach mojego blogu chciałbym pokazać czym są pliki VLF,  jakie czynniki należy wziąć pod uwagę dobierając wielkość wirtualnych plików logu do potrzeb konkretnej bazy oraz w jaki sposób sprawić by nasz log transakcyjny spełniał oczekiwania wydajnościowe.

Czym jest Virtual Log File?

Każda baza danych zarządzana przez SQL Server musi mieć przynajmniej jeden plik, na którym przechowywany jest jej log transakcyjny. Wewnętrzna struktura tego pliku jest podzielona na logicznie części, które noszą nazwę VLF (Virtual Log File).

Dzięki takiemu podziałowi SQL Server może wydajnie zarządzać logiem transakcyjnym bazy. Jedynym warunkiem jest odpowiedni dobór wielkości VLF 🙂

Jak sprawdzić ile mamy VLF-ów?

W tym celu powinniśmy posłużyć się nieudokumentowaną instrukcją DBCC LOGINFO, której jako parametr możemy podać nazwę interesującej nas bazy:

DBCC LOGINFO ('tempdb')

Na przedstawionym powyżej zrzucie ekranu można zobaczyć, że log transakcyjny wybranej bazy ma 8 VLF-ów, z czego każdy ma wielkość około 32MB.

Czas na test:  wstawianie dużych ilości danych 

Najlepszym sposobem aby zobrazować jaką różnicę wydajnościową można uzyskać dobierając odpowiednia ilość/wielkość VLF-ów jest przeprowadzenie odpowiednich testów. Do tego celu może nadać się dowolna operacja generująca znaczną ilość wpisów w dzienniku transakcji, np. duży INSERT.

Na potrzeby testu stworzyłem dwie bazy danych (testA i testB). Obie mają zadeklarowaną tę samą wielkość pliku baz (2500MB) oraz logu transakcyjnego (6000MB). Jedyną różnicą miedzy tymi nimi jest ilość VLF-ów, z którego składa się ich dziennik transakcji. TestA ma 24000 VLF-ów po około 250KB każdy, zaś testB 16 VLF po około 375MB.

Na początek wykonajmy kilkukrotnie zapytanie testowe:

-- niech dane do zapisu będą w buforze
SELECT m1.*
FROM sys.messages m1
CROSS JOIN (SELECT TOP 10 * FROM sys.messages) m2
GO 10

Różnicę w wydajności sprawdzimy wstawiając jego wynik do tabel w obu bazach danych:

-- sprawdzamy czas wykonania instrukcji przy małych VLF-ach
SET STATISTICS TIME ON

SELECT m1.*
INTO testA.dbo.tblMessages
FROM sys.messages m1
CROSS JOIN (SELECT TOP 10 * FROM sys.messages) m2

Dla bazy z małymi VLF-ami czas wykonania wynosi 13,731 sekundy. Zobaczmy jak poradzi sobie serwer przy insercie do drugiej bazy:

-- sprawdzamy czas wykonania instrukcji przy dużych VLF-ach
SET STATISTICS TIME ON

SELECT m1.*
INTO testB.dbo.tblMessages
FROM sys.messages m1
CROSS JOIN (SELECT TOP 10 * FROM sys.messages) m2

Gdy tę samą instrukcję wykonaliśmy zapisując dane do bazy o większych VLF-ach, to czas wykonania wsadu skrócił się do 12,732 sek. dając tym samym zysk wydajnościowy około 7 procent.

Podsumowanie

Ilość plików VLF wewnątrz logu transakcyjnego ma znaczenie wydajnościowe. Większe pliki wirtualne dziennika transakcji pozwalają na szybsze wykonanie operacji zapisujących, kasujących lub zmieniających dane.  Zanim jednak podejmiemy decyzję co do wielkości VLF-ów naszych baz produkcyjnych przeanalizujmy jakie wady niesie ze sobą zwiększenie ich rozmiaru:

Ilość VLF a wydajność SQL Server cz. 2