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:

USE [WideWorldImportersDW]
GO

CREATE TABLE dbo.FactSaleCCI(
	[Sale Key] [bigint] NOT NULL,
	[City Key] [int] NOT NULL,
	[Customer Key] [int] NOT NULL,
	[Bill To Customer Key] [int] NOT NULL,
	[Stock Item Key] [int] NOT NULL,
	[Invoice Date Key] [date] NOT NULL,
	[Delivery Date Key] [date] NULL,
	[Salesperson Key] [int] NOT NULL,
	[WWI Invoice ID] [int] NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Package] [nvarchar](50) NOT NULL,
	[Quantity] [int] NOT NULL,
	[Unit Price] [decimal](18, 2) NOT NULL,
	[Tax Rate] [decimal](18, 3) NOT NULL,
	[Total Excluding Tax] [decimal](18, 2) NOT NULL,
	[Tax Amount] [decimal](18, 2) NOT NULL,
	[Profit] [decimal](18, 2) NOT NULL,
	[Total Including Tax] [decimal](18, 2) NOT NULL,
	[Total Dry Items] [int] NOT NULL,
	[Total Chiller Items] [int] NOT NULL,
	[Lineage Key] [int] NOT NULL
)
GO

SET NOCOUNT ON
GO

INSERT INTO dbo.FactSaleCCI WITH (TABLOCK)
SELECT * FROM Fact.Sale
GO 50

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

SELECT COUNT(*) FROM dbo.FactSaleCCI

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

sp_spaceused 'dbo.FactSaleCCI'

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:

CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FactSaleCCI ON dbo.FactSaleCCI

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ć:

ALTER TABLE dbo.FactSaleCCI  
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;

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.

ALTER TABLE dbo.FactSaleCCI  
REBUILD PARTITION = ALL WITH (MAXDOP=1,DATA_COMPRESSION =  COLUMNSTORE) ;

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:

SELECT i.object_id,
       OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       i.index_id,
       i.type_desc,
       CSRG.row_group_ID,
       CSRG.delta_store_hobt_id,
       CSRG.state_description,
       CSRG.total_rows,
       CSRG.size_in_bytes
FROM sys.indexes AS i
     JOIN sys.column_store_row_groups AS CSRG ON i.object_id = CSRG.object_id
                                                 AND i.index_id = CSRG.index_id
WHERE OBJECT_NAME(i.object_id) = 'FactSaleCCI'
ORDER BY OBJECT_NAME(i.object_id),
         i.name,
         row_group_id;

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ę):

SELECT [City Key],
       SUM(Quantity) AS Qty
FROM dbo.FactSaleCCI
GROUP BY [City Key];

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

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

SELECT [Invoice Date Key],
       [City Key],
       [Stock Item Key],
       SUM(Quantity) AS Qty,
       SUM(SUM(Quantity)) OVER(PARTITION BY [City Key],
                                       [Stock Item Key] ORDER BY [Invoice Date Key]) AS RunningTotal
FROM dbo.FactSaleCCI
GROUP BY [Invoice Date Key],
         [City Key],
         [Stock Item Key];

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:

ALTER TABLE dbo.FactSaleCCI  
REBUILD PARTITION = ALL WITH (MAXDOP=1,DATA_COMPRESSION =  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.