Nowości w mechanizmie składowania danych SQL Server 2008 – mechanizm kompresji danych

Autor: Marcin Goł

Opublikowano: 28 sierpnia 2008

Zawartość strony

	Kompresja wierszy (ang. row compression)
	Kompresja stron (ang. page compression)[iii]
	Kompresja stron – podsumowanie
	Kompresja danych – podsumowanie

Wydajność serwerów baz danych w największym stopniu ograniczana jest przez podsystemy dyskowe. Jednym ze sposobów ich odciążenia jest kompresja danych. Zależność jest dość prosta: skompresowane dane zajmują mniej miejsca, a skoro zajmują mniej miejsca, to ich odczytanie zajmuje mniej zasobów. Niniejszy artykuł poświęcony jest funkcjonalności kompresji danych wprowadzonej w Microsoft SQL Server w wersji 2008 CTP6.

Kompresja wierszy (ang. row compression)

Kompresja wierszy opiera się na typach danych (sposobie zapisu wartości) i polega na przechowywaniu danych w słowach o zmiennej długości. Przykładem ilustrującym to zagadnienie może być sytuacja, w której w polu typu int chcemy przechować wartość 1. Liczba ta mieści się na jednym bajcie, a pozostałe 3 bajty nie są używane (tzn. są oszczędzane).

Kompresja wierszy jest mechanizmem, którego początki zostały wprowadzone już w SQL Server 2005 wraz z dodatkiem Service Pack 2. Wówczas został udostępniony format przechowywania danych dla typów decimal i numeric - vardecimal, który przechowuje dane w słowach o zmiennej długości. Minusem tego rozwiązania jest fakt, że jest ono ograniczone do wersji serwera w wersji Enterprise oraz Developer[i] oraz tylko do 2 typów danych. Wprowadzenie nowej reprezentacji miało widoczny wpływ na kolumny definiowane jako tzw. szerokie np.: decimal(38,6). Wiele tego rodzaju pól zawierają bazy danych oprogramowania SAP, więc kompresja dla tych tabel jest naprawdę duża.

Dostarczając narzędzie do kompresji danych firma Microsoft dodała udostępniła również specjalizowaną metodę:

master.dbo.sp_estimate_rowsize_reduction_for_vardecimal ‘tablename’

która pozwala łatwo estymować ilość zaoszczędzonego miejsca. Niestety ale powyższa procedura składowana nie jest uniwersalna, za jej pomocą możemy próbować przewidzieć stopień kompresji tylko dla typów korzystających z formatu vardecimal.Wraz z wprowadzeniem produktu SQL Server w wersji 2008 postanowiono rozszerzyć zakres funkcjonalności kompresji na inne typy danych, zaliczają się do nich: bigint, binary, bit, char, datetime, datetime2, datetimeoffset, decimal, float, int, money, char, numeric, real, smallint, smallmoney, timestamp/rowversion.

Jako przykład podam zaprezentowaną poniżej implementację przechowywania informacji dla danych typu datetime:

  •  typ oparty jest na dwóch liczbach typu int (długość 2x 4bajty);

  •  pierwsza wartość oznacza liczbę dni od 1 stycznia 1900 roku – przy czym 2 pierwsze bajty zawierają daty do roku 2079;

      •  jeśli nie używamy dat późniejszych niż rok 2079 zawsze oszczędzamy 2 bajty;

  •  drugi bajt jest wykorzystywany do przechowywania czasu;

  •  pierwsze dwa bajty są wykorzystywane w godzinach przed 16:00;

      •  po 16 wykorzystywany jest również 4 bajt.

Podsumowując:

Kompresja typu datetime może oszczędzić do 4 bajtów (50% długości), ale zależny to od danych, które podlegają kompresji. Zakładając, że typ datetime byłby kompresowany jak „zwykły” integer, to można zaoszczędzić 2 bajty, co daje zysk 25%[ii].

Inne istotne uwagi o kompresji wierszy to:

  •  wartości null oraz 0 nie są w ogóle przechowywane (dotyczy wszystkich typów danych);

  •  dla kolumn nie dłuższych niż 8 bajtów został wprowadzony nowy format zapisu wiersza – zarządzanie offsetami, zajmuje wówczas 4 bity (w pozostałych przypadkach są to 2 bajty).

W celu włączenia kompresji wierszy należy użyć opcji:

create table <nazwa_tabeli> (<definicja>) with (data_compression=row)

podczas tworzenia tabeli. Natomiast, jeśli tabela juz istnieje, to należy wykonać polecenie:

alter table <nazwa_tabeli> rebuild with (data_compression=row)

Skoro można kompresować dane to można również kompresować indeksy. Proces włączania kompresji dla indeksu jest dokładnie taki sam jak dla tabeli. Istnieją następujące zależności pomiędzy tabelami, indeksami a kompresją:

  •  jeśli na tabeli z indeksem zgrupowanym włączana jest kompresja, indeks otrzymuje takie sam rodzaj kompresji;

  •  jeśli na tabeli jest włączona kompresja, a następnie tworzymy na niej indeks zgrupowany, to dziedziczy on ustawienia kompresji z tabeli (chyba że zostało wyspecyfikowane inaczej);

  •  indeksy niezgrupowane nie dziedziczą ustawień kompresji po tabeli, na której są tworzone

Podobnie, jak ma to miejsce w przypadku mechanizmu w SQL Server 2005, który opisano powyżej, również w nowej wersji produktu zawarto procedury służące estymowaniu zajętości powierzchni dyskowej przez skompresowane obiekty. Podstawowe wywołanie takiej procedury wygląda jak na listingu poniżej:

exec sp_estimate_data_compression_savings 'dbo', 'moja_tabela', NULL, NULL, 'ROW';

uogólniając:

exec sp_estimate_data_compression_savings 

'<schemat>', '<tabela>', <id indeksu>, <nr partycji>, '<typ kompresji>';

Wykonanie takiego zapytania dla tabeli Sales.SalesOrderDetail w bazie danych AdventureWorks2008 dało wyniki wyniki jak na Rysunku 1.

Rysunek 1: Wyniki estymacji stopnia kompresji.

Alternatywą dla włączania mechanizmu kompresji z poziomu kodu T-SQL jest możliwość skorzystania z konsoli SSMS. Aby to zrobić należy postępować zgodnie z poniższą instrukcją:

  1.  Wywołać menu kontekstowego na obiekcie, jaki chcemy kompresować. Następnie wybór opcji Storage a w kolejnym menu Manage Compression. Spowoduje to uruchomienie kreatora odpowiadającego za zarządzania kompresją obiektów (poszczególne kroki przedstawia Rysunek 2).

Rysunek 2: Uruchamianie kreatora odpowiedzialnego za kompresję danych.

  2.  Standardowy ekran powitalny kreatora

Rysunek 3: Ekran powitalny kreatora.

  3.  Wybór typów kompresji dla poszczególnych partycji, w przykładzie z Rysunku 4 zastosowano opcje Row Compression dla wszystkich partycji tabeli.

Rysunek 4: Wybór formy kompresji oraz obiektów jakie zostaną jej poddane.

  4.  Wybór formy wdrożenia

Rysunek 5: Okno umożliwiące wybór sposobu wdrożenia kompresji.

  5.  Podsumowanie czynności jakie zostały przeprowadzone

Rysunek 6: Okno podsumowania.

  6.  Raport z wykonania poszczególnych kroków przedstawiono na Rysunku 7.

Rysunek 7: Raport wykonania kreatora.

  7.  Ponieważ jako formę wdrożenia wybrano skrypt w zakładce SSMS efekt pracy kreatora jest widoczny w formie z Rysunku 8.:

Rysunek 8: Wynik pracy kreatora kompresji.

Jak przedstawiono powyżej proces włączania kompresji jest bardzo prosty i intuicyjny. Może podobać się również praktyka raportów wykonania, co jest szczególnie istotne w przypadku przeprowadzania bardziej skomplikowanych i trudniejszych operacji.

Na zakończenie tego rozdziału warto zwrócić uwagę, że firma Microsoft ma jednoznaczną wizję rozwoju produktu związaną z rozszerzaniem zakresu funkcjonalności oraz porządkowaniem stanu obecnego, np.: zostały wyniesione opcje dotyczące mechanizmu składowania danych na najwyższe poziomy menu/okien. Niestety w tym przypadku zaowocowało to zubożeniem informacji w oknie Właściwości tabeli:

Rysunek 9: Okna Właściwości tabeli z SQL Server 2008.

W SQL Server 2008 aby dowiedzieć się, ile miejsca zajmuje dana tabela oraz ile ma wierszy, musimy jednak wybrać opcję „Storage” z menu okna właściwości obiektu i dopiero wówczas uzyskamy potrzebne informacje:

Rysunek 10: Zakładka Storage okna Właściwości tabeli.

 Do początku strony

Kompresja stron (ang. page compression)[iii]

Kompresja stron jest mechanizmem, który jest niezależny od typu danych (ang. type agnostic) i opiera się całkowicie na powtarzających się ciągach bitów. Co ciekawe, ze względów wydajnościowych ograniczono się tylko do powtarzających się na początku wartości w danym polu, np.: dla danych 0x0945FFAA oraz 0x0945FBAA kompresowany będzie początkowe 0x0945 i pomimo, że na końcu jest jeszcze część wspólna w postaci 0xAA, to nie zostanie ona uwzględniona. Decyzja o takiej konstrukcji kompresji została podjęta ze względu na próbę ograniczenia negatywnego wpływu na procesory serwera baz danych.

Proces kompresji stron można podzielić na dwie fazy:

  •  Kompresję prefiksów (ang. column-prefix compression)

  •  Kompresję słownikową (ang. dictionary compression)

Obydwa procesy postępują niezależne od siebie, ale kompresja słownikowa korzysta ze stron skompresowanych w pierwszym etapie.

Kompresja prefiksów

Dane, które są potrzebne na tym etapie kompresji, przechowywane są w nowym rekordzie nazwanym Anchor Record (ang. anchor - kotwica, AR). Rekord ten – pod względem logicznym – jest przechowywany w tym samym schemacie, co tablica lub indeks, której dotyczy. Fizycznie znajduje się za nagłówkiem na stronie danych. Jeśli żadna z kolumn nie została skompresowana, to rekord AR nie jest tworzony. Poniżej przedstawiono kolejne etapy tworzenia tego rekordu.

  1.  Sytuacja początkowa, strona danych jest zapełniona i należy przeprowadzić jej kompresję (wygląd wewnętrzny strony został uproszczony). Jak widać na stronie ( Rysunek 11 ) danych znajdują się 3 wiersze i każdy ma 3 kolumny.

Rysunek 11: Kompresja prefiksów – stan początkowy.

  2.  Mechanizm kompresji prefiksów wyszukuje najdłuższe wspólne prefiksy dla poszczególnych kolumn, a po ich znalezieniu wartość pola z najdłuższym wspólnym prefiksem umieszczana jest w rekordzie AR na odpowiedniej pozycji dla każdej z kolumn.

Przykładowo — dla kolumny pierwszej wspólny prefiks to AA, a ponieważ najdłuższy pasujący do niego ciąg znaków znajduje się we wierszu pierwszym, to jest on przepisywany do rekordu AR (kotwicy). Warto tutaj zwrócić od razu uwagę, że wiersze 1 i 3 mają dłuższą część wspólną a jest nią AAACCCCC. Powyższe operacje zostały przedstawione na Rysunku 12.

Rysunek 12: Drugi etap kompresji prefiksów, przepisanie danych do rekordu AR.

  3.  Ostatnim krokiem jest dopasowanie wzorca z rekordu kotwicy (AR) do danych w wierszach, np.: dla pierwszej kolumny kotwica ma wartość AAACCCC, a ponieważ odpowiada to wartości z pierwszego wiersza, to ciąg w nim zapisany zostaje usunięty (zostaje pozostawiona wartość null); w przypadku drugiego wiersza część wspólna to AA. W związku z powyższym wpis w wierszu może zostać ograniczony do offsetu wpisu z rekordu AR oraz brakującej części wartości (efekt końcowy: 2BBB). Należy zwrócić uwagę na to, że jeśli rekord nie powinien nic pobrać z rekordu kotwicy, to na jego początku jest umieszczana odpowiednia informacja – jest to „0” (np.: trzecia kolumna, trzeci wiersz na Rysunku 13).

Rysunek 13: Wynik kompresji prefiksów.

Kompresja słownikowa

Drugim etapem kompresji stron jest kompresja słownikowa, która różni się od pierwszej części głównie tym, że wyszukuje powtarzające się wzorców na całej stronie, a nie tylko w poszczególnych kolumnach. Dodatkowo, kompresji słownikowej podlegają całe ciągi bitów a nie ich fragmenty.

Algorytm przeprowadzania kompresji słownikowej zaprezentowano poniżej.

Rysunek 14: Sytuacja wyjściowa, strona po kompresji prefiksów.

  1.  Tworzony jest słownik powtarzających się wartości, podobnie jak rekord AR znajdzie się on w nagłówku strony. W przykładzie powtarzającymi się wpisami są 0DFBVVA, 4BB. Dzięki temu, że najpierw przeprowadzono kompresję prefiksów to system ma do dyspozycji wpis w słowniku w postaci 4BB ,ale gdyby prefiksy pozostały w stanie pierwotnym, to nie możnaby danych skompresować (obrazuje to Rysunek 15).

Rysunek 15: Utworzenie słownika z powtarzającymi się wartościami.

  2.  Ostatnim krokiem jest przepisanie do wierszy w miejsce powtarzających się wartości identyfikatorów ze słownika. Efekt tego działania przedstawiono na poniższym rysunku.

Rysunek 16: Wynik kompresji stron.

 Do początku strony

Kompresja stron – podsumowanie

Aby uzmysłowić lepiej, jak bardzo mechanizmy kompresji wpływają na obciążenie procesorów na serwerze baz danych, zaprezentowano poniżej krok po kroku czynności potrzebne do odczytania 3 wiersza skompresowanej wcześniej strony.

  1.  Pobranie zawartości z 3 wiersza;

  2.  W danych z wiersza nr 3, SQL Server odczytuje identyfikatory 0 i 1 – będące wskazaniami na słownik kompresji;

  3.  Wartości otrzymanego identyfikatorów zastępowano są ich wartościami, odpowiednio 0DFBVVA i 4BB;

  4.  Dalsza obróbka wiersza wskazuje, że na początku wartości zostały umieszczone offsety kompresji prefiksów. Wartość "0" w ciągu 0DFBVVA oznacza, że wartością jest "DFBVVA"; w przypadku 4BB doklejane są pierwsze 4 znaki z pola zapisanego w rekordzie AR;

  5.  Do dalszej obróbki poza Mechanizmem składowania danych przekazywany jest wiersz w postaci: AAACBB, DFBVVA, DFBVVA.

Należy przy tym pamiętać o kilku ważnych rzeczach:

  •  Ze względu konstrukcję zastosowane algorytmy, kompresją obciążone są wszystkie operacje DML;

  •  strony w postaci skompresowanej są przechowywane również w pamięci RAM, a nie tylko na dysku twardym (czyli dostęp do bufora danych również obciąża procesory), ale dzięki temu więcej danych jest się w stanie zmieścić w buforze danych;

  •  mechanizm kompresji jest uruchamiany dla stron pełnych lub prawie pełnych;

  •  mechanizm kompresji jest uruchamiany dla danej strony, jeśli „only if the space savings is greater than an internally defined threshold” (cytat pochodzi z bloga zespołu odpowiedzialnego za mechanizm składowania danych w SQL Server). takie postępowanie może wydawać się słuszne, ale można sobie również wyobrazić że użytkownik ma prawo definiować granicę kompresji dla stron , a wtedy mechanizm kompresji byłby jeszcze bardziej elastyczny;

  •  w przypadku kompresji indeksów, kompresja może być stron stosowana tylko na liściach drzewa, natomiast kompresja wierszy może występować zarówno na liściach jak i węzłach nie będących liśćmi (ang. non-leaf levels);

  •  kompresja umożliwia przechowywanie większej liczby wierszy na stronie, ale nie zwiększa maksymalnego rozmiaru wiersza;

  •  jeśli tabela zawiera wiersze, dla których wiersz i narzut związany z kompresją zajmie więcej niż 8060 bajtów, to wówczas na takiej tabeli nie da się włączyć kompresji;

  •  jeśli kompresja została uruchomiona na stercie (ang. heap), czyli tablicy nie posiadającej indeksu zgrupownego, to wówczas nowe strony alokowane na potrzeby tej tabeli będą miały aktywny mechanizm kompresji tylko wówczas, gdy:

     •  Użyto operacji BULK INSERT;

     •  Użyto operacj iINSERT INTO z opcją TABLOCK;

     •  Tabela została przebudowana opcją rebuild lub stworzeniem i usunięciem indeksu zgrupowanego.

 Do początku strony

Kompresja danych – podsumowanie

Jak przedstawiono powyżej nowe mechanizmy zarządzania danymi , które użytkownicy będą mogli wykorzystać w SQL Server 2008 są bardzo ciekawe i elastyczne. Podsumowując kompresję danych zaimplementowaną w SQL Server 2008, zostaną wymienione zalety i wady rozwiązania.

Wady:

  •  Dość duży narzut na operacje DML, w niektórych testach wstawiania danych do tabeli skompresowanej trwa kilkukrotnie dłużej niż do tabeli nieskompresowanej; źródła powiązane z firmą Microsoft twierdzą, że wraz z wersja RTM ma zostać to znacząco poprawione;

  •  Dostęp do bufora stron również jest obciążony koniecznością dekompresji strony;

Zalety:

  •  Serwer bazy danych może umieścić więcej stron w buforze stron, przy tej samej ilości pamięci operacyjnej;

  •  Obserwowany współczynnik kompresji na bazach „Klienckich” wynosi od 2 do 6 – w zależności od tego jak bazy były projektowane;

  •  Zauważalne jest przyspieszenie przy dużych operacjach na dysku – jak np. operacje skanowania tabeli (ang. table scan ). Testy pokazują przyspieszenie rzędu przynajmniej podobnego jak współczynniki liczby stron do odczytania;

  •  Łatwość implementacji, dobre narzędzia, dobra dokumentacja.

Uwagi inne:

  •  Kompresja stron jest bardziej obciążająca dla procesora niż kompresja wierszy;

  •  Kompresja stron lepiej kompresuje dane niż kompresja wierszy;

  •  Poziom kompresji tabeli (na której jest założony indeks zgrupowany) jest zależny od rozkładu danych.

  •  Funkcjonalność ta występuje tylko w edycji Enterprise i Developer serwera SQL 2008;

  •  Rodzaj kompresji oraz obiekty jej podlegające zawsze powinny być dobierane po indywidualnej analizie.

---

Marcin Goł Architekt baz danych, ISCG Aktualnie pracuje w ISCG, gdzie projektuje i optymalizuje bazy danych; w swojej pracy głównie wykorzystuje technologie Microsoft SQL Server. Wcześniej brał udział w wielu wdrożeniach systemów OSS dla sektora telekomunikacyjnego oraz pracował jako administrator środowiska opartego o technologię Microsoft. Lider Polish SQL Server User Group (PLSSUG); aktywny użytkownik portalu WSS.pl, prowadzi blog SQL Server. Specjalizuje się w analizie i rozwiązywaniu problemów wydajnościowych. W styczniu 2009 został nagrodzony tytułem Most Valuable Professional w kategorii SQL, ponadto posiada certyfikaty firmy Microsoft, min.: MCITP: Database Administrator.

Do początku strony

---