Rzut okiem w log transakcyjny

6-Mar-2012

Log transakcyjny składa się z Virtual Log Files. Podobnie jak właściwa baza danych składa się ze stron i extentów, tak log transakcyjny składa się z VLF. Zaraz po utworzeniu bazy danych (i loga) można sprawdzić ile i jakich VLF zostało utworzonych:

CREATE DATABASE x
ALTER DATABASE x SET RECOVERY FULL
DBCC LOGINFO (x)

W tym przypadku plik log ma 2 VLF. Utwórzmy teraz dużo transakcji:

CREATE TABLE x
(x char(8000))
GO
 
INSERT x VALUES (‚x’)
GO 1000
 
DBCC LOGINFO (x)

Po takiej czynności log tranakcyjny musiał urosnąć. Tutaj ma już 3 kawałki.  Jak widać, jeżeli często plik loga musi się rozrastać, będzie się składał z dużej liczby małych VLF. A to nie dobrze…

Zobaczmy to również z drugiej strony. Utworzymy bazę danych, której log będzie miał rozmiar od razu 1 GB:

CREATE DATABASE x2 ON PRIMARY
( NAME = ‚x2′, FILENAME = N’C:\temp\x2.mdf’ , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = ‚x2_log’, FILENAME = N’C:\temp\x2_log.ldf’ , SIZE = 1024MB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Ile jest VLF w pliku LOG-a?

DBCC LOGINFO (x2) 

8 kawałków. Na dodatek kawałki te są stosunkowo duże. Skoro log jest duży, to wydaje się to rozsądne. Jaki mamy wpływ na zmienianie plików log-a i jego podziału na VLF? Wszystko zależy od tego jak duży jest log transakcyjny tworzony za pierwszym razem:

  • <=64 MB utworzonych zostanie do 4 VLF
  • <=1 GB, 8 VLF
  • >1GB, 16 VLF

Jeżeli wydasz polecenie skurczenia w miarę pustego pliku log-a:

DBCC SHRINKFILE (N’x2_log’ , 1)
DBCC LOGINFO (x2)

To zostaną już tylko 2 kawałki.

Wnioski:

  1. Jeżeli pracujesz z wielką bazą danych, utwórz od razu wielkiego loga. Będzie podzielony na mniejszą ilość większych VLF.
  2. Jeżeli zechcesz takiego wielkiego loga skurczyć, to może się to nie udać, bo log będzie mógł być skurczony tylko do rozmiaru jednego z VLF.
  3. Jeżeli pracujesz z wielką bazą danych i nie posłuchałeś rady nr 1, więc masz dużo maleńkich VLF, to po zbackupowaniu log-a, zmniejsz rozmiar loga, a potem ręcznie powiększ loga do dużego rozmiaru. W efekcie zostanie jeden maleńki VLF i reszta dużych.

Zbyt duża liczba małych VLF to tzw. fragmentacja VLF. Ilość ok 100-200 części jest za duża! Jeżeli log transakcyjny powiększa się automatycznie, to dobrze by było, aby rozmiar tworzonych VLF nie był większy niż 500 MB (łatwiej zwalniać miejsce z takiego kawałka po backupie loga). Powiększając loga o 16 GB za jednym razem tworzysz 16 VLF po 1GB każdy, więc są one za duże. Powiększając o 16 GB w dwóch krokach, tworzysz 32 VLF po 500 MB każdy. Stąd też zalecenie, by duże logi powiększać np tylko o 4 GB w jednym kroku.

Pamiętaj o defragmentacji dysku na którym znajduje się log (przy wyłączonym SQL Serwerze), rozdzieleniu danych od loga, a nawet logów od siebie na różne fizyczne dyski, nadawaniu odpowiedniego rozmiaru plikowi loga już na początku, dbaj o wydajny dysk (RAID 1 lub 10), wykonuj częste backupy loga(zwalniasz miejsce i wykonując coś częściej nie czynisz z tego ciężkiego zadania), korzystaj z operacji o minimalnym logowaniu (BULK LOGGED), wyłącz mirroring lub replikację transakcyjną jeśli jej nie używasz (i po prostu wisi…)

 Więcej ciekawostek na ten temat:

http://bradmcgehee.com/wp-content/uploads/presentations/St%20Louis_Inside%20the%20SQL%20Server%20Transaction%20Log.pdf

http://www.sqlservercentral.com/blogs/aloha_dba/2012/02/27/most-dbas-dont-seem-to-know-that-transaction-logs-can-be-tuned/

Dodaj komentarz:

Autor: Rafał Kraik