Projektowanie, Programowanie, Codzienność – BeniaminZaborski.com

28 Grudzień 2014

SQL Server – optymalne rozmieszczenie plików

Filed under: How Can I Take ... — Tags: , , , — Beniamin Zaborski @ 22:10

Utworzenie nowej bazy danych w SQL Server zapewni nam poniższe polecenie języka T-SQL:

CREATE DATABASE ENT_PROJECT
GO

Co tak naprawdę się stanie po wykonaniu tego polecenia? Zanim coś więcej o tym, to wcześniej należy wspomnieć o sposobie przechowywania danych na dysku przez SQL Server.

SQL Server mapuje bazy danych na zestaw plików przechowywanych w systemie plików systemu operacyjnego. Zapisywane są dwa rodzaje danych: właściwe dane oraz log transakcji. Dane i log nigdy nie są mieszane w jednym pliku – zawsze znajdują się w osobnych plikach. Jeden plik zawsze odnosi się tylko do jednej bazy danych – nigdy nie zawiera danych czy informacji z kilku baz.

SQL Server posiada zasadniczo trzy typy plików:

Primary data files – zawiera dane oraz wskazania na inne pliki bazy danych, istnieje tylko jeden taki plik na bazę danych, posiada rozszerzenie mdf.
Secondary data files – zawiera dane, może istnieć wiele plików tego typu na bazę danych, posiada rozszerzenie ndf.
Log files – zawiera log transakcji potrzebny do odtworzenia bazy danych, musi być przynajmniej jeden taki plik na bazę danych ale może być ich więcej, posiada rozszerzenie ldf.

Wskazane rozszerzania plików są jedynie rozszerzeniami sugerowanymi i możemy użyć innych jeśli mamy taką potrzebę. Oprócz samych plików SQL Server wprowadza także pojęcie filegroups, które są nazwanymi grupami plików i są tworzone w celu ułatwienia zarządzania bazą danych. Możemy wyróżnić dwa rodzaje grup plików:

Primary – zawiera pliki typu primary data files
User-definied – opcjonalnie utworzone przez użytkownika, zawierają dodatkowe pliki danych utworzone przez użytkownika.

Grupy plików zawierają a raczej grupują jedynie pliki danych. Pliki logów transakcji nie są przechowywane w grupach plików. Dodać należy, że w grupie Primary będą przechowywane wszystkie obiekty systemowe bazy danych (oczywiście pośrednio, gdyż de facto znajdują się one w plikach danych).

Wróćmy do przykładu polecenia T-SQL tworzącego bazę danych. Nie wyspecyfikowałem w nim żadnych informacji odnośnie plików czy grup plików podczas tworzenia bazy danych, więc został przyjęty model domyślny. Model domyślny oznacza jedną grupę plików PRIMARY z jednym plikiem danych oraz jeden plik logów (oczywiście poza jakąkolwiek grupą). Wszystkie moje dane jak i dane systemowe będą przechowywane w jednym pliku danych – primary.

W niewielkich bazach danych czy w bazach które nie są zbyt intensywnie wykorzystywane (niewielka ilość transakcji na sekundę) taka konfiguracja może być OK. Co innego gdy mamy do czynienia z dużym dedykowanym systemem klasy enterprise. W takim wypadku wskazane jest utworzyć kilka grup plików. Nasuwają się pytania: ile utworzyć grup plików, jak je optymalnie rozmieścić? Istnieje kilka dobrych praktyk i postaram się te znane mi przytoczyć.

Po pierwsze musimy być świadomi ilości dysków/kontrolerów dyskowych naszego serwera, a także ilości rdzeni CPU. Jeśli mamy już taką wiedzę weźmy pod uwagę poniższe:

– Pliki logów powinny być na osobnym dysku, szybkim dysku, gdyż do nich odbywa się intensywny zapis. Dostęp do plików logów jest sekwencyjny więc nie ma większego sensu tworzyć więcej niż jednego pliku logów. Jedynym powodem do tego może być sytuacja gdy skończy się przestrzeń na dysku.

– Warto utworzyć tyle plików danych/grup plików ile dysków pomniejszając liczbę o dyski na pliki logów (w najprostszym przypadku o 1).

– Dobrym pomysłem jest odseparowanie danych systemowych od całej reszty. Uzyskamy to poprzez oznaczenie którejś z grup plików utworzonej przez użytkownika jako domyślnej (innej niż primary która standardowo jest domyślną).

– Pozytywnie na wydajność wpłynie utworzenie kilku plików dla bazy tempdb która jest intensywnie wykorzystywana. Jedna z zasad mówi, że plików danych powinno być tyle ile rdzeni CPU naszego serwera, a plików logów tyle ile fizycznych dysków (co stanowi wyjątek od tego co napisałem w punkcie pierwszym).

Utworzenie odpowiedniej ilości plików danych niejako determinuje nam konfiguracja sprzętowa, ale to tylko jedna strona medalu. Jeśli utworzyliśmy wiele grup plików to musimy odpowiednio rozmieścić nasze obiekty bazodanowe we właściwych grupach. Służy nam do tego słowo kluczowe ON filegroup. Zatem np. w poleceniu T-SQL do tworzenia tabeli CREATE TABLE możemy wskazać w której grupie plików nasza tabela będzie przechowywana. Jeśli nie wskażemy tej lokalizacji to będzie przyjęta domyślna czyli grupa plików z ustawionym znacznikiem DEFAULT. Optymalne rozmieszczenie tabel czy indeksów w określonych grupach plików zależy już mocno od specyfiki samego systemu jaki tworzymy. Na pewno nie jest to łatwe zadanie, ale kierować się można poniższymi wskazówkami:

– Warto myśleć w kategoriach zrównoleglenia wykonywanych operacji IO przez system bazodanowy versus obszary funkcjonalne naszej aplikacji.

– Dobrym pomysłem będzie umieszczenie indeksów tabeli jak i samej tabeli w różnych grupach plików. Inne podejście jest jeszcze bardziej radykalne i mówi o odseparowaniu od siebie także indeksów klastrowych i nieklastrowych.

 

Uzbrojeni w tę wiedzę, dysponując serwerem z pięcioma dyskami (C, D, E, F, G) i jednym cztero-rdzeniowym procesorem moglibyśmy utworzyć bazę danych np. w taki sposób:

CREATE DATABASE ENT_PROJECT
ON PRIMARY
(
NAME = 'ENT_PROJECT0_dat',
FILENAME = 'C:\DBs\ENT_PROJECT0_dat.mdf'
),
FILEGROUP G1
(
NAME = 'ENT_PROJECT1_dat',
FILENAME = 'E:\DBs\ENT_PROJECT1_dat.ndf'
),
FILEGROUP G2
(
NAME = 'ENT_PROJECT2_dat',
FILENAME = 'F:\DBs\ENT_PROJECT2_dat.ndf'
),
FILEGROUP G3
(
NAME = 'ENT_PROJECT3_dat',
FILENAME = 'G:\DBs\ENT_PROJECT3_dat.ndf'
)
LOG ON
(
NAME = 'ENT_PROJECT_log',
FILENAME = 'D:\DBs\ENT_PROJECT_log.ldf'
)
GO

ALTER DATABASE ENT_PROJECT
MODIFY FILEGROUP G1 DEFAULT
GO

ALTER DATABASE tempdb 
ADD FILE 
(
	NAME = 'tempdev1',
	FILENAME = 'D:\DBs\tempdb1.ndf'
),
(
	NAME = 'tempdev2',
	FILENAME = 'E:\DBs\tempdb2.ndf'
),
(
	NAME = 'tempdev3',
	FILENAME = 'F:\DBs\tempdb3.ndf'
)
GO

ALTER DATABASE tempdb 
ADD LOG FILE 
(
	NAME = 'templog1',
	FILENAME = 'D:\DBs\templog1.ldf'
),
(
	NAME = 'templog2',
	FILENAME = 'E:\DBs\templog2.ldf'
),
(
	NAME = 'templog3',
	FILENAME = 'F:\DBs\templog3.ldf'
),
(
	NAME = 'templog4',
	FILENAME = 'G:\DBs\templog4.ldf'
)
GO

Przykład zakłada że w każdej z grup istnieje tylko jeden plik danych. Jednakże spełnia nasze założenia, tj. każdy plik jest na osobnym dysku, grupa primary nie jest domyślną grupą, baza tempdb posiada kilka plików. Celowo pominąłem w przykładzie T-SQL kwestię rozmiaru plików bazy danych, aby skupić się tylko na samym rozmieszczeniu plików na dyskach. Nie oznacza, to że jest to mało istotna rzecz z punktu widzenia wydajności naszej bazy danych. Wręcz przeciwnie. Warto oszacować rozmiar naszej bazy i jej miesięczny przyrost. SQL Server potrafi zarezerwować w systemie plików wskazaną przestrzeń na dane by nie walczyć o nią gdy jej zabraknie. Z drugiej strony, dzięki funkcji autogrowth, SQL Server może powiększyć „zaalokowaną” przestrzeń dyskową gdy zacznie jej brakować. Należy jednak pamiętać, że to dość kosztowna operacja i nie powinna być wykonywana za często, gdyż wtedy potrafi rozłożyć całą maszynę na łopatki.

Teraz możemy utworzyć tabelę oraz indeks we wskazanej grupie plików:

CREATE TABLE USERS (
USER_ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
LOGIN VARCHAR(20) NOT NULL,
IS_ACTIVE BIT NOT NULL DEFAULT(1)
)
ON G2
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_USERS_LOGIN
ON USERS(LOGIN ASC)
ON G3
GO

Jak widać tabela zostanie utworzona w grupie G2, a indeks nieklastrowy na tej tabeli zostanie utworzony w grupie G3.

Rozmieszczenie tabel w poszczególnych grupach plików należy szczególnie przemyśleć, gdyż po utworzeniu nie da się ich przenieść do innych grup plików.

Przedstawiłem kilka znanych mi reguł, ale pamiętajmy że są to jedynie suche reguły! Przed zaaplikowaniem ich na własnym środowisku należy spojrzeć na nie przez pryzmat własnego systemu.

Dodaj komentarz »

Brak komentarzy.

RSS feed for comments on this post. TrackBack URI

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s

Blog na WordPress.com.

%d bloggers like this: