Kompresja stron i wierszy a wstawianie i odczytywanie danych w SQL Server

SqlCompressionDelays_00

Kompresja w SQL Server istnieje już bardzo długo pod różnymi mechanizmami i funkcjonalnościami. Pierwsze co nam przychodzi na myśl jak mówimy o tym temacie to kompresja stron i wierszy, w dalszym planie pojawia się kompresja słownikowa dostępną w indeksach kolumnowych oraz kompresja funkcjami TSQL takimi jak COMPRESS. Oczywiście jeśli byśmy głębiej poszukali moglibyśmy dorzucić tutaj jeszcze kilka dodatkowych nazw. W powszechnie panującym przekonaniu mówi się, że kompresja jest czymś dobrym ponieważ zmniejsza miejsce na dysku potrzebne do przechowania danych, a co za tym idzie odczyty z dysku są szybsze, a to one są bardzo często wąskim gardłem naszej bazy danych. Jednakże wszystko ma swoje wady i zalety – nie inaczej jest w przypadku kompresji. Myśląc o tym mechanizmie obok zalet warto pamiętać o tym, że przede wszystkim zwiększa ona zużycie CPU oraz może mieć negatywny wpływ na czas wstawiania danych do tabeli. Ze względu na fakt, iż chcę do tematu podejść czysto praktycznie to przetestuję jaki wpływ ma kompresja stron i wierszy na odczyty i zapisy do tabeli – zapraszam do lektury!

Nasza testowa baza danych będzie miała prealokowane 1GB miejsca i dziennik transakcyjny o wielkości 260MB. Dodatkowo baza będzie w trybie SIMPLE tak abyśmy mogli użyć minimalnie logowanych operacji:

Stworzymy sobie trzy tabelę na podstawie Fact.Sale znajdującej się w WideWorldImportersDW:

Każda z tabel będzie stertą i w zamierzeniu każdą z nich poddamy innemu stopniu kompresji: od kompresji stron Stron, aż po brak kompresji:

Mając gotowe obiekty możemy rozpocząć nasz test, wstawimy dane poleceniem INSERT.. SELECT. Oczywiście przed każdą operacją wyczyścimy buffer pool z danych, które mogą się tam znajdować oraz wyczyścimy cache proceduralny aby uzyskać bardziej wiarygodne rezultaty. Jako wskaźnik wydajności posłuży nam przede wszystkim czas potrzebny na wykonanie konkretnej operacji. Informacyjnie dorzucimy również ilość stron odczytanych stron z dysku. Aby nie mieć wątpliwości co do równoległego przetwarzania każda operacja będzie wykonywana z MAXDOP 1 co potencjalnie wydłuży nam czas przetwarzania ale jednocześnie uprości otrzymany wynik. Tak więc zaczynajmy, na pierwszy ogień wrzucimy tabelę nie poddaną kompresji (dorzuciliśmy wskazówkę TABLOCK aby operacja wstawiania była minimalnie logowana):

Otrzymane rezultaty:

Mamy same odczyty LOB ponieważ tabela źródłowa ma założony Clustered Columnstore Index – interpretacją zajmiemy się później. Wykonajmy te same działania dla tabeli z kompresją wierszy oraz dla tabeli z kompresją stron:

Rezultat wstawienie danych z kompresją wierszy:

Test na tabeli z kompresją stron:

Rezultat testu wstawiania danych do tabeli z kompresją stron:

Przedstawmy teraz wyniki naszego testu. Pierwszą rzeczą na jaką warto zwrócić uwagę jest ilość odczytów w każdym z testów – były one we wszystkich trzech przypadkach dokładnie takie same tj:

  • lob logical reads:1770
  • lob physical reads: 129
  • lob read-ahead reads: 2013

Dzięki tej informacji możemy założyć, że odczyty z tabeli źródłowej nie wpłynęły na nasze testy i za każdym razem mieliśmy takie same warunki. Spójrzmy teraz na czasy wstawienia danych bo chyba ta informacja jest dla nas najbardziej interesująca:

To co otrzymaliśmy raczej nie jest zaskakujące – wstawienie danych do tabeli ze zdefiniowaną kompresją trwa dłużej, im wyższy stopień kompresji tym trwa to dłużej. Większość czasu jaki upływa związany jest z tym ile czasu procesor spędza na kompresowaniu wartości. Tutaj ciekawy może wydawać się pytanie czy wstawienie danych i późniejsza kompresja może zmienić sytuację: sprawdźmy to!  Wstawię dane do kolejnej tabeli, która będzie stertą by zaraz po wstawieniu całość poddać kompresji stron. W tym miejscu aby mieć pewność, że wszystkie operacje nie będą wykonywane równolegle ustawimy konfigurację bazy na MAXDOP 1 (ponieważ nie możemy użyć hinta przy ALTER TABLE):

Wynik tej części testu również nie jest dla nas zaskoczeniem ponieważ podejście z wstawianiem danych i późniejszą kompresją zajmuje jeszcze więcej czasu:

W takim razie możemy założyć na podstawie powyższych testów, że kompresja znacznie wydłuża czas potrzebny na wstawienie danych. Warto tutaj również zaznaczyć, że test powtórzyłem kilkukrotnie aby mieć pewność co do otrzymanych rezultatów. Oczywiście w praktyce jedne tabele kompresują się lepiej inne gorzej co również może mieć wpływ na czas wstawienie i odczytania danych jednakże ogólny trend jest zgodny z tym co przedstawiał powyższy test.

Skoro nasze tabele są już załadowane to sprawdźmy jak dużo miejsca zajmują – dowiemy się tego wykonując zapytanie na widokach systemowych, które dostarczą nam odpowiednich danych:

Jak możecie zauważyć różnica w rozmiarze pomiędzy tabelą bez kompresji, a tabelą z kompresją wierszy jest dwukrotna, a w porównaniu z kompresją stron aż czterokrotna! Rozmiar danych w tabeli(partycji) w konkretnej konfiguracji możemy sprawdzić używając procedury sp_estimate_data_compression_savings:

Dzięki tej procedurze możemy określić jak bardzo efektywny będzie dany mechanizm kompresji. Oczywiście pamiętajmy, że otrzymany rezultat bazuje tylko na próbce danych i nie będzie w 100% dokładny ale mimo wszystko warto zawsze sprawdzić potencjalne zyski.

Przejdźmy dalej i sprawdźmy jaki wpływ mają użyte algorytmy kompresji na wydajność zapytań – pierwszym zapytaniem jest prosta suma na wszystkich wierszach:

W rezultacie otrzymaliśmy następujące wyniki:

Kompresja stron była znacznie szybsza od konkurentów (chociaż kompresja wierszy nie jest konkurentem bo jak wiadomo kompresja stron zawiera w sobie kompresję wierszy)- ciekawe jest to, że kompresja wierszy nie dała jakiegoś dużego skoku wydajnościowego. Spróbujmy przetestować nasze tabele w jeszcze jednym trochę bardziej rozbudowanym zapytaniu:

Rezultat testu:

Rezultaty są już bardziej klarowne i widać wzrost wydajności przy odczytywaniu tabel skompresowanych w porównaniu do tabeli nie poddanej kompresji.

Oczywiście można kontynuować testy na wielu różnych zapytaniach ale ogólnie rzecz biorąc należy wysnuć wnioski, że kompresja ma pozytywny wpływ na odczyty natomiast wydłuża czas wstawiania danych. Co prawda w naszym teście wstawialiśmy jedną operację ponad 200 tysięcy jednakże podobne rezultaty otrzymamy wstawiając mniej wierszy wieloma odrębnymi sesjami. Jeżeli znacie swój workload to możecie sami sobie odpowiedzieć na pytanie czy kompresja będzie dla Was dobra czy też nie. W systemach OLTP gdzie operacje wstawiania danych nie są dominujące i kładziemy nacisk na odczyty to kompresja może dać nam wiele o ile czas potrzebny na dekompresje nie trwa zbyt długo – wszystko musimy testować. Pamiętajmy jednak o tym, że jeżeli mamy problemy z przeciążonym procesorem i dorzucimy mu jeszcze kompresję to możemy wpaść w poważne problemy z wydajnością całej bazy. W przypadku hurtowni danych obecnie prawie zawsze lepszym wyborem dla tabel faktów są indeksy kolumnowe, które posiadają swoje własne mechanizmy kompresji. Warto jednak rozważyć kompresję tabel wymiarów, szczególnie tych większych. Kwestią otwartą pozostaje kompresja tabel z obszaru przejściowego potrzebnych w czasie zasilania hurtowni – tam musimy wyważyć czy zysk przy odczytach równoważy stratę przy zapisie. Mam nadzieję, że ten wpis będzie dla Was przydatny i pokaże wpływ tradycyjnych metod kompresji na operacje wstawiania i odczytu danych.

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 *