Przychodzi taki czas, kiedy należy wymienić sprzęt na nowy. Kupujemy nowy, wydajniejszy sprzęt i już cieszymy się, że nasz system oparty o bazę danych będzie działał dużo szybciej. Wykonujemy migrację bazy na nowy serwer i jest – system działa prawie 2 razy szybciej. Wszyscy są zadowoleni, pełen sukces.

Czy na pewno pełen sukces? Nie zawsze. Całkiem prawdopodobne, że dzięki dobrze wykonanej migracji moglibyśmy osiągnąć jeszcze więcej. Dzięki defragmentacji bazy możemy zmniejszyć rozmiar bazy danych (czasem nawet dwukrotnie) i przyspieszyć jej działanie. O ile przyspieszyć? To już zależy od konkretnej bazy danych, stopnie jej fragmentacji itp. Zazwyczaj przyspieszymy od kilkunastu do dwudziestu kilku procent, ale poszczególne procesy mogą przyspieszyć nawet kilkadziesiąt razy.

Co to jest fragmentacja?

Fragmentacja tabel

Wraz z przyrostem danych zwiększa się rozmiar tabel oraz indeksów na nich opartych. Jeżeli usuniemy część danych, rozmiar tabeli w bazie pozostanie taki sam, ale wewnątrz jej pojawi się wolna przestrzeń, która będzie mogła być uzupełniana nowymi danymi.

Jeżeli usuwanie danych odbywa się cyklicznie, np. przechowujemy historię operacji za ostatnie 5 lat i co miesiąc usuwamy dane starsze niż 5 lat, to rozmiar tabeli będzie pozostawał z grubsza taki sam (o ile nie będzie się zwiększała ilość operacji w miesiącu). Po usunięciu startych danych pojawi się ok. 1,3% wolnego miejsca, które będzie zapełniane, aż do kolejnego usunięcia niepotrzebnych danych.

Inaczej wygląda sytuacja, gdy jest to pojedyncza operacja, np. zdecydowaliśmy się zarchiwizować i usunąć wszystko, co starsze niż 3 lata, albo usunąć wszystkie transakcje klientów, z którymi nie współpracujemy od ponad roku. Wtedy usuwamy dużą część danych (30%? 50%?). Uzyskujemy w tabelach dużą ilość wolnej przestrzeni, która kiedyś (być może za kilka lat) zapełni się danymi, ale na razie niepotrzebnie zajmuje miejsce.

Tabele z dużą ilością „pustego miejsca” wpływają niekorzystnie na wydajność. Jak? Na kilka sposobów.

1. Pogarszają wykorzystanie cache-a bazodanowego (bufora bazodanowego)

Cache bazy danych to obszar pamięci, gdzie przechowywane są najczęściej wykorzystywane bloki/strony danych. Jeżeli baza potrzebuje odczytać dany blok/stronę, najpierw próbuje go znaleźć w cache-u, a gdy go nie znajdzie, wtedy odczytuje go z dysku. Operacje odczytu z dysku są nieporównanie wolniejsze od odczytu z pamięci, dlatego też im więcej danych znajduje się w cache-u, im większy współczynnik trafienia w bufor (procent danych odczytywanych z cache-u), tym szybciej działają zapytania w bazie.

Przyjmijmy, że usunęliśmy z tabel połowę danych i że dane były tak ułożone, że w każdym bloku/stronie danych zostało 50% danych. Oznacza to, że tylko połowa cache-a zawiera dane, druga połowa zawiera „puste powietrze”. Czyli efekt taki, jakbyśmy mieli niepofragmentowane dane i cache 2 razy mniejszy.

Gdybyśmy zdefragmentowali te tabele, osiągnęlibyśmy efekt taki sam jak dwukrotne zwiększenie wielkości bufora.

1. Wydłużają czas operacji typu full scan

Operacje typu full scan są ogólnie najmniej wydajnym sposobem dotarcia do danych i są raczej niepożądane w bazie. Full scany są raczej niepożądane w bazie, ale są pewne typy działań, gdzie to one są najszybsze. Chodzi przede wszystkim o zapytania SQL, gdzie odczytujemy lub łączymy ze sobą pełną zawartość tabel, np. raport o wszystkich transakcjach (bez ograniczeń, np. czasowych lub wybranych klientów), zestawienie wszystkich klientów, miesięczne zestawienia sprzedaży  od początku działania firmy.

Full scan odczytuje wszystkie bloki tabeli, które były wykorzystywane, nawet jeżeli aktualnie są puste. Jeżeli mieliśmy tabelę wielkości 50GB ze 100 milionami rekordów, usunęliśmy z niej wszystkie rekordy poza jednym, i nie wykonaliśmy defragmentacji, to full scan odczyta pełne 50GB (choć dane są faktycznie w jednym bloku).

Fragmentacja indeksów

Indeksy to nic innego jak uporządkowane wskaźniki do odpowiednich rekordów w tabeli. Ich głównym zadaniem jest przyspieszenie dotarcia do wybranego, konkretnego rekordu.

Najczęściej wykorzystywany jest indeks typu b-tree i na jego przykładzie omówię zjawisko fragmentacji.

Aby dotrzeć (odczytać) konkretny rekord w tabeli odczytywane jest tzw. drzewo indeksu – bloki gałęzi i bloki liści. W większości przypadków, aby dotrzeć do konkretnego rekordu w tabeli wystarczy odczyt 2 lub 3 bloków indeksu oraz docelowy blok w tabeli. Blok tabeli musimy odczytać – tam tkwią dane. Natomiast im mniej bloków indeksu będziemy musieli odczytać, tym szybciej i z mniejszą ilością operacji odczytu dotrzemy do danych.

Block level, czyli ile bloków indeksu będziemy musieli odczytać, aby dostać się do pojedynczego rekordu, zależy od ilości rekordów, o których „pamięta” indeks.

Wyobraźmy sobie indeks oparty na numerze kolejnym transakcji (ID). Każda nowa transakcja ma numer o jeden wyższy. Indeks to struktura uporządkowana, w związku z tym dodanie nowego rekordu o nowym wyższym ID spowoduje, że zostanie on zapisany na końcu indeksu, niezależnie od tego, czy poprzednie bloki zawierają jakiekolwiek dane.

W przypadku cyklicznego (np. comiesięcznego) usuwania starych danych tabela zachowa stały rozmiar, natomiast indeks opisany powyżej będzie ciągle rósł. Może się okazać, że po pewnym czasie indeks będzie kilkukrotnie większy niż sama tabela.

Indeksy rosną nie tylko wskutek cyklicznego usuwania danych. Rosną także w przypadku zmiany danych. Jeżeli następuje zmiana danych w rekordzie (najczęściej będą to dane typu STATUS, Data ostatniej operacji, Ostatni modyfikujący itp.) to indeks oparty na tych polach będzie coraz większy.

Przyrost wielkości indeksu to zazwyczaj także zwiększenie block level, czyli zwiększenie ilości bloków indeksu, które muszą być odczytane. A co za tym idzie spowolnienie dostępu do danych w tabeli. Zwiększenie block level z 2 do 3 oznacza, że aby odczytać dany rekord musimy przeczytać nie 3 a 4 bloki, czyli 25%-owy spadek wydajności w użyciu tego indeksu.

W pewnych sytuacjach następuje także full scan indeksu, analogicznie jak full scan tabeli, odczytywane są wtedy wszystkie bloki, które były kiedyś użyte, nawet te aktualnie puste. I analogicznie jak w przykładzie z tabelą, nawet jeżeli ze 100 mln rekordów pozostał nam tylko jeden rekord, to full scan indeksu odczyta dokładnie wszystkie bloki indeksu.

Fragmentacja bazy

Zdarza się, że w bazie usuwane są jakieś obiekty. Np. tworzymy kopię danych, na których chcemy coś przetestować, a następnie ją usuwamy. Albo też wykonaliśmy defragmentację tabel / indeksów, dzięki czemu zmniejszyliśmy ich rozmiar.

Po ich usunięciu zostaje wolna przestrzeń, która może być w przyszłości wykorzystywana przez inne tabele/indeksy.

Fragmentacja bazy nie wpływa na wydajność pracy bazy, ale powoduje, że baza zajmuje więcej miejsca i może wydłużyć czas trwania backup-u.

W kolejnym artykule opiszę dlaczego defragmentacja nie wykonuje się samoczynnie, opiszę jakie korzyści można osiągnąć dzięki defragmentacji, oraz wyjaśnię, dlaczego bardzo często pomija się defragmentację przy okazji migracji na nowy serwer.

Jeżeli kogoś zainteresował ten temat i chciałby się dowiedzieć czegoś więcej, zachęcam do kontaktu: blazej.pastuszka@commit-it.pl