Columnstore Index – mechanizmy kompresji

Indeksy kolumnowe to jeden z kilku moich ulubionych tematów związanych z SQL Server. Na przestrzeni kilku ostatnich wersji SQL Server zmieniły się one nie do poznania i wywarły bardzo duży wpływ na to jak z technicznego punktu widzenia budujemy hurtownie danych. Ich premiera odbyła się wraz z Denali, czyli SQL Server 2012 gdzie były pierwszą implementacją pomysłu kolumnowego składowania i przetwarzania danych. Pierwsze ich wydanie było bardzo mocno ograniczone przez co liczba scenariuszy w jakich mogliśmy ich użyć drastycznie spadała. Miałem możliwość implementacji indeksów kolumnowych i przedstawienia prezentacji na ich temat jednakże zauważyłem, że na niniejszym blogu jest bardzo mało treści z nimi związanych! Postanowiłem to zmienić i niniejszy wpis będzie traktował o kompresji zawartej w Columnstore (bez względu na to czy są to indeksy zgrupowane czy też nie gdyż algorytmy o których mowa są wykorzystywane dokładnie w ten sam sposób w obu implementacjach) i jej wpływie na wydajność pobierania danych z dysku- zapraszam do lektury.Oczywiście na samym początku zainteresowanych tematem indeksów kolumnowych odsyłam do Niko Neugebauera, który w tym temacie napisał już ponad 100 artykułów – również o kompresji. My jednakże do tematu podejdziemy nieco inaczej i wykonamy własne testy.

W ramach indeksów kolumnowych możliwe jest do zaimplementowania kilka uzupełniających się algorytmów kompresji:

  • Value Scale
  • Bit-Array
  • Run-length compression
  • Dictionary Encoding
  • Kodowanie huffmana (Huffman encoding możecie poczytać o nim tutaj)
  • Binary compression (kompresja xVelocity, której algorytm nie jest znany)

Pierwsze cztery z nich można wyjaśnić w miarę kompaktowo i to też uczyniłem poniżej. Oczywiście poniższy opis jest  jedynie poglądowy i w rzeczywistości opiera się na bardziej zaawansowanych algorytmach próbkowania i dobierania danych.

Value Scale to nic innego jak zmiana skali liczb i przykładowo poniżej widzimy jak liczby zostały przeskalowane do mniejszych zakresów dla skali bazowej 1000:

Bit array – mapa bitowa  przyporządkowująca bity do konkretnych wartości. Oczywiście jest ona tworzona wtedy gdy ilość unikalnych wierszy w kolumnie jest stosunkowo mała.

Run length compression – polega na tym,że dane są sortowane po jakiejś kolumnie i duplikaty przechowywane są jednokrotnie wraz z liczbą ich wystąpień. Oczywiście sortowanie po różnych kolumnach może dać różne rezultaty i zadaniem SQL Servera jest zdecydowanie na podstawie której kolumny będzie sortował. Jak możecie się domyślać ten algorytm sprawdza się do kolumn z dużą ilością duplikatów:

Dictionary encoding – wartość zamieniana jest na mniejszą wartość. Oryginalna wartość przechowywana jest w słowniku o rozmiarze do 16MB – jeśli limit ten będzie osiągnięty to zmniejszana jest liczba wierszy w row group na co trzeba uważać.

Wiemy jak wyglądają podstawowe algorytmy kompresji dlatego też przechodząc dalej stwórzmy sobie tabelę, na której będziemy wykonywać testy – będzie to kopia Fact.Sale z WideWorldImporters. Zwiększymy jej rozmiar 50 razy:

Tak więc mamy stertę z dosyć dużą ilością wierszy, sprawdźmy ile dokładnie mamy wierszy:

Aby mieć całość informacji sprawdźmy ile miejsca nasza tabela aktualnie zajmuje:

Nie jest ona bardzo duża jednakże ponad 14 milionów wierszy i ponad 3GB miejsca na dysku do naszych testów powinno w pełni wystarczyć. Na tej tabeli postawmy sobie zgrupowany Columnstore:

Na moim komputerze założenie takiego indeksu trwało około 96 sekund. W tym miejscu warto zauważyć, że tworząc indeks kolumnowy nasze dane są poddawane automatycznie kompresji w SQL Server nazwanej właśnie Columnstore na którą składa się wymienione wcześniej algorytmy. Na ten moment zobaczmy jak wygląda rozmiar naszej tabeli:

Całkiem dobry wynik! W porównaniu do tabeli nieskompresowanej zaoszczędziliśmy potężną ilość miejsca – teraz nasz zbiór danych zajmuje 180MB!

Ale to jeszcze nie wszystko co mamy do dyspozycji! W indeksach kolumnowych istnieje coś takiego jak kompresja COLUMNSTORE_ARCHIVE. W dokumentacji czytamy, że jest to bardzo wydajny algorytm kompresji, który sprawdzi się do przechowywania danych archiwalnych, które są odczytywane sporadycznie. Oparty jest on o zoptymalizowany algorytm LZ77 o nazwie Microsoft Xpress. Oczywiście bardziej agresywny mechanizm kompresji niesie ze sobą dodatkowy koszt jakim jest CPU. Sprawdźmy jego wydajność na naszym zbiorze testowym. Aby go włączyć wystarczy przebudować indeks wraz ze wskazaniem tego, że chcemy go użyć:

Przebudowa istniejącego indeksu kolumnowego w taki sposób zajęła 97 sekund czyli nieznacznie więcej w porównaniu do “zwykłego” indeksu. Zobaczmy teraz ile miejsca zajmują nasze dane:

Dane zajmują nieco ponad 100MB – całkiem imponujący wynik prawda? Bez dwóch zdań dane, które niejednokrotnie “leżą” na naszym serwerze hurtowni danych i są bardzo rzadko odpytywane powinny mieć założony ten indeks. Jeszcze w celu uzupełnienia tematu poniżej zestawiłem rozmiar danych po poddaniu naszej tabeli testowej metodom kompresji COLUMNSTORE oraz z wykorzystaniem tradycyjnych mechanizmów  takich kompresja stron i werszy (pisałem o nich dosyć niedawno tutaj):

Powyższe zestawienie tylko potwierdza fakt, że indeksy kolumnowe nie mają sobie równych jeśli chodzi o kompresję. Wiemy już, że założenie Columnstore daje nam fajne rezultaty jeśli chodzi o miejsce zajmowane przez dane, ale jaki ma on wpływ na wydajność zapytań?

Przebudujmy nasz indeks do standardowej kompresji kolumnowej używając jednego wątku. Dzięki temu, że używamy pojedynczego wątku powinniśmy otrzymać maksymalny rozmiar grupy wierszy, co z kolei powoduje, że dane kompresja powinna być najlepsza, a co za tym idzie wydajność powinna być dobra.

Oczywiście operacja taka jest czasochłonna iw moim przypadku wyniosła 5 minut i 35 sekund ale dzięki temu otrzymałem bardzo dobrze rozłożone grupy wierszy.Chciałbym również zaznaczyć, że w niniejszym teście nie martwimy się o dobre ułożenie wierszy tak aby zapewnić eliminację segmentów (temu poświęcę osobny artykuł) i będziemy działać na wszystkich danych w tabeli.

Sprawdźmy jak wyglądają wspomniane grupy wierszy:

Rezultat powyższego zapytania prezentuje się następująco:

Widzimy, że prawie wszystkie grupy otrzymały maksymalną możliwą liczbę wierszy (1048576), jedynie ostatnia grupa wierszy otrzymała mniej ze względu na fakt, iż po prostu w tabeli nie ma więcej wierszy aby ją “dopełnić”. Odpytajmy naszą tabelę i sprawdźmy jak wiele czasu zajmuje zwrócenie rezultatu. Pierwsze zapytanie to będzie prosta suma po kluczu miasta (w tym miejscu zaznaczam, że w każdym moim teście czyszczę cache nawet gdy o tym nie piszę):

Rezultat został zwrócony 965ms i1329ms czasu procesora (wykorzystane dwa wątki procesora).

Drugie zapytanie testowe to agregat + agregat ruchomy po 3 kolumnach:

Zapytanie wykonało się w 6483ms i 4969ms czasu procesora (znów zaangażowane były dwa wątki). Mamy już wyniki dla kompresji COLUMNSTORE, sprawdźmy teraz te same zapytania dla COLUMNSTORE_ARCHIVE:

Całość trwała .. 5 minut i 30 sekund czyli bardzo zbliżona wartość do poprzedniej  funkcji kompresującej. Wynik działania naszych zapytań testowych przedstawia się następująco:

  • zapytanie 1 – 1214ms, 1813ms CPU
  • zapytanie 2 – 7097ms, 6016 CPU

Zbiorczo wykonanie powyższych zapytań w obu trybach wyglądają następująco:

Powtarzałem powyższy test kilkukrotnie dla różnych zapytań dla różnych zbiorów danych i za każdym razem wynik był zbliżony tzn. COLUMNSTORE_ARCHIVE zawsze wykonywał się od 5 do 20% wolniej. Oczywiście jak w przypadku każdego test tego typu wszystko zależy, od konkretnego zbioru danych i tego jak bardzo dane “dają się” kompresować. Mam jednak nadzieję, że niniejszy post da Wam pogląd na to jak oba mechanizmy działają. COLUMNSTORE_ARCHIVE daje gorsze rezultaty i nie ma możliwości aby było inaczej, jednakże nie jest to diametralna różnica i w momencie gdy z jakichkolwiek przyczyn nie macie miejsca na swoje dane lub wasze dane są używane rzadko/bardzo rzadko ale musicie je przechowywać warto rozważyć kompresję COLUMNSTORE_ARCHIVE szczególnie, że daje ona lepsze rezultaty niż tradycyjna kompresja stron/wierszy. Opcja ta może być również przydatna w momencie gdy mamy problemy z IO wtedy też możemy je zminimalizować i w ogólnym rozrachunku wyjść na plus.

 

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

Leave a Comment

Your email address will not be published. Required fields are marked *